This example demonstrates:
•Using an "indicator column" to control calculations
•Using text functions to include a discount amount and deadline date in a customer message
•Referencing a header field (Invoice date) in formulas
•Using trigger Items to control where calculations occur
 | This is a continuation from Part 1. Refer to Part 1 for a background on the reasons why Jerry Swartz, manager of Skyview Farm Service, wants an "available discount" customer message on the company's invoices. |
Limiting the Discount to Specific Items
Overview
continued from Part 1...
We've learned that Jerry, the manager at Skyview Farm Service, now wants the available discount to apply only to specific Item lines on the Invoice—only to Items representing fertilizer products. Adding that capability will require a few simple steps. You will need to:
•Add a custom field to the Items list to indicate which Items are discountable, and include that field on Skyview's Invoices. We'll call that field an "indicator column", because of the job it does.
•Add another column to Skyview's Invoices for holding the dollar amount of discountable Items. It will contain the results of a formula which multiplies the indicator column by the Amount column.
•Take a new snapshot of the Invoice with FormCalc SST, because the Invoice layout has changed.
•Add formulas to the snapshot. They will be only slightly different from those in Part 1.
The result will be a customer message at the bottom of invoices, which displays an available discount amount and the discount deadline date, like this:
*** DISCOUNT of $123.45 if paid by 11/14/2013 ***
QuickBooks Setup
Adding a custom field in the Items list, and storing data in it
 | Only the main steps are shown here. For more detail see the Defining and using custom fields topic. |
1.Define a new a custom field in the Items list, and name it Discountable.
2.Edit each of the fertilizer Items in the Items list, and enter a "1" in the Discountable field (in the Custom Fields window).
| Only do this for discountable Items; in this case, fertilizer Items. |
| Why not use an "x" or some other character? Because using a "1" will keep the FormCalc SST formulas simple. |
Adding columns to the Invoice
1.Click on the command to customize the Invoice form's layout.
| The Additional Customization window will open. |
2.On the Columns tab of the Additional Customization window, checkmark boxes in the Screen column for the Discountable field, and the Other 1 field.
•Discountable is the custom field added in the steps above.
•Other 1 and Other 2 are spare fields you may use for any purpose. In this case you will use one of them (either will do) to hold calculated results from a FormCalc SST formula.
3.You can also change the fields' display names if you want, in the Title column, and change their order by renumbering the Order column.
| In the screenshot above, Discountable has been re-titled as Dsctbl (so it will need less column width on the Invoice), and Other 1 has been renamed Discnt$ because it will hold discount dollar amounts. |
| Here's a partial view of the Invoice after customization, showing the added columns: |
FormCalc SST Setup
1.Take a new snapshot of the Invoice.
| FormCalc SST will gather data about the Invoice form and build a new spreadsheet based on it, in the Snapshot tab. |
| The snapshot taken in Part 1 cannot be reused, because the Invoice form's layout has changed (columns were added). |
2.Assign column types on the Column types row and column labels on the next row.
| When you are done the two rows should look something like this: |
3.Add the following formula in the Dscnt$ cell on the Formulas row.
=H20*J20
| This formula multiplies the Dsctbl column (H) by the Amount column (J). Since Dsctbl will contains a "1" for discountable items and nothing (equivalent to "0") for non-discountable Items, Dscnt$ will end up displaying amounts for only the discountable Items. |
4.Enter the following formula in the Description column on the Item-triggered formulas 1 row:
="*** DISCOUNT of "&DOLLAR(SUM(I26:I28)*0.02,2)&" if paid by: "&TEXTD(B6)&" ***"
| This formula does basically the same thing as the formula from Part 1 (see the formula breakdown in Part 1 for details). But this time it sums the Discnt$ column (I), so that the discount is calculated only on the value of fertilizer Items in the Invoice, not on the Invoice total. |
| Here's a view of the snapshot with the formula in place: |
Processing an Invoice
Here's the result of processing an Invoice in QuickBooks using this new FormCalc SST setup:
Do you see the problem?
The old customer message (from Part 1) wasn't fully erased before FormCalc SST wrote the new one to the Invoice, with this being the result:
*** DISCOUNT of $108.29 if paid by:
*** DISCOUNT of $91.50 if paid by: 4/2/2019 ***4/12/2019 ***
This won't do! It's alerting us to the fact that if an Invoice gets processed a second time, the customer message won't be displayed properly.
A simple fix
Formatting the Description column's results differently in FormCalc SST will fix the problem
1.Right-click on the Description column's cell on the Item-triggered formulas 1 row, and select the Format item from the pop-up menu.
| The Format data or results window will open. |
2.In the right pane of the window, checkmark the Results replace Description option.
| This tells FormCalc SST to erase the contents of the Description column before writing results there. |
3.Click OK to close the window.
Processing an Invoice Again...Success!
And here's the result of processing the Invoice again. As you can see, the customer message now correctly replaces the prior one.
Related topics: