This example demonstrates:
| ® | Setting up an Inventory Item to provide the text that will trigger a subtotal calculation |
| ® | Using the item on a Xero form, to control where subtotals occur |
| ® | Connecting the Inventory Item to a subtotal formula in a FullSpeed macro |
| ® | Entering a formula to subtotal a column |
| ® | How to have column subtotals and totals |
Required reading... This discussion assumes you've read the Column subtotals and totals (QBOL) topic, so please read that topic first if you have not done so already. |
Column Subtotals with FullSpeed
As this is written, Xero does not have the ability to provide subtotals on data entry forms (invoices, purchase orders, etc.). A FullSpeed macro can give you subtotals, and they work almost identically to totals, as described in the Column total (Xero) topic.
This example shows how to subtotal the quantity column on a Xero form.
Getting Ready
Instead of using existing Inventory Items to trigger FullSpeed calculations, usually you will set up new items specifically for that purpose. This assures the item won't record income unless you want it to (if the Unit Price is set to 0) and it lets the item provide any descriptive information you may want.
Here's an Inventory Item, SubtotQty, added for subtotaling the quantity column.
Notes:
| ® | The Item Code / Name and Description can be anything you want. |
| ® | We've left the Description blank, for less clutter on Xero forms. But you can enter a description if you want. |
| ® | No Accounts or Tax Rates are selected, because this item should not post any income or expense. If you select accounts that's OK though, because with the Unit Price set to 0, no income or expense should ever be recorded. |
Use the item on a form
Select the SubtotQty item on the line immediately below each group of lines you want to subtotal.
Enter a formula for subtotaling the Qty column, and its trigger text
| 1. | Enter the formula on a text-triggered formula row. |
The Qty column is the third column on the form (see the screenshot above), so put the formula in at least the third column of the macro worksheet. You need to leave room for form's first column—the Item column—because it will need to contain the trigger text, SubtotQty.
Notice that this formula actually sums the Sandbox rows of the Qty column. That is a shorthand way of telling FullSpeed to sum the entire Qty column when the macro runs.
 | The easy way to enter the formula would be as: =SUM(E0:E0). FullSpeed converts row 0 references to Sandbox row references, so you don't need to know what they are. |
| 2. | Enter the trigger text (SubtotQty) on the same row. |
That way, when FullSpeed encounters SubtotQty in Xero it will calculate the formula.
| Though optional, entering column names on the Column or field names row makes it easier to remember how the worksheet's columns relate to the invoice. |
Choose macro options
Choose appropriate options for running the macro:
| ® | Mark the Qty column as the Begin field. (Any column would do; it's a matter of choice.) |
| ® | Mark the Item column as the Match column. That tells FullSpeed where to look for the trigger text. |
| ® | Use macro options toolbar settings as shown below—especially the target application (Xero) and the repeat type (Run until "X" blank fields). |
| ® | Assign a hotkey by clicking the hotkey button. Shift+Ctrl+1 is assigned in this example. |
| Remember to save the FullSpeed file periodically, to avoid losing your work! |
Running the Macro
| 1. | Click on the first cell in the Qty column in Xero. |
Why the first cell? Because FullSpeed macros process a form downward from where the macro is invoked (run). To total the entire Qty column we must begin at the top of the column.
Why the Qty column? Because that's what we selected as the Begin field in the macro worksheet.
| 2. | Press the hotkey assigned to the macro, Shift+Ctrl+1 in this example. |
FullSpeed will traverse the form, gathering data. As it reaches each SubtotQty row it will write a subtotal in the Qty field.
Can I have Subtotals and Totals?
Yes! You can nest any number of subtotal and total levels on your forms. Just use a different trigger item for each level.
Here's the same example as the above, but with a TotalQty item (from the Column totals (Xero) example) added to the form (note the green arrow):
And here is the macro worksheet with the TotalQty item name and formula added on a separate text-triggered formula row:
Notes:
| ® | The TotalQty formula sums the same range of cells as the SubtotQty formula. Why? Because there's no way in FullSpeed for a total's formula to refer to individual subtotal rows. The only difference in how these two formulas work is caused by the fact that the SubtotQty item appears more than once on the invoice. Each time it appears, FullSpeed resets the subtotal to 0 before processing more rows. |
Finally, here's the result of a macro run with both subtotals and a total:
Can I Subtotal the Amount Column?
Yes. The technique is a bit different and is discussed in the next topic, Subtotaling the Amount column (Xero).