Formula reference
Operators and functions available in computed fields, with examples and what is not supported.
The functions and operators you can use in a computed field. The syntax is the same spreadsheet style used in Sheets, but only the subset below works on records.
Writing a formula
- Use other fields by their key, written as it appears in the Key column, for example
price,unit_cost. The dialog lists the keys you can use under Fields:. - Text goes in double quotes:
"paid". To put a double quote inside text, double it:"say ""hi""". - Numbers are written plainly:
0.18,100. TRUEandFALSEare the yes/no values.- Function names are not case-sensitive. Spaces are ignored.
- A formula can be up to 2,000 characters.
- Cell addresses (such as
A1) and ranges do not exist for records.
Operators
| Operator | Meaning | Example |
|---|---|---|
+ - * / |
Add, subtract, multiply, divide | quantity * unit_price |
-x |
Negative | -discount |
= <> < <= > >= |
Compare (= means "equals", <> "not equal") |
status = "paid" |
& |
Join text | first_name & " " & last_name |
( ) |
Group | (price - cost) / price |
Operations are evaluated in the usual order: brackets, then * /, then + -, then &, then comparisons.
Note: Text comparison is case-sensitive:
"Paid"is not equal to"paid". The power operator^is not supported.
Functions
| Function | What it does | Example |
|---|---|---|
IF(test, then, else) |
Returns then when test is true, else else. The else part is optional; leaving it out gives an empty value. |
IF(total > 1000, "big", "small") |
AND(a, b, …) |
True when all conditions are true | AND(paid = TRUE, total > 0) |
OR(a, b, …) |
True when any condition is true | OR(status = "late", status = "open") |
NOT(a) |
Reverses true and false | NOT(archived) |
ROUND(number, digits) |
Rounds. digits is optional. |
ROUND(total * 0.18, 2) |
ABS(number) |
Removes the minus sign | ABS(balance) |
MIN(a, b, …) |
Smallest value | MIN(quote, estimate) |
MAX(a, b, …) |
Largest value | MAX(quote, estimate) |
SUM(a, b, …) |
Adds its arguments | SUM(net, tax, shipping) |
AVERAGE(a, b, …) |
Average of its arguments | AVERAGE(q1, q2, q3) |
UPPER(text) |
Capital letters | UPPER(code) |
LOWER(text) |
Small letters | LOWER(email) |
CONCAT(a, b, …) |
Joins up to 16 values. CONCATENATE works too. |
CONCAT(first_name, " ", last_name) |
YEAR(date) |
Year of a date | YEAR(order_date) |
MONTH(date) |
Month of a date | MONTH(order_date) |
DAY(date) |
Day of a date | DAY(order_date) |
What is not supported
These are refused when you save, naming the function:
- Any other function, such as
LEN,VLOOKUP,COUNT,TEXT. TODAYandNOW. A computed value is stored, so it would freeze at the time of saving.- The power operator
^. - Cell and range references.
- Arithmetic on dates (adding days to a date).
- Fields that hold lists:
multiselectandrefs.
Result type
The field's own type decides how the result is stored: string, text, number, currency, bool, date or datetime. If the result does not fit the type, the value is left empty and the record still saves.