Computed Fields


Tip: See "Adding Statistics" if you are looking for information about stats boxes.

A computed field is an advanced feature which can calculate complicated aritmetical expressions and display the resulting value. This field is not used for entering information, instead, it is used to create and display new information using the available information on other fields. The expression should be typed into the expression box.

Return Type

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 and consider the consequences if there are manually handled values.

Numeric / Text / Date

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

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.

Recalculate Method

Automatic Always: This is the default behavior. The computed field value is recalculated automatically whenever any of the dependent values are changed. Beware that a computed field of an individual record might be automatically recalculated and rewritten to the database, whenever SpeedBase detects a change in any data existing on other records which are referenced on computed field expression. This option should NOT be selected for example when an invoice total is calculated from retrieved values of "current" product prices, as prices may change in the future while totals shown in completed invoices may not.
The field value cannot be edited by user in this option.

Auto on Create, Manual on Update: The computed field value is calculated automatically when you create a new record. Once the record is saved and closed / reopened, it will not be recalculated automatically any more however, you will always have the option to trigger the recalculation by clicking the "recalculate" button shown on right side of the field.
The field value can be edited only if the "allow editing" box is checked and the record is reopened for update purpose.

Auto on Create, Disabled on Update: The computed field value is calculated automatically when you create a new record. Once the record is saved and closed / reopened, its value becomes permanent and cannot be changed or recalculated any more.
The field value cannot be edited by user in this option.

Manuel on Create, Disabled on Update: You may trigger the automatic recalculation only if you click the "recalculate" button shown on right side of the field during creation of a new record. Once the record is saved and closed / reopened, its value becomes permanent and cannot be changed or recalculated any more.
The field value can be edited only if the "allow editing" box is checked and the record is just being created as new.

Manuel on both Create and Update: You may trigger the automatic recalculation only if you click the "recalculate" button shown on right side of the field at both creation or update of a record.
The field value can be edited at all times if the "allow editing" box is checked.


Computed Field Options

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.



Important!
- When you need to use decimal marks, you should always type a dot, not comma (independent from your locale).
- If you want to concatenate text, use always & character, but not a plus sign.


Available functions and examples are given below. Function names are not case sensitive.


Functions Available by Script Engine

Consult the following document for the full list of functions supported by vbscript engine:

VBscript Function Reference (msdn)

VBscript Function Reference (w3schools)


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.

Examples

Remember to replace all field names given in the sample expressions with the actual field name.

Testing a Checkbox Field

Apply 10% discount to the price if a discount approval checkbox was checked.
Assume the price field named as "Price" and checkbox as "apply discount".
nField("Price") * XIF( nField("apply discount")=1, 0.9 , 1 )

Calculate Age From Birth Date

Calculate the current age of a person assuming the date field "dob" is used for saving birthdate.
YEAR(NOW) - YEAR(DFIELD("dob")) + INT( MONTH(DFIELD("dob"))*100 + DAY(DFIELD("dob")) > MONTH(NOW)*100 + DAY(NOW) )

Calculate the Birth Day for Current Year

Calculate the birth day for current year by replacing the birth year with current year, assuming the date field "dob" is used for saving birthdate.
DATESERIAL(YEAR(NOW), MONTH(DFIELD("dob")), DAY(DFIELD("dob")) )

Calculate Number of Days Between Dates

Calculate the number of days between the date fields "dt1" and "dt2"
Replace "D" with "W", "M" or "Y" to calculate number of weeks, months or years.
DATEDIFF("D", DFIELD("dt2"), DFIELD("dt1") )

Calculate the number of days between the date field "dt1" and today.
DATEDIFF("D", DFIELD("dt1"), NOW )

Find Out If a Predefined Date Exceeded

Mark a contract as "expired" if the date saved to the field "contract end time" is before today

XIF( DATEDIFF("d", DFIELD("contract end time"), NOW) > 0, "Expired", "Valid" )

Calculate a Past / Future Date

Add a specified number of e.g. 10 days to the date "dt1"
Replace "D" with "W", "M" or "Y" to add weeks, months or years.
Add a minus sign before the number to calculate a past date.
DATEADD("D", 10, DFIELD("dt1") )

Extract Day / Month / Year from Date

Extract the day from the date "dt1".
Replace the function name "DAY" with "MONTH" or "YEAR" to extract months or years.
DAY( DFIELD("dt1") )

Extract name of the weekday.
WEEKDAYNAME( WEEKDAY( DFIELD("dt1") ) )

Auto-incrementing Field

SpeedBase automatically creates the system field "ID" with each catalog you create.
This field is read-only and is automatically set to the next number with each record you create.
It starts from 1 but if you need to add some offset number you may use a computed field instead:
NFIELD("ID") + SomeOffsetNumber

If you want to assign a more complex alphanumeric number (e.g. order number) to each new record,
you may use MD5 function and preferably use the first n chars. to make it shorter.
LEFT( MD5( NFIELD("ID") ), 8 )
Beware that this function may return the same result for multiple records if you create large number of
records and select a small length of chars.

Count Characters in a Field

Find the number of characters entered into a text field named as "description".
LEN(SFIELD("description"))

Split Text by a Separator Character

Get the text part before the separator character "/" from the field "sampletext"
SPLIT(SFIELD("sampletext"),"/")(0)

Get the text part after the separator character "/" from the field "sampletext"
SPLIT(SFIELD("sampletext"),"/")(1)

Modify Text According to a Selection

Add Mr. or Mrs. at the beginning of the name field "full name" depending on the selection of option list box "gender".
Assuming the option list contains two values: "female" and "male":
XIF(SFIELD("gender")="female", "Mrs. ", "Mr. ") & SFIELD("full name")

Assign Numeric Values to each item of an Option List Box

You may assign a different numeric value to each option item of a drop-down listbox field from the
field properties window. You may then use the nField function to get the option value:
NFIELD("fieldName")

The following expression is required for older versions (v3.1.4 and before) of SpeedBase:
Assign 1 for "Poor", 2 for "Average" and 3 for "Excellent" displayed in an option list box named as "performance".
XIF(SFIELD("performance")="Poor",1,0) + XIF(SFIELD("performance")="Average",2,0) + XIF(SFIELD("performance")="Excellent",3,0)




If you are unsure how to build the right expression, request support.


Online Help Home Page   ::   SpeedBase Software Home Page