This example demonstrates:
| ® | Setting up a Products and Services item to provide the text that will trigger a calculation |
| ® | Entering a formula to total a column |
| ® | Connecting the item to the formula in a FullSpeed macro |
 | This technique works on any QuickBooks Online form which has a numeric column such as the quantity (Qty) column: invoices, purchase orders, sales receipts, etc. |
This example provided in this topic shows how to total the quantity (Qty) column on a QuickBooks Online 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 (QuickBooks Online in this case).
Totaling a column mostly involves placing a Products and Services item on the last line of the invoice or other form, and entering that item's name in the macro worksheet as the text which will trigger calculation of the column total.
| 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
Set up a Products and Services item
Instead of using existing Products and Services 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 Price/Rate is set to 0) and it lets the item provide any descriptive information you may want.
Here's a new item, TotalQty, added to use in totaling the quantity column:
Notes:
| ® | The item Name and Description can be anything you want. |
| ® | The Income Account you select doesn't matter much because the Price/Rate is set to 0, so no income or expense should be recorded. |
Use the Item on a 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 Products/Services column—because it contains the trigger item, TotalQty.
| We have entered column names on the Column or field names row. Though optional, that makes it easier to remember how the macro worksheet's columns relate to the Xero invoice. |
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 automatically 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 Product/Service 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 (QuickBooks Online) and the repeat type (Run until "X" blank fields). |
| ® | Assign a hotkey by clicking on the hotkey button. Shift+Ctrl+3 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 QuickBooks Online. |
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+3 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.
