How to use Formulas in your RowShare tables?

RowShare formulas are based on the value of the other cells of the same row. Add a Formula 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 Formulas, then decide if your formula will return text or numbers. After having named your column and clicked on Add, and scroll to the bottom to enter the formula:

Formulas 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 are true
• (bool1) OR (bool2): boolean operator that returns true if at least one expression is true
• NOT (bool): returns the opposite of a boolean. true returns false and false returns 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.

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.

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

• 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]

Important Notes

• Formulas can not (yet) reference other formulas. As a workaround, copy your first formula in the second one.
• Formulas do not function if column names include one of the following characters:
  • .
  • \
  • [
  • ]
• You can show formulas as percentage or colored text: choose the column type you want, and then check "Formula" in its settings.