Computed Fields
Tip: See "Displaying Statistics" if you are looking for information about stats boxes.
A computed field is a special type of data field which can calculate complicated aritmetical expressions and display the resulting value.
Although capable, this field is normally not used for entering information, instead, it is used to create and display a new information using the
available information on other fields.
You may select one of the 3 methods available to do the calculation.
Compute Using: Expression
You may write a single line expression to read and calculate with the data available on neighboring data fields. Scroll down this page to
see example expressions.
Return Type: Numeric / Text / Date
The return type specifies the data type used to save and process data for this field.
Note that the return type cannot be changed once the computed field is created. If you need to change the type,
you should create a new computed field to replace the current one and you must also take into account how to handle editable computed fields
in case you have used that configuration.
Make sure to select the right "return type" when creating a new computed field. e.g. If you expect to
generate a result containing letters, you should choose "text". Choosing a wrong type may affect how the records are sorted and may also
cause loss of data.
A simple example expression for a numeric computed field which displays sum of values entered into "Price" field and "Tax" field would be as following:
NFIELD("price") + NFIELD("tax")
Compute Using: Relational Data Function
This option is used to process data from related records and allows you to choose from 7 built-in function to generate the desired data.
If you did not create any relationships for current catalog, this calculation option will not become available as there is no relational data to process.
Relational Functions
Available functions are as following:
Copy Data From Parent Record:
This option is available only if you have created an appropriate relationship with other catalogs.
Example: If you have a relationship between customer and order catalogs, where you assign many orders to
each customer, you may add a computed field to the order window to display any data from the parent
customer record (e.g. order form may show the customer's email address etc.).
Count Sub Records:
This option is available only if you have created an appropriate relationship with other catalogs.
Example: If you have a relationship between customer and order catalogs, where you assign many orders to
each customer, you may add a computed field to the customer window to display the total number of orders
assigned to that customer.
Get Sum/Average of a Field From Sub Records:
This option is available only if you have created an appropriate relationship with other catalogs.
Example: If you have a relationship between customer and order catalogs, where you assign many orders to
each customer, you may add a computed field to the customer window to display the sum of payments
of all related order records assigned to that customer.
Get Data from Top / Bottom Sub Records:
This option is available only if you have created an appropriate relationship with other catalogs.
This function allows you to find out some data from the top/last related historical record.
Between all existing related records, the one which has either the greatest ("top") or smallest ("bottom") value on the "reference field" is retrieved first.
Data is then read and returned from the field which you have selected as "data field".
Example-1: Assume that, you have a relationship between customer and order catalogs, where you assign multiple orders to
each customer. You may add a computed field to the customer window to display the highest payment ever
among all related order records assigned to that customer.
Both data and reference fields should be selected as the field which shows payment value in this case.
Example-2: Assume that, you have a relationship between customer and service contract catalogs, where you assign
many contracts to each customer. You may add a computed field to the customer window to find out and display
the description of the contract which has the latest expiration date among all related contract records assigned to that customer.
The "reference field" should be selected as the expiration date field and "data field" should be selected as the description field in this case.
Get Data From Previous Record:
This option is used to receive data from the record which was created just before the current record. If a certain data on each record
should be based on the data saved to previous record, this function might be useful. The previous record is determined by choosing
the record with highest ID which is less than current record ID.
Data Path
Select the relational data object to apply the selected function.
Data Field
Select the data field on relational record(s) to apply the selected function.
Compute Using: Procedure
This option can be considered as a more advanced type of expression. You may write a multiline procedure to process available data
to generate a final result. If you need to do the calculation in multiple steps or use conditional statements, loops etc. you should choose this option.
Once the desired result is generated, it can be transfered to the computed field box by the function ReturnValue.
The following computed field procedure takes the sum of values entered into "Price" and "Tax" fields
and then applies 10% discount in case the result is greater than 200.
See Script Editor and Function Reference pages for more information.
Computed Field Options
Auto Recalculate
If set to automatic (default), the field is automatically recalculated every time whenever any other data field on the same record
or on a relational record is changed. Beware that the recalculation can be triggered even if the actual record is not opened.
If at some point, you want the data to freeze and never changed again, e.g. contract terms, invoice pricing etc. you must disable automatic calculation
and use manual calculation (via calculation button) only.
Caution!
Automatic recalculation setting does NOT trigger calculation by time. If the calculation expression contains "NOW" function, the calculation result depends on "current time" or "current date".
That makes the calculation result impossible to remain correct since time continuously changes. If you want the automatic calculation triggered by time (e.g. once a day),
you may easily create a scheduled recalculation task. See scheduled tasks section for more information.
Show Calculate Button
If enabled, a button to trigger the recalculation is displayed on side of the computed field. A calculation button is most useful
if you have turned off automatic calculation and you want to execute the calculation only if you click the button.
Allow Editing
If enabled, users will be able to modify the computed field data freely and overwrite the calculated value. This option is typically enabled together with a
calculate button and automatic calculation is turned off, else, data which you manually entered might be overwritten by automatic recalculation at a later time.
Display Long Text Field
If enabled, the computed field will displayed as a long text field rather than a single line text field. You must enable this option if there is any possibility
that the computed result size is longer than 255 characters.
Suppress Calculation Errors
When the calculation of the computed field expression generates an error, an error icon
will be displayed near the computed field. In some cases the error is a result of empty, unused fields on some records. You may
check this box to prevent that error icon appearing.
Insert Function
Click this button to see and easily insert from a list of available functions.
Recalculate All Records
If you are creating the computed field in a catalog which already contains records, you may
click this button to trigger the recalculation of all records according to the expression you entered. Beware that recalculation
may take time depending on the number of records.
Recommendations To Avoid Calculation Errors
When writing expressions,
- Always use a dot (not comma) when you need to type a decimal mark in a numeric value.
- Always use & character (not "+" sign) when you want to concatenate text strings or functions which return text.
- Make sure that the number of opening parentheses are equal to the number of closing parentheses else it will generate an error.
- Leave a space character between each operator, value or function. Excessive use of spacing won't hurt anything.
- Remember that, expressions are not equations, writing something like s = FIELD("name") will only generate errors.
- Remember that, expressions may not contain multi-line statements, loops, IF-ELSE statements etc which can only be used in procedures.
- Remember that neither function names nor field names are case sensitive. They are displayed here in uppercase for better readability.
- Remember that the field names chosen in the examples below are arbitrary. You must replace the names with the actual field name you used in your database.
To review some example expressions check example expressions
Functions Available by VB Script Engine
Consult the following document for the full list of functions supported by vbscript engine:
VBscript Function Reference (msdn)
VBscript Function Reference (w3schools)
Tip:
There is an application template which shows and guides thru a number of script examples in version 5.5 and later.
You may get a good idea about how to implement computed field expressions or action driven scripts by examining that template.
To import this template, click "Design" menu, select "Import Application Template" and then select the template "Calculation and Scripts".
This will create one new catalog named as "Script Examples". Create a new record on this catalog and check each of the examples explained on that record window.
Built-in Functions by SpeedBase
See function reference page for the full list of available built-in script functions.
See expression examples page to review examples of single-line scripts you may use in computed fields.
See procedure examples page to review examples of multi-line scripts you may use in computed field procedures.