Calculations
pro-Forms supports robust calculations that allow you to create powerful and customized forms. Calculations can be used to compute field values, set default values and conditionally control visibility of form questions.
The Basics
To perform a calculation on a question, click a question within the form build section to edit it. Then go to the ’Advanced’ Tab:
Clicking on the 'Visibility' allows you to control when a field should become visible. If the calculation specified evaluates to true then the field is becomes visible. If false then the field becomes hidden.
Clicking on the ’Calculation’ field will then show the calculation window:
The main text area is where the calculation can be added. Using the dropdown above the text area will allow you to use the answers from any form field within your calculation.
Available Functions
Boolean Functions
String Functions
Statistical Functions
Math Functions
Date Functions
Misc Functions
Multi-Response Page Functions
Boolean Functions
| Function | Description | Example |
| TRUE() | returns true |
TRUE(); returns true |
| FALSE() | returns false |
FALSE(); return false; |
| ISTRUE(expression) | returns true if expression is true |
ISTRUE( EXACT("same", "same") ) returns true
ISTRUE( EXACT("same", "not same") ) returns false |
| ISFALSE(expression) | returns false if expression is false |
ISFALSE( EXACT("same", "same") ) returns false
ISFALSE( EXACT("same", "not same") ) returns true |
| IF(logical_test, value_if_true, value_if_false) | Specifies a logical test to perform |
IF( EXACT(1,1), '1 does equal 1', '1 does NOT equal 1') |
| IFS() | Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. |
IFS(78>89, "A", 78>79, "B", 78>69, "C", 78>59, "D", TRUE, "F") returns "C" |
| NOT(expr) | Reverses the logic of the expr. |
NOT( TRUE() ) return FALSE;
NOT( EXACT("same", "not same") ) return true; |
| XOR(......) | Returns a logical exclusive OR of all arguments. |
XOR(3>12,4>6) returns false; Because all test results evaluate to FALSE, FALSE is returned. At least one of the test results must evaluate to TRUE to return TRUE. Another way to say it is, if one, but not both arguments are true, then the result is true. |
| SWITCH(expression , ....) | Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. |
SWITCH(1,1, "One", 2, "Two", "No Match") returns "One" |
String Functions
| Function | Description | Example |
| CONCAT() | Joins several text items into one string |
CONCAT("The", "quick", "brown", "fox"); returns "Thequickbrownfox" |
| CONCAT_WS() | Joins several text items into one text item separated with text specified in first position |
CONCAT_WS(" ", "The", "quick", "brown", "fox"); returns "The quick brown fox" |
| LEFT(text, num_chars) | Returns the leftmost characters from a text value. |
LEFT("The quick brown fox", 6) return "The qu" |
| RIGHT(text, num_chars) | Returns the rightmost characters from a text value. |
LEFT("The quick brown fox", 6) return "wn fox" |
| LEN(text) | Returns the length text value |
LEFT("The quick brown fox") returns 19 |
| LOWER(text) | Returns the text in lowercase. |
LOWER("The quick brown fox") return "the quick brown fox" |
| UPPER(text) | Returns the text in uppercase. |
UPPER("The quick brown fox") return "THE QUICK BROWN FOX" |
| MID(text, start_num, num_chars) | Returns a specific number of characters from a text string starting at the position you specify |
MID("The quick brown fox",5,5) returns "quick" |
| REPLACE(old_text, num_chars, length, new_text) |
Replaces characters within text: old_text: Text in which you want to replace some characters.
|
Replaces five characters in abcdefghijk with a single * character, starting with the sixth character (f) REPLACE("abcdefghijk",6,5,"*") returns "abcde*k"
Replaces the last two digits (09) of 2009 with 10: REPLACE("2009",3,2,"10") returns "2010"
Replaces brown with red: REPLACE("The quick brown fox",11,5,"red") returns "The quick red fox"
|
| TRIM(text) |
Removes spaces from start and end of text. |
TRIM(" fox") returns "fox" |
| EXACT(text1, text2) |
returns true if the strings specified match. This also checks if the case of each text matches |
EXACT("test string", "test string") returns true EXACT("test string", "Test string") return false |
| CONTAINS(needle, haystack) |
returns true if the needle is found within the haystack |
CONTAINS("fox", "The quick brown fox") returns true;
CONTAINS("red", "The quick brown fox") returns false; |
| STRING(value) |
Converts value to a string. |
STRING(5) returns "5"; |
| PADSTART(value, length, padString) |
Pads the value with another string (multiple times, if needed) until the resulting string reaches the given length. The padding is applied from the start of the current string |
PADSTART("1234",8,"*") returns ****1234 |
| PADEND(value, length, padString) |
Pads the value with another string (multiple times, if needed) until the resulting string reaches the given length. The padding is applied to the end of the current string |
PADEND("1234",8,"*") returns 1234**** |
| STARTSWITH(string, startString) |
Determines whether the string begins with the characters of startString, returning true or false as appropriate |
STARTSWITH("The quick brown fox","The") returns true;
STARTSWITH("The quick brown fox","quick") returns false |
| ENDSWITH(string, endString) |
Determines whether the string ends with the characters of endString, returning true or false as appropriate |
ENDSWITH("The quick brown fox","fox") returns true;
STARTSWITH("The quick brown fox","quick") returns false |
| MAXWORDS(value, maxWords) |
Returns true if the value exceeds maxWords |
MAXWORDS("This is a test", 3) returns true;
MAXWORDS("This is a test", 15) returns false; |
| MAKE_ID(prefix, length, type) |
Creates an random ID : prefix - Specify what the ID should start with length - this is the length of the ID (including prefix) type - this is optional. If not specified, it uses all methods below. If specified it can be one of the following:
Please Note: This function does not guarantee that the ID is unique. |
MAKE_ID("ID-", 12) returns (for example) ID-gnIBkaZMO
MAKE_ID("ID-", 12, "upper") returns (for example) ID-OCIBFGUCJ
MAKE_ID("ID-", 12, "numbers") returns (for example) ID-329989321 |
Statistical Functions
| Function | Description | Example |
| AVERAGE(.....) |
Returns the average of all the values specified |
AVERAGE(1,2,3) returns 2 |
| SUM(....) | Returns the sum of all the values specified |
SUM(1,2,3) returns 6 |
| MIN(....) | Returns the minimum of all the values specified |
MIN(1, 2, 3) returns 1 |
| MAX(....) | Returns the maximum of all the values specified |
MAX(1, 2, 3) returns 3 |
| MEDIAN(....) | Returns the median ( the middle number of the sequence ) of all the values specified |
MEDIAN(2, 4, 5, 7, 1, 8, 1) returns 4 |
| MODE(....) | Returns the mode( the number that occurs most often) of all the values specified |
MODE(2, 4, 6, 2, 2) returns 2 |
| RANGE( ... ) | Returns the difference between the largest and smallest number |
RANGE(2, 4, 5, 7, 1, 8, 1 returns 7 |
Math Functions
| Function | Description | Example |
| ADD( value1, value2) | Returns the value of the two numbers supplied added together |
ADD(1,2) return 3 |
| SUBTRACT( value1, value2) | Returns the value of the two numbers supplied subtracted |
SUBTRACT(1,2) returns -1; |
| DIVIDE( value1, value2) | Returns the value of the two numbers supplied divided |
DIVIDE(6, 2) returns 3 |
| MULTIPLY(value1, value2) | Returns the value of the two numbers supplied multiplied |
DIVIDE(6, 2) returns 12 |
| POWER(value1, value2) | Returns the value1 raised to the power of value2 |
POWER(3,2) returns 9 |
| TRUNC(value) | Returns the integer part of a number by removing any fractional digits. |
TRUNC(4.5) returns 4 |
| LOG10(value) | Returns the base 10 logarithm of a number. |
LOG10(2) return 0.3010299956639812 |
| ABS(value) | Returns the absolute (positive) value of a number. |
abs(-100) returns 100 |
| ACOS(value) | Returns the arc cosine of a number. |
acos(0.5) returns 1.05 |
| ASIN(value) | Returns the arc sine of a number. |
ASIN(0.5) returns 0.52 |
| ATAN(value) | Returns the arc tangent of a number in radians. |
ATAN(1) returns 0.7854 |
| ATAN2(value) | Returns the arc tangent of two variables x and y. |
ATAN2(4, 8) returns 0.4636 |
| COS(value) | Returns the cosine of a number. |
COS(2) returns -0.42 |
| FLOOR(value) | Rounds a number down to the nearest integer. |
FLOOR(1.7) returns 1 |
| LOG(value) | Returns the natural logarithm of a number. |
LOG(2) returns 0.69 |
| ROUND(value) | Rounds a number to the nearest integer. |
ROUND(2.5) returns 3 |
| SIN(value) | Returns the sine of a number. |
SIN(3) returns 0.14 |
| SQRT(value) | Returns the square root of a number. |
SQRT(25) returns 5 |
| TAN(value) | Returns the tangent of a number. |
TAN(45) returns 1.62 |
| E() | Return the Euler’s number. |
E() returns 2.7183 |
| LN2() | Returns the natural logarithm of 2. |
LOG2() returns 0.6931 |
| NATLOG10() | Returns the natural logarithm of 10. |
NATLOG10() returns 2.3026 |
| LOG2E() | Returns the base-2 logarithm of E. |
LOG2E () returns 1.4427 |
| LOG10E() | Returns the base-10 logarithm of E. |
LOG10E() returns 0.4343 |
| PI() | Returns the value of PI. |
PI() returns 3.1416 |
| COUNT(value) | Returns the number of selected options in a Multiple Choice or Single Choice element. |
Date Functions
| Function | Description | Example |
| TODAY() | returns todays date |
TODAY() returns "07-11-2022" (It was 07-11-2022 at the time of writing) |
| GETTIME() | returns the current time |
GETTIME() returns 16:15 (It was 16:15 at the time of writing) |
| ISDATEBEFORE( date1, date2) |
returns true if date1 comes before date2 (Please Note: dates need to be in DD-MM-YYYY format) |
ISDATEBEFORE("01-01-2020", "07-11-2022") returns true; |
| ISDATEAFTER( date1, date2) |
returns true if date1 comes afterdate2 (Please Note: dates need to be in DD-MM-YYYY format) |
ISDATEBEFORE("01-01-2020", "07-11-2022") returns false; |
| ADDDAY(days, date) |
Adds number of days to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
ADDDAY(5, "07-11-2022") returns 12-11-2022 |
| ADDWEEK(weeks, date) |
Adds number of weeks to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
ADDWEEK(3, "07-11-2022") returns 28-11-2022 |
| ADDMONTH(months, date) |
Adds number of months to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
ADDMONTH(5, "07-11-2022") returns 07-04-2023 |
| ADDYEAR(years, date) |
Adds number of years to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
ADDYEAR(5, "07-11-2022") returns 07-11-2027 |
| SUBDAY(days, date) |
Subtracts number of days to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
SUBDAY(5, "07-11-2022") returns 17-10-2022 |
| SUBWEEK(weeks, date) |
Subtracts number of weeks to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
SUBWEEK(3, "07-11-2022") returns 28-11-2022 |
| SUBMONTH(months, date) |
Subtracts number of months to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
SUBMONTH(5, "07-11-2022") returns 07-06-2022 |
| SUBYEAR(years, date) |
Subtracts number of years to the specified date (Please Note: dates need to be in DD-MM-YYYY format) |
SUBYEAR(5, "07-11-2022") returns 07-11-2017 |
| DIFFDAYS( date1, date2) |
Returns the difference in days for the two dates specified (Please Note: dates need to be in DD-MM-YYYY format) |
DIFFDAYS("07-11-2022", "12-11-2022") returns 5 |
| DIFFWEEKS( date1, date2) |
Returns the difference in weeks for the two dates specified (Please Note: dates need to be in DD-MM-YYYY format) |
DIFFWEEKS("07-11-2022", "28-11-2022") returns 3 |
| DIFFMONTHS( date1, date2) |
Returns the difference in months for the two dates specified (Please Note: dates need to be in DD-MM-YYYY format) |
DIFFMONTHS("07-11-2022", "07-12-2022") returns 1 |
| DIFFYEARS( date1, date2) |
Returns the difference in years for the two dates specified (Please Note: dates need to be in DD-MM-YYYY format) |
DIFFDAYS("07-11-2020", "07-11-2022") returns 2 |
| DIFFHOURS( time1, time2) |
Returns the difference in time for the two times specified (Please Note: dates need to be in HH:mm format) |
DIFFHOURS("12:00", "13:30") returns 1.5 |
| AGE( date ) |
Returns the age specified by the date (Please Note: dates need to be in DD-MM-YYYY format) |
AGE("26-05-1978") returns 44 |
Misc Functions
| Function | Description | Example |
| ISEMPTY(value) | Returns true if value is empty or blank |
ISEMPTY("") returns true;
ISEMPTY("TEXT") returns false |
| EXACT(value1, value2) | Returns true if the two values match. |
EXACT("test string", "test string") returns true;
EXACT(1, "1") returns false; This is because the first value is a number, the second is a string |
| GREATERTHAN(value1, value2) | Returns true if value1 is greater than value2 |
GREATERTHAN(7, 5) returns true |
| LESSTHAN(value1, value2) | Returns true if value1 is less than value2 |
GREATERTHAN(7, 5) returns false |
| DECIMAL( value, numDecimals) | Converts value to a decimal to the specified decimal places |
DECIMAL( "5", 2) returns 5.00 |
| NUMBER( value ) | Converts value to a number. Returns 0 if the value cannot be converted |
NUMBER("50") returns 50
NUMBER("fifty") returns 0; |
Multi-Response Page Functions
| Function | Description | Example |
| LOOKUP(tag, condition) |
Returns an Array (list of values) of all the answers entered for the question with tag. You can also specify a condition for the lookup. Condition is optional. The values returned can be also used with CONCAT, CONCAT_WS, LEN, SUM, MIN, MAX, ADD, SUBTRACT, DIVIDE, MULTIPLY functions |
LOOKUP("name") returns all the name answers
LOOKUP("name", "age>18") returns all the name answers where the age entered is greater than 18.
CONCAT_WS(' , ', LOOKUP("name")) returns all of the names separated by ,
|
| CALC(calculation, condition) |
Runs a calculation on each row of the multi-response table and returns an Array with the results of each row. Condition is optional The values returned can be also used with CONCAT, CONCAT_WS, LEN, SUM, MIN, MAX, ADD, SUBTRACT, DIVIDE, MULTIPLY functions |
CALC("quantity*price") returns an array with the result of quantity*price for each row in the table
CALC("quantity*price", "inWarranty='No') returns an array with the result of quantity*price for each row in the table where the inWarranty question has been set to 'No'.
SUM( CALC("quantity*price") ) returns the total sum of quantity*price for all rows found. |
| FIRST(list) | Returns the first item found in the array(list) |
FIRST(LOOKUP("name")) returns the first name found |
| LAST(list) | Returns the last item found in the array(list) |
LAST(LOOKUP("name")) returns the last name found |
| NTH(position, list) |
Returns the item found at position in the array(list). Please note that the position is zero based. This means to access the first item use 0, second item use 1, third item use 2 etc. |
NTH(1, LOOKUP("name")) returns the name found in the 2nd row of the table |
| MINDATE(dates) |
returns the earliest date specified in the list (Please Note: dates need to be in DD-MM-YYYY format) |
MINDATE(LOOKUP("visitdate")) returns the earliest visitdate value found |
| MAXDATE(dates) |
returns the latest date specified in the list (Please Note: dates need to be in DD-MM-YYYY format) |
MAXDATE(LOOKUP("visitdate")) returns the latest visitdate value found |