Formula Operators & 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

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

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

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

Function

Operator

Example

Notes

not

NOT(expression)

NOT({{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({{question1}} = 'yes' AND {{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'.

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(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.

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