Functions and Formulae
Below is a comprehensive list of FormsFly formula functions that can be used in various field properties throughout the platform wherever the hammer icon is present.
| 1. Contextual | 8. Choices |
| 2. System Values | 9. Repeats/Tables |
| 3. Maths | 10. Lists/Sets of Values |
| 4. Text/String | 11. Data Sources |
| 5. Date/Time | 12. Location |
| 6. Logic | 13. Data Interchange |
| 7. Data Conversion | 14. Process Steps |
1. Contextual
| Purpose | Function |
| User's Email Address | USEREMAIL() |
| User's First Name | USERFIRSTNAME() |
| User's Last Name | USERLASTNAME() |
| User's External Id | USEREXTERNALID() |
| Value Matches Current User | USERINGROUP() |
| Organisation Name | ORGNAME() |
| Global Value | GLOBALVAL('keyname') |
| Organisation Meta Value | ORGMETA('key') |
| User Meta Value | USERMETA('key') |
| Device Name | DEVICENAME() |
| Operating System Name | DEVICEOS() |
| Operating System Version | DEVICEOSVERSION() |
| Current App Version | APPVERSION() |
| Current Screen Version | SCREENVERSION() |
| Screen Last Updated (UTC) | SCREENDATE() |
| Direct Value | VAL('dataname') |
| Screen Parameter | COUNTER() |
| Linked Task Data - APP ONLY | TASK('key') |
| USERLANG() |
2. System Values
| Purpose | Function |
| Current Date | TODAY() |
| Current Date and Time | NOW() |
| Current UTC (GMT) Date | UTCTODAY() |
| Current UTC (GMT) Date & Time | UTCNOW() |
| TASK-FIRSTAVAILABLE() | |
| TASK-FIRSTTOCLAIM() |
3. Maths
You must put a space between mathematical operators. e.g. {{price1}}-{{price3}} is not valid, but {{price1}} - {{price3}} is valid.
| Purpose | Function |
| Add | + |
| Subtract | - |
| Multiply | * |
| Divide | DIV |
| Modulo | MOD |
| Rounding | ROUND(value, decimals) |
| Maximum | MAX(val1, val2) |
| Minimum | MIN(val1, val2) |
| Truncate | TRUNC(val) |
| Power | POW(val, power) |
| Floor | FLOOR(val) |
| Ceiling | CEILING(val) |
| Absolute | ABS(val) |
| Random Number | RANDOM(length) |
4. Text/String
| Purpose | Function |
| Join bits of text together | CONCAT(value1, value2, ...) |
| Get part of a piece of text | |
| Length of a piece of text | STRING-LENGTH(value) |
| Join values with a separator | JOIN(separatorString, value1, value2, ...) |
| Substitute new text for old text | SUBSTITUTE(val, old_text, new_text) |
| Lower Case | LOWER(val) |
| Upper Case | UPPER(val) |
| Starts With | STARTSWITH(val, startswith) |
| Contains Text | CONTAINS(val, contains) |
| Index / Position of Text | INDEXOF(input, value, optionalStartIndex, optionalCount) |
| Split String | SPLIT(input, delimiter, optionalIndex) |
| Random String | RANDOMSTR(length) |
| GUID | GUID() |
| New Line Character | "\n" |
5. Date/Time
| Purpose | Function |
| Add To Date | DATEADD(startdate, numberunits, unit) |
| Difference Between Dates | DATEDIFF(startdate, enddate, unit) |
| Convert UTC Date to Local Date | DATETOLOCAL (utcdateval) |
| Convert Local Date to UTC Date | DATETOUTC (localdateval) |
| Year | YEAR(dateval) |
| Month | MONTH(dateval) |
| Day | DAY(dateval) |
| Hour | HOUR(dateval) |
| Minute | MINUTE(dateval) |
| Second | SECOND(dateval) |
| Day of Week | DAYWEEK(dateval) |
| Day of Year | DAYYEAR(dateval) |
| Week of Year | WEEKYEAR(dateval) |
| Creation Date / Time of Image | IMGDATE(imagefield) |
6. Logic
| Purpose | Function |
| Not | NOT(val) |
| And | AND |
| Or | OR |
| True | TRUE() |
| False | FALSE() |
| Equal to | = |
| Greater than Greater than or equal | > >= |
| Less than Less than or equal | < <= |
| Conditional (if/else) | IF(condition, trueval, falseval) |
| Is Blank or Empty | ISBLANK(val) |
| Not Blank or Empty | NOTBLANK(val) |
| First non-empty value | COALESCE(val1, val2) |
| Regularly used expressions | REGEX(input, pattern) |
| Regular Expression Replacement | REPLACE(input, pattern, replacement) |
7. Data Conversion
| Purpose | Function |
| Convert to number | NUMBER(value) |
| Convert to string | STRING(value) |
| Convert to date | DATE(value) |
| Convert Date to text in a particular format | FORMAT-DATE(value, format) |
| Format Number to Text | FORMAT-NUM(val, format, optionalCulture) |
| Format Location to Text | FORMAT-GEO(val, format) |
| To Integer | INT(val) |
| To Boolean | BOOLEAN(val) |
| To CheckBox (Ticked or Crossed) | CBOX(val, matchTo) |
| To CheckBox (Ticked or Blank) | CBOXB(val, matchTo) |
| To File URL | FILEURL(fieldname) |
8. Choices
| Purpose | Function |
| Check if an answer is selected | SELECTED(dataname, value) |
| Count selected items | COUNT-SELECTED(dataname) |
9. Repeats/Tables
| Purpose | Function |
| Repeat/Row Position | POSITION({{repeat}}) |
| Prior Repeat Values | PRIOR('dataname', occurrences) |
| Count no of repeats | COUNT({{repeat}}) |
| Add repeated values | SUM({{numfield}}) |
| Average Repeat/Rows | AVERAGE({{numfield}}) |
| Median Repeat Value | MEDIAN({{numfield}}) |
| Maximum from a list of repeated values | MAX({{numfield}}) |
| Minimum from a list of repeated values | MIN({{numfield}}) |
| First Repeat Value | FIRST({{repeatfield}}) |
| Last Repeat Value | LAST({{repeatfield}}) |
| Join repeat answers with a separator | JOIN(separatorString, repeatvalue) |
10. Lists/Sets of Values
| Purpose | Function |
| List of Values | LIST(pattern, 'optionalFilterFormula') |
| Convert To List | TOLIST(value, 'optionaldelimiter', 'optionalFilterFormula') |
| Convert To List (Multi-field) | TOLIST({{myfield1}}, {{myfield2}}, ..., {{myfieldN}}, 'optionalFilterFormula') |
| In List of Values | IN(value, list) |
| NOT In List of Values | NOTIN(value, list) |
| Count List Values | COUNT(list) |
| Sum List Values | SUM(list) |
| Average List Value | AVERAGE(list) |
| Median List Value | MEDIAN(list) |
| Minimum List Value | MIN(list) |
| Maximum List Value | MAX(list) |
| First List Value | FIRST(list) |
| Last List Value | LAST(list) |
11. Data Sources
| Purpose | Function |
| Count Rows | DSCOUNT(dsId, 'optionalFilterFormula') |
| Sum Values in Column | DSSUM(dsId, columnIndex, 'optionalFilterFormula') |
| Average Value in Column | DSAVG(dsId, columnIndex, 'optionalFilterFormula') |
| Maximum Value in Column | DSMAX(dsId, columnIndex, 'optionalFilterFormula') |
| Minimum Value in Column | DSMIN(dsId, columnIndex, 'optionalFilterFormula') |
| First Value in Column | DSFIRST(dsId, columnIndex, 'optionalFilterFormula') |
| Last Value in Column | DSLAST(dsId, columnIndex, 'optionalFilterFormula') |
12. Location
| Purpose | Function |
| Latitude | LAT(locationval) |
| Longitude | LON(locationval) |
| Heading | HEADING(locationval) |
| Altitude | ALTITUDE(locationval) |
| Accuracy | ACCURACY(locationval) |
| Street Number | STREETNUM(locationval) |
| Street Name | STREET(locationval) |
| City / Locality | CITY(locationval) |
| County / District | COUNTY(locationval) |
| Postal / Zip Code | POSTCODE(locationval) |
| Country Code | COUNTRY(locationval) |
| Miles Between | MIBETWEEN(startPoint, endPoint) |
| Kilometres Between | KMBETWEEN(startPoint, endPoint) |
| Is In Polygon (geofence) | INPOLYGON(point, polygonPoints) |
13. Data Interchange
| Purpose | Function |
| HTTP status code | HTTPSTATUS({{restField}}) |
| Value From JSON | JSONVAL({{myjson}}, 'resp.token') |
| List of Values From JSON | JSONLIST({{myjson}}, 'resp.products.id') |
| Value From XML | XMLVAL({{myxmlfield}}, 'resp/token') |
| List of Values From XML | XMLLIST({{myxml}}, 'resp/products/id') |
14. Process Steps
| Purpose | Function |
| Current Step Name | STEP-CURRENT() |
| Is Current Step | STEP-ISCURRENT('dataname') |
| Last Completed Step | STEP-COMPLETED() |
| STEP-RESULT('dataname') | |
| Step User Email | STEP-EMAIL('dataname') |
| Step User First Name | STEP-FIRST('dataname') |
| Step User Last Name | STEP-LAST('dataname') |