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
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)
Logic
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)
Math
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
-1 if number is negative
0 if zero
1 if positive 
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)
Text
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)
​
​

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	`-1` if number is negative `0` if zero `1` if positive	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)

Rollups

Next - 🔗 Data Sources

MySQL

Last modified 5mo ago