Totaling and subtotaling columns

This example demonstrates:

•How to set up a trigger Item in the QuickBooks Item list

•How to total a column

•How to subtotal a column

How to Total a Column

Totaling columns is a common need, and the most common use for Item-triggered formulas. Item-triggered formulas are those which FormCalc SST applies only when a specific Item name is encountered—the Item name you have entered on the Item-triggered formula's row in FormCalc SST.

Items which "trigger" Item-triggered formulas are often referred to as trigger Items.

Totaling a column with FormCalc SST involves (1) setting up a trigger Item in QuickBooks, (2) using that Item name on QuickBooks forms where you want the total to appear, and (3) associating the Item name with an Item-triggered formula row which calculates the total.

Setting up a trigger Item

Trigger Items are usually QuickBooks Items you have set up specifically for the purpose of triggering FormCalc SST calculations.

This isn't always true. See Tips for Trigger Items in the Item-triggered formulas topic.

For this example we will set up a Service-type Item named Total.

1.Open the Item list by choosing Lists > Item list from the QuickBooks main menu.

2.Choose New from the Item button's menu at the bottom of the Item list window.

3.Fill the New Item window's fields as shown here.

•The Item name and Description can be anything you want.

•The Account this Item posts to is an equity-type account set up specifically for working with FormCalc SST trigger Items, called FormCalc Item Posting. We could have posted to an income or expense account, but using an equity account dedicated to this purpose makes spotting errors easier (unintentionally posting income or expense, etc.). For an explanation, see Tips for Trigger Items in the Item-triggered formulas topic.

Using the trigger Item on QuickBooks forms

The location of the Total Item will determine where FormCalc SST calculates a total. Generally you will want to enter it as the last line in the form's Detail section, like this:

The total won't be calculated, of course, until FormCalc SST processes the form.

Setting up formulas in FormCalc SST

1.Take a snapshot of the QuickBooks form.

2.Choose a column type for each column in the snapshot by right-clicking in each column on the Column types row, then selecting the column type from the pop-up menu, as shown here for the Amount column:

3.Enter a formula to total the desired column.

"Wait a minute! This formula appears to sum cell F1. Is that really what will happen?" No. When entering formulas you can refer to the whole column of data without using exact cell coordinates. FormCalc SST will adjust your formula to the required coordinates immediately after you've entered it, as described below. (For details, see "Close enough" cell referencing in the Formulas and cell references topic.)

4.Enter the trigger Item's name (Total, in this case) in the Item column of the same Item-triggered formulas row where you entered the formula.

 The item name is what will connect the formula with a particular row of data on your QuickBooks forms.

 Here's a view of the Item-triggered formulas 1 row with the formula and item name in place:

5.Click on the Save button to save the file.

FormCalc SST files are identifiable by their .SST filename extension (.SST in prior versions).

Processing QuickBooks invoices

To process an Invoice in QuickBooks:

1.Switch to QuickBooks, and open or click on the Invoice you want to process.

2.Press the FormCalc SST hotkey, which is F11 unless you have changed it in Preferences.

3.Process the form by either (1) pressing the hotkey a second time, or (2) clicking on the Process the QuickBooks form button.

How to Subtotal Columns

The simplest way to subtotal columns on QuickBooks forms is to use the same trigger Item name in several places on the form. Let's prove this by making minor changes to the Item name and FormCalc SST setup, then processing an Invoice.

You can also calculate subtotals using different Item names. That approach may be necessary when additional calculations are needed on the subtotal rows, and those calculations need to be different for each Item. This is a more advanced approach: the rows included in each subtotal may have to be controlled by using IF() functions in the formulas.

Changing the Item name

The Total Item we set up earlier would work for subtotals too, as is. But having the word "Total" appear on several rows of an Invoice might be confusing to someone else looking at the Invoice. So let's change the Item name to Subtotal:

1.Open the Item list by choosing Lists > Item list from the QuickBooks main menu.

2.Select the Total item by clicking on it.

3.Choose Edit Item from the Item button's menu at the bottom of the Item list window.

4.Change the Item's Name and Description from Total to Subtotal:

5.Click OK to close the window.

Updating the FormCalc SST file

We have not changed the layout of the QuickBooks Invoice, so we don't need to take a new snapshot. We only need to update the Item-triggered formulas 1 row with the Item's new name, Subtotal.

Processing QuickBooks forms...with subtotals

The steps for processing forms is the same as describe above. We've added more data rows to the example Invoice, to better demonstrate how subtotals work.

Before processing:

After processing with FormCalc SST (note the subtotals):