This example demonstrates:
•How to add a blank column to a QuickBooks form
•How to calculate a running total in that column
•Using anchor rows in FormCalc SST formulas
How to Calculate a Running Total
EverStone Products sells decorative and architectural stone throughout the Midwest. Most is shipped on pallets on the company's own trucks. The office staff has customized the Packing Slip template for QuickBooks' invoices to serve as a Bill of Lading. They apply the customized template to completed invoices, then print off the Bills of Lading to send down to the crew in the load-out yard.
When loading a truck it would be useful to have a running count of the pallets to be loaded, to help verify the palette count of each product as loading proceeds. EverStone's manager would like a running total column added to the Bill of Lading.
Customizing the Bill of Lading (Packing Slip) template
Add a column to the Bill of Lading template to hold the running total.
1.Open the Invoice form.
2.Apply the Bill of Lading (or Packing Slip) template to the Invoice if it isn't applied already, by selecting it in the Template field.
3.Click on Formatting > Customize Data Layout in the form's menu/ribbon bar.
| The Additional Customization window will open. |
4.Click on the Columns tab.
| We need an unused field we can add to the form to hold the running total. Other 1 and Other 2 are spare fields available for any purpose, so let's use Other 1. |
5.On the Other 1 line, checkmark the box in the Screen column, change the field's title to Run.Total, and enter 4 in the Order column, which will place the field just to the right of the Quantity column, which is column 3.
 | Be sure the Message field is selected on the Footer tab, also, even if you don't need it. The Message field is required by FormCalc SST. |
6.Click OK to close the Additional Customization window.
| Here's how the modified Bill of Lading template should look when applied to an Invoice. Notice the Run.Total field: |
 | For more customization details, see Customizing QuickBooks forms for FormCalc SST. |
Taking a snapshot and setting up the formula
Next, FormCalc SST needs to take a snapshot of the Invoice with the Bill of Lading template applied.
1.In FormCalc SST, choose File > New from the main menu to begin a new file.
| This step is not required, but it assures you won't overwrite an existing FormCalc SST file. |
2.Click the Action button in the main toolbar.
| The focus will switch to QuickBooks, and the Choose an action dialog will appear: |
3.Click on the New Snapshot button.
| FormCalc SST will take a few seconds to gather information about the form, then display a representation of the form on the Snapshot tab. |
4.Indicate the column type of the Item column and of the Run.Total column, at least.
| You must always indicate which is the Item column. Beyond that, you must identify the column type for all column which will have formulas—in this case, just the Run.Total column. |
| Here's a partial view of the snapshot with column types of the Item column and a couple others already selected, and the Run.Total column's pop-up (right-click) menu displayed on the Column types row—Run.Total's column type is about to be selected as Other. |
5.Display the Formulas row's anchor row by clicking on the [ A ] button on the toolbar and selecting Formulas anchor row.
| The anchor row should now be visible immediately above the Formulas row: |
6.Enter the formula for the running total in the Run.Total column.
| The formula is pretty simple: =F16+G15 |
| It adds the value in the Quantity column on the current row (F16), to the value of the Run.Total column from the prior row—represented by a reference to the anchor row cell G15. (When FormCalc SST actually processes QuickBooks forms, it will copy this formula for each row of data on the form.) |
| Formula references to an anchor row are always relative, meaning they always refer to the "prior" row of QuickBooks form data. |
7.Click on the Save As button and supply a filename, to save the FormCalc SST file.
| FormCalc SST files are identifiable by their .SSF filename extension (.SST in prior versions). |
| FormCalc SST is now ready to use for calculating running totals on the Bill of Lading template. |
Processing QuickBooks forms
To process an Invoice in QuickBooks:
1.Be sure the Bill of Lading template is applied to the Invoice.
| The FormCalc SST file is specific to the layout of that template. |
2.Press the FormCalc SST hotkey, which is F11 unless you have changed it in Preferences.
| The Choose an action dialog will appear: |
3.Process the form by either (1) pressing the hotkey a second time, or (2) clicking on the Process the QuickBooks form button.
| Here's a view of the form after processing. The numbers in the Run.Total column were calculated by FormCalc SST. |
