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)

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

Operator NameDescriptionUsage

+

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)

WORKDAY

Date representing the number of working days before or after the starting date. Working days exclude weekends and any dates identified as holidays.

WORKDAY(start, numberOfDays, [holidays]

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

OperatorActionExample 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

OperatorActionUsage 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

OperatorActionUsage 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)

UPPER

Returns a string in uppercase

UPPER(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)

Last updated