Invoice Manager for Excel supports custom fields added to Customer, CustomerShipAddress, Product, Invoice Header and Invoice Body database tables.
NOTE: INCORRECT USE OF CUSTOM FIELDS COULD DAMAGE YOUR DATABASE! So please backup your database before modifying it, and make sure that you understand what will happen when you add/modify/delete a field. Contact the service team of Invoice Manager for Excel if you are unsure of the results of a modification.
Introduction - What is custom field?
A custom field is a user-added field that is not defined in the default database structure provided by Invoice Manager for Excel.
For example, the default Customer database table does not have the "fax" field; you can add "fax" as a custom field. By adding "fax" as a custom field to the Customer database table, you essentially add a new column "fax" to the Customer table, so that for each customer account you can store its fax number in the field.
How to use custom field?
The usage of custom field involves two parts - add it to database, map it to an Excel cell.
Creating a custom field in database tables is much easy in Invoice Manager for Excel than in Excel Invoice Manager. Invoice Manager for Excel provides a built-in "Custom Field Manager" which could be opened by clicking the "Customer" button on the "Invoice" ribbon tab, and then click the "Custom Field" button on the toolbar.
Map database field name to Excel cell / range name
Once a custom field is created, how do I use it on the invoice form? For example, if you add a custom field "fax" to the "Customer" database table, how to make it appear on the invoice form on the location you like?
The answer is simple and short - by define a cell name "oknFax". That is, the relationship between a database field and a cell is established by using cell names.
If you add a custom field Fax to the Customer database table, and define a cell name oknFax for a cell, the customer's fax number is automatically filled into the oknFax cell when you pick up a customer by clicking the 'select a customer' icon button on the invoice form.
The cell naming rule is also simple - just add the prefix 'okn' to the database field name. So for example if a database field is named Company, then the corresponding cell should be named oknCompany.
Add custom field to "Customer" database table
If the data you want to add to your invoicing program is an attribute or property of the client, the custom field should be defined in the "Customer" database table. For example, the customer's company name, or fax number. You can also define the corresponding cell / range name on the Excel invoice form. This enables you:
- When you click the "Save As New Customer" button on the "Invoice" worksheet, the new customer account is created and saved along with the information provided in the custom field, such as client's company name or fax number.
- When you pick up a customer on creating invoices by using the in-cell pickup button, or by filling the customer# directly into the correct cell, the information stored in the custom field is shown on the sheet too.
A step by step example on adding custom field to the "Customer" database table could be found at Consultant Template Sample - New Company Name Field.
Add custom field to "Invoice Header" database table
If you want to save the additional data along with an invoice when the "Save Invoice" button or the "Save To DB" button is clicked, and the data appears once for each invoice, the custom field should be defined in the "Invoice Header" database table (i.e. the table named "InvHdr" in the Access database).
In the previous section we use "Client's Company Name" and "Fax Number" as an example of adding custom fields to the "Customer" database table. Since this type of data should be saved along with each invoice, so the custom field should be defined in the "Invoice Header" database table too. Visit the consultant.htmlConsultant Template Sample - New Company Name Field to find out how.
As a comparison, let's consider a "discount" field:
- Unlike "Company Name" and "Fax" number, "Discount" is not an attribute of a client, so that you don't want to fill it when picking up a customer.
- The "Discount" information should be saved along with each invoice, so that you can restore an invoice completely once it is saved.
- The "Discount" appears once for each invoice -- i.e. this is a discount applied to the entire invoice, not an invoiced item.
So unlike the "Company Name" field that is added to both the "Customer" and the "Invoice Header" database tables, such a "Discount" field is added only to the "Invoice Header" database table, as shown on Simple Sample - Discount Amount Field.
Add custom field to "Invoice Body" database table
If you don't want a "Discount" field applied to an entire invoice, but want to show discount in detail for each invoiced item, then you have to add the "Discount" as a column on the invoice detail section. This "discount" value, since it appears more than once for each invoice -- i.e. it appears once for each invoiced item, so it should be defined in the "Invoice Body" database table. An example could be found at Simple Sample - Adding Discount Column.
Add custom field to "Product" database table
If the additional column, as the "Discount" column shown in the previous example, should be loaded when you pick up products / items -- i.e. the additional data represents the property or attribute of a product.
For example, for a clothing store, the "Size" information should be loaded and filled into the form along with unit price. So this field should be added to both the "Invoice Body" and "Product" table. A sample could be found at Clothing Store (Manufacturer) Invoice with Size Breakdown.
The cell naming rules for the "Product" and "Invoice Body" database tables are a bit different. If you add a custom field "ItemSize" to the "Product" database table, on the invoice body of the invoice form, it appears as a column. You name the column of cells "oknItemSize_1", "oknItemSize_2", "oknItemSize_3", and so on. This way, if you pick up a product by clicking the 1st "select a product" icon button, the "ItemSize" information stored in the database will be filled into the "oknItemSize_1" cell; and if you click the 2nd "select a product" icon button, it is filled into "oknitemSize_2" cell.
Rules for applying custom field default values
Each custom field has a "Default Value" property. By default, the default value is empty.
If you assign a non-empty default value, it is applied when a new record is created. In addition to that, the "default value" defined in the following database tables are filled into Excel sheet when you click "Clear & New" command:
- Invoice Header
- Quotation Header
- Purchase Order Header
The order of filling default values is that the default values in the "header" tables are filled first, and then the default values defined in "Customer" or "Supplier" are filled. So, for example if you click "Clear & New" on the "Invoice" worksheet:
- First, the default values of custom fields in the "Invoice Header" table are filled.
- Next, the default values of custom fields in the "Customer" table are filled. If there are custom fields that have the same name defined in both "Invoice Header" and "Customer", but they have different values, the default values defined in "Customer" overwrite default values defined in "Invoice Header".
These documents were originally published on office-kit.com, and were written for Excel Invoice Manager. While Invoice Manager for Excel provides built-in Custom Field Manager that much simplify the usage of custom field, these documents still provides usable information.
Questions and answers
Can a custom field be used on the search bar?
Yes. Once a custom field is added, for example to the Customer table, it appears on the search bar located on the Customers data list window. You can also make the custom field appear on the data list window by clicking the Columns button.
A cell is correctly named, but the value is not filled when it is supposed to do?
- Make sure the cell is unlocked.
- Make sure the cell does not have a formula assigned.
Why custom fields defined for the Invoice Header (InvHdr) and Invoice Body (InvBdy) database tables cannot be edited on the "Invoice Edit" window?
This is by design. While the custom fields defined in the "Customer", "Product", "CustomerShippingAddress" database tables are all editable on the corresponding "Edit" windows. the "Invoice Edit" window does not allow you to edit the value of a custom field.
This is because many of the field values on the invoice form should be calculated based on the data pulled from the "Customer" and "Product" database tables. For example, a line total value is calculated from price and quantity. Only the invoice form knows how to do such calculations, and the "Invoice Edit" window is responsible for displaying those values only.
If you need to change the value of a custom field created for the invoice header or invoice body database tables, simply use the "Edit In Excel" command, and then save the modified invoice using the "Save To DB" command on the "Invoice" worksheet.