BoostExcel main content

Custom Fields - Invoice Manager for Excel

Contents

Invoice Manager for Excel supports custom fields added to the 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, or 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 a 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 do I use custom fields?

The use of a custom field involves two parts: adding it to the database and mapping it to an Excel cell.

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 clicking the "Custom Field" button on the toolbar.

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".

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?

  • Make sure the cell is unlocked.
  • Make sure the cell does not have a formula assigned.

Why can custom fields defined for the Invoice Header (InvHdr) and Invoice Body (InvBdy) database tables not be edited in the "Invoice Edit" window?

This is by design. While the custom fields defined in the "Customer", "Product" and "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's 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.