Formulas
Add a property SalexHub works out from the rest of the record, and look up the 24 functions, the operators and the limits of a formula.
Who can do this: Owners and Admins, or a role with Organization settings.
A formula is a property whose value SalexHub works out from other properties of the same record. Nobody types it, and AI never fills it in.
Add a formula#
Open the properties#
Go to Settings > Data > Properties, choose the Object, then Create property.
Choose the type#
Give it a Label and pick Formula as its Type. Note the Internal name of each property you want to use: a formula refers to properties by it.
Write the formula#
Type the expression in Formula, such as ROUND_MONEY(value * 0.1).
Choose how the result shows#
Leave Result on Automatic, or choose Number, Percent, Currency, Yes or no, Text, Date or Date and time. Then Create property.
The value shows in the table and on the record. You can also ask the Agent to add a formula: it shows you a card to approve first.
Syntax#
| Part | Written as |
|---|---|
| A property | Its internal name: hours, hourly_rate |
| Text | In double or single quotes: "Late" |
| Numbers, yes and no, empty | 1250, 0.15, true, false, null |
| Arithmetic | + - * / % |
| Comparison | == != < > <= >= |
| Logic | && (and), || (or), ! (not) |
| Either or | hours > 8 ? "Long" : "Short" |
| A function | DAYS_UNTIL(visit_date); names are not case-sensitive |
Arithmetic is exact on decimals, so 20.1 * 5 / 100 is 1.005. A division by zero gives an empty value.
An empty input gives an empty answer, never a guess: hours * hourly_rate is empty while hours is, and visit_date < TODAY() is empty, not yes, on a record without a date. IF over an empty condition is empty too.
Functions#
| Function | What it returns | Example |
|---|---|---|
IF(condition, then, else) | then when the condition holds, else else | IF(hours > 8, "Long", "Short") |
COALESCE(a, b, ...) | The first value that is not empty | COALESCE(nickname, name) |
NOT(x) | Yes when x is no or empty | NOT(is_billable) |
OUTCOME(stage) | "won", "lost" or "open", by what the stage's option declares | OUTCOME(stage) == "open" |
CONCAT(a, b, ...) | The values joined as text | CONCAT(code, "-", year) |
UPPER(text), LOWER(text) | The text in capitals, or in lower case | UPPER(code) |
TRIM(text) | The text without spaces at either end | TRIM(name) |
LEN(text) | The number of characters | LEN(notes) |
CONTAINS(text, part) | Yes when the text holds the part, ignoring case | CONTAINS(name, "kit") |
ROUND(x, places) | x rounded half up; negative places round to tens, hundreds | ROUND(1.005, 2) is 1.01, ROUND(1250, -2) is 1300 |
ROUND_MONEY(x), ROUND_MONEY(x, code) | x rounded to its currency's minor unit, or to the one named | ROUND_MONEY(price, "AED") |
FLOOR(x), CEIL(x) | x rounded down, or up, to a whole number | CEIL(hours) |
ABS(x) | x without its sign | ABS(balance) |
MIN(a, b, ...), MAX(a, b, ...) | The lowest, or highest, of the numbers that are not empty | MAX(hours, 1) |
TODAY() | Today's date in your organization's time zone | TODAY() |
DAYS_BETWEEN(from, to) | The days from one date to the other | DAYS_BETWEEN(start_date, end_date) |
DAYS_SINCE(date) | The days since a date, positive while it is past | DAYS_SINCE(last_visit) |
DAYS_UNTIL(date) | The days until a date, positive while it is ahead | DAYS_UNTIL(visit_date) |
YEAR(date), MONTH(date) | The year, or the month from 1 to 12 | YEAR(visit_date) |
REPORTING_CURRENCY() | Your organization's reporting currency code | REPORTING_CURRENCY() |
ROUND_MONEY rounds to two decimals when no currency can be named. A formula that uses TODAY() or a DAYS_ function changes with the day.
Limits#
- A formula reads only its own record's properties. To count or total linked records, use a rollup: Count, Sum, Minimum, Maximum or Average over a relationship. Money never totals across currencies.
- It is an expression and nothing more: no code, no functions of your own, no loops, no way to change a value.
- A formula can use another formula; each is worked out after the ones it reads.
- A save is refused with Invalid formula for a mistake in the expression, and with Formula references unknown properties for a name the object does not have.
- An amount converted into the reporting currency is worked out after formulas, so a formula cannot read it. Use the original amount.
- A person who cannot read one of a formula's inputs does not see the formula either.
- An expression holds at most 2,000 characters.