Appearance
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
TRUEandFALSE. - Functions in capitals with their arguments in brackets, separated by commas:
ROUND(price * 1.2, 2).
| Operator | What 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 - number | A day so many days later or earlier |
date - date | The 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.
| Setting | What it does |
|---|---|
| Formula | The formula (required) |
| Digits after the point | For a number, 0 to 8 (default 2); the result is rounded half away from zero |
| Text before the number, text after the number | For 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 lists | For 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()orNOW(), 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.
| Setting | What it does |
|---|---|
| From the record of | A related record field that holds one record, or the parent record |
| Its field | The field of that record to show |
| Text before the number, text after the number | For a number, as for formulas |
| Show the total under lists, Show the average under lists | For 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.
| Setting | What it does |
|---|---|
| Records | The sub-records, or the records pointing here by a related record field |
| Summary | Count (default), sum, average, smallest or largest |
| Of their field | For anything but a count: a number field (or a date, for the smallest and largest) |
| Digits after the point | For a number, 0 to 8 (default 2) |
| Text before the number, text after the number | As for formulas: a total shown as ₺1.250,00 |
| Show the total under lists, Show the average under lists | For 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
| Function | What 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.