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, then hover over the Visibility property on the right hand side, you'll see the "hammer" icon appear.

Once in 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, and 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


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 behaviour 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