Custom Field - Tutorial - Invoice Manager for Excel

Analyzing Requirements

First, let's look at the Automotive.xls invoice template and find out what is required to create this Automotive.xls template.

By comparing the two templates, we find out that the main differences are as follows:

1. Invoice.xls has two taxes, whereas Automotive.xls has one tax.
2. Automotive.xls does not include shipping information.
3. Other cell names and controls need to be removed:
   • Sales Rep. Name (oknSalesRepName)
   • Ship Date (oknShipDate)
   • Ship Via (oknShipVia)
   • Terms (oknPaymentTerm)
   • Customer Country (oknWhoCountry)
   • Ship To-Selection button (image)
   • Sales Rep. Name-Selection button (image)
   • Ship Via-Selection button (image)
   • Terms-Selection button (image)
   • Customer Country (label)
   • Customer ID (label)
4. Automotive.xls has 28 rows on its invoice body.
5. We need the following custom fields:

   Product Table
   Field Name	Field Type	Note
   Disc	Decimal (5,2)
   CoreExt	Decimal(14,4)

   Invoice Header Table
   Field Name	Field Type	Note
   Misc	Decimal (15,4)	Entered manually on the Invoice worksheet.
   DealerExtTotal	Decimal (15,4)	The sum of all Dealer Ext. cells in the current invoice.
   DiscExtTotal	Decimal (15,4)	The sum of all Disc Ext. cells in the current invoice.
   CoreExtTotal	Decimal (15,4)	The sum of all Core Ext. cells in the current invoice.

   Invoice Body Table
   Field Name	Field Type	Note
   Disc	Decimal (5,2)	Corresponding to the Disc field in the Product table. That is, when saving an invoice, the Disc value retrieved from Product table will be stored in the Disc field in the Invoice Body table.
   CoreExt	Decimal(14,4)	Corresponding to the CoreExt field in the Product table.
   DealerExt	Decimal(15,4)	Equal to Price * Quantity. It is calculated automatically by Excel. Thus it is not necessary to create a corresponding field in the Product table.
   DiscExt	Decimal(15,4)	Equal to DealerExt - LineTotal.
   NetEach	Decimal(15,4)	Equal to Price * (1-Disc/100).
   NetExt	Decimal(15,4)	Equal to Quantity * NetEach.
   Note: In Automotive.xls template, the ProductID column is labeled as Part Number, the Price column is labeled as Dealer Each, and the LineTotal column is labeled as Net Ext.

Customizing Database

In this section, we will customize the database definition file and database file for our Automotive.xls example.

1. In Windows Explorer, create a folder Automotive and copy FDINFO10.rst and Sample.mdb (or an empty database file created using Excel Invoice Manager) from the data file folder of Excel Invoice Manager into Automotive subfolder. Rename Sample.mdb to Automotive.mdb.
2. In Windows Explorer, browse to the Start menu -> Programs -> Office-Kit.com -> Excel Invoice Manager -> Database Designer and click to launch it. You will see the Introduction screen of Database Designer.

   

3. Click Next. This screen allows you to open a database definition file and save a backup.

   

4. Click Open Database Definition File and browse to the Automotive folder we just created. Select and open FDINFO10.rst.

   

5. Because the database definition file FDINFO10.rst was copied from the data file folder of Excel Invoice Manager, we can safely skip the database definition file backup process. Click the Next button to go to the following screen.

   

   The Tables list shows all tables that are modifiable. The Columns/Fields list shows all custom fields in the table currently selected, or shows all fields in the table currently selected if the Display Standard Fields box is checked. Standard fields are required by Excel Invoice Manager and are not modifiable.

6. Now we create the first custom field Misc for the Invoice Header table, which is a decimal value with a precision of 15 and a scale of 4. Click Invoice Header in the Tables list, and then click the Add Field button. This displays the following screen.

   

7. In Add a New Field screen, enter field name Misc. Select Decimal as its field type. Enter 15 and 4 in the Precision and Scale box respectively. Click the OK button to close the dialog box.

   

8. You can see the field just added in the Columns/Fields list. In case you need to modify an existing custom field, just select the field in question and click the Modify Field button. To delete a field, select it and click the Delete Field button.

   

9. Repeat the above steps to create all required custom fields.
10. Click the Save button to save the modified database definition file.
11. Click Next. Here we will apply the database definition file to databases.

    

12. First, specify the database file by clicking the Select database file button and select Automotive.mdb from the Automotive folder.
13. Because the Automotive.mdb database was copied from Sample.mdb, we will skip the Backup database step. (No backup is necessary in this example. However, if you try to modify your working databases and templates, it is strongly recommended that you backup all of them prior to making any changes.)
14. Now click the Apply! button. Database Designer modifies the database structure according to the database definition file. When finished, it displays "OK" near the Apply! button, or displays error messages if there were problems found during the process.

    

15. Click the Finish button to exit Database Designer.

Customizing Invoice Template

In this section, we will create the Automotive.xls template from the standard Invoice.xls template. Before starting, make sure you've read the Customizing invoice template chapter, which provides general information about customizing invoice templates.

1. Copy the Invoice.xls template in the data file folder of Excel Invoice Manager into the Automotive subfolder. Name it Automotive.xls.
2. Open Automotive.xls in Excel.
3. Click the Settings button on the Invoice worksheet. Switch to Taxes tab. Choose one tax and set tax name to 'Sales Tax'.
4. Unprotect the Invoice worksheet and switch it into design mode.
5. Display row/column headers and grid lines.
6. Remove all unused ActiveX controls. To remove an ActiveX control, just click it and hit the DELETE key.
7. Remove all unused cell names.

   To remove a cell name in Excel 2000 - 2003, click Excel menu Insert -> Name -> Define. Browse to the name in question and select it. Click Delete.

   To remove a cell name in Excel 2007/2010, go to the Formula tab, in the Defined Names group, click Name Manager, select the name you want to delete, and then click Delete.

   

8. Move the following unused cell names out of the PRINT_AREA. These cell names cannot be deleted, as they are required for validating invoices. However, you can move them out of the PRINT_AREA or even put them into hidden rows/columns.
   • oknPayments
   • oknBalanceDue
9. Move the Invoice Date (cell name oknInvoiceDate) and the Invoice # (cell name oknInvoiceID) to a free, unused area, such as column U and column V.

   

10. Right click the row header "7", and then insert a new row. Repeat 3 times to insert 3 rows.
11. Move the company information area near the LOGO image to the area starting from the K6 cell. Enter company name, address, etc.
12. Enter company name in the cells next to the LOGO image.
13. Move the "Bill To" label and customer information cells to the proper place. Here we use 4 customer information cells:
    • oknWhoName
    • oknWhoAddress
    • oknWhoCityStateZip
    • oknWhoPhone
14. Move the 4 ActiveX labels which correspond to the 4 customer information cells to their proper place or position.
15. Right click the Name ActiveX label and click Properties. Set its TextAlign property to 1-fmTextAlignLeft. Repeat this step for other customer information ActiveX labels.
16. Move the Customer-Selection icon button (magnifier icon) to the right of customer information labels.
17. Move the View Customer Info button to its proper position. To make it the same width as the Save As New Customer button:
    1. Right click the Save As New Customer button and click Properties.
    2. Browse to the Width property and note its value.
    3. Click the View Customer Info button without closing the Properties box and enter the width value in its Width field.
    4. Close the Properties box.
18. Select all Product ID cells by dragging your mouse. Move these cells outside the PRINT_AREA and place them in a free/unused space, such as column U.

    

19. Select all Quantity cells by dragging your mouse. In Excel 2000 - 2003 click the Merge and Center button on the Formatting toolbar once; Or in Excel 2007/2010 go to Home tab and click the Merge and Center button in the Alignment group. This will unmerge all Quantity cells.
20. Select all oknQuantity_** cells by dragging your mouse. Move them to the first column on the invoice body. Change the first column header on the invoice body from Product ID to Qty.

    

21. Select all Product Description cells by dragging your mouse. Unmerge them. Move all cells named as oknProductName_** to a free area, such as column V. In the Aotomotive.xls template, we do not need the Product Description/Product Name column. However, this column is necessary when Excel Invoice Manager validates invoices. So the oknProductName_** cells cannot be deleted, but can be put into a hidden column.

    Tip: If the Product-Selection icon buttons are moved with the Product Description cells, just select all Product-Selection icon buttons by pressing CTRL key and clicking each of them, and then press the arrow key one or several times - this ensure the icon buttons and the product description cells are not overlapped.

22. Move all oknProductID_** cells to column H. Fill the column header cell with text "Part Number".
23. Unmerge the cell with text "Description" in it.
24. Right click column I and select Insert. This inserts a new column next to the "Part Number" column.
25. Enter "Disc %" as the column header. Name the following cells as oknDisc_1 to oknDisc_12.

    

26. In the next column (column J), enter "Dealer Each" as the invoice body's column header. Move all oknPrice_** cells to this column.
27. In the next column (column K), enter "Dealer Ext." as the invoice body's column header. Name the following cells as oknDealerExt_1 to oknDealerExt_12 respectively.
28. Repeat the above steps to setup these columns:

    Column Number	Column Header Text on Invoice Body	Cell Name
    L	Disc Ext.	From oknDiscExt_1 to oknDiscExt_12
    M	Net Each	From oknNetEach_1 to oknNetEach_12
    N	Net Ext.	From oknLineTotal_1 to oknLineTotal_12
    O	Core Ext.	From oknCoreExt_1 to oknCoreExt_12

29. Create formulas to calculate cell values on the invoice body. Replace RowNumber in the following formulas with appropriate row numbers.

    Column Number	Column Header	Formula
    K	Dealer Ext.	=ROUND(oknPrice_RowNumber*oknQuantity_RowNumber,2)
    L	Disc Ext.	=oknDealerExt_RowNumber - oknLineTotal_RowNumber
    M	Net Each	=ROUND(oknPrice_RowNumber * (1-oknDisc_RowNumber/100),2)
    N	Net Ext.	=ROUND(oknQuantity_RowNumber*oknNetEach_RowNumber,2)

30. Select all rows on the invoice body by dragging your mouse on the row headers. Right click any of the row headers, click Row Height. Set Row Height to 12.75 to decrease the height of all rows on the invoice body.
31. Move the following cells to its proper location. Create/format label cells as needed.

    Cell Name	Move to This Address	Label Text
    oknWhoID	K13	Account #
    oknOrderID	L13	P.O.
    oknDueDate	M13	Due Date
    oknInvoiceDate	N13	Date
    oknInvoiceID	O13	Invoice #

32. Clear all cells between the top row of the invoice body and the row we just filled in. If there are merged cells in this area, unmerge them.

    

33. Click and select column B and column F. Right click one of the headers, and then click Unhide on the menu.
34. As you can see from the following screen shot, C13 ~ C18 are used to store default tax options. Select all of them and move them to a free area, such as C3 ~ C8.

    

35. Select all cells on the invoice body, including those in hidden columns, and then move them up to start on row 16.

    

36. Move the Quick View area several rows up to leave space for the Taxable checkboxes.
37. Move all Taxable checkboxes up to the right place.
38. Automotive.xls needs 28 rows on the invoice body, whereas the standard invoice template has only 12 rows. Now we need to add more rows to the invoice body. For a detailed tutorial about this, please refer to Multiple-page invoice templates.
39. Create/set several cells for calculating totals of certain columns, as listed in the following table:

    Cell Name	Formula
    oknDealerExtTotal	=SUM(oknDealerExt_1:oknDealerExt_28)
    oknDiscExtTotal	=SUM(oknDiscExt_1:oknDiscExt_28)
    oknCoreExtTotal	=SUM(oknCoreExt_1:oknCoreExt_28)

40. Fine-tune the invoice template, such as setting up fonts, colors, text labels, borders and number formats.
41. When finished, in Excel 2000 - 2003 exit design mode by clicking the Design Mode button on the Control Toolbox toolbar; In Excel 2007/2010 exit design mode by clicking the Design Mode button in the Controls group of the Developer tab. Hide row/column headers and gridlines.
42. Test the template.