Below is a comprehensive list of Appenate’s formula functions that can be used in various field properties throughout the platform, wherever the hammer icon is present.
CONTEXTUAL
| USEREMAIL() | User’s Email Address. |
| USERFIRSTNAME() | User’s First Name. |
| USERLASTNAME() | User’s Last Name. |
| USEREXTERNALID() | User’s External Id. |
| USERINGROUP() | Returns True/False if the signed-in user’s group name or external ID matches the given name/external ID.USERINGROUP('group name or 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’s setup contains a metadata 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 metadata key of billing ID, then you may access this value across the platform with USERMETA('billing_id') |
| DEVICENAME() | Returns the system manufacturer and device information. |
| DEVICEOS() | The device operating system. |
| DEVICEOSVERSION() | The current version of the app is installed on the user’s device. |
| DEVICEALIAS() | Returns the user-set name of the device. Note, for iOS devices: iOS 15 and lower returns the device’s user set name. iOS 16 and higher, returns the device’s model / similar to DEVICENAME() |
| APPVERSION() | The current version of the app is 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 | The VAL formula is intended for use when a dynamic dependency would lead to circular reference issues and is not dynamic when used alone, returning the value of the field referenced, ‘dataname’. 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. |
| 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. Concatenate counter() with user data to generate unique numbers. NOTE: Counter numbers are device-specific. |
| EVAL() | Evaluate a formula dynamically by using generic indexed numeric placeholders with a relating comma-separated ordered list of fields to be substituted into the formula. EVAL(“IF({{0}} = {{1}},’True’,’False’”,{{textField1}},{{textField2}}) where {{0}} is replaced with the value of {{textField1}} and {{1}} is replaced with the value of {{textField2}}. Alternatively, a datasource reference containing a formula can also be used to populate the formula value.Example:
|
| TASK(‘key’) Linked Task Data – APP ONLY | Returns the currently set user language in the app settings. e.g. IF(USERLANG() = 'English', true, false)Used in this format, you can control visibility to certain fields in your app, so if a user is English-speaking (for example), then this visibility formula could be used to hide the French fields from them and only show the English fields. |
| USERLANG() | Returns the currently set user language in the app settings. e.g. IF(USERLANG() = 'English', true, false)Used in this format, you can control visibility to certain fields in your app, so if a user is English-speaking (for example), then this visibility formula could be used to hide the French fields from them and only show the English fields. |
SYSTEM VALUES
| TODAY() Current Date | The current local date reported by the device. NOTE: Device dates can be inaccurate if the local time is incorrect. |
| NOW() Current Date and Time | The current local date and time reported by the device. NOTE: Device times may be inaccurate if the local time is not correct. |
| 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. |
| 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 the ‘Send Form To’ value of a Process Step field. 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 the first available user.TASK-FIRSTAVAILABLE('mygroup') will assign randomly to the first available user in the User Group named ‘mygroup’TASK-FIRSTAVAILABLE({{myfield}}) will assign randomly to the first available user in the User Group name/ID matching the answer of the 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 usersTASK-FIRSTTOCLAIM('mygroup') will be visible to users in the User Group named ‘mygroup’TASK-FIRSTTOCLAIM({{myfield}}) will be visible to users in User Group name/ID matching the answer value of the field with name ‘myfield’. |
MATH
| + 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 | Truncates the given number value to an integer. Effectively rounds number down to zero decimal places. |
| TRUNC(val) Truncate | Truncates the given number value to an integer. Effectively rounds the 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 5ABS(-5.6) returns 5.6 |
TEXT
| 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 CDEFSUBSTR({{myfield}}, 2, 1) gives C |
| CONCAT(val1, val2, val3) Concatenate | Joins the given values end-to-end. |
| JOIN(‘seperator’ , {{dataname}}) | Joins the given values end-to-end, separated by the given separator. Examples JOIN(‘-‘ , {{field1}}, {{field2}}, {{field3}}, etc) JOIN(‘|’, {{repeatField}}) |
| SUBSTITUTE(val, old_text, new_text) Substitute | Substitutes 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 value to uppercase. 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 | Splits text input into a List of values based on the specified delimiter character. This resulting List can be used within aggregate functions such as SUM() or COUNT(). If an optional zero-based index is specified, then it returns the single value at the given index or BLANK if the index is not within the list. If an optional zero-based index is specified, then it returns the single value at the given index or BLANK if the index is not within the list. 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. |
| 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 0e.g. INDEXOF('AAA|BBB|CCC', 'BD') is -1e.g. INDEXOF('AAA|BBB|CCC', 'B', 5) is 5e.g. INDEXOF('AAA|BBB|CCC', '|', 4, 3) is 7 |
| SPLIT(input, delimiter, optionalIndex) Split String | Splits text input into a List of values based on the specified delimiter character. This resulting List can be used within aggregate functions such as SUM() or COUNT(). If an optional zero-based index is specified, then it returns the single value at the given index or BLANK if the index is not within the list. If an optional zero-based index is specified, then it returns the single value at the given index or BLANK if the index is not within the list. e.g. SPLIT({{nfcField}}, ',', 2) returns the 3rd element in the comma-separated 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 its elements.e.g. MAX(SPLIT('1-2-5-4-3', '-')) is 5.e.g. SPLIT('AAA,BBB,CCC', ',' , 99) is BLANK as there are not 100 elements in the input list. |
| 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 |
| “\n” New line character | While this is not a function() per se, 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”. |
DATE/TIME
| 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 | Calculates the total number of minutes, hours, days, months, or years between two date/times. 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 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. |
| DATETOLOCAL(utcdateval) Convert UTC Date to Local Date | 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 | 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 Organization’s Default time zone. e.g.DATETOUTC({{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 weekday for the specified date value. Values range from 0 through to 6 for Sunday through Saturday.DAYWEEK({{mydatefield}}) |
| DAYYEAR(dateval) Day of Year | Returns the numbered day of the year for the specified date value. Values returned are between 1 and 366. e.g. DAYYEAR({{mydatefield}}) |
| WEEKYEAR(dateval) Week of Year | Returns the numbered week of the year for the specified date value. Values returned are between 1 and 52. e.g. WEEKYEAR({{mydatefield}}) |
| IMGDATE(imagefield) Creation Date/Time of Image | Returns the original creation date and time of the given image field’s file, as found in the file’s EXIF metadata. If this metadata is not available, the date and time of capture are returned to the image field. The date/time returned are in the local time zone of the device. |
LOGIC
| = 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 | Returns true if any one of the operands is true. |
AND | Returns true if both of the operands are 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 answers. |
| 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 powerful and advanced feature. Learn about regular expressions. .NET Regular Expressions – .NET | Microsoft Learn Regular Expression Language – Quick Reference – .NET | Microsoft Learn Options for regular expression – .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}}, '_')Regular Expressions Examples (Regex) |
DATA CONVERSION
| FORMAT-DATE(val, format) Format Date/Time To Text | Convert a date/time to a formatted string value. Function FORMAT-DATE(now(), 'MM/dd/yy H:mm:ss')Output 06/10/11 15:24:16 Typical format specifies: 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) |
| FORMAT-NUM(val, format, optionalCulture) Format Number To Text | Convert a number to a formatted text 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.97e.g. FORMAT-NUM({{numfield}}, '00.00', 'fr-FR')outputs 4.9675 as: 04,97 Typical format specifiers 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 Learn more about format specifiers |
| FORMAT-GEO(val, format) Format Location To Text | Converts a geo location to a formatted text value. Format options: ‘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 Examples FORMAT-GEO({{mygpsfield}}, 'DDS')FORMAT-GEO('41.40338 2.17403', 'DMS') |
| 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 the 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) 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 ☐ |
| FILEURL(fieldname) To File URL | Generates the web URL to the given file field (e.g., Media or Attachment field types). Helpful in assigning to a Data Source image column or for providing direct download links in Connector outputs. |
DATA SOURCES
| DSCOUNT(dsId, ‘optionalFilterFormula’) Count Rows | Counts rows 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). 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 its External Id (from the Data Source -> Settings page). Refer to the column by its 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 | Average of 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. DSVAG('STAFF', 2)average of the 3rd column’s values Add a filter formula using {{this[column]}} to refer to columns. e.g. DSVAG('STAFF', 2, '{{this[5]}} = "BOB"')average of the 3rd column where 6th column = BOB |
| DSMAX(dsId, columnIndex, ‘optionalFilterFormula’) Maximum Value in Column | Gets the maximum column value 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. DSMAX('STAFF', 2)gets the 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 the minimum column value 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. DSMIN('STAFF', 2)gets the 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 the first column value 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. DSFIRST('STAFF', 2)gets the 3rd column’s first value Add a filter formula using {{this[column]}} to refer to columns. e.g. DSFIRST('STAFF', 2, '{{this[5]}} = "BOB"')this will return the value of the 3rd column of the first row where the 6th column = BOB |
| DSLAST(dsId, columnIndex, ‘optionalFilterFormula’) Last Value in Column | Gets the last column value 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. DSLAST('STAFF', 2)gets the 3rd column’s last value Add a filter formula using {{this[column]}} to refer to columns. e.g. this will return the value of the 3rd column of the last row where the 6th column = BOB |
LISTS/SETS OF VALUES
| 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. The second optional parameter applies a filter formula to the gathered answers, retaining only those that meet the condition. Use {{this}} to refer to the answer value in the 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 are equal to 5 |
| TOLIST(value, ‘optionaldelimiter’, ‘optionalFilterFormula’) Convert To List | Converts the given value to a List. The value must be a text string containing delimited List elements – e.g., 34|76|9. The Second optional parameter is the delimiter character separating elements. Default is pipe character. The 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 a formula. e.g. TOLIST({{myfield}})TOLIST({{myfield}}, 'STARTSWITH({{this}}, "B")')TOLIST('3,6,9,62', ',', '{{this}} > 5') |
| TOLIST({{myfield1}}, {{myfield2}}, …, {{myfieldN}}, ‘optionalFilterFormula’) Convert To List (Multi-field) | Converts the given fields to a List where each field value is an element in the list. The 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 the 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. The 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]+'))e.g. SUM(TOLIST('1|2|3|4|5'))e.g. SUM(TOLIST({{listfield}})) |
| AVERAGE(list) Average List Value | Averages the values in the given numeric List. The parameter must be a valid List function, such as LIST() or PRIOR() e.g. AVERAGE(LIST('q[0-9]+')) |
| MEDIAN(list) | Gets the median value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR() e.g.MEDIAN(LIST('q[0-9]+'))e.g. MEDIAN(TOLIST('1|2|3|4|5'))e.g. MEDIAN(TOLIST({{listfield}})) |
| MIN(list) Minimum List Value | Gets the 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]+')) |
REPEATS/TABLES
| 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. The 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 the 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 answerse.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}}) |
| MEDIAN({{numfield}}) Median Repeat Value | Median value of a Number field across all repeats/rows of a Page/Table. e.g. MEDIAN({{mynumberfield}}) |
| 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}}) |
CHOICES
| 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. |
DATA INTERCHANGE
| 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. e.g. JSONVAL({{myjson}}, ‘resp.token’)Learn more about JSONPath 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. e.g. JSONLIST({{myjson}}, ‘resp.product.id’)Learn more about JSONPath 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. XMLLIST({{myxml}}, ‘resp/products/id’)Learn more about XPath |
LOCATION
| 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 for the given location value. Matches ‘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 kilometers 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 a pipe-seperated string of geo-points. e.g. INPOLYGON({{myGpsVal}}, '-8.6782523 27.2918257|-8.6672229 28.7094422|-7.6447228 29.3849982') |
PROCESS STEPS
| 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-COMPLETED() Last Completed Step | Returns the name of the Step most recently completed in the process. If no Step has been completed yet (e.g., a new form entry in progress on the app), then this function returns a blank value. This function is ideal for use in Connectors because it will always return a value (since a Step is completed as soon as the entry is uploaded). |
| 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 who 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 who 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 who decided the result (if any) of the named Process Step field |
ADVANCED MATHS
| 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 itself, 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 a number which, when multiplied by itself, will produce the given value. |
| COS(val) Cosine | Returns the cosine of the 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 the tangent of the 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, ranging from 0 (zero) to π. |
| ATAN(val) Arctangent | Returns the arctangent, or inverse tangent, of a number. The returned angle is given in radians in the range -π/2 to π/2. |
| SINH(val) Hyperbolic Sine | Returns the hyperbolic sine of the 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 the hyperbolic cosine of the 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 the hyperbolic tangent of the 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. |