Sterodata
Get started
All documentation

Documentation Calculations

Formulas

Add a formula field, then write the formula: field names, fields of linked records, text, numbers, conditions and empty values.

A formula field works its value out on every row from the row's other fields. Use one for a line total, a status that follows from dates and amounts, a full name joined from two fields, or any value you would otherwise type by hand and keep correcting.

Add a formula field

  1. Open the table and choose + (Add Field) at the right end of the column headers.
  2. Give the field a Name, and under Type choose Calculation.
  3. Keep Custom Formula and type the formula into the Formula box, for example Quantity * "Unit Price".
  4. Read the line under the box. It names what the formula gives, such as → Number or → Currency (€). If the formula cannot be read, the line says why instead, and the field cannot be saved until it is fixed.
  5. Leave Display as on Auto, or choose how the result is shown: Number, Currency, Percentage, DateTime and others, each with its own settings such as decimal places.
  6. Choose Save.

To change a formula later, open the field's menu from its column header and choose Edit field. A view can have formula fields of its own: in a view, Add Field offers only Calculation.

A formula shown as a number or a currency with a set number of decimal places is rounded to those decimals in the value itself, half away from zero. A filter, a total and an export all use the figure you see.

How a formula is written

  • Refer to a field by its name, written as it reads in the column header: Quantity.
  • Put a field name in double quotes when it has a space or any character other than letters, digits and the underscore: "Unit Price", "VAT %". Quote a name that is also a word formulas use, such as "Date", "Year", "Order" or "End".
  • Write text in single quotes: 'Paid'. Write numbers with a full stop for decimals: 0.19.
  • Function names work in capitals or not: ROUND and round are the same.
  • Use brackets to say what is worked out first: (Price - Discount) * Quantity.

Operators

OperatorWhat it doesExample
+ - * /Add, subtract, multiply, divide. Dividing by zero gives an empty value.Quantity * Price
=, != or <>Equal, not equalStatus = 'Won'
< <= > >=Less than, at most, more than, at leastBalance > 0
AND, OR, NOTCombine conditionsBalance > 0 AND NOT Cancelled
IN (…)Is one of a listRegion IN ('DACH', 'Iberia')
BETWEEN … AND …Within a range, both ends includedScore BETWEEN 1 AND 5
LIKEMatches a pattern: % stands for any run of characters, _ for one characterCode LIKE 'INV-%'
IS NULL, IS NOT NULLIs empty, is not empty"Paid on" IS NULL
||Joins text"First name" || ' ' || "Last name"

A Percent field holds a fraction: 15% is 0.15, so Price * Discount is 15% of the price. On tables kept in Sterodata, LIKE tells capitals from small letters; to ignore the difference, compare LOWER(Code) LIKE 'inv-%'.

Fields of a linked record

A Linked record field points at a record in another table. Add a dot and a field name to read a field of that record: Customer.City, or Customer."Billing City" when the name has a space. Add another dot to go one link further: on an invoice, Tenancy.Unit.Name reads the name of the unit its tenancy is for. The value is read from the linked record as it is now.

Conditions

IF(condition, then, else) gives one of two answers: IF("Due Date" < TODAY(), 'Overdue', 'On time'). Without the third part, the answer is empty when the condition is not true.

CASE chooses among several. The first WHEN that is true gives the answer, and ELSE is the answer when none is:

CASE WHEN Balance <= 0 THEN 'Paid' WHEN "Due Date" < TODAY() THEN 'Late' ELSE 'Open' END

To compare one field with several values, name it once: CASE Status WHEN 'Won' THEN Amount ELSE 0 END. Every answer of an IF or a CASE must be one kind of value: all text, all numbers, all dates, or all yes-or-no values.

Empty values

  • A calculation that uses an empty value gives an empty result: when Discount is empty, Price - Discount is empty too.
  • COALESCE(Discount, 0) uses 0 wherever Discount is empty, so Price - COALESCE(Discount, 0) always has a value.
  • A comparison with an empty value is neither true nor false, so IF and CASE move on to their ELSE. Test for it with IS NULL.
  • LEAST and GREATEST pass over empty values.

Every function a formula can use is listed in Functions. Dates have their own page: Dates and time.

Last updated 29 October 2026