Simple Sample: Total Quantity on Sales Report

This form adds a custom field to calculate and store the total quantity on an invoice, and then add this information to the sales report.

1. First, make sure you have created a backup copy of the template.
2. Open the template.
3. Click "Invoices" on the ribbon tab.
4. Click "Custom Fields" on the toolbar to open the "Custom Field Manager" dialog box.
5. Make sure "Database Table" is showing "Invoice Header".
6. Click "Add Field" to open the "Add New Custom Field" dialog box.
7. Define a new field as the following:
   • Field Name: TotalQuantity
   • Type: Decimal
   • Precision: 10
   • Scale: 2
8. Click "OK" to create the new field.
9. Close all dialog boxes and return to the Excel sheet.
10. Switch to design mode by clicking the "Design Mode" button on the ribbon.
11. Click the cell where you want to place the total quantity. In this example, we choose $I$34, i.e., the cell below the "Quantity" column.
12. Enter the formula to calculate the sum of the quantities:

    =sum(oknQuantity_1:oknQuantity_12)

13. On the formula bar, click to activate the name box located at the left end and enter the cell name "oknTotalQuantity".

Next, we add this field to the sales report.

1. First, click and activate the "Sales Report" worksheet.
2. Click the cell with the text "Balance Due".
3. Click "Format Painter" in Excel's "Home" ribbon tab.
4. Click the cell next to "Balance Due", i.e., $R$11. This copies the format of the "Balance Due" cell to $R$11 to make it look like a report heading.
5. In the name box of the formula bar, enter the cell name "oknRsTotalQuantity".
6. Enter the label of this column, "Total Quantity".
7. Click and activate the "Invoice" worksheet.
8. Click "Design Mode" on the ribbon to exit design mode.
9. Save the template.

Now the form calculates the total quantity for each invoice, and when you create a new sales report, the total quantity of each invoice is retrieved and shown.