Formulas
Learn more about Formulas
A formula field contains a value that is calculated from a spreadsheetlike formula. They can perform operations on nonformula 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
TODATE
operator)  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 name
E.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)
*
price
Below 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:
"mmddyyyy" or "yyyymmdd" 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, 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) 
UPPER  Returns a string in uppercase  UPPER(text) 
Last modified 5mo ago