Formula Cheat Sheet

Example:
IF(ISBLANK({{oneField}}), VAL(‘otherField’), {{oneField}}). This formula will evaluate once when the form loads and thereafter will only ever re-evaluate when the value of ‘oneField’ changes. This formula is not dynamically dependent on ‘otherField’ in any way since it does not contain a dynamic reference {{otherField}}. When the value of ‘otherField’ changes, this formula will not re-evaluate.

e.g. TASK(‘NAME’) returns the linked Task’s name value.

Returns the ‘First Available’ automatic user assignment identifier.

Useful for creating new Tasks that are randomly assigned to the first available user.

Also handy to use as a Process Step field’s ‘Send Form To’ value.

The optional parameter allows you to restrict the Task assignment to only users within the given User Group name or external ID.

Examples of use:

TASK-FIRSTAVAILABLE() will assign randomly to first of all available users

TASK-FIRSTAVAILABLE(‘mygroup’) will assign randomly to first available user in User Group named ‘mygroup’

TASK-FIRSTAVAILABLE({{myfield}}) will assign randomly to first available user in User Group name/ID matching the answer of field with name ‘myfield’

Returns the ‘First To Claim’ user assignment identifier.

Useful for creating new Tasks to be performed by the first user to claim.

Also handy to use as a Process Step field’s ‘Send Form To’ value.

The optional parameter allows you to restrict the Task visibility to only users within the given User Group name or external ID.

Examples of use:

TASK-FIRSTTOCLAIM() Task will be visible to all users

TASK-FIRSTTOCLAIM(‘mygroup’) will be visible to users in User Group named ‘mygroup’

TASK-FIRSTTOCLAIM({{myfield}}) will be visible to users in User Group name/ID matching the answer value of field with name ‘myfield’

e.g. if myfield has value ‘ABC|DEF’, then:
SUBSTITUTE({{myfield}}, ‘|’, ‘\n’) replaces | with new line

e.g. LOWER({{myfield}})

e.g. UPPER({{myfield}})

e.g. if myfield has value ‘ABCDEF’, then:
STARTSWITH({{myfield}}, ‘ABC’) result is true

e.g. if myfield has value ‘ABCDEF’, then:
CONTAINS({{myfield}}, ‘CDE’) result is true

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘A’) returns 0
e.g. INDEXOF(‘AAA|BBB|CCC’, ‘BD’) is -1
e.g. INDEXOF(‘AAA|BBB|CCC’, ‘B’, 5) is 5
e.g. INDEXOF(‘AAA|BBB|CCC’, ‘|’, 4, 3) is 7

e.g. SPLIT({{nfcField}}, ‘,’, 2) returns the 3rd element in the comma seperated list
e.g. SPLIT(‘AAA|BBB|CCC’, ‘|’, 0) is AAA
e.g. SPLIT(‘AAA,BBB,CCC’, ‘,’) is a list with AAA, BBB and CCC as it’s elements
e.g. MAX(SPLIT(‘1-2-5-4-3’, ‘-‘)) is 5
e.g. SPLIT(‘AAA,BBB,CCC’, ‘,’ , 99) is BLANK as there is not 100 elements in the input list

e.g. DATEADD({{mydatefield}}, 6, ‘MM’)

Unit specifiers are:
YY – whole years
MM – whole months
DD – whole days
HH – whole hours
MI – whole minutes
SS – whole seconds

Unit specifiers are:
YY – whole years
MM – whole months
DD – whole days
HH – whole hours
MI – whole minutes
SS – whole seconds

If you wish to exclude weekend days from the calculation, specify the optional true/false parameter to exclude weekends as follows:

e.g. DATEDIFF(‘2017-04-19’, ‘2017-04-27’, ‘DD’, true())

where the above function would return 6 days.

e.g. DATETOLOCAL({{mydatefield}}

Converts the given local date time value to UTC date time.

When used in a Form Design the local time is based on the device’s local time.

If used in a template, local time is based on the Organisation’s Default time zone.

e.g. DATETOUTC({{mydatefield}})

e.g. YEAR({{mydatefield}})

e.g. MONTH({{mydatefield}})

e.g. DAY({{mydatefield}})

e.g. HOUR({{mydatefield}})

e.g. MINUTE({{mydatefield}})

e.g. SECOND({{mydatefield}})

Returns the numbered weekday for the specified date value.

Values are 0 through 6 for Sunday through Saturday

e.g. DAYWEEK({{mydatefield}})

Retur