Custom Line Fields

A Custom Line Field (CLF) is an extra column in the grid (body) of Sales Orders and Debtor Invoices. Unlike Extra Fields, the Custom Line Field can perform calculations to manipulate the unit price of the Sales Order/Debtor Invoice line automatically as the line is entered, ensuring that the line total is always quantity * unit price - discount. (While it is possible to have a calculated Extra Field, the calculation is only applied once the Sales Order is saved.)

Three types of Custom Line Field function are available:

Type 0 - No calculation is performed; instead, the Custom Line Field is populated with data from a specified lookup field in the STOCK_ITEMS table.
Type 1 - Automatically recalculates unit price using the formula:

Unit price = CLF value * Unit price per kg / Quantity

Where the Unit price per kg is specified by the PRICEPERKG field of the STOCK_ITEMS table.
Type 2 - Automatically recalculates unit price using the formula:

Unit price = CLF value / Sell price per quantity

Where the Sell price per quantity is specified by the PRICEQTY field of the STOCK_ITEMS table.

Example

A business sells whole fish, where the price is determined by weight - fish are $2 per kg, i.e. the PRICEPERKG field for the FISH stock item is set to $2.00. The Custom Line Field is set up to contain the weight of the fish. The unit price is calculated using the type 1 calculation, by multiplying the weight by the price per kg to determine the unit price.

A single fish weighing 2.5kg is sold - this will cost $5 (2.5kg @ $2 per kg). A value of 2.5 is entered into the CLF column (which has been given the name "Weight"), and the Sales Order line is updated with a unit price of $5 (2.5 * 2), ensuring that the line total is $5.00:

A single fish weighing 3kg is sold - this will cost $6 (3kg @ $2 per kg). A value of 3 is entered into the Weight CLF column, and the Sales Order line is updated with a unit price of $6 (3 * 2), ensuring that the line total is $6.00:

Four fish weighing a total of 6.5kg are sold - this will cost $13 (6.5kg @ $2 per kg). A value of 6.5 is entered into the Weight CLF column, and the Sales Order line is updated with a unit price of $3.25 (6.5 * 2 / 4), ensuring that the line total is $13.00:

Setting Up Custom Line Functions

To set up EXO Business for Custom Line Fields:

Add fields to the EXO Business database to hold the CLF data.
Set up the Extra Fields that will be used by the CLF calculations.
Configure the CLF profile settings to activate and set up the CLF column.

Setting up the Database

A database field must be added to the DR_INVLINES and SALESORD_LINES tables to store the data that is entered in the CLF field. Whatever name is chosen for this field, it must be the same in both tables.

This field can be added from SQL Server Management Studio, or by using the Add Field button when setting up Extra Fields:

Note: While you can add the new field from the Extra Fields screen, it cannot be set up as a visible Extra Field; if it is, errors will appear when you try to process a Sales Order or Debtor Invoice. After clicking Add Field and setting up the new database field, click Cancel to exit without setting up an Extra Field.

The Stock item fields that are used by the CLF calculations, PRICEPERKG and PRICEQTY, are added automatically.

Note: Prior to EXO Business 8.4, the PRICEPERKG and PRICEQTY fields had to be added manually. As the PRICEQTY field had to be set up as an Extra Field, it would be called X_PRICEQTY. In an upgrade to EXO Business 8.4 or later, if an X_PRICEQTY field is detected in the STOCK_ITEMS table, it is renamed to PRICEQTY. A warning will appear on the Database Update window if this happens.

Setting up Extra Fields

If you are using the type 1 or type 2 calculations, the PRICEPERKG or PRICEQTY field must be added to the Stock screen as an Extra Field, so that data can be entered into it.

To set up Extra Fields for Custom Line Fields:

Open EXO Business Config and go to the System > Extra Fields section.
Click New.
Select "STOCK_ITEMS" for the Table name.
For the Field name, select "PRICEPERKG" if you are using the type 1 calculation, or "PRICEQTY" if you are using the type 2 calculation.
Set a descriptive Display name, e.g. "Price per kg".
Enter a Position value to determine where the field will appear on the Extra Fields tab of the Stock item window.
Click Save.

The Extra Field now appears on the Extra Fields tab of the Stock item window, allowing this information to be set for any stock item:

Setting up Profile Settings

The following Company-level profile settings are used to set up Custom Line Functions:

Enable custom line field

This setting controls whether or not CLF functionality is available. It is disabled by default.

Custom line field (CLF) calculation type

This setting specifies the calculation function that the Custom Line Fields will use. Enter 0 (no calculation), 1 (CLF field * Price per kg) or 2 (CLF field / Price quantity).

Note: In previous versions, a calculation type 3 was available. This calculation type is now redundant, but the system still accepts a value of 3 here, to remain compatible with older versions.

Custom line field (CLF) column name

This setting specifies the name of the CLF column as it appears on the Sales Orders/Debtor Invoices screen.

Custom line field (CLF) database field name

This setting specifies the database field in the DR_INVLINES and SALESORD_LINES tables that the CLF data will be stored in. This is the field that you created in the previous step.

Custom line field (CLF) data type

This setting specifies the type of data that the CLF will contain. Choose "Number" or "Text".

Custom line field (CLF) number of decimal places

If the CLF contains numeric data, i.e. if the data type is set to "Number", this setting specifies the number of decimal places to be used.

Custom line field (CLF) stock item lookup field

This setting applies when the calculation type is set to "0". It specifies the field from the STOCK_ITEMS table that will be used to populate the CLF field.

Custom line field (CLF) passed to debtor invoice line

If this setting is enabled, the CLF value from the Sales Order is passed to the CLF field on the related Debtor Invoice.

Custom line field (CLF) width

The setting specifies the width (in characters) of the CLF column on the Sales Orders/Debtor Invoices screen.

Example Configuration

In the previous example, the CLF profile settings are set as follows: