Examples For Computed Field Expressions

You may write a script for computed fields either as an expression or a procedure.
Expressions do numeric calculation or date/string processing using mathematical operators and built-in functions by VBScript or SpeedBase. When an expression is evaluated, the resulting value is displayed in the computed field box and at some point saved to the database.

When writing expressions,

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.


Concatenating Text Pieces

Add the word "Welcome" in the beginning of a name.
Assume that you have a field to enter name-surname and that field was named as "Full Name".
"Welcome " & SFIELD("Full 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.
AGECALCULATE("dob")     (requires version 5.5 and above)

Caution: Beware that the calculation result of a computed field depending on current time becomes incorrect as time passes. To avoid this problem, you may create a daily recalculation task using the scheduled task window.

For versions older than 5.5 you may use the following expression:
XIF( SFIELD("dob")="" , 0, 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 "YYYY" to calculate number of weeks, months or years respectively.
Replace ""H", "N" or "S" to calculate the difference in hours, minutes or seconds respectively.
DATEDIFF("D", DFIELD("dt2"), DFIELD("dt1") )

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

Caution: Beware that the calculation result of a computed field depending on current time becomes incorrect as time passes. To avoid this problem, you may create a daily recalculation task using the scheduled task window.

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

Caution: Beware that the calculation result of a computed field depending on current time becomes incorrect as time passes. To avoid this problem, you may create a daily recalculation task using the scheduled task window.

Calculate a Past / Future Date

Add a specified number of e.g. 10 days to the date "dt1"
Replace "D" with "W", "M" or "YYYY" 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") ) )

Extract name of the month.
MONTHNAME( MONTH( 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 value of the selected option:
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, you may request support.






Online Help Home Page   ::   SpeedBase Software Home Page