TABLE OF CONTENTS
Whenever you see a "hammer" icon appear next to a field, you can click this icon to launch the Formula Builder. For example, in the Form designer, if you click on a form field and then hover over the Visibility property on the right hand side, you'll see the "hammer" icon appear.
Within the Formula Builder, the list of available formula functions will appear on the right-hand side of the dialog as a drop-down field.
Formula Builder Hints
When you click the drop-down, the list of functions displays; if you mouse over these, you should see detailed hints appear next to most of these functions. These hints explain how to use each function. When you click a function in the list, you'll see it added to the formula text area with placeholders for the required inputs. More broadly, we make use of friendly help prompts heavily through the platform to explain how various functions work.
Whenever you see text with a question mark icon next to it, there's a hint available. We often put quite detailed text into these hints, so they are always worth checking out.
EXAMPLES OF COMMONLY-USED FUNCTIONS
Contextual Functions
Function | Operator | Example | Notes |
| current field's answer | . | . < 10.51 | The current answer must be less than 10.51 |
| today | today() | today() | Return today's date |
| now | now() | now() | Return a timestamp for this instant Note that now() will not return a time value unless used in conjunction with FORMAT-DATE. See this article for more information. |
Math Functions
You must put a space between your math operators in formulae - e.g., {{price1}}-{{price3}} is not valid, but {{price1}} - {{price3}} is.
Function | Operator | Example | Notes |
| addition | + | {{price1}} + {{price2}} | |
| subtraction | - | {{price1}} - {{price2}} | |
| multiplication | * | {{price1}} * {{price2}} | |
| division | div | {{price1}} div {{price2}} | You must use the 'div' operator. Using a '/' will not work |
| greater than | > | {{price}} > 10.51 | Greater than 10.51. Can also be combined with equals: >= |
| less than | < | {{price}} < 10.51 | Less than 10.51. Can also be combined with equals: <= |
| rounding | round(value, power) | round({{price}}, 3) | Return the rounded value of q1, as in Excel |
Text/String Functions
Function | Operator | Example | Notes |
| concatenated string values | concat(value1, value2, ...) | concat({{name}}, ' with id: ', {{national_id}}) | Returns the concatenation of the string values. Add line breaks with '\n' - e.g., concat('Hello', '\n', 'Goodbye') gives: Hello Goodbye |
| extract a substring | substr(value, start) substr(value, start, length) | substr('Test',1, 2) = 'es' | Returns the sub string beginning at the specified zero-indexed start and runs to the end of the string, unless the optional character length is specified. |
| length of a string | string-length(value) | string-length('Test')=4 | Return the length of a non-empty string |
| concatenate values with a separator | join(separatorString, value1, value2, ...) | concat('The answers are: ', join(', ', {{question1}}, {{question2}})) | Returns the concatenation of the answers using the first argument as a separator |
Repeatable Group Functions
Function | Operator | Example | Notes |
| sum repeated values | sum(repeatname) | sum({{prodprice}}) | Returns the sum of answer values for all repeats of a specified question |
| concatenate repeat answer with a separator | join(separatorString, repeatvalue) | concat('The products ordered are: ', join('\n', {{prodname}})) | Returns the concatenation of the repeated answer using the first argument as a separator. Add line breaks with '\n'. |
| count repeats | count(repeatname) | count({{products}}) | Returns a count of a repeatable question/group's occurrences |
| max of repeated values | max(repeatname) | max({{prodprice}}) | Returns the maximum answer value from all repeats of a specified question |
| min of repeated values | min(repeatname) | min({{prodprice}}) | Returns the minimum answer value from all repeats of a specified question |
Logic Functions
Function | Operator | Example | Notes |
| not | not(expression) | not(selected({{select1}}, 'c')) | As long as 'c' is not selected in the specified Choices question |
| and | and | selected(., 'c') and selected(., 'd') | Both 'c' and 'd' need to be selected in the current prompt |
| or | or | selected(., 'c') or selected(., 'd') | Either 'c' or 'd' needs to be selected in the current prompt |
| true | true() | true() | |
| false | false() | false() | |
| regular expression | regex(expression) | regex(., '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') | This particular regex checks for a valid email address |
| equal to | = | . = number('10') | Current answer must be equal to 10 |
| conditional | if(condition, a, b) | if(selected({{question1}}, 'yes') and selected({{question2}}, 'yes'), 'yes', 'no') | If true return a; else return b |
| first non-empty value | coalesce(a, b) | coalesce({{name}}, {{national_id}}) | If name has a value, return name; else return national_id |
| at least X of, at most X of | checklist(min, max, v1, v2, v3, ..., vn) | checklist(min, max, v1, v2, v3, ..., vn) | v1 through vn are a set of n yes/no answers. return true if the count of 'yes' is between min and max, inclusive. min or max may each be -1 to indicate 'not applicable'. |
Data Conversion Functions
Function | Operator | Example | Notes |
| convert to number | number (placeholder or fixed/literal value) | number({{age}}) | Conversion varies depending on data type of x |
| convert to string | string (placeholder or fixed/literal value) | string ({{age}}) | Conversion varies depending on data type of x |
| convert to date | date (placeholder or fixed/literal value) | date('2011-11-12') | Conversion varies depending on data type of x. Format is yyyy-mm-dd |
| boolean from string | boolean-from-string(placeholder or fixed/literal value) | boolean ({{isover30}}) | Returns true if x is "true" or "1", false otherwise. note that this is different behavior than boolean(x) |
| date formatted as string | format-date(placeholder, format) | format-date(today(), 'yyyy-MMM-dd') | Returns the date value of the field formatted as defined by the format argument. The format argument must be a valid .NET date format string. Typical format specifiers include: yy - 2 digit year yyyy - 4 digit year MM - 2 digit month MMM - 3 character abbreviated month dd - 2 digit day There are many more format specifiers available - click here to see full details. |
Choices Functions
Function | Operator | Example | Notes |
| selected | selected(placeholder, value) | selected({{question}}, 'n') | Checks if choices question answer is selected |
| count selected | count-selected(multi-select placeholder) | count-selected({{multiplechoice}}) | Return the number of selected answers |