Skip to content

Formulas, lookups and rollups ​

A formula field works its value out from the record's other fields and the installation's variables: a total, a full name, a due date, whether a deal is overdue.

price * qty * (1 + {vat_rate})
first_name & " " & last_name
IF(stage = "won", amount, 0)
DATEADD(start, 30)
due < TODAY()

Add one like any other field, with the type Formula. The editor checks the formula as you type: it says what the formula gives, or what is wrong and at which character. Below it, the fields, variables and functions you can use are a click away.

Writing a formula ​

  • Fields by their key: price, first_name. Keys are in the field list of design mode. Text, numbers, money, dates, yes or no, single choices (their key, such as "won", or their number) and automatic numbers can be used, and other formula fields, but not one that uses this one.
  • Variables by their key in braces: {vat_rate}.
  • Text in double quotes: "Paid". Write a quote inside text twice: "Say ""hi""".
  • Numbers with a dot for decimals: 0.5, whatever the installation's number format.
  • Yes and no as TRUE and FALSE.
  • Functions in capitals with their arguments in brackets, separated by commas: ROUND(price * 1.2, 2).
OperatorWhat it does
+ - * /Arithmetic, * and / before + and -; brackets as usual
&Joins text: code & "-" & year. Numbers, dates and yes or no join as text
= <> (or !=) < <= > >=Compare two values of the same kind; text without regard to case
date + number, date - numberA day so many days later or earlier
date - dateThe number of days between two days (part of a day for dates with a time)

Empty values ​

An empty number counts as 0 in sums, and empty text as nothing when text is joined. A day that is empty leaves the result empty: due + 30 stays empty until there is a due date. An empty value equals only another empty value and is neither smaller nor larger than anything. ISBLANK(field) tells whether a field is empty.

A formula that cannot be worked out for a record, such as a division by zero, leaves the value empty for that record.

Numbers of choices ​

Each item of a choice list can have a value, a number. CHOICEVALUE(field) reads the value of the item a single choice field holds: with Likelihood and Impact lists valued 1 to 5, a risk's level is

CHOICEVALUE(likelihood) * CHOICEVALUE(impact)

so Likely (4) and Major (5) give 20. An item without a value, or no choice at all, is empty, which counts as 0 in sums. The field itself goes in the brackets, not a value worked out. Comparing the field still compares the key: likelihood = "likely".

When an item gets another value, every record's formula that reads its list is worked out again, as when a variable changes.

What it gives ​

The formula decides what kind of value the field holds: a number, text, a date, a date and time, or yes or no. The kind stays once the field is made; to give another kind, add another field.

SettingWhat it does
FormulaThe formula (required)
Digits after the pointFor a number, 0 to 8 (default 2); the result is rounded half away from zero
Text before the number, text after the numberFor a number: ₺ before gives ₺120,12, days after (with 0 digits) gives 80 days. The stored value stays a number
Show the total under lists, Show the average under listsFor a number: rows under the list with the total or average of every record it finds; see totals

Text keeps its first 255 characters. Values are stored with the record, so lists sort and filter by them, and exports include them.

When it is worked out ​

  • When a record is saved, from its values as they are then. While a form is filled in, it shows the value as it would be.
  • For every record when the formula changes, when a variable it uses gets another value, and when a choice it reads with CHOICEVALUE() gets another number.
  • Every day for formulas that use TODAY() or NOW(), so that "days left" stays true for records nobody touches.

Saving a formula field works out the first records at once; for many records the rest follows in the background, a few thousand a minute.

A field a formula uses keeps its key: change the formula before renaming it.

Lookups ​

A lookup field shows a field of another record: of the record a related record field points at, or of a sub-record's parent record. An order shows its customer's phone; a quote's line shows the quote's currency.

SettingWhat it does
From the record ofA related record field that holds one record, or the parent record
Its fieldThe field of that record to show
Text before the number, text after the numberFor a number, as for formulas
Show the total under lists, Show the average under listsFor a number: rows under the list with the total or average of every record it finds; see totals

The value is stored with the record, like a formula's, so lists sort and filter by it and formulas can use it. A choice shows as its label. When the other record changes, every record that looks into it follows.

Rollups ​

A rollup field sums up other records: the record's sub-records, or the records of another entity whose related record field points at it. A quote shows the total of its lines; a customer, its number of orders.

SettingWhat it does
RecordsThe sub-records, or the records pointing here by a related record field
SummaryCount (default), sum, average, smallest or largest
Of their fieldFor anything but a count: a number field (or a date, for the smallest and largest)
Digits after the pointFor a number, 0 to 8 (default 2)
Text before the number, text after the numberAs for formulas: a total shown as ₺1.250,00
Show the total under lists, Show the average under listsFor a number: rows under the list with the total or average of every record it finds; see totals

Records in the trash are left out. When one of the records is added, changed, moved elsewhere or removed, the rollup follows.

How changes travel ​

Lookups, rollups and formulas can build on each other: a line looks up its quote's customer discount, a formula applies it to the line's amount, and the quote's rollup adds the lines up. A change to the customer's discount travels all the way, as soon as it is saved. When a record is read by very many others (a customer with thousands of orders), they follow from the background within minutes.

A field that a lookup or rollup reads keeps its key while it does.

Who sees what ​

Lookups and rollups follow what each person may see. Someone who cannot see a related record sees its lookups empty, and a rollup counts only the records they can see; a formula built on them is worked out from what they see. For them, lists do not sort, filter or search by such a field, since its stored value is worked out from every record. Neither formulas, lookups nor rollups read a sensitive field.

Functions ​

FunctionWhat it gives
IF(condition, then, else)then when the condition holds, else else (empty without one). Both answers are of the same kind
AND(a, b, …), OR(a, b, …), NOT(a)Yes or no; an empty yes or no counts as no
ISBLANK(value)Whether a value is empty
ROUND(number, digits)Rounded half away from zero; digits may be negative: ROUND(1234, -2) is 1200
ROUNDUP(number, digits), ROUNDDOWN(number, digits)Rounded away from zero, or towards it
ABS(number), MOD(number, divisor)The size of a number; the remainder, with the divisor's sign
MIN(a, b, …), MAX(a, b, …)The smallest or largest; empty values are left out
CONCAT(a, b, …)Everything joined as text
UPPER(text), LOWER(text), TRIM(text)In capitals, in small letters, without spaces at the ends
LEN(text)The number of characters
LEFT(text, count), RIGHT(text, count), MID(text, start, count)Characters from the start, from the end, or from a position (the first is 1)
SUBSTITUTE(text, old, new)Every old replaced with new
TODAY(), NOW()Today in the installation's time zone; the present moment
YEAR(date), MONTH(date), DAY(date)Parts of a day
DATEDIFF(start, end, unit)Whole days, weeks, months or years from one day to another: "day" (default), "week", "month", "year"; negative when end is earlier
CHOICEVALUE(field)The value of the item a single choice field holds; see numbers of choices
DATEADD(date, amount, unit)A day so many days, weeks, months or years later (or earlier, for a negative amount). Adding a month to 31 January gives the last day of February

Function names can be written in any case. Plugins can add functions of their own.