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:

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 (v5)

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,

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

nField("fieldname")

- Gets the current numeric value of the field whose name is given in quotes.
- If the field data type is checkbox, this function returns 1 for a checked box and 0 otherwise.
- If the field data type is option list, this function gets the numeric value assigned to the selected option. (see field properties window)

Examples:
Assume that you have a decimal type field which is named as "price". The example given below calculates the 20% discounted price
NFIELD("price") * 0.8
or
NFIELD("price") / 100 * 80
Assume that you have two numeric fields one is "price", another is "tax rate" (as percentage).
We will create two computed fields, one for "tax value", another for "final price".
This expression will calculate the tax value:
NFIELD("price") * ( NFIELD("tax rate") / 100)
This expression will calculate final price (assume that the previous):
NFIELD("price") + NFIELD("tax value")

sField("fieldname")

Gets the current text content of the field whose name is given in quotes.
When used for a checkbox field, this function returns by default "Yes" for a checked box and "No" otherwise (unless you have modified it from preferences window).
Assume that you have a small text field named as "Name" and an option list named as "Colors" including options of "green","yellow","red" etc. This expression creates and displays a full sentence from existing like "John likes green color.".
sField("Name") + " likes " + sField("Colors") + " color."

dField("fieldname")

Gets the date value of the field whose name is given in quotes. You should use this function to read date data if you want to process the result with other date functions.

XIF(expression, true part, false part)

If the result of the expression is true, evaluates and returns the true part, otherwise evaluates and returns the false part.
Example:
Assume that you have an option list box named "membership" containing the items "standard" and "premium".
Assume also that you wish to apply 20% surcharge to the price when premium is selected.
NFIELD("price") * XIF(sField("membership")="premium", 1.2, 1)

CharCount("fieldname")

Counts the number of characters in a text field.

WordCount("fieldname")

Counts the number of words in a text field.

Limit(Value, MinVal, MaxVal)
Limit("fieldname", MinVal, MaxVal)

Limits the input value to MinVal at minimum and to MaxVal at maximum.
You may use this function to get limited value from a numeric field or select the min. or max. value from two fields.

Examples:
LIMIT ("age", 18 )   returns the value from the "age" field limiting the returned value with 18 at least.
LIMIT ("age", , 30 )   returns the value from the "age" field limiting the returned value with 30 at most.
LIMIT ("age", 18 , 30 )   returns the value from the "age" field limiting the returned value between 18 and 30
LIMIT ("age-1", NFIELD("age-2") )   returns the greater one between the fields "age-1" and "age-2"
LIMIT ("age-1", , NFIELD("age-2") )   returns the smaller one between the fields "age-1" and "age-2"

RoundUp(Value, Digits)   RoundDown(Value, Digits)
RoundUp("fieldname", Digits)   RoundDown("fieldname", Digits)

Rounds the input value upwards/downwards to the specified digits. If you omit digit part, it will round to integer.
Remember to use the Round function instead if you want to round the value towards nearest direction.
If digit is negative, the value is rounded to towards next multiple of power of ten.
Example:
Assume that the decimal field "dec1" has value 3.14
Assume that the decimal field "dec2" has value 2.71
Assume that the decimal field "dec3" has value 151

Round("dec1",1)   returns 3.1
RoundUp("dec1",1)   returns 3.2
RoundDown("dec2")   returns 2
RoundUp("dec3", -1)   returns 160

MD5(Value)

Calculates the MD5 hash of the value. The hash is represented as 32 chars of hexadecimal.



To review some example expressions check example expressions






Online Help Home Page   ::   SpeedBase Software Home Page