Expressions

Computed fields, showIf/validIf, onlyIf, initial values, and action updates are all written in the same formula language — a spreadsheet-style syntax with {field} references and a large built-in function library.

Referencing fields

• {field} — a field on the current record.
• {ref.field} — traverse a reference field to read a field on the linked record (e.g. {product.name}). Chains of references work too: {shift.location.name}.
• {_THISRECORD.field} — inside a query condition, refers to the outer record the query was run from (as opposed to the candidate row currently being tested).
• {_PREVROW.field} / {_NEXTROW.field} — the previous/next row in a list pass.
• {_INDEX} / {_TOTAL} — the current position and total count in a list pass.
• {_FORM.field} — a value entered into a form_action dialog.
• {_THISUser.field} — the logged-in user's own record (id, email, name, etc.).

Logic

IF(cond, then, else), IFS(cond1, val1, cond2, val2, ..., default), AND, OR, NOT, EQUAL, GT, LT, GTE, LTE, ISBLANK, and ROLE("name") (checks a role name against the app's configured roles — handy inside EQUAL(USERPROFILE("role"), ROLE("admin"))).

There are no +, -, *, /, >=, && or || operators — everything is a function call (ADD, SUBTRACT, GTE, AND, …).

Text

CONCATENATE, JOIN, UPPER, LOWER, TRIM, LEN, LEFT, RIGHT, CONTAINS, REPLACE.

Math

ADD, SUBTRACT, MULTIPLY, DIVIDE, MOD, POWER, SQRT, ROUND, ABS, MIN, MAX, AVERAGE, DISTANCEBETWEEN (for two geolocation values).

Dates

NOW(), TODAY(), YESTERDAY(), TOMORROW(), TIMENOW(), DATE, TIME, DAYOFWEEK, STARTOFDAY, ENDOFDAY, STARTOFMONTH(), ENDOFMONTH(), STARTOFYEAR, ENDOFYEAR, YEAR, MONTH, DAY, HOUR, MINUTE, WEEKDAY, DATEDIFF, DATEADD, DATEFORMAT, ISBEFORE, ISAFTER, ISBETWEEN. Compare dates with ISBEFORE / ISAFTER / ISBETWEEN, not GT / LT.

Generating values

RAND(min?, max?), UNIQUEID() and UNIQUECHAR(type, length) produce fresh random values — UNIQUECHAR("alphanumeric", 6) gives a short random code, where type is letters, numeric, alphanumeric, special, or all. They generate a new value every time they're evaluated, so use them as a field's initial value (which runs once when the create form opens), never in a computed field. UNIQUECHAR is random, not guaranteed unique; use UNIQUEID() when a value must never repeat.

Cross-collection queries

These look at other records, not just the current one:

• COLLECTIONSELECT("collection", "condition") — every record matching the condition.
• FILTER("collection", "condition") — same idea, lighter weight.
• REF_ROWS("collection", "foreignKey") — shorthand for the common reverse-lookup case: every record in collection whose foreignKey field points back at this record. Equivalent to COLLECTIONSELECT("collection", "EQUAL({foreignKey},{_THISRECORD.id})"). Takes an optional third argument — REF_ROWS("collection", "foreignKey", "extraCondition") — AND-ed onto the auto-generated equality check. Drop to COLLECTIONSELECT/FILTER directly once you need anything beyond that (sorting, OR logic, multiple extra conditions).
• SELECT("collection", "returnColumn", "condition") — just the values of one column.
• EXISTS("collection", "condition") — true if any record matches.
• COUNT(...), FIRST(...), LAST(...), SUMARRAY(...) — operate on the array a query returns.
• LOOKUP(value, "collection", "lookupColumn", "returnColumn") — single-value lookup.
• ORDERBY / ORDERBYDESCENDING — sorted query results.
• USERPROFILE("field") — a field from the current user's profile record.
• HERE() — the device's current geolocation.

Writing condition arguments

The condition argument of COLLECTIONSELECT / FILTER / EXISTS / SELECT / ORDERBY is evaluated per row. Write it as a plain expression, just like any other argument:

EXISTS("cartitem", EQUAL({product}, {_THISRECORD.product}))

The older style of passing the condition as a quoted string (EXISTS("cartitem", "EQUAL({product}, {_THISRECORD.product})")) still works, but a quoted condition can't contain string literals of its own — so prefer the plain form.

For every function with its exact parameters, see the Schema Reference.

Special-purpose

• IN(value, list) — true if value appears anywhere in an array.
• IFS(...) — first matching condition wins; the trailing argument is the default.