Formulas

Learn more about Formulas

A formula field contains a value that is calculated from a spreadsheet-like formula. They can perform operations on non-formula fields on the same record, allowing for mathematical, logical, text & date based processing. For example, a full name field could be added to the User collection that concatenates the first name and last name fields.

Creating a formula field

  • On the data tab of your portal navigate to the collection you would like to add the formula field to

  • Add a new field and choose Formula as the type.

  • Enter the formula. To reference another field hit the plus button to add the reference at the current location.

When do they calculate?

A formula's value for a given record will calculate when any of the following occur:

  • When the formula field is created

  • When the formula is updated

  • When a new record is added

  • When a field referenced in a formula is updated

If a formula doesn't reference any other fields it will only be calculated for a given record when:

  • A formula is added initially

  • The formula is edited

  • The record is initially created

Output Types

When a formula is added or updated, we will check the output and assign a type based on the output patterns. The possible outputs are:

  • Boolean (formula must return true or false)

  • Date (formula must return value wrapped in TODATE operator)

  • Decimal

  • Integer

  • Text

Examples

Field references will be highlighted green in the below examples

Joining text fields

E.g. creating a full name from a first name & last name, ensuring to add a space in between if both fields have a value first name & IF(OR(ISBLANK(first name),ISBLANK(last name)),""," ") & last name

Replacing part of a text field

E.g. replace "-old" with " (discontinued)" for a nicer record view

SUBSTITUTE(name, "-old", " (discontinued)")

Adding days to a date

E.g. creating a deadline a week after an important date

TODATE(important date + 7)

Calculations

E.g. calculating the gross sales

(number sold - number refunded) * price

Supported Operations

Below is a reference of the officially supported operators in our functions.

Dates & Time

Logic

Math

Text

Last updated