Formula Functions — the complete reference

A formula field shows a value HARi works out for you from other fields — a full name built from a first and last name, a total of every invoice on a company, or the number of days until a renewal. You never type the answer; you type a short formula and HARi recalculates it automatically whenever the underlying data changes. This page lists every function you can use, grouped by what it’s for, with a plain example of each. To add one, open a field’s settings, choose the Formula field type, and type the expression — HARi checks it as you type and shows a clear message if something is off.

Formulas are one of the most common places non-technical users get tripped up in any tool: a cell that looks empty can still hold hidden empty text that a blank-check misses. HARi’s functions are written to behave the way a business user expects, and where they deliberately differ from a spreadsheet or database, this page says so.

Every function at a glance

Function names work in either case — SUM and sum are the same. Basic math (+ - * / %) and parentheses work as you’d expect.

Group	Functions
Text	`CONCAT`, `TRIM`
Totals across related records	`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `FIRST`, `LAST`, `PLUCK`, `WHERE`
Dates	`TODAY_MINUS_DAYS`, `DATEDIFF`
Choosing a value	`COALESCE`, `IFNULL`, `NVL`, `NULLIF`, `SWITCH`
Related records	`DISPLAY`

Text

Function	What it does	Example
`CONCAT(a, b, …)`	Joins values into one piece of text. Missing values become blank; numbers become text.	`CONCAT(first_name, " ", last_name)` → `Jane Smith`
`TRIM(text)`	Removes leading and trailing spaces. A missing value stays missing.	`TRIM(" hello ")` → `hello`

You can also join two values with the ~ operator: "HK " ~ city.

These work on a list of related records (for example every invoice linked to a company). To use them, list the relation in the field’s related records setting so HARi loads it for the formula.

Function	What it does	Example
`COUNT(list)`	How many records are in the list.	`COUNT(invoices)` → `12`
`SUM(list)`	Adds up the numbers (anything non-numeric is ignored).	`SUM(PLUCK(invoices, "amount"))`
`AVG(list)`	The average of the numbers.	`AVG(PLUCK(reviews, "score"))`
`MIN(list)` / `MAX(list)`	The smallest / largest value — works on numbers, dates, and text.	`MIN(PLUCK(invoices, "issue_date"))`
`FIRST(list)` / `LAST(list)`	The first / last item in the list.	`FIRST(PLUCK(orders, "status"))`
`PLUCK(list, "field")`	Pulls one field’s value out of every record, giving you a list to total or count.	`PLUCK(deals, "value")`
`WHERE(list, "field", "op", value)`	Keeps only the records that match a condition, so you can total a subset.	`SUM(PLUCK(WHERE(deals, "stage", "eq", "won"), "value"))`

WHERE comparisons (op): eq (equals), neq (not equal), gt, gte, lt, lte, in, nin (not in), is_null, is_not_null, contains.

Dates

Function	What it does	Example
`TODAY_MINUS_DAYS(n)`	The date `n` days ago, as `YYYY-MM-DD`. Use it to compare against a stored date.	`TODAY_MINUS_DAYS(30)`
`DATEDIFF(date_a, date_b)`	The number of days between two dates (positive when `date_a` is later).	`DATEDIFF(renewal_date, TODAY_MINUS_DAYS(0))` → days until renewal

Choosing a value

SWITCH picks between several options; the null-handling family below picks the first value that isn’t blank.

Function	What it does	Example
`SWITCH(input, c1, v1, …, [default])`	Compares `input` to each case and returns the matching value; an optional last argument is the fallback.	`SWITCH(tier, "gold", "VIP", "silver", "Priority", "Standard")`
`COALESCE(a, b, …)`	The first argument that isn’t blank; if all are blank, returns nothing.	`COALESCE(nickname, first_name, "Unknown")`
`IFNULL(a, b)` / `NVL(a, b)`	`a` when it has a value, otherwise `b`. (`NVL` is the same function under the name Oracle users know.)	`IFNULL(shipping_note, "None")`
`NULLIF(a, b)`	Nothing when `a` equals `b`, otherwise `a` — handy for turning a placeholder like `"N/A"` back into a blank.	`NULLIF(status, "N/A")`

For a simple inline choice you can also use condition ? value_if_true : value_if_false — e.g. amount > 1000 ? "Large" : "Standard".

The one rule that trips people up: what “blank” means

Zero is a value, not blank. COALESCE(discount, 100) where discount is 0 returns 0, not 100. These functions only skip a missing value or empty text — never a real 0, and never false. (NULLIF compares loosely, so read it as “does this look the same as the value I want to hide?” rather than a strict, type-exact match.)

Worked example: a display name that never shows a stray space

The most common use is a full name when the last name might be missing. This is the exact formula HARi ships for a person’s display name:

TRIM(CONCAT(first_name, " ", COALESCE(last_name, "")))

COALESCE(last_name, "") — use the last name if there is one; otherwise fall back to blank.
CONCAT(first_name, " ", …) — join the first name, a space, and that result.
TRIM(…) — strip the leftover space when the last name was blank.

`first_name`	`last_name`	Result
`John`	`Doe`	`John Doe`
`John`	(blank or missing)	`John`

Without the empty-as-blank rule, a blank last_name would leave "John " with a trailing space, which TRIM then cleans.

Function	What it does	Example
`DISPLAY(relation)`	Shows a linked record’s name (the target’s display field) instead of its ID. Returns nothing when the link is empty.	`DISPLAY(account_manager)` → `Jane Smith`

Like the totals functions, DISPLAY needs the relation listed in the field’s related records setting.

Creating a formula field

Open any entity in Settings → Schema, add or edit a field, choose the Formula field type, and type your expression. Formula fields are calculated at read time and are always read-only — they never store a value, so they stay correct automatically. See Entities and field types for where the Formula type sits alongside text, number, date, and relation fields.