Work out values with a Calculation field
Work out a margin, a balance, a duration or a score from the record’s fields, and the result updates automatically when any input changes.
Before you start · for ownersThe permission to edit units
Before you start · for owners
The permission to edit units
- You need: The permission to edit units
Steps
1
Open Add Field and choose Calculation from the Advanced tab.
2
Write the formula with field names in curly braces, such as
{quantity} * {unit_price}.3
Choose the Result Type, the Decimal Precision and the Currency.
4
Choose how empty values are handled in When Input Is Null, and division by zero in Division by Zero.
5
Save.
The formula is checked before saving, then calculated for the existing records in the background.
How it works
Every function, with examples, is in Formula functions reference, with examples.
Settings
Setting
Options
Result Type
Number, Text, Boolean, Currency, Percentage, Date
Decimal Precision
A whole number, 0 or more
Currency
Required with the currency type
When Input Is Null
Return null (default), Treat as 0, or Treat as empty
Division by Zero
Return null, Return 0, or Show error
One empty value makes the whole formula follow the When Input Is Null setting. To get around it for one field, use COALESCE({field}, 0).
When is it recalculated?
- When the record is saved.
- When a rollup that the formula reads changes.
- When the source record of a lookup that it reads is edited, or the relation link changes.
- When the field is created or its formula is edited: for every record, and for every calculation that depends on it.
- Once a day, for formulas that contain
TODAY()orNOW().
Changing only the result type, the decimal places or the currency doesn’t recalculate.
Recalculating doesn’t change the record’s last-modified date.
What it reads from each type
Type
What the formula sees
Number, Rating
The number
Currency
The amount, without the code
Rollup
Its numeric value
Dates
A real date, for subtracting and comparing
Another calculation
Its result
Lookup
A number only, under some conditions. See Show a field from the linked record with a Lookup field
The field is read-only: it can’t be written by hand or by import. See Who can edit a field: locked and API-only fields.
If something goes wrong
What you see
Why
What to do
“Unknown field” when saving
A wrong or deactivated field name.
Use the internal name, as it shows in the settings.
An error when subtracting dates
A number subtracted from a date.
Use
DATEADD.A circular dependency message
Two calculations that each read the other.
Rearrange the formulas.
The result is empty
An empty field in the formula.
Use
COALESCE, or change When Input Is Null.The values are empty right after the field is created
The calculation runs in the background.
Wait a moment and refresh.
A message that the currency type needs a currency code
A currency result with no code.
Choose the currency.
A real example
Namaa Academy
The situation
The accountant works out what each trainee still owes by hand.
What they did
- A “Remaining” calculation:
{course_fee} - COALESCE({paid}, 0)with a currency result. - A “Payment status” calculation:
IF({course_fee} - COALESCE({paid}, 0) > 0, "Owes", "Paid").
The result
An “Owes” list, ready in a saved view.
A made-up company, to show the idea. The numbers are rounded.
Good to know
With dates, you can subtract a date from a date. To shift a date, use
DATEADD.A date can’t be compared with a number or text.
The “date” result type needs a formula that returns a date.
Loops between a unit’s calculations aren’t allowed.
A calculation that reads a lookup isn’t recalculated daily, even if it contains
TODAY().Did this guide solve it?
If not, we’ll ask what was missing.