Understanding Formulas

Below, you’ll find a bit more information about the concepts related to Formulas. If you want a field to present its own value, a formula field can be useful. A formula can be used to calculate a field value based on other table values.

The formula field computes a value based on values in other fields. Any field on the table can be used as a reference in a formula that refers to another field.

See the understanding baserow formulas guide if you instead want a general guide of how to use formulas as a user within Baserow.

enter image description here

When referencing a field, it’s a best practice to surround the field name with single quotation ‘ ‘

Formula functions

Types Functions Details Syntax Examples
Multiple select
variance sample Calculates the sample variance of the values and returns the result. The sample variance should be used when the provided values are only for a sample or subset of values for an underlying population. variance_sample(numbers from lookup() or field()) variance_sample(lookup(“link field”, “number field”)) variance_sample(field(“lookup field”)) variance_sample(field(“link field with number primary field”))
variance pop Calculates the population variance of the values and returns the result. The population variance should be used when the provided values contain a value for every single piece of data in the population. variance_pop(numbers from lookup() or field()) variance_pop(lookup(“link field”, “number field”)) variance_pop(field(“lookup field”)) variance_pop(field(“link field with number primary field”))
sum Sums all of the values and returns the result. sum(numbers from lookup() or field()) sum(lookup(“link field”, “number field”)) sum(field(“lookup field”)) sum(field(“link field with number primary field”))
stddev sample Calculates the sample standard deviation of the values and returns the result. The sample deviation should be used when the provided values are only for a sample or subset of values for an underlying population. stddev_sample(numbers from lookup() or field()) stddev_sample(lookup(“link field”, “number field”)) stddev_sample(field(“lookup field”)) stddev_sample(field(“link field with number primary field”))
stddev pop Calculates the population standard deviation of the values and returns the result. The population standard deviation should be used when the provided values contain a value for every single piece of data in the population. stddev_pop(numbers from lookup() or field()) stddev_pop(lookup(“link field”, “number field”)) stddev_pop(field(“lookup field”)) stddev_pop(field(“link field with number primary field”))
min Returns the smallest number from all the looked up values provided. min(numbers from a lookup() or field()) min(lookup(“link field”, “number field”)) min(field(“lookup field”)) min(field(“link field with text primary field”))
max Returns the largest number from all the looked up values provided. max(numbers from a lookup() or field()) max(lookup(“link field”, “number field”)) max(field(“lookup field”)) max(field(“link field with text primary field”))
join Concats all of the values from the first input together using the values from the second input. join(text from lookup() or field(), text) join(lookup(“link field”, “number field”), “_”) join(field(“lookup field”), field(“different lookup field”)) join(field(“link field with text primary field”), “,”)
filter Filters down an expression involving a lookup/link field reference or a lookup function call. filter(an expression involving lookup() or field(a link/lookup field), boolean) sum(filter(lookup(“link field”, “number field”), lookup(“link field”, “number field”) > 10)) filter(field(“lookup field”), contains(field(“lookup field”), “a”)) filter(field(“link field”) + “a”, length(field(“link field”)) > 10")
every Returns true if every one of the provided looked up values is true, false otherwise. every(boolean values from a lookup() or field()) every(field(“my lookup”) = “test”)
count Returns the number of items in its first argument. count(array) count(field(‘my link row field’))
avg Averages all of the values and returns the result. avg(numbers from lookup() or field()) avg(lookup(“link field”, “number field”)) avg(field(“lookup field”)) avg(field(“link field with number primary field”))
any Returns true if any one of the provided looked up values is true, false if they are all false. any(boolean values from a lookup() or field()) any(field(“my lookup”) = “test”)

Types Functions Details Syntax Examples
Formula
when empty If the first input is calculated to be empty the second input will be returned instead, otherwise if the first input is not empty the first will be returned. when_empty(any, same type as the first) when_empty(field(“a”), “default”)
row id Returns the rows unique identifying number. row_id() concat("Row ", row_id())
minus Returns its two arguments subtracted. number - number minus(number, number) date - date date - date_interval date_interval - date_interval 3-1 = 2
lookup Looks up the values from a field in another table for rows in a link row field. The first argument should be the name of a link row field in the current table and the second should be the name of a field in the linked table. lookup(‘a link row field name’, ‘field name in other the table’) lookup(‘link row field’, ‘first name’) = lookup(‘link row field’, ‘last name’)
field Returns the field named by the single text argument. field(‘a field name’) field(‘my text field’) = ‘flag’
add Returns its two arguments added together. number + number text + text date + date_interval date_interval + date_interval date_interval + date add(number, number) 1+1 = 2 ‘a’ + ‘b’ = ‘ab’
date interval Returns the date interval corresponding to the provided argument. date_interval(text) date_interval(‘1 year’) date_interval(‘2 seconds’)

Types Functions Details Syntax Examples
Date and time
year Returns the number of years in the provided date. year(date) year(field(“my date”))
todate Returns the first argument converted into a date given a date format string as the second argument. todate(text, text) todate(‘20210101’, ‘YYYYMMDD’)
second Returns the number of seconds in the provided date. second(date) second(field(“dates”)) == 2
month Returns the number of months in the provided date. month(date) month(todate(“2021-12-12”, “YYYY-MM-DD”)) = 12
day Returns the day of the month as a number between 1 to 31 from the argument. day(date) day(todate(‘20210101’, ‘YYYYMMDD’)) = 1
datetime format Converts the date to text given a way of formatting the date. datetime_format(date, text) datetime_format(field(‘date field’), ‘YYYY’)
date diff Given a date unit to measure in as the first argument (‘year’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘seconds’) calculates and returns the number of units from the second argument to the third. date_diff(text, date, date) date_diff(‘yy’, todate(‘2000-01-01’, ‘YYYY-MM-DD’), todate(‘2020-01-01’, ‘YYYY-MM-DD’)) = 20

Types Functions Details Syntax Examples
Boolean
or Returns the logical or of the first and second argument, so if either are true then the result is true, otherwise it is false. or(boolean, boolean) or(true, false) = true and(true, true) = true or(field(‘first test’), field(‘second test’))
not equal Returns if its two arguments have different values. any != any not_equal(any, any) 1!=2 ‘a’ != 'b’
not Returns false if the argument is true and true if the argument is false. not(boolean) not(true) = false not(10=2) = true
less than or equal Returns true if the first argument less than or equal to the second, otherwise false. any <= any 1 <= 1 = true if(field(‘a’) <= field(‘b’), ‘a smaller’, ‘b is greater than or equal’)
less than Returns true if the first argument less than the second, otherwise false. any < any 2 < 1 = false if(field(‘a’) < field(‘b’), ‘a is smaller’, ‘b is bigger or equal’)
isblank Returns true if the argument is empty or blank, false otherwise. isblank(any) isblank(‘10’)
if If the first argument is true then returns the second argument, otherwise returns the third. if(bool, any, any) if(field(‘text field’) = ‘on’, ‘it is on’, ‘it is off’)
greater than or equal Returns true if the first argument is greater than or equal to the second, otherwise false. any >= any 1 >= 1 = true if(field(‘a’) >= field(‘b’), ‘a is bigger or equal’, ‘b is smaller’)
greater than Returns true if the first argument greater than the second, otherwise false. any > any 1 > 2 = false if(field(‘a’) > field(‘b’), ‘a is bigger’, ‘b is bigger or equal’)
equal Returns if its two arguments have the same value. any = any equal(any, any) 1=1 ‘a’ = ‘a’
and Returns the logical and of the first and second argument, so if they are bothtrue then the result is true, otherwise it is false. and(boolean, boolean) and(true, false) = false and(true, true) = true and(field(‘first test’), field(‘second test’))

Types Functions Details Syntax Examples
Number
tonumber Converts the input to a number if possible. tonumber(text) tonumber(‘10’) = 10
multiply Returns its two arguments multiplied together. number * number multiply(number, number) 2*5 = 10
least Returns the smallest of the two inputs. least(number, number) least(1,2) = 1
greatest Returns the greatest value of the two inputs. greatest(number, number) greatest(1,2) = 2
divide Returns its two arguments divided, the first divided by the second. number / number divide(number, number) 10/2 = 5

Types Functions Details Syntax Examples
Text
upper Returns its argument in upper case. upper(text) upper(‘a’) = ‘A’
trim Removes all whitespace from the left and right sides of the input. trim(text) trim(" abc ") = “abc”
totext Converts the input to text. totext(any) totext(10) = ‘10’
t Returns the arguments value if it is text, but otherwise ‘’. t(any) t(10)
search Returns a positive integer starting from 1 for the first occurrence of the second argument inside the first, or 0 if no occurrence is found. search(text, text) search(“test a b c test”, “test”) = 1 search(“none”, “test”) = 0
right Extracts the right most characters from the first input, stops when it has extracted the number of characters specified by the second input. right(text, number) right(“abcd”, 2) = “cd”
reverse Returns the reversed text of the provided first argument. reverse(text) reverse(“abc”) = “cba”
replace Replaces all instances of the second argument in the first argument with the third argument. replace(text, text, text) replace(“test a b c test”, “test”, “1”) = “1 a b c 1”
regex replace Replaces any text in the first input which matches the regex specified by the second input with the text in the third input. regex_replace(text, regex text, replacement text) regex_replace(“abc”, “a”, “1”) = “1bc”
lower Returns its argument in lower case. lower(text) lower(‘A’) = ‘a’
length Returns the number of characters in the first argument provided. length(text) length(“abc”) = 3
left Extracts the left most characters from the first input, stops when it has extracted the number of characters specified by the second input. left(text, number) left(“abcd”, 2) = “ab”
contains Returns true if the first piece of text contains at least once the second. contains(text,text) contains(“test”, “e”) = true
concat Returns its arguments joined together as a single piece of text. concat(any, any, …) concat(‘A’, 1, 1=2) = ‘A1false’

Formula operators

Number Syntax Examples
divide Returns its two arguments divided, the first divided by the second. number / number divide(number, number) 10/2 = 5
multiply Returns its two arguments multiplied together. number * number multiply(number, number) 2*5 = 10
Boolean
equal Returns if its two arguments have the same value. any = any equal(any, any) 1=1 ‘a’ = ‘a’
greater than Returns true if the first argument greater than the second, otherwise false. any > any 1 > 2 = false if(field(‘a’) > field(‘b’), ‘a is bigger’, ‘b is bigger or equal’)
greater than or equal Returns true if the first argument is greater than or equal to the second, otherwise false. any >= any 1 >= 1 = true if(field(‘a’) >= field(‘b’), ‘a is bigger or equal’, ‘b is smaller’)
less than Returns true if the first argument less than the second, otherwise false. any < any 2 < 1 = false if(field(‘a’) < field(‘b’), ‘a is smaller’, ‘b is bigger or equal’)
less than or equal Returns true if the first argument less than or equal to the second, otherwise false. any <= any 1 <= 1 = true if(field(‘a’) <= field(‘b’), ‘a smaller’, ‘b is greater than or equal’)
not equal Returns if its two arguments have different values. any != any not_equal(any, any) 1!=2 ‘a’ != ‘b’
Formula
add Returns its two arguments added together. number + number text + text date + date_interval date_interval + date_interval date_interval + date add(number, number) 1+1 = 2 ‘a’ + ‘b’ = ‘ab’
minus Returns its two arguments subtracted. number - number minus(number, number) date - date date - date_interval date_interval - date_interval 3-1 = 2

If you’re looking for something else, please feel free to make recommendations or ask us questions in our online community —we’re ready to assist you!