Formula syntax
Values
-
Numbers:
1.1
. -
Strings:
'abc'
,"abc"
. -
Boolean values:
true
,false
. -
Dates:
date('2012-01-01')
. -
Empty cell:
null
. -
Value in another column in the same record:
[Column name]
.Checkbox column contains boolean values. A reference to a nested table produces the number of records in that nested table.
If the column name changes, don't worry - the formula will be updated automatically.
Operators
-
^
,*
,/
,+
,-
for numbers. -
+
for strings. -
=
,!=
for all value types, includingnull
. Types of values on two sides, if other thannull
, should match. -
>
,>=
,<
,<=
for number and date. Value types on two sides should match. -
and
,or
for booleans. -
Unary
-
andnot
.
Functions: text
-
number_to_string(<number>)
Converts a number to a string.
-
string_to_number(<string>)
Converts a string to a number.
-
contains(<string>, <string>)
Returns a boolean value indicating whether the first string contains the second string (this function is case-sensitive).
-
starts_with(<string>, <string>)
Returns a boolean value indicating whether the first string starts with the second string (this function is case-sensitive).
-
ends_with(<string>, <string>)
Returns a boolean value indicating whether the first string ends with the second string (this function is case-sensitive).
-
to_lower_case(<string>)
Converts a string to lower case.
-
to_upper_case(<string>)
Converts a string to upper case.
-
slice(<string>, <number>)
,slice(<string>, <number>, <number>)
Returns the part of the string between the starting index (first number) and ending index (second number, optional).
slice('abc', 0)
will return'abc'
. Negative indexes are counted from the end of the string:slice('abc', 1, -1)
will return'b'
.
Functions: mathematical
-
abs(<number>)
Absolute value.
-
ceil(<number>)
Returns the value rounded upwards to the nearest integer.
-
exp(<number>)
e in power of argument.
-
floor(<number>)
Returns the value rounded downwards to the nearest integer.
-
log(<number>)
Natural logarithm (base of e).
-
max(<number>, <number>, ...)
,max(<date>, <date>, ...)
Returns the highest/latest of the arguments.
-
min(<number>, <number>, ...)
,min(<date>, <date>, ...)
Returns the lowest/earliest of the arguments.
-
round(<number>)
Returns the value rounded to the nearest integer.
Functions: date
-
date(<string>)
Returns a date. The argument should be a string in ISO 8601 format, such as
'2012-01-01'
or'20120101'
. -
add_years(<date>, <number>)
Adds the supplied number of years to a date. The number can be either positive or negative.
-
add_months(<date>, <number>)
Adds the supplied number of months to a date. The number can be either positive or negative.
-
add_days(<date>, <number>)
Adds the supplied number of days to a date. The number can be either positive or negative.
-
add_business_days(<date>, <number>)
Adds the supplied integer number of days to a date, skipping over the weekends. The number can be either positive or negative. Holidays are not taken into account.
-
diff_days(<date>, <date>)
Returns the number of days between two dates.
Functions: other
-
if(<boolean>, <any value (returned if true)>, <any value (returned if false)>)
If the first argument is
true
, returns the second argument, otherwise returns the third argument. -
map(<base value>, <choice 1>, <result 1>, <choice 2>, <result 2>, ..., <default result>)
If
<base value>
equals<choice 1>
, returns<result 1>
, so on for other choices. If none of the choices match, returns<default result>
. Example:map([My text column], 'a', 1, 'b', 2, null)
.
Case sensitivity and escaping
The syntax isn't case-sensitive.
The delimiters ]
(for column names) and '
, "
(for strings) are escaped by
doubling them like so: 'guv''nor'
= guv'nor.