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, 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)
UPPER	Returns a string in uppercase	UPPER(text)