Simple Invoice Sample: Sales Rep. Name on Product Report

This tutorial demonstrates how to add the sales rep's name to the "Product" report by using custom fields.

By default, the "Product" report contains fields such as Product#, Product Description, Invoice#, Date, Quantity, Unit Price, Line Total, etc.

Here are the steps.

1. Backup the template.
2. Open the template.
3. Click the Design Mode button on the ribbon tab.
4. Choose a blank (non-used) area. In this example, we choose the range at O22:O33 and assign it a slightly different background color.
5. Name the cells "oknItemSalesRep_1", "oknItemSalesRep_2", "oknItemSalesRep_3"... "oknItemSalesRep_12", from top to bottom. That is, O22 is named "oknItemSalesRep_1", O23 is named "oknItemSalesRep_2", and so on.
6. Create a formula for each of the cells. For the "oknItemSalesRep_1" cell, the formula should be:

   =IF(oknProductName_1="","",oknSalesRepName)

   and for the "oknItemSalesRep_2" cell, the formula should be:

   =IF(oknProductName_2="","",oknSalesRepName)

   These formulas check if the product name (the "Description" column) cells are blank, and if not, they retrieve the content of the "oknSalesRepName" cell and fill it into the current cell.

7. Click the "Design Mode" button on the ribbon tab to exit design mode.
8. Click the "Invoices" button on the ribbon tab.
9. Click "Custom Fields".
10. On the "Custom Field Manager" dialog box, choose "Invoice Body" from the "Database table" list.
11. Click "Add Field".
12. On the "Add New Custom Field" dialog box, define the new field as below:
    • Name: ItemSalesRep
    • Type: Text
    • Size: 50
13. Click OK to create the new field.
14. Click "Close" to close the "Custom Field Manager" dialog box.
15. Now switch to the "Product" worksheet.
16. By default, it has the following columns: Product ID (cell name: oknRpProductID), Date, Invoice #, Description, Quantity, Price, Line Total, and Unit Cost.
17. Click to select the "Unit Cost" cell.
18. On the Excel "Home" ribbon tab, click "Format Painter".
19. Click the cell next to "Unit Cost" on the Product worksheet. This copies the format of the "Unit Cost" cell to the new (empty) cell ($J$10).
20. Enter the column heading into $J$10. In this case, it is "Sales Rep.".
21. Name the cell "oknRpItemSalesRep". Note that in this name, "oknRp" is the name prefix and "ItemSalesRep" is the field name in the database definition.
22. Save the template.

Now, when you create and save an invoice, the sales representative's name will be saved with each item. And when you generate a report, the sales representative's name will show too.