Knowledgebase

Formula Cheat Sheet Part 1

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

 

 


1. 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() Organisation Name

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

ORGMETA('key')
Organisation Meta Value
Gets the Provider Meta Data Value for the specified key (if any).
For example if your Organisation 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
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 zero’s to the specified pad width length. Concatenate counter() with user data to generate unique numbers.
NOTE: Counter numbers are device-specific.

TASK('key')
Linked Task Data - APP ONLY
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.

 

 


2. SYSTEM VALUES

 

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.

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 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'

 

 

 


3. 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
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

 

 


4. 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 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
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 optional zero-based index is specified, then 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

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

 

 


5. 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 [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 Organisation's Default time zone.

e.g. DATETOLOCAL({{mydatefield}}

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

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 Organisation'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 are 0 through 6 for Sunday through Saturday

 

e.g. 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 into the image field is returned.
The date/time returned is in the local timezone of the device.


 

Formula Cheat Sheet Part 2 Formula Cheat Sheet Part 3
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Designing Forms

After creating a new Form Screen, you will be taken to the Form Design screen.   Every Form must...

Allowing Users to Jump Pages (Custom Form Navigation)

By default, every Form you create has automatic navigation buttons in the form of arrows that...

Dynamic Titles and Hints

Sometimes you may want your field's Title or Hint text to dynamically include a value from a...

Dynamically Displaying Images from Data Source, Web or Map sources

The "Dynamic Image" property allows you to load images dynamically into Media, Sketch and...

Formula Quick Question/Answer Reference

Below is a list of common scenarios that involve the use of a formula to achieve. This is purely...