Formulas allow you to transform, calculate, and manipulate the information stored in your columns. They are executed automatically based on the values in your table and help you build calculation rules, dynamic labels, or suitable output formats.
1 : Add a Formula type column
To create a formula, add a new column, then choose the Formula type.
2 : Understanding the formula editor
After creating the column, the formula editor opens. It lets you write the formula, consult the glossary, and define the result format.
Editor elements
- Input area: write your formula.
- Output format: define how to display the result (text, number, date, etc.).
- Column options: access the field settings. See the article
- Full screen: enlarge the editor.
- Indentation: improve readability (useful for long formulas).
- Glossary: search for and insert a function, field, or operator.
- Show / hide the glossary: gain workspace.
3 : Writing a formula
You can compose a formula in two ways:
- Directly enter the name of a function or operator to get suggestions.
- Insert from the glossary (functions, fields, operators).
-
Enter
$to quickly insert a field from your table.
- $field_name = field (link, linked table column, number, selection, formula, text...).
- "free text" = free text to add between quotation marks.
- do not use quotation marks for numbers.
Example 1 : Display the day of the week
This formula returns the day of the week associated with a date (e.g. date of birth).
Composition:
-
1 function:
TEXT_DAY() -
1 field:
[Date of birth]
Formulas can be nested: the result of one function can be used as a parameter of another. This logic lets you build more advanced rules.
Example 2 : Determine “Adult” or “Minor”
The example below compares today's date with the date of birth + 18 years.
Breakdown
-
YEAR_ADD([Date of birth], 18): adds 18 years to the date of birth. -
>: compares the two dates. -
TODAY(): returns today's date. -
TERNARY_CONDITION(condition ? A : B): returns a result depending on whether the condition is true or false.
Expected result
The formula returns "Minor" if the condition is true, otherwise "Adult".
4 : Output format options
In the Options tab of a Formula type field, you can define the output format of the result.
This setting lets you control how the result is displayed, without changing the formula. The output format applies after the calculation: the formula produces a value, the format determines how that value is presented.
Available formats
Why use output format options?
The output format is useful in two main cases:
-
When you combine several functions
- Example: concatenating text with a date or a number.
- The output format lets you unify the final display (often as text).
-
Even with a single function
- Adapt the result to the intended use: numeric display, readable date, clickable link, etc.
A good output format ensures clear reading and consistent behavior of the data.
Compatibility between formats
Some formats aren't compatible with each other. If the chosen format doesn't match the actual result, the field may display an empty value, or in some cases an incorrect result with no visible error.
To secure assemblies, you can explicitly convert to text using dedicated functions such as FORMAT_DATE() or FORMAT_NUMBER().