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)
Adding working days to a date
E.g. creating a deadline several working days after an important date, taking holidays and weekends into account
TODATE(WORKDAY(important date, 3, {"2024-06-03","2024-12-25"})
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