Functions — Sterodata docs

Every function a formula can use, grouped by kind: what each takes, what it gives back, and an example you can copy.

A function takes values in brackets and gives one value back: ROUND(Amount * Rate, 2). This page lists every function the formula box reads, grouped by what it works on; the app and the server read the same list of names. Write function names in capitals or not, and separate the values with commas.

How formulas are written, with operators, field names and conditions, is described in Formulas.

Text

Function	Takes	Gives	Example
`CONCAT`	Two or more values	Text: the values joined in order. `a \|\| b` does the same.	`CONCAT("First name", ' ', "Last name")`
`LOWER`	Text	The text in small letters	`LOWER(Email)`
`UPPER`	Text	The text in capitals	`UPPER(Code)`
`LENGTH`	A text, or a field that holds several items	A number: the characters in a text, or the items in a Multiple select, an Attachment field or a lookup with several matches. Not for a lookup with one match.	`LENGTH(Photos)`
`SEARCH`	Words in single quotes, then one or more fields	A number from 0 to 1: how well the words match the start of the fields' text, capitals ignored. A match in an earlier field counts for more.	`SEARCH('acme', Name, City)`
`FUZZY_MATCH`	Two texts	A number from 0 to 100: how alike the two texts are, their words compared in any order. On tables kept in Sterodata only.	`FUZZY_MATCH(Name, "Name on invoice")`

Numbers

Function	Takes	Gives	Example
`ROUND`	A number, and optionally how many decimals, written into the formula as a whole number	The number rounded, halves away from zero: 2.5 becomes 3 and -2.5 becomes -3. Without decimals, a whole number. An amount stays an amount in its currency.	`ROUND(Amount * Rate, 2)`
`LEAST`	Two or more values of one kind	The smallest. Empty values are passed over; the answer is empty only when every value is.	`LEAST(Price, "Price cap")`
`GREATEST`	Two or more values of one kind	The largest, passing over empty values the same way	`GREATEST(Balance, 0)`

Conditions and empty values

Function	Takes	Gives	Example
`IF`	A condition, a value for true, and optionally a value for otherwise	The first value when the condition is true, else the second, or empty without one	`IF(Balance > 0, 'Due', 'Paid')`
`CASE`	`WHEN` conditions, each with a `THEN` value, an optional `ELSE`, and `END`	The value of the first condition that is true	`CASE WHEN Score >= 90 THEN 'A' WHEN Score >= 75 THEN 'B' ELSE 'C' END`
`COALESCE`	Two or more values	The first that is not empty. `IFNULL` is another name for it.	`COALESCE(Discount, 0)`

Every answer of an IF or a CASE, and every value of LEAST or GREATEST, must be one kind of value: all text, all numbers, all dates or all yes-or-no values.

Changing a value's kind

Function	Takes	Gives	Example
`TO_NUMBER`	A value, usually text	A number, or empty when the value cannot be read as one	`TO_NUMBER("Imported price")`
`TO_TEXT`	Any value	The value as text	`TO_TEXT(Quantity)`
`TO_DATETIME`	A value, usually text such as `2026-10-29` or `2026-10-29 14:30:00`	A date and time, or empty when the value cannot be read as one	`TO_DATETIME("Date as text")`
`TO_BOOLEAN`	A value, such as the text `true` or `false`	A yes-or-no value, or empty when the value cannot be read as one	`TO_BOOLEAN("Active flag")`

Dates

These are explained with more examples in Dates and time.

Function	Takes	Gives	Example
`TODAY`	Nothing: `TODAY()`	Today's date, without a time	`TODAY() - "Due Date"`
`YEAR`	A date	The year, as a whole number	`YEAR("Ordered on")`
`MONTH`	A date	The month, from 1 to 12	`MONTH("Ordered on")`
`DAY`	A date	The day of the month, from 1 to 31	`DAY("Ordered on")`
`DATE_TRUNC`	One of `'year'`, `'quarter'`, `'month'`, `'week'` or `'day'`, then a date	The first day of the year, quarter, month or week the date falls in, or the day itself	`DATE_TRUNC('month', "Ordered on")`

A date function needs a date. To use one on a date kept as text, wrap the text in TO_DATETIME first: YEAR(TO_DATETIME("Date as text")).

JSON fields

Function	Takes	Gives	Example
`JSON_EXTRACT`	A JSON field, and a path in single quotes: keys joined by dots, list positions in square brackets counting from 0	The value at that path, still as JSON. Wrap it in `TO_TEXT` or `TO_NUMBER` to use it as text or a number.	`TO_NUMBER(JSON_EXTRACT(Data, 'Items[0].Price'))`

Values over several rows

These functions work a value out over a set of rows, such as a running total or a rank, and give it to each row. You write them with OVER (…), which says which rows count:

PARTITION BY splits the rows into groups, each worked out on its own: one running total per customer.
ORDER BY puts each group's rows in order. Add DESC for largest or latest first.
ROWS sets which rows around the current one count: ROWS UNBOUNDED PRECEDING for every row up to this one, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for this row and the two before it.

Function	Takes	Gives	Example
`SUM`	A number or an amount	The total over the rows	`SUM(Amount) OVER (PARTITION BY Customer ORDER BY "Ordered on" ROWS UNBOUNDED PRECEDING)`
`COUNT`	A field, or `*` for rows	How many rows have a value, as a whole number	`COUNT(*) OVER (PARTITION BY Customer)`
`AVG`	A number or an amount	The average over the rows	`AVG(Amount) OVER (PARTITION BY Region)`
`MIN`	A value	The smallest over the rows	`MIN("Ordered on") OVER (PARTITION BY Customer)`
`MAX`	A value	The largest over the rows	`MAX(Amount) OVER (PARTITION BY Customer)`
`GROUP_CONCAT`	Text	The rows' texts joined into one	`GROUP_CONCAT(Product) OVER (PARTITION BY "Order")`
`ROW_NUMBER`	Nothing: `ROW_NUMBER()`, with an `ORDER BY`	1, 2, 3 and on, in the order given	`ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY "Ordered on")`
`RANK`	Nothing: `RANK()`, with an `ORDER BY`	The position in the order, ties sharing one: 1, 2, 2, 4	`RANK() OVER (ORDER BY Total DESC)`
`LAG`	A value, optionally how many rows back (a whole number, 1 if left out) and a value for when there is no such row	The value from an earlier row	`LAG(Reading, 1, 0) OVER (PARTITION BY Meter ORDER BY "Read on")`
`LEAD`	The same as `LAG`	The value from a later row	`LEAD("Start date") OVER (PARTITION BY Employee ORDER BY "Start date")`

ROW_NUMBER, RANK, LAG and LEAD work only with OVER (…). In a formula field, use SUM, COUNT, AVG, MIN, MAX and GROUP_CONCAT with OVER (…) as well.
Only ROWS sets which rows count. Write the whole OVER (…) in each formula: a window cannot be given a name and reused.
DISTINCT cannot be used over rows like this.
To count or add up only some rows, put the condition inside: SUM(CASE WHEN Paid THEN Amount END) OVER (PARTITION BY Customer).

For a running total without typing, choose Window Function instead of Custom Formula when you add the field. Pick the Column to add up, the fields to Partition by and the Sort within groups. It builds a SUM; every other function on this list is typed into the box. The running total of sales to date in How to calculate tiered commission is a worked example.

The person reading

CURRENT_USER() stands for whoever is reading. You do not type it: choose Me in a filter on a User field and the app writes it for you. See Views that show each person their own records.

Names that are not for formulas

The formula box also knows a few names the app uses for its own work: MEDIAN, ARRAY_AGG, JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECT, ADJUST_ATTACHMENTS, AI and WINDOW. They are not formula functions: leave them out of your formulas.