How to use Formulas in your RowShare tables?

Updated 3 weeks ago by Antoine Driard

RowShare calculated columns are formulas based on the value of the other cells of the same row. Add a "Calculated value" column and specify the formula in this column’s settings. Yes, this means that the formula will always be the same in a given column.

Start by adding a new column to your table, and click "Automatic" and “Calculated Value”. Give a name to your column, and scroll to the bottom to enter the formula:

Calculated values can include 4 types of content:

References to other columns

A column is referenced by its name, surrounded by squared brackets [ ]. Examples:

[Name]
[Customer Name]
[Price]

Constant values

These values are typically numbers or text. Text must be surrounded by double quotes. Examples:

1
878
3.14
"Dr"

Operators

4 standard mathematic operations: +, -, *, /
Comparison operators: >, <, >=, <=, =, !=, <> (the last two ones are equivalent and mean "not-equal")
bool1 AND bool2: boolean operator that returns true if both expressions (bool1, bool2) are true
bool1 OR bool2: boolean operator that returns true if at least one expression (bool1, bool2) is true

Functions

RowShare offers additional functions to manipulate numbers, text, dates and introduce logical operations.

Numbers

min(n1, n2, n3...) : returns the smallest number
max(n1, n2, n3...) : returns the largest number
sum(n1, n2, n3...) : returns... the sum!
avg(n1, n2, n3...) : returns the average
rand() : returns a random numeric value between 0 and 1
abs(number) : returns 1 for positive numbers and -1 for negative numbers
floor(number) : Returns the largest integral value less than or equal to the specified number
ceiling(number) : Returns the smallest integral value greater than or equal to the specified number
round(number) : Rounds a number to the nearest integer number
truncate(number): returns the integral part of a number.

Time

datediff(leftDate, rightDate) : returns the number of days between 2 dates
adddays(date, numberofdays): adds a number of days to the date. Removes days if numberofdays is negative.
now() : returns the current date and hour, based on the current user timezone as set in his account.
utcnow() : returns the current UTC date and hour.

The following functions take a date as parameter and return the corresponding year/month/day/hour...

year(date)
quarter(date)
month(date)
weekofyear(date)
day(date)
dayofweek(date)
hour(date)
minute(date)
second(date)
millisecond(date)

String

lower(string) : converts a string to lowercase
upper(string) : converts a string to uppercase
trim (string) : removes all leading and trailing spaces in a string
substring (string, first, nb) : returns nb characters from string, starting from first position, given that the first character is numbered 0.

Example : to get the 2 first characters from a Zip code column, you should use substring([Zip code],0,2). For 94016, you'll get 94.

Logic

IF(condition, ifTrue, ifFalse) takes 3 parameters: 1st is the condition, 2nd is the result if the condition is met, the 3rd is the result otherwise.

Example: to specify in a cell 1 if another cell is positive, and -1 if it is null or negative, use: IF(COL1 > 0, 1 , -1). You could also have used Abs(COL1) to get the same result.

ISNULL(column, defaultvalue) takes 2 parameters: if the first one is NULL (empty cell), it will return the second parameter. If the first one is not null, it will be returned.

Example: if you want to add the last name to the first name, but you are not sure if there is a first name, you should use: ISNULL([First Name],"") + " " + [Last Name]

ISNULL(column) takes 1 parameter and returns true if the cell in that column is empty and false if it's not empty.

Examples

· Numbers: To calculate the sum of two columns Total Priceand VAT, the formula is: = [Total Price] + [VAT]

· Text: To combine the columns First Name and Last nametogether, the formula is: [First name] + [Last name]

Of course, it would look better with a space between first name and last name. Add it between double quotes: [First name] + " " + [Last name]

It is possible to add comments, useful for complex formulas. In formulas, lines starting with // are ignored and can therefore be used as comments.
Example :
[Price] *
// If the VAT column is checked, we multiply by 1.2
if([VAT],1.2)

Important Notes

Calculated values can not reference other calculated values.
Calculated values do not function if referenced column names include one of the following characters:
- .
- \
- [
- ]
You can show calculated field as percentage: go to the column settings by clicking on the settings button on the header of that column, change your column type to “Advanced”, set the data type to “Number”, and you will find “Percentage” box, set it as “Yes”.