Below is a comprehensive list of iForms formula functions that can be used in various field properties throughout the platform wherever the hammer icon is present.
|
1. Contextual |
6. Logic | 11. Choices |
| 2. System Values | 7. Data Conversion | 12. Data Interchange |
| 3. Math | 8. Data Sources | 13. Location |
| 4. Text | 9. Lists/Sets Of Values | 14. Process Steps |
| 5. Date/Time | 10. Repeats/Tables | 15. Advanced Maths |
| = 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. https://msdn.microsoft.com/en-us/library/hs600312(v=vs.110).aspx |
| 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) |
| 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 |
| 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') |
| 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) 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). 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 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 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) |
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]+')) |
| 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 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}}) |
| 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}}) |
| Formula Cheat Sheet Part 1 | Formula Cheat Sheet Part 3 |
