Service Invoice with Horizontal Logo

This Excel form is a revised version of Service Bill with Discount Percentage (c4005). It contains a horizontal rectangle logo instead of the usual square logo. It also demonstrates how to use custom fields.

If you are only interested in using the template, then all you need to do is download the template, extract it from the package, and then use it. If you would like to learn more about how this template was created, read on.

Two custom fields, Company and Fax Number, are added to the "Bill To" section. These custom fields must be added to both the Customer and InvHdr database tables.

• They must be added to the Customer database table because you need them to be auto-populated when you pick a customer by clicking the "select a customer" button.
• They must be added to the InvHdr database table because you want them to be saved on a per-invoice basis so that the full invoice can be restored later by clicking the "Edit In Excel" command.

It is always recommended to backup the template that you want to modify. This allows you to easily go back to the original version if you need to start again. You can also make a backup before making any major changes. To make a backup, simply copy the Excel workbook file in Windows Explorer, or open the template as usual and execute the "Save As" command in Excel.

Add the custom fields to the database

1. Click the "Customers" button on the ribbon tab, or click the "Customers" button on the worksheet.
2. Click "Custom Fields" on the toolbar.
3. Make sure "Database Table" shows "Customer". Click "Add Field" to open the "Add New Custom Field" dialog box.

   Add the custom field "CustomerCompany" with the following specification, and then click "OK" to add the field.

   • Name: CustomerCompany
   • Type: Text
   • Size: 100
4. Repeat the above step to add the "Fax" field with the following specification:
   • Name: Fax
   • Type: Text
   • Size: 30
5. When you click the "OK" button on the "Add New Custom Field" dialog box, you are returned to the "Custom Field Manager" dialog box.
6. Choose "Invoice Header" from the "Database Table" list.
7. Repeat the above steps to add the following three custom fields:

   Name	Type	Size
   Fax	Text	30
   CustomerCompany	Text	100
   InvDiscount	Decimal	(6,3)

8. Click "OK" to close the "Add New Custom Field" dialog box. Click "Close" to close the "Custom Field Manager" dialog box.
9. Close the "Customers" data list window.

Add the custom fields to the Excel sheet

1. Open your template in Excel.
2. Push down the "Design Mode" button on the "Invoice" ribbon tab.
3. Insert a new row above the "Address" line in the Bill To section.
4. Add the text label "Company" to the cell above the "Address" label.
5. Merge the cells above "oknWhoAddress". To do this, drag your mouse to select J12:L12, and then click the "Merge and Center" button on the Excel "Home" ribbon tab.
6. Name the newly merged cell "oknCustomerCompany". Here "CustomerCompany" is the name of the field that was added to the "Customer" and "InvHdr" database tables.

   If the field "CustomerCompany" exists in the "Customer" database table, then the value of the "oknCustomerCompany" cell will be saved to the customer record when you click the "Save As New Customer" button.

   If the field "CustomerCompany" exists in the "InvHdr" database table, then the value of the "oknCustomerCompany" cell will be saved along with the other data when you click "Save To DB".

7. Repeat the above steps to add another line to the form. Add the label "Fax#" and name the new cell oknFax.
8. Repeat the above steps to add the Discount field at the bottom of the form, above the "Total" line. The cell name of the discount field should be "oknInvDiscount".
9. Using Excel's "Format Cells" dialog box, assign the new discount cell a percentage format.
10. Modify the formula of the "Total" cell. It looks like this:

    =ROUND(IF(oknInvDiscount=0,oknSubTotal,
    oknInvDiscount*oknSubTotal),2)
    

    This formula means that if the oknInvDiscount value is zero (not filled in), then the total is equal to the subtotal. Otherwise, the total is equal to oknInvDiscount * oknSubtotal.

11. The last step is to fine-tune the template—set colors, fonts, and borders as you like. Most of the work can be done by right-clicking a cell and then clicking Format Cells.
12. Once you're finished, click "Design Mode" again to exit design mode.
13. Save the template.