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.

NOTE: Functions names are not case-sensitive.

1. Contextual	7. Data Conversion
2. System Values	8. Choices
3. Maths	9. Repeats/ Tables
4. Text/String	10. Lists/Sets of Values
5. Date/Time	11. Data Sources
6. Logic	12. Location

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	SUBSTR(value, start) SUBSTR(value, start, length)
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)
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)