Formula Cheat Sheet

Kendall Kunz

Advanced Math

  • 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 it self, 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 sine of 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.

  • COS(val) – Cosine

    Returns cosine of 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 tangent of 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 in the range 0 (zero) to pi.

  • ATAN(val) – Arctangent

    Returns the arctangent, or inverse tangent, of a number.


    The returned angle is given in radians in the range -pi/2 to pi/2.

  • SINH(val) – Hyperbolic Sine

    Returns hyperbolic sine of 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 hyperbolic cosine of 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 hyperbolic tangent of 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.

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.

Contextual Formulas

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

  • USEREMAIL()

    User’s Email Address

  • USERFIRSTNAME()

    User’s First Name

  • USERLASTNAME()

    User’s Last Name

  • USEREXTERNALID()

    User’s 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 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

    Returns the current answer value of the given field. Useful for avoiding circular reference issues, e.g. when filtering repeated values in a Choices field based on its own current value.

  • PARAM1() – 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 [BETA]

    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.

Data Conversion

  • 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)

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

  • 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

  • DATE(val) – To Date

    Converts the given value to a date value.

  • STRING(val)DATE(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 ☐

  • IMGURL(imgfieldname) – To Image URL

    Generates the web URL to the given image field.

    Useful for assignment into 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 it’s 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

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

    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

  • 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 Organization’s Default time zone.


    e.g. DATETOLOCAL({{mydatefield}}

  • DATETOUTC(localdateval) – Convert Local Date to UTC 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 Organization’s Default time zone.


    e.g. DATETOLOCAL({{mydatefield}})

  • YEAR(dateval) – Year

    Returns the year portion of the specified date value.


    e.g. YEAR({{mydatefield}})

  • MONTH(dateval) – Month

    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 day of the year for the specified date value.

    Values returned are between 1 and 366.


    e.g. DAYYEAR({{mydatefield}})

Excel Financial Formulas

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.

    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’))

  • 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]+’))

  • 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]+’))

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 area for the given location value. Mainly USA specific.


    Matches ‘sub_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 kilometres 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 pipe-seperated string of geo-points.


    e.g. INPOLYGON({{myGpsVal}}, ‘-8.6782523 27.2918257|-8.6672229 28.7094422|-7.6447228 29.3849982’)

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 – 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}}, ‘_’)

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.

    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.

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-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 that 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 that 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 that decided the result (if any) of the named Process Step field.

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. 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}})

  • 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]+’))

  • 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}})

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

  • 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

Supported Devices

iPhone - iPad - iPod

Android

Windows 7 & 10

Standard Features

Powerful Drag N' Drop Form Designer

Secure Web Dashboard

Works Offline

Dispatch & Workflow

Image Capture, Annotation & Sketching

Audio & Video

GPS, Mapping & Navigation

Barcode & NFC Scanning

Signature Capture

Data Lookup From Your Systems

Excel & Word Templates

Email PDF

Formula builder cheat sheet now available on the Knowledge Base.

By Kendall Kunz | Feb 8th, 2017 | Form Design

Share by: