Comment on page
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.
- 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.
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
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
TODATEoperator) - Decimal
- Integer
- Text
Field references will be highlighted green in the below examples
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 nameE.g. replace "-old" with " (discontinued)" for a nicer record view
SUBSTITUTE(name, "-old", " (discontinued)")E.g. creating a deadline a week after an important date
TODATE(important date + 7) E.g. calculating the gross sales
(number sold - number refunded) * priceBelow is a reference of the officially supported operators in our functions.
Operator Name | Description | Usage |
|---|---|---|
+ | Adds a number of days | date + 1 |
- | Subtracts a number of days | date - 2 |
TODATE | Converts a date to the correct output format for a Noloco date. | TODATE(date) |
DATE | Converts a provided day, month and year to a serial date. | DATE(year, month, day) |
DATEVALUE | Converts a date from text in one of the two formats:
"mm-dd-yyyy" or "yyyy-mm-dd" to a serial date. | DATEVALUE(dateText) |
DAY | The day from a date | DAY(date) |
DAYS | Number of days between two dates | DAYS(end, start) |
EDATE | Date representing a date a number of months before or after provided date | EDATE(date, numberOfMonths) |
EOMONTH(date, numberOfMonths) | Date representing the last day of a month a provided number of months before or after provided date | EOMONTH(date, numberOfMonths) |
HOUR | The hours from a datetime or time value | HOUR(datetime) |
ISOWEEKNUM | The ISO week number a date falls on | ISOWEEKNUM(date) |
MINUTE | The minutes from a valid datetime or time value. | MINUTE(datetime) |
MONTH | The month of a provided date in numeric format | MONTH(date) |
SECOND | The seconds from a valid datetime or time value. | SECOND(datetime) |
TIME | Converts a provided hour, minute and second to a fractional time. | TIME(hour, minute,second) |
TIMEVALUE | Converts a time in the accepted formats to a valid fractional time.
Accepts:
"1:10 AM""1:10:05 AM""18:20"
"18:20:15" | TIMEVALUE(timeText) |
WEEKNUM | The week number the date falls on | WEEKNUM(date) |
YEAR | The year from a date | YEAR(date) |
YEARFRAC | The number of years and fractional years between two dates | YEARFRAC(start,end) |
Operator | Action | Example Usage |
|---|---|---|
< | First is less than second | 1 < 2 |
> | First is greater than second | 1 > 2 |
= | First equals second | 1 = 2 |
AND | Returns true if all arguments are true | AND(logicValue,logicValue,...) |
FALSE | Returns the value of false | FALSE() |
IF | Returns different value based on whether the first argument is true | IF(logicValue, trueValue, falseValue) |
NOT | Returns the opposite of a logical value | NOT(TRUE())
returns FALSE() |
OR | Returns true if any argument is true | OR(logicValue,logicValue,...) |
TRUE | Returns the value of true | TRUE() |
NULL | Returns the value of null | NULL() |
ISNUMBER | Returns true if the argument is a number | ISNUMBER(value) |
Operator | Action | Usage Example |
|---|---|---|
+ | Adds the values to either side | 10 + 2 |
- | Subtracts the values to either side | 10 - 2 |
/ | Divides the values to either side | 10 / 2 |
* | Multiplies the values to either side | 10 * 2 |
ABS | Absolute value | ABS(number) |
CEILING | Rounds a number up to the closest multiple of significance | CEILING(number, significance) |
DECIMAL | Converts text of a number to a decimal | DECIMAL(text) |
EVEN | Rounds a number to nearest even integer | EVEN(number) |
FACT | Factorial of a number | FACT(number) |
FLOOR | Rounds a number down to the closest multiple of significance | FLOOR(number,significance) |
INT | Rounds a number to the nearest integer (less than or equal) | INT(number) |
LOG | Logarithm of a number for a provided base | LOG(number, base) |
MOD | Modulo of a number | MOD(number, divisor) |
ODD | Rounds a number to nearest odd integer | ODD(number) |
POWER | Raises a number to a power | POWER(number, power) |
PRODUCT | Multiplies the arguments | PRODUCT(number, number2,...) |
ROUND | Rounds a number to the closest value for a number of decimal places | ROUND(number, places) |
ROUNDDOWN | Rounds a number down for a number of decimal places | ROUNDDOWN()ROUND(number, places) |
ROUNDUP | Rounds a number up for a number of decimal places | ROUNDUP(number, places) |
SIGN |
| SIGN(number) |
SQRT | Positive square root of a positive number | SQRT(number) |
SUM | Sums the arguments | SUM(number, number2,...) |
TRUNC | Returns the integer component of a number | TRUNC(number) |
Operator | Action | Usage Example |
|---|---|---|
& | Appends two strings to one another | "Hello " & "World" |
CLEAN | Removes non printable characters from a string | CLEAN(text) |
CONCATENATE | Appends two strings to one another | CONCATENATE(text1, text2) |
EXACT | Checks if two strings are identical | EXACT(text1,text2) |
FIND | Returns the first position a string occurs in text | FIND(searchQuery, text) |
ISBLANK | Returns true if a string is blank | ISBLANK(text) |
LEFT | Returns a substring from the beginning of a string | LEFT(text, numberOfCharacters) |
LEN | Returns the length of a string | |
LOWER | Returns a string in lowercase | LOWER(text) |
MID | Returns a substring from the middle of a string | MID(text, startPosition, numberOfCharacters) |
PROPER | Returns a string with words capitalized | PROPER(text) |
REPLACE | Replaces part of a string with another provided string | REPLACE(text,startPosition, replacementLength, replacementText) |
REPT | Repeats a string a number of times | REPT(text, numberOfTimes) |
RIGHT | Returns a substring from the end of a string | RIGHT(text, numberOfCharacters) |
SEARCH | Returns the first position in a string occurs in text after the start character position | SEARCH(searchQuery, text, startPosition) |
SUBSTITUTE | Replaces all occurrences of a provided query with the provided replacement in a string | SUBSTITUTE(text, searchQuery, replacement) |
TEXT | Converts a number into text according to a specified format | TEXT(number, format) |
TRIM | Removes spaces from the beginning & end | TRIM(text) |
Last modified 12d ago