This example demonstrates:
| ® | Setting up an Inventory Item in Xero |
| ® | Placing the item on a Xero form to control where subtotals are triggered |
| ® | Duplicating Xero's Amount column calculations with a Scratchpad formula |
| ® | Entering a formula to calculate subtotals |
| ® | Connecting an Inventory Item to the subtotal formula |
Required reading... This discussion assumes you've read the Column subtotals and totals (Xero) topic, so please read that topic first if you have not done so already. |
Prior topics showed how to subtotal a column of numbers—the Qty (quantity) column. This example deals specifically with getting subtotals in the Amount column.
Unlike some programs, in Xero the Amount column is not accessible to FullSpeed. This may change in the future, but for now subtotaling financial amounts in Xero is not a straightforward task—it's simple to do, just not straightforward.
Mainly, your macro must duplicate Xero's Amount column calculations. The only problem with this approach is that Xero and FullSpeed may not always round financial amounts the same way. Though rare, if this happens it could cause FullSpeed's subtotals and Xero's total for the form to differ by a cent or two.
 | Subtotal rounding errors should be very rare, and should never happen if you sell things in whole-number quantities, at dollar-and-cent Unit Prices (no fractional quantities or fractional-cent prices). Also, subtotals calculated this way are completely safe to use from an accounting standpoint, because they are merely "descriptive": they do not in any way affect the amounts Xero posts to your accounts. |
The risk of a subtotal rounding error may or may not be acceptable to you. The benefits of having financial subtotals may outweigh the small risk of rounding errors in several situations:
| ® | If you use draft invoices as a way to prepare job estimates, having a subtotal for estimated labor charges or for different types of materials may be useful to the estimating process. |
| ® | On purchase orders it may be helpful to have subtotals for groups of different kinds of items you are purchasing. |
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 a new Inventory Item, Subtotal, added for the purpose of triggering a subtotal calculation in a FullSpeed macro:
Notes:
| ® | The Item Code / Name and Description can be anything you want. |
| ® | We've left the Description blank for less clutter on the Xero form, 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 Xero form
Select the Subtotal item on the line immediately below each group of lines you want to subtotal.
Label columns in FullSpeed
In FullSpeed, add column labels to the macro worksheet. This step is optional but will help you remember how the macro worksheet relates to the Xero form.
Enter a formula to duplicate Xero's Amount column calculations
Scroll to the right until you reach the first Scratchpad column, then enter a formula to multiply the Qty column by the Unit Price column, and round the result to 2 decimal places:
=ROUND(E5*F5,2)
Let's break down the formula to see how it works:
= |
Every formula begins with an equal sign (=). |
E5*F5 |
Multiply the Qty by the Unit Price. |
ROUND( ,2) |
Round a number to 2 decimal places. |
Here's the macro worksheet after entering the formula and adding some sample data, so we can see if the formula works:
Notes:
| ® | Your Scratchpad column will likely be much farther to the right than the one shown here. We've "scrunched down" this worksheet to just a few columns to simplify the illustration. |
Enter a subtotal formula, and its trigger text
Enter the formula in the Description column of a text-triggered formula row. It should sum the range of Sandbox cells in the Scratchpad column. That tells FullSpeed to sum all of the Scratchpad rows accumulated so far, when the macro runs. Also, enter the trigger item's text in the Item column of that same row:
Notes:
| ® | Why put the subtotal in the Description column? It is a text field in Xero, where we will be able to format the results with a currency symbol ($) and two decimal places. |
Format the subtotal formula cell as currency
The amount (233.7) is correct but needs to be displayed as dollars and cents (currency). Right-click the cell and select Format cell from the menu.
Then select a currency formatting option:
After closing the formatting dialog, the cell should display the results as dollars and cents:
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 "Subtotal" trigger text. |
| ® | Use the macro options toolbar settings shown below—especially the target application (Xero) and the repeat type (Run until "X" blank fields). |
| ® | Assign a hotkey. Shift+Ctrl+2 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 subtotal the entire Qty column the macro must begin at the top of the column.
| 2. | Press the hotkey assigned to the macro, Shift+Ctrl+2 in this example. |
FullSpeed will traverse the form, gathering data. As it reaches each Subtotal line it will put a subtotal in the Description field. Here's how it should look after the macro has run:
