This article walks through how to create a custom formula field.
Overview
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 types of formulas are supported?
Currently, only calculations on numbers and dates are supported. However, we have plans to include functions for other field types in the future including picklist options, text, checkboxes, collections, and more.
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
Warning: These functions are not available yet. They are coming soon.
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 |
ABS(number) Returns the absolute value of a given number. |
ABS(-2.6) | 2.6 |
CEILING(number) Rounds a given number away from zero, to the nearest multiple of a given number. |
CEILING(2.6) | 3 |
FLOOR(number) 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 |
TODAY() Returns the current date in the current user’s time zone. |
TODAY() | Today |
Logical
Warning: These functions are not available yet. They are coming soon.
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 |
ISBLANK() Returns TRUE when a given string is null or empty, otherwise, return FALSE. |
ISBLANK(Null) | True |
NOT(logical_expression) 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') | Four |
String
Warning: These functions are not available yet. They are coming soon.
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 |
LEN(string) Gets the length (number of characters) of a string. |
LEN('Construction') | 12 |
LOWER(string) 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(string) 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) | struction |
TRIM(string) Removes extra spaces from text. |
TRIM(' Construction') | Construction |
UPPER(string) Converts all letters in the specified string to uppercase. |
UPPER('Construction') | CONSTRUCTION |