Formula column
With formula column you can perform a calcolated result based on a specified expression which may also contain the value of one or more columns in the same row.
For example, given a table with two columns "Product Name" and "Cost", we can add a third formula column that will return the value of Cost column includes VAT.
How to use the value of other columns in the formula
You can use the value of the columns in the formula by writing the name of the column enclosed in square brackets.
Example:
[Name] + [Lastname]
How to write text values, numbers, date and yes/no into the formula
Text
Text values must be written in quotes. If these marks are used in the text must be preceded by a quote.
Example:
[Name] + ' McGiver'
Numbers
Numeric values must use the "." to separate decimals.
Example:
[Cost] + 5.25
Date
The date values must be enclosed in "#" character, expressed in UTC and in the following format: month/day/year.
Example:
[Birthdate] < #1/31/1982#
Yes/No
Yes/No values must be written true or false.
Example:
[IsValid] = true
Using null values
Null values in a formula can be identified with NULL word.
Example:
[Name] = NULL
Using operators in the formula
In a formula you can use the following operators:
+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
% (Module)
< (Less than)
> (Greater than)
<= (Less than or equal to)
>= (Greater than or equal)
<> (Not equals)
= (Equals)
IN (On the list)
LIKE (Contains)
How to use the "IN" operator
The "IN" operator checks if a text value, numeric or date is in a list.
Examples:
[City] IN ('New York', 'San Francisco', 'Chicago')
Wildcards in the text
The "*" and "%" can be used as wildcards in comparisons using the LIKE operator. If the text already contains these characters, then they must be enclosed in square brackets (eg [*] or [%]). If the brackets are already present in the text, then they must be enclosed in square brackets (eg [[] or []]). The wildcards are allowed only at the beginning and end of text and are not allowed in the middle of text.
Examples:
[Object] LIKE '*product*'
[Object] LIKE '*product'
[Object] LIKE 'product*'
Functions
In the formula you can use the following functions:
LEN
Returns the number of characters in a text.
Example:
LEN('test')
IIF
Gets one of two values depending on the result of a logical expression.
Example:
IIF([Cost] > 50, true, false)
ISNULL
Returns a determined value if a given expression is null.
Example:
ISNULL([Cost], 20)
TRIM
Remove the following characters in a text: \r \n \t, ' '.
Example:
TRIM('test ')
SUBSTRING
Returns a text of specified length starting from a specific point.
Example:
SUBSTRING('sample text', 1, 5)