Calculation field : function reference |
|
There are several functions that you can use in a calculation field. The functions can be categorized as Mathematical functions, Conditions, and date functions.
Note that the arguments to these functions can be fields in the form or constants.
General functionsif_else(condn, true_part, false_part)Evaluates the condition 'condn' and returns 'true_part' or 'false_part' depending on the evaluation result of the condition.
Examples: if_else(total>1000,10,0) if the value entered in the field total is greater than 1000, return 10. If total is less than or equal to 1000, return 0.
is_empty(input_name)Checks whether the input field is empty or not. Returns 1 if empty 0 if there is value in the field. If the input is only spaces, it is taken as empty.
map_to_number(mapstr,value)map from string value to number. This function is useful for including radio groups or list boxes in a formula. The format of the string value map is "string1 => value1, string2=>value2, string3 => value3 " and so on.
Example: imagine you have a 'subscription' radio option with three cases: none, basic and advanced with cost 0, 100 and 200. The following call can be used in this case: map_to_number("none=>0, basic=>100, advanced=>200",subscription)
Date Functionsdays_between(date1,date2)returns the number of days between two dates ( result = date1 - date2 ) The return value can be negative if date1 is before date2. date1 and date2 should be DateFields.
days_from_today(other_date)returns the number of days from today to other_date. (that is, result = other_date - today) The return value can be negative if other_date is in the past.
age(date)returns the number of years after date. This function can be used to determine the age if you have a date of birth field in your form. If the date is in the future, the return value will be negative. month(date)returns the month of the date. 1 for January and 12 for December. For example, if date is 2/1/2017 (and the date format setting is mm/dd/yyyy) month() function returns 2 year(date)returns the year part of the date. if date is 2/1/2017, the function returns 2017 day_of_month(date)If date is 2/15/2014 (in mm/dd/yyyy format) the function returns 15, the day of the month part. day_of_week(date)returns the day of the week. 1 for Sunday, 7 for Saturday. If date is 17 July 2014, the function will return 5, representing Thursday. Mathematical functionsThe mathematical functions are for doing general mathematical operations. max(p1,p2,p3,...)find the maximum value from the arguments. You can pass any number of arguments.
min(p1,p2,p3,...)find the minimum from the arguments. You can pass any number of arguments.
avg(p1,p2,p3,...)find the average of the passed-in values. You can pass any number of arguments.
abs(num)Returns the absolute value of the number
Examples: abs(-3) is 3 abs(3) is 3 abs(-3.44) is 3.44
ceil(num)returns the number rounded upwards to the nearest integer
Example: ceil(2.1) is 3 ceil(2.001) is 3 ceil(-2.001) is -2
floor(num)returns the value of the number rounded downwards to the nearest integer
Examples: floor(2.1) is 2 floor(2.001) is 2 floor(-2.001) is -3
round(num,dec)rounds the number to dec decimal places. num: the number that should be rounded. dec: the number of decimal places to round
Examples: round(2.1115,2) is 2.11 round(2.1115,3) is 2.112 round(2.5,2) is 2.5 round(2.5,0) is 3
pow(x,y)returns the value of x to the power of y.
Examples: pow(3,2) is 9 pow(3,3) is 27
sqrt(x)calculates the square root of x
log(x)calculates the natural logarithm(Base E) of x
perc_of(total,perc)returns the 'perc' percentage of total. For example, to find the 10% of amount, the formula is perc_of(amount,10)
Example: perc_of(1000,10) is 100
find_perc(fraction,max)calculate the percentage value of fraction, given max. for example: find_perc(100,1000) is 10
Trigonometric functionsThe following trigonometric functions also are supported
sin(x) cos(x) tan(x)
asin(x) acos(x) atan(x)
String literal functionsSimfatic Forms 5 adds String literal functions to the calculation field. Here are the string functions that are supported concat(str1,str2,str3,str4 ...)The concat function concatenates multiple strings in to one. For example, if you have First name and Last name fields in the form and want to create a Full name field, here is the formula that you can use: concat(FirstName,str(" "),LastName) Notice the str(" ") You can't use string literals directly in the formula. In order to use a string literal in the calculation, use str() function.
str(" literal " )The str function is to use a string literal in the calculation. For example, if you want to check the input in a text field is 'yes', here is the formula if_else(txt == str("yes"),1,0)
Deprecated FunctionsThese functions are for backward compatibility. There are alternatives for these functions in the current version. format_currency ( amount, add_comma )The decimal places are rounded to two digits. (example: 12.129 becomes 12.13) If there is only one digit after the decimal point, 0 is appended.(example:12.5 becomes 12.50)
The currency format depends on the localization settings selected in the Form:General page->Localization tab
Examples: format_currency(12,0) $12.00
format_currency(12.1,0) $12.10
format_currency(12.129,0) $12.13
format_currency(1000,0) $1,000.00
is_checked(checkbox_name)Useful for including a checkbox in a formula. Returns 1 if the check box is selected, 0 otherwise.
Example: Suppose subscribing to the newsletter enables a 10% discount. You can do this using formula: is_checked(checkBoxSubscribe) * 10
example chkSubscribe * price
is_checked_grp(value,checkbox_group)This function can be used for including a check box group in a formula. It checks whether the check box with the given value is selected. Remember that one can select multiple options from a check box group. Returns 1 if selected, 0 otherwise.
Example: is_checked_grp("PCMag",Magazines)
example Magazines["PCMag"] * price |
In the current version, you can enable 'Format as currency' in the calculation field properties box instead of using this function.