- Document for Excel Invoice Manager
This version of the document, "Introduction to Custom Fields", was written for Excel Invoice Manager, which was now renamed to Uniform Invoice Software. For the latest version of this document, please download Uniform Invoice Software.
Starting with Version 2.5 Build 1008,supports custom fields added to Customer, Product, Invoice Header and Invoice Body database tables. This greatly improves the application flexibility. You can now customize your invoice application not only on the Excel invoice template level, but also on the database structure level. Combined with Excel's powerful calculation ability, custom fields give you a new way to calculate, store, organize and utilize invoicing data.
Note: This feature is available in Platinum, Pro and Enterprise editions only.
You need a basic knowledge of's database tables and database definition files to understand how custom fields work. When creating an invoice, you pull data from the Customer table to fill in the invoice header and you pull data from the Product table to fill in the invoice body. The following figure explains how custom fields are used when creating invoices.
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, the 4 primary tables are Customer, Product, Invoice Header and Invoice Body.
|Customer||Stores customer-related information, such as customer name, customer address and account balance, etc.|
|Product||Stores product-related information, such as product description, stock, price, etc.|
|Invoice Header (InvHdr in Access databases)||Stores all information regarding the invoice header and footer, such as invoice #, customer name for the current invoice, total, etc. |
If a data item must be included once for each invoice, then it is stored in the Invoice Header table.
|Invoice Body (InvBdy in Access databases)||Stores all information regarding the invoice body, such as product IDs, product prices for the current invoice, quantities, and line totals. |
If a data item must be included once for each product item, then it is stored in the Invoice Body table.
uses database definition files to provide database structure information. Database definition file creates a completely abstract layer between databases and COM add-in. By using database definition files, 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. 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 thefeature located on the tab of the window, the new database will have the same structure as defined in the current database definition file.
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:
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 undermenu -> -> -> -> . 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 . 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 theworksheet.
When you select a customer by clicking the Customer-Selection icon button on theworksheet, 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 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 theworksheet, 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 thebutton on the 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, 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, 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 theworksheet, the value of the custom field won't be written to the 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 the Reference chapter. For 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 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:
Custom fields are also available on general dialog boxes, such as thewindow, the window and the window. For example, if we add a Children field to the Customer table, the window displays a Custom Fields tab:
Custom fields on thewindow and the window are modifiable, whereas custom fields on the window are read-only.