Skip to content
Templaio

Guide

Calculated fields

A formula field fills itself from other fields of the same record: a line total, a margin, the days left before a deadline, a full name. Add one in the module editor, type = price * quantity and see the result before you save.

The basics

  • A formula starts with = and names other fields by their key, the lowercase name shown in the module editor: = unit_price * quantity.
  • Operators: + - * / % ^, comparisons = <> < <= > >=, and & to join text.
  • Text goes in quotes: "Paid". A yes/no field can be used directly: IF(paid, 0, total).
  • An empty field counts as 0 in a calculation and as empty text when joined.
  • Pick what the formula gives: a number, an amount (with its currency and decimals), text, a date or yes/no. It cannot change afterwards.

Locked or editable

A locked formula is always calculated and nobody can type in it. An editable formula is calculated until someone types a value: the form then shows “Typed value” and the formula stops overwriting it. Reset to formula hands it back.

Good to know

  • A formula reads fields of its own module only, and two formulas cannot depend on each other in a loop.
  • A field that a formula reads cannot be deleted until the formula is changed.
  • Values are calculated when a record is saved, imported or changed by an automation, so you can filter, sort, chart and print them like any field.
  • Formulas using TODAY() or NOW() are refreshed every night (00:05 UTC).
  • If a calculation fails, for example a division by zero, the value stays empty and the record still saves.
  • CSV import cannot fill a formula column; it fills itself.

Recipes

Line total

= unit_price * quantity

Result: Currency

Total with VAT

= ROUND(unit_price * quantity * 1.19, 3)

Result: Currency (3 decimals for TND)

Balance due

= total - deposit

Result: Currency

Full name

= first_name & " " & last_name

Result: Text

Days left

= DATEDIFF(due_date, TODAY())

Result: Number

Overdue?

= AND(NOT(paid), due_date < TODAY())

Result: Yes / No

Margin in percent

= IF(price = 0, 0, ROUND((price - cost) / price * 100, 1))

Result: Number

Size band

= IFS(amount < 1000, "Small", amount < 10000, "Medium", TRUE, "Large")

Result: Text

Functions

Math

ROUND(number, digits)
Rounds half away from zero. ROUND(2.5) is 3, ROUND(1234.5678, -2) is 1200.
FLOOR(number, digits)
Rounds down.
CEIL(number, digits)
Rounds up.
ABS(number)
Removes the minus sign.
MIN(a, b, …)
Smallest or largest of the values.
SUM(a, b, …)
Total or average of the values.
MOD(number, divisor)
The remainder of a division.
POWER(number, exponent)
Power and square root.

Logic

IF(condition, then, else)
Picks a value. Only the branch that is needed is calculated.
IFS(cond1, value1, cond2, value2, …)
The value of the first condition that is true.
SWITCH(value, match1, result1, …, default)
Compares a value with a list of matches.
AND(a, b, …)
Combines yes/no values.
ISBLANK(field)
True when the field is empty.
COALESCE(a, b, …)
The first value that is not empty.

Text

CONCAT(a, b, …) or a & b
Joins texts and numbers.
LEFT(text, count)
The first or last characters.
UPPER(text)
Changes case, removes extra spaces.
LEN(text)
Number of characters.
SUBSTITUTE(text, from, to)
Replaces every occurrence of a text.
VALUE("12,5")
Turns text into a number, or a value into text.

Dates

TODAY()
Today's date, or the current date and time. Updated every night.
DATEADD(date, amount, "days")
Adds days, weeks, months, years, hours or minutes.
DATEDIFF(end, start, "days")
End minus start. DATEDIFF(due_date, TODAY()) is the days left, negative when late.
YEAR(date)
One part of a date.
FORMAT_DATE(date, "DD/MM/YYYY")
Writes a date as text.

Your CRM, ready in a minute

Pick a template, name your workspace, and start working. Free, no credit card.