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 `one`

,`two`

and `three`

respectively. If you then create a formula
field with the formula `concat('Number', field('text field'))`

the resulting table would
look like:

text field | formula field |
---|---|

one | Number one |

two | Number two |

three | Number three |

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:

`concat`

- Concat is one of many formula functions you can use. It will join together all the inputs you give to it into one single piece of text.

`(`

- To give inputs to a formula function you first have to write an opening parenthesis indicating the inputs will follow.

`'Number'`

- This is the first input we are giving to
`concat`

and it is literally just the text`Number`

. When writing literal pieces of text in a formula you need to surround them with quotes.

- This is the first input we are giving to
`,`

- As we are giving multiple inputs to
`concat`

we need to separate each input with a comma.

- As we are giving multiple inputs to
`field('text field')`

- This is the second and final input we are giving to
`concat`

. We could keep on adding however many inputs as we wanted however as long as each was separated by a comma. - This second input is a reference to the field in the same table with the name
`text field`

. For each cell in the formula field this reference will be replaced by whatever the value in the`text field`

field is for that row.

- This is the second and final input we are giving to
`)`

- Finally, we need to tell Baserow we’ve finished giving inputs to the
`concat`

function, we do this with a matching closing parenthesis.

- Finally, we need to tell Baserow we’ve finished giving inputs to the

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 `datetime_format`

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
like `+`

,`-`

,`*`

and `/`

. 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:

- Divide a number by zero.
- Convert text to a number using the
`tonumber`

function and failed because the text wasn’t a valid number. - Calculate a number which is larger than
`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 `>`

, `>=`

`<=`

, `<`

, `=`

and `!=`

operators.

Use 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 `date_interval`

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:

- Link to table
- File
- Single select
- Multiple select The ability to use these fields will be added in a future update.