Custom Fields - Invoice Manager for Excel

Map database field names to Excel cell or range names

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 do you make it appear on the invoice form at the location you like?

The answer is simple and short: 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: 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 a custom field to the "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 or range name on the Excel invoice form. This enables you to:

• 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 the client's company name or fax number.
• When you pick up a customer by using the in-cell pickup button or by filling the customer number directly into the correct cell, the information stored in the custom field is shown on the sheet too.

A step-by-step example of adding a custom field to the "Customer" database table could be found at Consultant Template Sample: New Company Name Field.

Add a custom field to the "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 used "Client's Company Name" and "Fax Number" as examples of adding custom fields to the "Customer" database table. Since this type of data should be saved along with each invoice, the custom field should be defined in the "Invoice Header" database table too. Visit Consultant 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 you don't want to fill it out 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 at Simple Sample: Discount Amount Field.

Add a custom field to the "Invoice Body" database table

If you don't want a "Discount" field applied to an entire invoice but want to show discounts in detail for each invoiced item, then you have to add "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, should be defined in the "Invoice Body" database table. An example could be found at Simple Sample: Adding a Discount Column.

Add a custom field to the "Product" database table

If the extra column, such as the "Discount" column in the previous example, should be loaded when you select products or items, then the extra data constitutes a product's characteristic or feature.

For example, for a clothing store, the "Size" information should be loaded and filled into the form along with the unit price. So this field should be added to both the "Invoice Body" and "Product" tables. 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 the "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 is filled into the Excel sheet when you click the "Clear & New" command:

• Invoice Header
• Quotation Header
• Purchase Order Header
• Customer
• Supplier

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:

1. First, the default values of custom fields in the "Invoice Header" table are filled.
2. 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 have different values, the default values defined in "Customer" overwrite the default values defined in "Invoice Header".