This example demonstrates:
•How to set up trigger Items in the QuickBooks Item list
•How to calculate a column from two other columns
•How to total a column
Overview
If you define a custom field in the Item list and you store the per-unit shipping weight there for each Item, then when you select an Item on an Invoice or other form QuickBooks can automatically bring the shipping weight into the form on that line.
With the shipping weight available on each line, FormCalc SST can multiply it by the quantity to calculate an extended shipping weight on each Item line (Quantity * Ship Wt = Extd Wt). FormCalc SST can also total the extended shipping weight column, to calculate a total shipping weight for the form (Invoice, etc.).
How to Calculate Per-Item and Total Shipping Weights
QuickBooks Setup
1.Define a custom field named Ship Wt in the Items list:
2.Enter a shipping weight for each Item—or at least, for those Items you ship:
3.Add an Item to the Items list, to use as a trigger Item.
| You will use this Item on QuickBooks forms to control where the extended weight column's total appears. Make it a Service-type Item and name it TotalShipWt. |
 | The FormCalc Item Postings account has been assigned to this Item, as discussed in the Tips for Trigger Items topic, but you may use an income or expense account if you wish. |
4.Customize the form where the shipping weights will be calculated.
| On the Columns tab, include the Ship Wt custom field. Also include the Other 1 field, and rename it Extd Wt. |
| Also, renumber the Order column if necessary, to order fields as you want them: |
5.Finally, fill out the form as you normally would, so it contains some sample data for FormCalc SST to copy when taking a snapshot of the form. The Extd Wt column is empty of course—it hasn't been calculated yet.
FormCalc SST Setup
1.Select File > New from the main menu to start a new FormCalc SST file.
| (Optional. You may overwrite the existing file if you want.) |
2.Take a snapshot of the QuickBooks form.
| FormCalc SST will gather data about the form and build a spreadsheet based on it. |
3.Assign column types on the Column types row, identifying the spreadsheet's column types with the corresponding QuickBooks columns.
| The column types row should look something like the following, depending on the order and number of columns on the QuickBooks form: |
4.Enter a formula in the ExtdWt column of the Formulas row, which multiplies the Quantity column by the ShipWt column.
| For example: |
| =F18*H18 ...your formula will be different if your form has a different column layout.) |
5.Enter a formula in the ExtdWt column of the Item-triggered formulas 1 row, to total the entire ExtdWt column.
| When referring to a whole column in a formula, you can simply referring to a one-cell "range" of the column: |
| =SUM(I1:I1) ...again, your formula may be different |
| FormCalc SST will adjust this one-cell range to the correct coordinates for summing the entire column, I24:I26 (see the "Close enough" cell referencing topic for details): |
6.Also enter the trigger Item name, TotalShipWt, in the Item column of the same row, as shown above.
7.Save the file to assure your changes are kept.
| FormCalc SST files are identifiable by their .SST filename extension (.SST in prior versions). |
Processing a QuickBooks Form
To verify that your FormCalc SST setup works as desired, just process a QuickBooks form containing some data.
From FormCalc SST
1.Press the Action button in the main toolbar.
| The focus will switch to QuickBooks, and the Choose an action dialog will appear: |
2.Click on the Process this QuickBooks form button to process the form. (See example results below.)
From QuickBook
1.Press the FormCalc SST hotkey, which is F11 unless you have changed it in Preferences.
The same Choose an action dialog as shown above will appear.
2.Process the form by either pressing the hotkey a second time (quickest for most users), or clicking on the Process the QuickBooks form button.
Results
After processing, the form should look something like this. As you can see, the correct extended weight has been calculated on each line and a total shipping weight has been calculated at the bottom.
| Note: When you save a QuickBooks form, the FormCalc SST-calculated results are saved with it. Other QuickBooks users who view the form will be able to see the calculated results even if they don't have FormCalc SST on their computer. |
|
If you don't want to have the EXTD WT column on the Invoice, another option is to omit that column when customizing the form, and use one of the Scratchpad columns for the extended weight calculation instead. |
Using your FormCalc SST setup
Once you have tested your FormCalc SST setup, it is ready to use for day-to-day data entry work. With the QuickBooks and FormCalc SST programs both running, most of the time you will (1) fill out a form, (2) invoke FormCalc SST from within QuickBooks by pressing the hotkey, (3) save the form, and (4) repeat.
Related topics: