Formula Cheat Sheet

• 1. CONTEXTUAL FORMULAS
• 2. MATH
• 3. TEXT
• 4. DATE / TIME
• 5. LOGIC

• 6. DATA CONVERSION 
• 7. DATA SOURCES
• 8. LISTS / SETS OF VALUES
• 9. REPEATS / TABLES
• 10. CHOICES

• 11. LOCATION 
• 12. PROCESS STEPS
• 13. ADVANCED MATH
• 14. SYSTEM VALUES
• 15. DATA INTERCHANGE

TODAY()
Current Date
 

The current local date reported by the device.
NOTE: Device dates can be inaccurate if the local time is not correct.
 

NOW()
Current Date and Time
 

The current local date and time reported by the device.
NOTE: Device times can be inaccurate if the local time is not correct.

NOTE: Now() will not return a time value unless used in conjunction with FORMAT-DATE. Please see section 6 b for more information.

UTCTODAY()
Current UTC (GMT) Date
 

The current Greenwich Mean Time (GMT) date reported by the device.

UTCNOW()
Current UTC (GMT) Date & Time
 

The current Greenwich Mean Time (GMT) date and time reported by the device.

NOTE: Now() will not return a time value unless used in conjunction with FORMAT-DATE. Please see section 6 below for more information. 

USEREMAIL()
 

User's Email Address
 

USERFIRSTNAME()
 

User's First Name
 

USERLASTNAME()
 

User's Last Name
 

USEREXTERNALID()
 

User's External Id
 

ORGNAME()
 

Organization Name
 

GLOBALVAL('keyname')
Global Value
 

Gets the Global Value for the specified key name (if any)

 

ORGMETA('key')
Organization Meta Value
 

Gets the Provider Meta Data Value for the specified key (if any).
For example if your Organization setup contains a Meta Data key of billing_id then you may access this value across the platform with ORGMETA('billing_id').
 

USERMETA('key')
User Meta Value
 

Gets the User Meta Data Value for the specified key (if any).
For example if your User setup contains a Meta Data key of billing_id then you may access this value across the platform with USERMETA('billing_id').
 

DEVICENAME()
 

The device name.
 

DEVICEOS()
 

The device operating system.
 

DEVICEOSVERSION()
 

The device operating system version.
 

APPVERSION()
 

The current version of the app installed on the user's device.
 

SCREENVERSION()
 

The version number of the current screen on the device.
 

SCREENDATE()
Screen Last Updated (UTC)
 

The date & time on which the current screen on the device was last updated for GMT (UTC) time zone.

 

VAL('dataname')
Direct Value
 

Returns the current answer value of the given field. Useful for avoiding circular reference issues - e.g.,, when filtering repeated values in a Choices field based on its own current value.
 

COUNTER()
Screen Parameter
 

Simple counter that increments by 1 every time a new Form entry is created in the app. Will left pad the counter with zeros to the specified pad width length, specified by an integer inside the parentheses -- e.g., counter(5) will add enough zeroes to make the number 5 digits in length. 

Concatenate counter() with user data to generate unique numbers.
NOTE: Counter numbers are device-specific.
 

TASK('key')
Linked Task Data [BETA]
 

Gets data value for the given key when this Screen is linked to a Task. Returns blank value if no Task is linked.
The following Task keys are available:
ADDITIONALINFO, COMPLETEATLAT, COMPLETEATLON, EXTERNALID, ID, ISOVERDUE, NAME, PERFORMWITHIN, COMPLETEBY, COMPLETEBYUTC, STARTTIME, STARTTIMEUTC
 (e.g.,, TASK('NAME') returns the linked Task's name value)

+
Add
 

Addition operator.
NOTE: Always put a space on either side of the '+'
 

-
Subtract
 

Subtraction operator.
NOTE: Always put a space on either side of the '-'
 

*
Multiply
 

Multiplication operator.
NOTE: Always put a space on either side of the '*'
 

DIV
Divide
 

Division operator.
NOTE: Always put a space on either side of the 'DIV'
 

MOD
Modulo
 

Modulo operator.

MOD is like division but returns the remainder only.
NOTE: Always put a space on either side of the 'MOD'
 

RANDOM(length)
Random Number
 

Generates a random number or string. Can be called with 0 or 1 parameter
RANDOM() returns a decimal number between 0 and 1.0
RANDOM(length) returns random integer of given length
 

ROUND(val, places)
Round
 

Rounds the given number to the specified number of fractional places

 

POW(val, power)
Power
 

Returns the given number  to the specified power
 

TRUNC(val)
Truncate
 

Truncates given number value to an integer.
Effectively rounds number down to zero decimal places.
 

MAX(val1, val2)
Maximum
 

Returns the larger of two numbers
 

MIN(val1, val2)
Minimum
 

Returns the smaller of two numbers
 

CEILING(val)
Ceiling
 

Returns the smallest integer value that is greater than or equal to the specified number.

 

FLOOR(val)
Floor
 

Returns the largest integer less than or equal to the specified number

 

ABS(val)
 Absolute

Returns the absolute (positive) value of a number.
ABS(-5) returns 5
ABS(-5.6) returns 5.6
 

STRING-LENGTH(val)
Length
 

Returns the number of characters in the given value
 

SUBSTR(val, startIndex, lengthOptional)
Substring
 

Retrieves a substring from the given value.
Substrings start at the zero-indexed start position and run to the end of the val unless an optional character length is specified.
e.g., if myfield has value 'ABCDEF', then:
SUBSTR({{myfield}}, 2) gives CDEF
SUBSTR({{myfield}}, 2, 1) gives C
 

CONCAT(val1, val2, val3)
Concatenate
 

Joins the given values end-to-end
 

JOIN('separator', val1, val2)
Concatenate With Seperator
 

Joins the given values end-to-end, separated by the given separator

 

SUBSTITUTE(val, old_text, new_text)
Substitute
 

Substitute’s new_text for old_text into the given value.
e.g., if myfield has value 'ABC|DEF', then:
SUBSTITUTE({{myfield}}, '|', '\n') replaces | with new line
 

LOWER(val)
Lower Case
 

Converts all characters in the specified val to lower case
e.g., LOWER({{myfield}})
 

UPPER(val)
Upper Case
 

Converts all characters in the specified val to upper case
e.g., UPPER({{myfield}})
 

STARTSWITH(val, startswith)
Starts With
 

Returns true or false result depending on if the given starts with text appears at the beginning of the given text value. Matching is case insensitive.
e.g., if myfield has value 'ABCDEF', then:
STARTSWITH({{myfield}}, 'ABC') result is true
 

CONTAINS(val, contains)
Contains Text
 

Returns true or false result depending on if the given contains text appears anywhere within the given text value. Matching is case insensitive.
e.g., if myfield has value 'ABCDEF', then:
CONTAINS({{myfield}}, 'CDE') result is true
 

INDEXOF(input, value, optionalStartIndex, optionalCount)
Index/Position Of Text
 

Gets the zero-based position for the first occurrence of the given value in the input text. Returns -1 if the search value is not found.
Optional start index will begin search at given zero-based index.
Optional count specifies how many characters to search within from the start index.
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
 

SPLIT(input, delimiter, optionalIndex)

Split String

RANDOMSTR(length)
Random String
 

Generates a random string of characters of the given length
 

GUID()
GUID
 

Generates a new Globally Unique Identifier
https://en.wikipedia.org/wiki/Globally_unique_identifier

While this is not an actual function(), you can use the '\n' character in any string or body of text to tell our platform to output text on a new line.

E.g. if myfield has value 'ABC|DEF', then:

SUBSTITUTE({{myfield}}, '|', '\n') replaces | with new line. When saving this text to a data source, make sure that the column data type where this is set to "Multiline Text".

DATEADD(startdate, numberunits, unit)
Add To Date
 

Returns a new Date/Time that adds the specified number of units to the specified starting date value.
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
 

DATEDIFF(startdate, enddate, unit)
Difference Between Dates
 

e.g., DATEDIFF({{mydatefield}}, now(), 'HH')
Unit specifiers are:
YY - whole years
MM - whole months
DD - whole days
HH - whole hours
MI - whole minutes
SS - whole seconds
 

DATETOLOCAL(utcdateval)
Convert UTC Date to Local Date [BETA]
 

Converts the given UTC date time value to local 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 Organization's Default time zone.
e.g., DATETOLOCAL({{mydatefield}}
 

DATETOUTC(localdateval)
Convert Local Date to UTC Date [BETA]
 

Converts the given UTC date time value to local 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 Organization's Default time zone.
e.g., DATETOLOCAL({{mydatefield}})
 

YEAR(dateval)
Year
 

Returns the year portion of the specified date value.
e.g., YEAR({{mydatefield}})
 

MONTH(dateval)
 

Returns the month portion of the specified date value.
e.g., MONTH({{mydatefield}})
 

DAY(dateval)
Day
 

Returns the day portion of the specified date value.
e.g., DAY({{mydatefield}})
 

HOUR(dateval)
Hour


 

Returns the hours portion of the specified date value.
e.g., HOUR({{mydatefield}})
 

MINUTE(dateval)
Minute
 

Returns the minutes portion of the specified date value.
e.g., MINUTE({{mydatefield}})
 

SECOND(dateval)
Second
 

Returns the seconds portion of the specified date value.
e.g., SECOND({{mydatefield}})
 

DAYWEEK(dateval)
Day of Week
 

Returns the numbered day of the year for the specified date value.
Values returned are between 1 and 366.
 e.g., DAYWEEK({{mydatefield}})

Returns the numbered day of the year for the specified date value.

Values returned are between 1 and 366.

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

=
Equal To
 

Returns true if both operands are equal

 

<
Less Than
 

Returns true if the first operand is less than the second
 

>
Greater Than
 

Returns true if the first operand is greater than the second
 

OR
Divide
 

Returns true if any one of the operands is true

 

AND
Modulo
 

Returns true if both of the operands is true
 

NOT(val)

 

Returns true if the value given is false, and false if the value given is true
 

TRUE()

 

Returns true
 

FALSE()
 

Returns false

 

IF(condition, trueval, falseval)
Conditional (if/else)
 

Let's you return one of two values based on whether the given condition is true or false. Useful for toggling a field's dynamic value based on previous answers.
e.g., IF({{score}} > 50, 'YOU PASS', 'YOU FAIL')
 

ISBLANK(val)
Is Blank or Empty
 

Returns true/false based on whether the given value is blank/empty.
An easy way to check if a field has no answer
 

NOTBLANK(val)
NOT Blank or Empty
 

Returns true/false based on whether the given value is not blank/empty.
An easy way to check if a field has any answer.
 

COALESCE(val1, val2)
First Non-Empty Value (coalesce)
 

Use this function when you want to guarantee a non-blank value is returned. This is useful when doing scoring calculations - wrap each answer in a COALESCE({{answer}}, 0) to ensure you get a zero if the user has not provided an answer.

 

REGEX(input, pattern)
Regular Expression Match


 

Returns true/false based on whether the regular expression finds a match in the input string. Regular expressions are a very powerful, advanced feature. Learn about regular expressions.

.NET Regular Expressions - .NET| Microsoft Learn

Regular Expression Language - Quick Reference - .NET | Microsoft Learn

Options for regular expressions - .NET | Microsoft Learn

REPLACE(input, pattern, replacement)
Regular Expression Replacement


 

Replaces the text matched by the given regular expression with the text specified in the replacement string. Note that the regular expression pattern can be static text or passed in from another field.
e.g., REPLACE({{input}}, 'ab*c', '_')
e.g., REPLACE({{input}}, {{regex}}, '_')
 

FORMAT-DATE(val, format)
Format Date/Time To Text


 

Convert a date/time to formatted string value.
e.g., FORMAT-DATE(now(), 'MM/dd/yy H:mm:ss')
outputs like: 06/10/11 15:24:16
Typical format specifies include:
yy - 2 digit year
yyyy - 4 digit year
MM - 2 digit month
MMM - 3 character abbreviated month
dd - 2 digit day
HH - hour in 24 hour clock
mm - 2 digit minute (00-59)
ss - 2 digit second (00-59)
 

Note that this is not an exhaustive list of formatting options. Please refer to this article for more.

FORMAT-GEO(val, format)
Format Location To Text
 

Converts a geo location to formatted text value.
Format options are:
'DMS' - Degrees, minutes and seconds e.g., 41°24'12'N 2°10'26.5'E
'DDS' - Decimal degrees, space delimited e.g., 41.40338 2.17403
'DDC' - Decimal degrees, comma delimited e.g., 41.40338,2.17403
'DIR' - Direction degrees e.g., 0°N
Usage examples:
FORMAT-GEO({{mygpsfield}}, 'DDS')
FORMAT-GEO('41.40338 2.17403', 'DMS')
 

OR
Divide
 

Returns true if any one of the operands is true

AND
Modulo
 

Returns true if both of the operands is true

NOT(val)

 

Returns true if the value given is false, and false if the value given is true.

TRUE()

 

Returns true

FALSE()
 

Returns false

FORMAT-NUM(val, format, optionalCulture)
Format Number To Text
 

Convert a number to a formatted string value.
By default, US formatting is applied; the optional culture parameter lets you specify the target format culture.
e.g., FORMAT-NUM({{numfield}}, '00.00') outputs 4.9675 as: 04.97
e.g., FORMAT-NUM({{numfield}}, '00.00', 'fr-FR')
outputs 4.9675 as: 04,97
Typical format specifies include:
0 - Replaces with digit or zero if none
# - Replaces with digit or nothing if none
. - Sets the decimal separator position
 , - Sets grouping operator position

Consider also for formatting a number into USD currency:

CONCAT('$',FORMAT-NUM({{sampleField}}, '#,###.##', en-US))

DATE(val, 'optionalFormat')
To Date
 

Converts the given value to a date value, optionally using the specified format.
e.g., DATE('12/14/17 5:10:08', 'MM/dd/yy H:mm:ss')
tells the template engine to read the specified value as if it is written in the format 'MM/dd/yy H:mm:ss'.
Field references can also be used with the formula.
e.g., DATE({{myDateField}}, {{myFormatField}})
To always ensure correct interpretation of the specified value to convert, it is recommended that you provide the optional format parameter.
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
HH - hour in 24 hour clock
mm - 2 digit minute (00-59)
ss - 2 digit second (00-59)
 

STRING(val)DATE(val)
To Text
 

Converts the given value to a string value
 

INT(val)
To Integer
 

Converts the given value to an integer value
 

NUMBER(val)
To Number
 

Converts the given value to a numerical value
 

BOOLEAN(val)
To Boolean
 

Converts the given value to a boolean value
 

CBOX(val, matchTo)
To CheckBox (Ticked or Crossed)
 

Returns a Unicode checkbox that is ticked or crossed, depending on whether val equals matchTo.
e.g., CBOX({{myfield}}, 'Yes')
outputs ☑ or ☒
 

CBOXB(val, matchTo)
To CheckBox (Ticked or Blank)
 

Returns a Unicode checkbox that is ticked or blank, depending on whether val equals matchTo.
e.g., CBOXB({{myfield}}, 'Yes')
outputs ☑ or ☐
 

IMGURL(imgfieldname)
To Image URL
 

Generates the web URL to the given image field.
Useful for assignment into a Data Source image column or for providing direct download links in Connector outputs.
 

Generates the web URL to the given file field (e.g. Media or Attachment field types).

Useful for assignment into a Data Source image column or for providing direct download links in Connector outputs.

DSCOUNT(dsId, 'optionalFilterFormula')
Count Rows
 

Counts rows in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page)
e.g., DSCOUNT('STAFF')
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSCOUNT('STAFF', '{{this[2]}} = "BOB"')
counts rows where 3rd column = BOB
 

DSSUM(dsId, columnIndex, 'optionalFilterFormula')
Sum Values in Column
 

Sums column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page).
Refer to the column by it's zero-based index.
e.g., DSSUM('STAFF', 2)
sums the 3rd column's values
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSSUM('STAFF', 2, '{{this[5]}} = "BOB"')
sums 3rd column where 6th column = BOB
 

DSAVG(dsId, columnIndex, 'optionalFilterFormula')
Average Value in Column
 

Averages column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by its External Id (from the Data Source -> Settings page). Refer to the column by its zero-based index.
e.g., DSAVG('STAFF', 2)
averages the 3rd column's values
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSAVG('STAFF', 2, '{{this[5]}} = "BOB"')
averages 3rd column where 6th column = BOB
 

DSMAX(dsId, columnIndex, 'optionalFilterFormula')
Maximum Value in Column
 

Gets maximum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page). Refer to the column by it's zero-based index.
e.g., DSMAX('STAFF', 2)
gets 3rd column's max value
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSMAX('STAFF', 2, '{{this[5]}} = "BOB"')
maxes 3rd column where 6th column = BOB
 

DSMIN(dsId, columnIndex, 'optionalFilterFormula')
Minimum Value in Column
 

Gets minimum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page). Refer to the column by it's zero-based index.
e.g., DSMIN('STAFF', 2)
gets 3rd column's max value
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSMIN('STAFF', 2, '{{this[5]}} = "BOB"')
mins 3rd column where 6th column = BOB
 

DSFIRST(dsId, columnIndex, 'optionalFilterFormula')
First Value in Column


 

Gets first column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page). Refer to the column by it's zero-based index.
e.g., DSFIRST('STAFF', 2)
gets 3rd column's first value
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSFIRST('STAFF', 2, '{{this[5]}} = "BOB"')
first of 3rd column where 6th column = BOB
 

DSLAST(dsId, columnIndex, 'optionalFilterFormula')
Last Value in Column
 

Gets last column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it's External Id (from the Data Source -> Settings page). Refer to the column by it's zero-based index.
e.g., DSLAST('STAFF', 2)
gets 3rd column's last value
Add a filter formula using {{this[column]}} to refer to columns.
e.g., DSLAST('STAFF', 2, '{{this[5]}} = "BOB"')
last of 3rd column where 6th column = BOB
 

LIST(pattern, 'optionalFilterFormula')
List of Values
 

Collects answers from fields with data names matching the given regular expression pattern. List results are typically used with functions like SUM()/COUNT() to calculate results on fields that follow a common naming convention - e.g., survey fields like q1, q2, q3 etc.
Second optional parameter applies a filter formula to the answers gathered, leaving only answers that pass the condition. Use {{this}} to refer to the answer value in formula.
e.g., SUM(LIST('q[0-9]+')) sums answers for fields named q1, q2 etc
e.g., COUNT(LIST('q[0-9]+', '{{this}} = 5')) counts q1,q2 etc fields where answers equal to 5

TOLIST(value, 'optionaldelimiter', 'optionalFilterFormula')
Convert To List
 

Converts the given value to a List. The value must be text containing delimited List elements - e.g., 34|76|9 Second optional parameter is the delimiter character separating elements. Default is pipe character.
Third optional parameter applies a filter formula to the List elements gathered, leaving only elements that pass the condition. Use {{this}} to refer to the element value in formula.
e.g., TOLIST({{myfield}})
e.g., TOLIST({{myfield}}, 'STARTSWITH({{this}}, "B")')
e.g., TOLIST('3,6,9,62', ',', '{{this}} > 5'))
 

TOLIST({{myfield1}}, {{myfield2}}, ..., {{myfieldN}}, 'optionalFilterFormula')

Convert To List (Multi-field)

Convert To List (Multi-field)

Converts the given fields to a List where each field value is an element in the list.

Second optional parameter applies a filter formula to the List elements gathered, leaving only elements that pass the condition. Use {{this}} to refer to the element value in formula.

e.g. TOLIST({{myfield1}}, {{myfield2}}, ..., {{myfield3}})

e.g. TOLIST({{myfield1}}, {{myfield2}}, ..., {{myfield3}},'STARTSWITH({{this}}, "B")')

IN(value, list)
In List of Values
 

Returns true if the given value is found within the given List.
e.g., IN('ABC', LIST('q[0-9]+'))
e.g., IN('ABC', PRIOR('repeatfield'))
 

NOTIN(value, list)
NOT In List of Values
 

Returns true if the given value is NOT found within the given List.
e.g., NOTIN('ABC', TOLIST({{listfield}}))
e.g., NOTIN('ABC', PRIOR('repeatfield'))
 

COUNT(list)
Count List Values
 

Counts the values in the given List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., COUNT(LIST('q[0-9]+'))
 

SUM(list)
Sum List Values
 

Sums the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., SUM(LIST('q[0-9]+'))
 

AVERAGE(list)
Average List Value
 

Averages the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., AVERAGE(LIST('q[0-9]+'))
 

MIN(list)
Minimum List Value
 

Gets minimum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., MIN(LIST('q[0-9]+'))
 

MAX(list)
Maximum List Value
 

Gets the maximum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., MAX(LIST('q[0-9]+'))
 

FIRST(list)
First List Value
 

Gets the first value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., FIRST(LIST('q[0-9]+'))
 

LAST(list)
Last List Value


 

Gets the last value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., LAST(LIST('q[0-9]+'))
 

POSITION({{repeat}})
Repeat/Row Position
 

The page/row number of the current repeat Page or Table row.
Useful for generating incremental numbers for sections/clauses (e.g., 1.1, 1.2, 1.3)
Parameter is the data name of the repeatable page or table.
e.g., POSITION({{repeatpage}})
 

PRIOR('dataname', occurrences)
Prior Repeat Value(s)
 

Gets the previous repeat/row value(s) of the named field within a Page/Table, starting from the current repeat context. Should only be used within a repeatable Page or Table. Optional second parameter specifies how many prior answers to retrieve. Prior repeat values are returned as a List unless the second parameter is 1; if so the value of the last prior answer is returned. Useful for performing aggregate functions on repeat values thus far  (e.g., SUM,COUNT) and, if second parameter is 1, for copying forward the previous repeat/row value into the current new instance.
e.g., PRIOR('myfield') List of all prior answers
e.g., PRIOR('myfield', 1) last prior value only
 

COUNT({{repeat}})
Count Repeats/Rows
 

Counts the repeats/rows of a Page/Table.
Useful for counting rows/repeats captured - e.g., an order line count
e.g., COUNT({{repeatPage}})
 

SUM({{numfield}})
Sum Repeats/Rows
 

Sums a Number field across all repeats/rows of a Page/Table.
Useful for totalling values captured - e.g., an order line total
e.g., SUM({{numberfield}})
 

AVERAGE({{numfield}})
Average Repeat/Rows
 

Averages a Number field across all repeats/rows of a Page/Table.
Useful for aggregating values captured - e.g., an average quantity
e.g., AVERAGE({{numberfield}})
 

SUM(list)
Sum List Values
 

Sums the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()
e.g., SUM(LIST('q[0-9]+'))
 

AVERAGE(list)
Average List Value


 

Averages the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g., AVERAGE(LIST('q[0-9]+'))
 

MAX({{numfield}})
Maximum Repeat Value
 

Maximum value of a Number field across all repeats/rows of a Page/Table.
e.g., MAX({{mynumberfield}})
 

MIN({{numfield}})
Minimum Repeat Value
 

Minimum value of a Number field across all repeats/rows of a Page/Table.
e.g., MIN({{mynumberfield}})
 

FIRST({{repeatfield}})
First Repeat Value
 

Value of the first occurrence/row of a repeatable Page or Table field.
e.g., FIRST({{myrepeatfield}})
 

LAST({{repeatfield}})
Last Repeat Value
 

Value of the last occurrence/row of a repeatable Page or Table field.
 e.g., LAST({{myrepeatfield}})

SELECTED({{choicesfield}}, 'val')
Choice Is Selected
 

Returns true if the value given is selected in the given choices field, false otherwise.

COUNT-SELECTED({{choicesfield}})
Count Selected Choices
 

Returns the number of choices selected on the given choices field.

LAT(locationval)
Latitude
 

Returns the latitude in decimal degrees of the given location value.
 

LON(locationval)
Longitude
 

Returns the longitude in decimal degrees of the given location value.
 

HEADING(locationval)
Heading
 

Returns the heading against true north in decimal degrees of the given location value.

 

ALTITUDE(locationval)
Altitude
 

Returns the altitude above/below sea level in metres of the given location value.

 

ACCURACY(locationval)
Accuracy
 

Returns the accuracy in metres of the given location value.
 

STREETNUM(locationval)
Street Number
 

Returns the street number for the given location value.
Matches 'sub_thoroughfare' on OASIS Specification.
 

STREET(locationval)
Street Name
 

Returns the street name for the given location value.
Matches 'thoroughfare' on OASIS Specification.
 

CITY(locationval)
City / Locality
 

Returns the city / locality name for the given location value.
Matches 'locality' on OASIS Specification.
 

COUNTY(locationval)
County / District
 

Returns the county / district area for the given location value. Mainly USA specific.
Matches 'sub_admin_area' on OASIS Specification
 

STATE(locationval)
State / Province
 

Returns the state / province for the given location value.
Matches 'admin_area' on OASIS Specification.
 

POSTCODE(locationval)
Postal / Zip Code
 

Returns the postal / zip code of the given location value.
Matches 'postal_code' on OASIS Specification.
 

COUNTRY(locationval)
Country Code
 

Returns the ISO country code of the given location value.
Matches 'country' on OASIS Specification.
 

MIBETWEEN(startPoint, endPoint)
Miles Between
 

Finds the miles between two geo-points, using great-circle math.
Geo-points are strings in 'lat lon' format, Location field answers are also geo-points.
e.g., MIBETWEEN('-8.45234 27.7623423', {{myGpsField}})
 

KMBETWEEN(startPoint, endPoint)
Kilometres Between
 

Finds the kilometres between two geo-points, using great-circle math.
Geo-points are strings in 'lat lon' format, Location field answers are also geo-points.
e.g., KMBETWEEN('-8.45234 27.7623423', {{myGpsField}})
 

INPOLYGON(point, polygonPoints)
Is In Polygon (geofence)
 

Returns a true/false answer on whether the given geo-point is within the given polygon.
Polygon values must be pipe-seperated string of geo-points.

 le.g., INPOLYGON({{myGpsVal}}, '-8.6782523 27.2918257|-8.6672229 28.7094422|-7.6447228 29.3849982')

STEP-CURRENT()
Current Step Name
 

Returns the name of the current Step in the process. If no Step has occurred yet, then this function returns a blank value.

 

STEP-ISCURRENT('dataname')
Is Current Step
 

Returns true if the named Process Step field is the current Step in the process. If no Step has occurred yet, this function returns true for ANY Process Step name.

 

STEP-RESULT('dataname')
 

Returns the result of the named Process Step field, if any is set

 

STEP-EMAIL('dataname')
Step User Email
 

Returns the email of the user that decided the result (if any) of the named Process Step field

 

STEP-FIRST('dataname')
Step User First Name
 

Returns the first name of the user that decided the result (if any) of the named Process Step field

 

STEP-LAST('dataname')
Step User Last Name
 

Returns the last name of the user that decided the result (if any) of the named Process Step field

PI()
Pi
 

Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits.
 

DEGREES(angle)
Degrees
 

Converts radians to degrees
 

RADIANS(angle)
Radians
 

Converts degrees to radians
 

SQRT(val)
Square Root
 

Returns a number which, when multiplied by it self, will produce the given value.

 

LOG(val, base)
Logarithm (log)
 

Returns the exponent to which the given base must be raised to produce the given value.

 

SIN(val)
Sine
 

Returns sine of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

COS(val)
Cosine
 

Returns cosine of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

TAN(val)
Tangent
 

Returns tangent of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

ASIN(val)
 

Returns the arcsine, or inverse sine, of a number.
The returned angle is given in radians in the range -pi/2 to pi/2.
 

ACOS(val)
Arccosine
 

Returns the arccosine, or inverse cosine, of a number.
The returned angle is given in radians in the range 0 (zero) to pi.
 

ATAN(val)
Arctangent
 

Returns the arctangent, or inverse tangent, of a number.
The returned angle is given in radians in the range -pi/2 to pi/2.
 

SINH(val)
Hyperbolic Sine
 

Returns hyperbolic sine of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

COSH(val)
Hyperbolic Cosine
 

Returns hyperbolic cosine of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

TANH(val)
Hyperbolic Tangent
 

Returns hyperbolic tangent of given radian angle value.
If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
 

TODAY()

Current Date

The current local date reported by the device.

NOTE: Device dates can be inaccurate if the local time is not correct.

NOW()

Current Date and Time

The current local date and time reported by the device.

NOTE: Device times can be inaccurate if the local time is not correct.

NOTE: Now() will not return a time value unless used in conjunction with FORMAT-DATE. Please see section 6 above for more information. 

UTCTODAY()

Current UTC (GMT) Date

The current Greenwich Mean Time (GMT) date reported by the device.

UTCNOW()

Current UTC (GMT) Date & Time

The current Greenwich Mean Time (GMT) date and time reported by the device.
NOTE: Now() will not return a time value unless used in conjunction with FORMAT-DATE. Please see section 6 above for more information. 

TASK-FIRSTAVAILABLE()
 

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'
 

TASK-FIRSTTOCLAIM()
 

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'

HTTPSTATUS({{restField}})
 

Returns the HTTP status code received when the given REST field last performed a REST request.

e.g., If the REST field named 'myRestField' executes a request successfully then HTTPSTATUS({{myRestField}}) should return a value of '200'.

Learn more about HTTP status codes

JSONVAL({{myjson}}, 'resp.token')

Value From JSON

Returns the value from the given JSON for the given JSONPath query.

Use the optional true/false validate parameter to raise an error if the query fails.

Note: The JSONPath '$.' prefix is not required.

Learn more about JSONPath

e.g., JSONVAL({{myjson}}, 'resp.token')

Test your JSONPath here

JSONLIST({{myjson}}, 'resp.products.id')

List of Values From JSON

Returns a List of values from the given JSON for the given JSONPath query.

Use the optional true/false validate parameter to raise an error if the query fails.

Note: The JSONPath '$.' prefix is not required.

Learn more about JSONPath
e.g., JSONVAL({{myjson}}, 'resp.token')

Test your JSONPath here

XMLVAL({{myxmlfield}}, 'resp/token')

Value From XML

Returns the value from the given XML for the given XPath query.

Use the optional true/false validate parameter to raise an error if the query fails.

Note: The opening XPath '/' is not required.

e.g., XMLVAL({{myxmlfield}}, 'resp/token')

Learn more about XPath

XMLLIST({{myxml}}, 'resp/products/id')

List of Values From XML

Returns a List of values from the given XML for the given XPath query.
Use the optional true/false validate parameter to raise an error if the query fails.

Note: The opening XPath '/' is not required.

e.g., XMLVAL({{myxmlfield}}, 'resp/token')

Learn more about XPath