Custom Invoice Fields - Tutorial
In this tutorial, we will look at how to create an invoice template for an automotive company, starting from the Standard Invoice.xls template. The resulting invoice template created in this tutorial is installed on your computer and located in the Automotive subfolder of the data file folder of.
First, let's look at the Automotive.xls invoice template and find out what is required to create this Automotive.xls template.
|Template||Invoice Worksheet||Printed Version|
|Standard Invoice Template (Invoice.xls)|
By comparing the two templates, we find out that the main differences are as follows:
- Invoice.xls has two taxes, whereas Automotive.xls has one tax.
- Automotive.xls does not include shipping information.
- 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)
- Automotive.xls has 28 rows on its invoice body.
- 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 Definition File and Database
In this section, we will customize the database definition file and database file for our Automotive.xls example.
- 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 into Automotive subfolder. Rename Sample.mdb to Automotive.mdb.
- In Windows Explorer, browse to the menu -> -> -> -> and click to launch it. You will see the screen of Database Designer.
- Click . This screen allows you to open a database definition file and save a backup.
- Click and browse to the Automotive folder we just created. Select and open FDINFO10.rst.
- Because the database definition file FDINFO10.rst was copied from the data file folder
Thelist shows all tables that are modifiable. The list shows all custom fields in the table currently selected, or shows all fields in the table currently selected if the box is checked. Standard fields are required by and are not modifiable.
, we can safely skip the database definition file
backup process. Click the button to go to the following screen.
- 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 in the list, and then click the button. This displays the following screen.
- In screen, enter field name Misc. Select as its field type. Enter 15 and 4 in the and box respectively. Click the button to close the dialog box.
- You can see the field just added in the list. In case you need to modify an existing custom field, just select the field in question and click the button. To delete a field, select it and click the button.
- Repeat the above steps to create all required custom fields.
- Click the button to save the modified database definition file.
- Click . Here we will apply the database definition file to databases.
- First, specify the database file by clicking the button and select Automotive.mdb from the Automotive folder.
- Because the Automotive.mdb database was copied from Sample.mdb, we will skip the 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.)
- Now click the button. Database Designer modifies the database structure according to the database definition file. When finished, it displays "OK" near the button, or displays error messages if there were problems found during the process.
- Click the 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.
Note: You can also download a zip file, which includes several templates created in the following steps. The numbers in the file names indicate the step number in which the templates were created.
- Copy the Invoice.xls template in the data file folder of into the Automotive subfolder. Name it Automotive.xls.
- Open Automotive.xls in Excel.
- Click the button on the worksheet. Switch to tab. Choose and set tax name to 'Sales Tax'.
- Unprotect the worksheet and switch it into design mode.
- Display row/column headers and grid lines.
- Remove all unused ActiveX controls. To remove an ActiveX control, just click it and hit the DELETE key.
- Remove all unused cell names.
To remove a cell name in Excel 2000 - 2003, click Excel menu-> -> . Browse to the name in question and select it. Click .
To remove a cell name in Excel 2007/2010, go to thetab, in the group, click , select the name you want to delete, and then click .
- 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.
- 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.
- Right click the row header "7", and then insert a new row. Repeat 3 times to insert 3 rows.
- Move the company information area near the LOGO image to the area starting from the K6 cell. Enter company name, address, etc.
- Enter company name in the cells next to the LOGO image.
- Move the "Bill To" label and customer information cells to the proper place.
Here we use 4 customer information cells:
- Move the 4 ActiveX labels which correspond to the 4 customer information cells to their proper place or position.
- Right click the ActiveX label and click . Set its property to 1-fmTextAlignLeft. Repeat this step for other customer information ActiveX labels.
- Move the Customer-Selection icon button (magnifier icon) to the right of customer information labels.
- Move the
button to its proper position.
To make it the same width as the button:
- Right click the button and click .
- Browse to the property and note its value.
- Click the button without closing the box and enter the width value in its field.
- Close the box.
- 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.
- Select all Quantity cells by dragging your mouse. In Excel 2000 - 2003 click the button on the toolbar once; Or in Excel 2007/2010 go to tab and click the button in the group. This will unmerge all Quantity cells.
- 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.
- 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
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.
validates invoices. So the oknProductName_** cells cannot be deleted,
but can be put into a hidden column.
- Move all oknProductID_** cells to column H. Fill the column header cell with text "Part Number".
- Unmerge the cell with text "Description" in it.
- Right click column I and select . This inserts a new column next to the "Part Number" column.
- Enter "Disc %" as the column header. Name the following cells as oknDisc_1 to oknDisc_12.
- In the next column (column J), enter "Dealer Each" as the invoice body's column header. Move all oknPrice_** cells to this column.
- 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.
- 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
- 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)
- Select all rows on the invoice body by dragging your mouse on the row headers. Right click any of the row headers, click . Set to 12.75 to decrease the height of all rows on the invoice body.
- 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 #
- 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.
- Click and select column B and column F. Right click one of the headers, and then click on the menu.
- 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.
- Select all cells on the invoice body, including those in hidden columns, and then move them up to start on row 16.
- Move the area several rows up to leave space for the checkboxes.
- Move all checkboxes up to the right place.
- 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 Creating an Invoice Template with Additional Rows .
- Create/set several cells for calculating totals of certain columns, as listed in the
Cell Name Formula oknDealerExtTotal =SUM(oknDealerExt_1:oknDealerExt_28) oknDiscExtTotal =SUM(oknDiscExt_1:oknDiscExt_28) oknCoreExtTotal =SUM(oknCoreExt_1:oknCoreExt_28)
- Fine-tune the invoice template, such as setting up fonts, colors, text labels, borders and number formats.
- When finished, in Excel 2000 - 2003 exit design mode by clicking the button on the toolbar; In Excel 2007/2010 exit design mode by clicking the button in the group of the tab. Hide row/column headers and gridlines.
- Test the template.