Custom Formula fields

This article walks through how to create a custom formula field.


Formula fields are a powerful custom field type that can be used to manipulate the data added to your forms. Formula fields allow you to calculate data using both simple and advanced functions against constants and fields on your form. Custom formula fields can be added as Company Fields which can be added to many projects or as standalone custom fields on an individual project. 

Example use cases where you might want a custom Formula field include:

  • Calculate the difference between two dates (how many days are between the due date and today's date)
  • Subtract a number from an input date (due date minus 14 days is when we need our materials)
  • Multiply numbers together (length times width to get total area)

Creating a formula

When adding a new custom Formula field, you will notice a box labeled "Formula". This box is where you type your custom formula. 

You can use constants and fields as variables in your Formula field. Use the "@" symbol to search for fields available on the form type. When you type "@" you will see a dropdown that lists fields that you have access to. Once a field is selected, it shows as purple text to help you clearly see which parts of your formulas are based on fields. 

What field types are supported in formulas?

You can create formulas based on the following field types:

  • Checkbox
  • Collection
  • Date
  • Email
  • Number
  • Number (Currency)
  • Phone
  • Project Directory (Business)
  • Project Directory (Contact)
  • Text (Multiline
  • Text (Single line)
  • URL

What field types are not currently supported in formulas?

  • Account Codes
  • Reference Collection
  • Cost Period
  • Formula
  • Number (Auto Number)
  • Picklist
  • Picklist (Cascading)
  • Text (Multiline Rich Format)

Simple Formulas

The Formula box supports simple addition (+), subtraction (-), multiplication (*), and division (/) operators for numerical calculations. Example functions using these simple operators include (don't forget about PEMDAS):

  • 2+2
  • @RFI Cost Impact*3.5
  • (2*(@Number of Units+150))/4

Advanced Formulas

We support many of the common functions used in your favorite spreadsheet applications which are broken down into five basic categories: Aggregate, Arithmetic, Date, Logic, and Strings.

Aggregate Functions

Syntax Example Expected Result

AVG(number1, {number2, ...})

Return average of numbers supplied.

AVG(2, 4, 8, 16) 7.5

MAX(number1, {number2, ...})

Return the biggest value from the numbers supplied.

MAX(2, 4, 8, 16) 16

MIN(number1, {number2, ...})

Return the smallest value from the numbers supplied.

MIN(2, 4, 8, 16) 2

SUM(number1, {number2, ...})

Return sum of numbers supplied.

SUM(2, 4, 8, 16) 30

Arithmetic Functions

Syntax Example Expected Result


Returns the absolute value of a given number.

ABS(-2.6) 2.6


Rounds a given number away from zero, to the nearest multiple of a given number.

CEILING(2.6) 3


Rounds a given number towards zero to the nearest multiple of a specified significance.

FLOOR(2.6) 2

ROUND(number, number_of_decimals)

Rounds a number to a fixed number of decimal places.

ROUND(2.345, 2) 2.35

Date Functions

Syntax Example Expected Result

DATEADD(number, date)

Adds a number of days to a date.

DATEADD(2, '8/15/2022') 8/17/2022

DATEDIFF(date1, date2)

Calculates the number of days between two dates.

DATEDIFF('8/15/2022','8/30/2022') 15


Returns the current date in the current user’s time zone.

TODAY() Today


Syntax Example Expected Result

AND(logical_expression1, [logical_expression2, {...}])

Determine if all conditions are TRUE.

AND(true, false, true) False

IF(logical_expression, value_if_true, value_if_false)

Returns TRUE when a given string is null or empty, otherwise, return FALSE.

IF(true, 'Yes', 'No') Yes


Returns TRUE when a given string is null or empty, otherwise, return FALSE.

ISBLANK(Null) True


To confirm one value is not equal to another.

NOT(false) True

OR(logical_expression1, [logical_expression2, {...}])

Determine if any conditions in a test are TRUE.

OR(true, false, true) True

SWITCH(expression, case1, value1, [default or case2, value2], {...})

Compares a list of values to a reference value and, of the first to equal the reference, returns its partner. If none are equal, returns null.

SWITCH(4, 2, 'Two', 4, 'Four', 8, 'Eight', 'Other')  Four


Syntax Example Expected Result

CONCAT(string1, {string2, ...})

Combines the text from multiple strings.

CONCAT('This',' ', 'is', ' ', 'cool!') This is cool!

INDEXOF(character, string)

Returns the location of a substring in a string (case sensitive).

INDEXOF('n', 'Construction') 3

LEFT(string, number)

Extracts a given number of characters from the left side of a supplied text string.

LEFT('Construction', 3) Con


Gets the length (number of characters) of a string.

LEN('Construction') 12


Converts all letters in the specified string to lowercase.

LOWER('Construction') construction

MID(string, starting_number, number_characters)

Extracts a given number of characters from the middle of a supplied text string.

MID('Construction',4,9) struction

REPLACE(string, string, string)

Replaces characters of a string specified in a given text string with another text string.

REPLACE('Do this', 'this', 'that')

REPLACE('Replace all of the es', 'e', 'X')

Do that

RXplacX all of thX Xs


Reverse a string.

REVERSE('Racecar') racecaR

RIGHT(string, number)

Extracts a given number of characters from the right side of a supplied text string.

RIGHT('Construction',3) ion


Removes extra spaces from text.

TRIM('   Construction') Construction


Converts all letters in the specified string to uppercase.