Invoice Manager for Excel supports custom fields added to Customer, CustomerShipAddress, Product, Invoice Header and Invoice Body database tables.
This is a chapter of Invoice Manager for Excel's help document. To get the help document, download and install Invoice Manager for Excel, open the default template by clicking "Invoice.xlsx" shortcut on Windows desktop, and then click "Help" on the "Invoice" ribbon tab.
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.
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.
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.
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.
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:
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.
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:
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.
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.
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.
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.
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.
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.