Custom Field - Overview - Invoice Manager for Excel

Database Tables

A database is a mechanism to store information effectively. In databases, information is organized with tables. A table, just like a worksheet in Excel, is constructed with rows/columns, or records/fields in database terms. In Excel Invoice Manager, the 4 primary tables are Customer, Product, Invoice Header and Invoice Body.

Database Definition File

Excel Invoice Manager uses database definition files to provide database structure information. Database definition file creates a completely abstract layer between databases and Excel Invoice Manager COM add-in. By using database definition files, Excel Invoice Manager is able to connect to and work with databases that are very different in structure.

Database definition files are named as FDINFO10.rst and stored in the same folder as the database files. When you open a valid invoice template in Excel, it connects to the database, and tries to load FDINFO10.rst from the folder where the database file is stored in. If unsuccessful, it next tries to load the default FDINFO10.rst file from the installation folder of Excel Invoice Manager. So if you customized the database structure of an Access database, you need to provide a corresponding database definition in the same folder as the database file.

If you create a new database using the Create New Database feature located on the Database tab of the Settings window, the new database will have the same structure as defined in the current database definition file.

Using Custom Fields

There are 5 steps to use custom fields: 1. Analyze requirements; 2. Modify database definition file and database; 3. Store the database definition file and database file in the same folder; 4. Customize invoice template; 5. Test.

1. Analyze business requirements and determine how many custom fields you need. Here are the rules for adding custom fields to tables:

1. If a data item is related to customers, but you don't want to include it in invoices or reports, then add it to the Customer table. Example: All of your customers are individuals and you desire to keep a record of each customer's date of birth (birthday). However, there is no need for this information to appear or print on invoices being sent to this customer. In this example. You can create a Birthday field in the Customer table and set its type to DATE.
2. If a data item must be included once for each invoice, but this data item is not related to customers, then add it to the Invoice Header table. For example, if you want to calculate the net profit of each invoice, you can add a NetProfit field to the Invoice Header table. Of course, you don't want the net profit to appear on the printed invoice or extracted invoice that you will send to your customer. It's easy to achieve this - just put the net profit cell outside Print_Area of the Invoice worksheet, or even put it into a hidden row/column.
3. If a data item is related to customer, and must be included once for each invoice, then add the fields both in the Customer table and the Invoice Header table, with the same field name and data type. For example, if you are running a travel agent business and need to know the number of children of a customer, then add a custom field Children to the Customer table and set its type to INTEGER. Next, add another custom field Children to the Invoice Header table and set its type to INTEGER.
4. If a data item is related to products, but is not intended to print on your invoice, then add it to the Product table.
5. If a data item must be included once for each line of the invoice body, but it is not related to products, or is unknown when creating product records, then add it to the Invoice Body table. For example, you are running a Web hosting service and want to show the date extension period on invoices for each product your customer purchased. Let's say you have a product "Virtual Host Plan A". When a customer purchases this product, you need to note the time period (Start Date and End Date) on the invoice. In this example, you can do this by adding two custom fields, StartDate and EndEnd (or DateFrom and DateTo), to the Invoice Body table, and set the field types to DATE.
6. If a data item is related to product, and must be included once for each line of the invoice body, then add it to the Product table and the Invoice Body table, with the same field name and data type. For example, your products have fixed discount rates, and you want to show the discount rates on all invoices. Please review the Automotive.xls example in the following section for a demonstration of this scenario.

2. Customize the database definition file and database file using Database Designer.

Database Designer is a tool designed to modify database definition file and Access database file. This tool allows you to add, modify and delete custom fields in the Customer, Product, Invoice Header and Invoice Body table. By default, it is located under Start menu -> Program Files -> Office-Kit.com -> Excel Invoice Manager -> Database Designer. For a detailed demonstration, see the Automotive.xls example below.

3. Store the customized database definition file and database file in the same folder.

When you open an invoice template, it tries to connect to the database file previously connected to, and attempts to load database definition files from the folder that the database file is stored in. If a database definition file cannot be found in that folder, next it will load the standard database definition file from the installation folder of Excel Invoice Manager. For this reason, you need to store the database definition file in the same folder as the database file if you use custom fields.

4. Customize your invoice template to utilize custom fields.

You can add ALL custom fields to the Invoice worksheet.

When you select a customer by clicking the Customer-Selection icon button on the Invoice worksheet, all custom fields in the Customer table are written to the cells identified by corresponding names. For example, if there is a cell name oknChildren on the Invoice worksheet, then this cell is filled with the data pulled from the Children custom field in the Customer table.

Similarly, when you select a product by clicking the Product-Selection icon button on the Invoice worksheet, all custom fields are written to the cells identified by corresponding cell names. The only difference is that cell names on the invoice body have a line number postfix. For example, if you click the Product-Selection icon button on the second line and select a product, a cell named oknDiscount_2 will be filled with the data pulled from the custom field Discount in the Product table.

When saving an invoice by clicking the Save To DB button on the Invoice worksheet, all custom fields defined in the Invoice Header and Invoice Body tables are filled with corresponding cell values. For example, if there is a NetProfit custom field in the Invoice Header table, Excel Invoice Manager tries to read the value from a cell named oknNetProfit. If there is a Discount custom field in the Invoice Body table, when saving the first line of the invoice body, Excel Invoice Manager will fill its Discount field with a value from oknDiscount_1 cell.

If a cell name corresponding to a custom field name does not exist on the Invoice worksheet, the value of the custom field won't be written to the Invoice worksheet when you display an invoice or select a customer/product. The value will remain unchanged and will not be affected when you save an invoice.

Custom fields can be added to report worksheet as well.

All custom fields defined in the Invoice Header table can be added to Sales Report, Customer Report, Customer Statement, and Sales Rep. Report. All custom fields defined in the Invoice Body table can be added to Product Report. However, each report has its own cell name prefixes. For a complete list of cell name prefixes on report worksheets, see Simple Sample - Discount Amount on Sales Report, Simple Sample - Customer Name on Product Report, Simple Invoice Sample - Sales Rep Name on Product Report, and Simple Sample - Total Quantity on Sales Report for detailed examples. For a simple example, if you want to display the Children custom field on the Sales Report, just add a cell name oknRsChildren to the report header row, and then use the Format Painter tool to set the format of the oknRsChildren cell.

5. Test. After customizing the template, you should carefully and thoroughly test your work product.

For a quick example, let's say you are running a travel agent business and need an additional field Children to store the number of children. You want to store this information in the Customer table, as well as in each invoice. To implement this, you:

1. Add a custom field Children to the Customer table, and set its type to INTEGER.
2. Add a custom field Children to the Invoice Header table, and set its type to INTEGER.
3. Add a cell name oknChildren to the Invoice worksheet. When you select a customer by clicking the Customer-Selection icon button, the cell named oknChildren is filled with the number pulled from the Customer table. When you save the invoice, the value of oknChildren cell is saved to the Children field of the Invoice Header table.
4. To display this value on reports, add a cell name "okn**Children" to report worksheets - "okn**" is the cell prefix of that report. For example, if you want to display it on Sales Report, then name the cell oknRsChildren.

Custom fields are also available on general dialog boxes, such as the Customer Edit window, the Invoice Edit window and the Product Edit window. For example, if we add a Children field to the Customer table, the Customer Edit window displays a Custom Fields tab:

Custom fields on the Customer Edit window and the Product Edit window are modifiable, whereas custom fields on the Invoice Edit window are read-only.