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,
- 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.
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")
If you are unsure how to build the right expression, you may request support.