Baserow formula fields allow you to dynamically calculate values for each cell in the field based on a formula. These formulas are similar to those found in other spreadsheet tools, have a growing collection of functions available and are lightning fast.
This guide will first explain what Baserow formulas are and how to use them. See the baserow formula technical guide if you are a looking for a technical understanding of how formulas are implemented within Baserow.
A Baserow Formula field lets you create a field whose contents are calculated based on a
Baserow Formula you’ve provided. A Baserow Formula is simply some text written in a
particular way such that Baserow can understand it, for example the text
1+1 is a
Baserow formula which will calculate the result
2 for every row.
Imagine you have a table with a normal text field called
text field with 3 rows
containing the text
three respectively. If you then create a formula
field with the formula
concat('Number', field('text field')) the resulting table would
|text field||formula field|
As you can see the formula field cells are calculated using the text field cell for each row.
In the next section we break down the formula bit by bit. If you are familiar with formulas already feel free to skip to the section after.
Let’s split apart the formula
concat('Number', field('text field')) to understand what
is going on:
concatand it is literally just the text
Number. When writing literal pieces of text in a formula you need to surround them with quotes.
concatwe need to separate each input with a comma.
concat. We could keep on adding however many inputs as we wanted however as long as each was separated by a comma.
text field. For each cell in the formula field this reference will be replaced by whatever the value in the
text fieldfield is for that row.
concatfunction, we do this with a matching closing parenthesis.
A function in a formula takes a number of inputs depending on the type of the function.
It does some calculation using those inputs and produces an output. Functions also
sometimes only take specific types of inputs. For example the
only accepts two inputs, the first must be a date (either a field reference to a date
field Or a sub formula which calculates a date) and the second must be some text.
All the available functions for you to use are shown in the expanded formula edit box which appears when you click on the formula whilst editing a formula field.
Formulas can be used to do numerical calculations. The standard maths operators exist
/. You can use whole numbers or decimal numbers directly in your
formula like so
(field('number field') + 10.005)/10
If you see an
Invalid Number in a formula cell it means that your formula for that row
has tried to do one of the following invalid operations:
tonumberfunction and failed because the text wasn’t a valid number.
10^50, the maximum value allowed.
If you need to do a calculation conditionally then the
if function and comparison
operators will let you do this. For example the following formula calculates whether a
date field is the first day of a month,
IF(day(field('some date')) = 1, true, false).
You can compare fields and sub-formulas using the
todate function to create a constant date inside a formula like so:
todate('2020-01-01 10:20:30', 'YYYY-MM-DD HH:MI:SS'). The first argument is the date
you want in text form and the second is the format of the date text.
Subtracting two dates returns the difference in time between the two dates:
field('date a') - field('date b'). The
date_interval function lets you create
intervals inside the formula to work with.
Need to calculate a new date based on a date/time interval? Use the
function like so:
field('my date column') - date_interval('1 year')
You cannot change the value of a formula field cell. This is because a formula field has one formula for the entire field which is used to calculate the individual cells values. Try converting the formula field back to a normal field if you are done with your calculation and now want to make specific edits to the results.
If you reference a field in a formula, if you then delete the referenced field, your formula field will become invalid and an error will be shown. To fix this you can either restore the deleted field, create a new field with the same name, change the formula to no longer reference the deleted field or rename another field.
Many more functions will be coming soon, please let us know which in particular are most important for you on our community forum.
It is not currently possible to reference and use the following fields in a formula: