This example demonstrates:
| ® | Setting up an Inventory Item to provide text that will trigger a calculation |
| ® | Entering a formula to total a column |
| ® | Connecting the Inventory Item to the formula in a FullSpeed macro |
 | This technique works on any Xero form which has a quantity column—invoices, bills, purchase orders, etc. |
This example shows how to total the quantity (Qty) column on a Xero invoice or other form. Totaling columns is a common need and may be the most common use for text-triggered formulas—formulas triggered by the appearance of specific text in the target application (Xero in this case).
Totaling a column mostly involves placing an Inventory Item on the last line of the invoice (or other Xero form), and entering that item's name in the macro worksheet as the text which will trigger the column total calculation.
| Text which "triggers" a calculation is often referred to as trigger text. In Xero the text is usually provided by an Inventory Item, which can be referred to as a trigger item. |
Getting Ready
Instead of using existing Inventory Items to trigger a FullSpeed calculation, usually you will set up a new item 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 a new Inventory Item, TotalQty, added to use in totaling the quantity column:
Notes:
| ® | The Item Code / Name and Description can be anything you want. |
| ® | No Accounts nor Tax Rates were 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 Xero form
Select the TotalQty item on the last line of a form—assuming you want to total all the lines above it.
Enter a formula for totaling the Qty column
Enter the formula on a text-triggered formula row. In which column? 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 to access the form's first column—the Item column—because it contains the trigger item, TotalQty.
| Entering column names on the Column or field names row is optional, but that makes it easier to remember how the worksheet's columns relate to the invoice's columns. |
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 actually 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. |
Enter the formula's trigger text
Enter the trigger text on the same worksheet row as the formula. That way, when FullSpeed encounters the trigger text in Xero it will calculate the formula.
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 on the hotkey button. Shift+Ctrl+1 is assigned in this example. |
| Remember to save the FullSpeed file, 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.
Why the Qty column? That's what we selected as the Begin field on the macro worksheet.
| 2. | Press the hotkey assigned to the macro, Shift+Ctrl+1 in this example. |
FullSpeed will traverse the form, gathering data. When it reaches the TotalQty row it will write the total in the Qty field.
