Formula fields let you create calculated values based on other fields in your records. Use formulas to automate calculations, derive new data, and build dynamic logic without writing code.

Overview

Formula fields evaluate expressions and return computed values. They update automatically whenever the referenced fields change, ensuring your data stays current.

Common use cases:

Calculate totals, commissions, and margins
Combine text fields into a full name or address
Determine days until a deadline
Set status based on conditions
Format values for display

Formula fields are read-only. Their values are computed automatically and cannot be manually edited.

Formula Syntax

Field References

Reference other fields using curly braces with the field slug:

{field_slug}

Examples:

{first_name}
{deal_value}
{due_date}
{is_active}

Field slugs are the lowercase, underscore-separated versions of field names. You can find the slug in the field settings or use the autocomplete when writing formulas.

Literals

Use literal values directly in formulas:

// Text - use double quotes
"Hello, World"
 
// Numbers
42
3.14
-100
 
// Booleans
true
false

Data Types

Formula fields support four output types. Choose the appropriate type when creating your formula field.

Type	Description	Example Output
Text	String values	`"John Smith"`
Number	Numeric values	`1250.00`
Date	Date/datetime values	`2024-03-15`
Boolean	True/false values	`true`

The output type affects how the formula result is displayed and how it can be used in filters, sorting, and other formulas.

Operators

Arithmetic Operators

Perform mathematical calculations on numeric values.

Operator	Description	Example	Result
`+`	Addition	`{price} + {tax}`	Sum of price and tax
`-`	Subtraction	`{revenue} - {cost}`	Revenue minus cost
`*`	Multiplication	`{quantity} * {unit_price}`	Total amount
`/`	Division	`{total} / {count}`	Average value
`%`	Modulo (remainder)	`{number} % 2`	0 if even, 1 if odd

Example: Calculate profit margin

({revenue} - {cost}) / {revenue} * 100

Comparison Operators

Compare values and return boolean results.

Operator	Description	Example
`==`	Equal to	`{status} == "Active"`
`!=`	Not equal to	`{priority} != "Low"`
`>`	Greater than	`{amount} > 1000`
`<`	Less than	`{days_remaining} < 7`
`>=`	Greater than or equal	`{score} >= 80`
`<=`	Less than or equal	`{quantity} <= 0`

Logical Operators

Combine boolean expressions.

Operator	Description	Example
`AND`	Both conditions true	`{is_active} AND {is_verified}`
`OR`	Either condition true	`{priority} == "High" OR {is_urgent}`
`NOT`	Negates a condition	`NOT {is_archived}`

Example: Complex condition

({deal_value} > 10000 AND {probability} >= 0.75) OR {is_priority_account}

String Operator

Concatenate text values using the ampersand operator.

Operator	Description	Example
`&`	Concatenation	`{first_name} & " " & {last_name}`

Functions Reference

Text Functions

Functions for manipulating string values.

Function	Description	Syntax
`CONCAT`	Join multiple values	`CONCAT(value1, value2, ...)`
`LEFT`	Extract characters from start	`LEFT(text, count)`
`RIGHT`	Extract characters from end	`RIGHT(text, count)`
`MID`	Extract characters from middle	`MID(text, start, count)`
`LEN`	Get text length	`LEN(text)`
`TRIM`	Remove leading/trailing spaces	`TRIM(text)`
`UPPER`	Convert to uppercase	`UPPER(text)`
`LOWER`	Convert to lowercase	`LOWER(text)`
`PROPER`	Capitalize each word	`PROPER(text)`
`SUBSTITUTE`	Replace all occurrences	`SUBSTITUTE(text, old, new)`
`REPLACE`	Replace by position	`REPLACE(text, start, count, new)`
`CONTAINS`	Check if text contains substring	`CONTAINS(text, substring)`
`STARTS_WITH`	Check if text starts with	`STARTS_WITH(text, prefix)`
`ENDS_WITH`	Check if text ends with	`ENDS_WITH(text, suffix)`

Examples:

// Combine first and last name
CONCAT({first_name}, " ", {last_name})
 
// Get first 3 characters of product code
LEFT({product_code}, 3)
 
// Extract domain from email
MID({email}, FIND("@", {email}) + 1, LEN({email}))
 
// Clean up user input
TRIM(PROPER({company_name}))
 
// Replace spaces with hyphens for URL slug
LOWER(SUBSTITUTE({title}, " ", "-"))
 
// Check if email is from specific domain
ENDS_WITH({email}, "@company.com")

Number Functions

Functions for numeric calculations.

Function	Description	Syntax
`SUM`	Add multiple values	`SUM(value1, value2, ...)`
`AVERAGE`	Calculate mean	`AVERAGE(value1, value2, ...)`
`MIN`	Find minimum value	`MIN(value1, value2, ...)`
`MAX`	Find maximum value	`MAX(value1, value2, ...)`
`ROUND`	Round to decimals	`ROUND(number, decimals)`
`FLOOR`	Round down	`FLOOR(number)`
`CEILING`	Round up	`CEILING(number)`
`ABS`	Absolute value	`ABS(number)`
`POWER`	Raise to power	`POWER(base, exponent)`
`SQRT`	Square root	`SQRT(number)`

Examples:

// Calculate total with tax
SUM({subtotal}, {tax}, {shipping})
 
// Find average deal size
AVERAGE({deal_1_value}, {deal_2_value}, {deal_3_value})
 
// Round currency to 2 decimals
ROUND({calculated_total}, 2)
 
// Calculate percentage and round up
CEILING({completed_tasks} / {total_tasks} * 100)
 
// Get absolute difference
ABS({budget} - {actual_spend})
 
// Calculate compound interest
{principal} * POWER(1 + {rate}, {years})

Date Functions

Functions for working with dates and times.

Function	Description	Syntax
`TODAY`	Current date	`TODAY()`
`NOW`	Current date and time	`NOW()`
`DATEADD`	Add time to date	`DATEADD(date, amount, unit)`
`DATEDIFF`	Difference between dates	`DATEDIFF(date1, date2, unit)`
`YEAR`	Extract year	`YEAR(date)`
`MONTH`	Extract month (1-12)	`MONTH(date)`
`DAY`	Extract day of month	`DAY(date)`
`WEEKDAY`	Day of week (1-7)	`WEEKDAY(date)`
`WORKDAYS`	Business days between dates	`WORKDAYS(start_date, end_date)`

Date units for DATEADD and DATEDIFF:

"days" - Calendar days
"weeks" - Weeks
"months" - Months
"years" - Years
"hours" - Hours
"minutes" - Minutes

Examples:

// Days until due date
DATEDIFF({due_date}, TODAY(), "days")
 
// Set follow-up date 2 weeks from now
DATEADD(TODAY(), 14, "days")
 
// Calculate contract end date
DATEADD({start_date}, {contract_months}, "months")
 
// Get fiscal quarter
CEILING(MONTH({date}) / 3)
 
// Check if date is in the future
{due_date} > TODAY()
 
// Business days remaining
WORKDAYS(TODAY(), {deadline})

Logical Functions

Functions for conditional logic.

Function	Description	Syntax
`IF`	Conditional value	`IF(condition, true_value, false_value)`
`IFS`	Multiple conditions	`IFS(cond1, val1, cond2, val2, ...)`
`SWITCH`	Match value to cases	`SWITCH(expr, case1, val1, case2, val2, default)`
`AND`	All conditions true	`AND(condition1, condition2, ...)`
`OR`	Any condition true	`OR(condition1, condition2, ...)`
`NOT`	Negate condition	`NOT(condition)`
`ISBLANK`	Check if empty	`ISBLANK(field)`
`ISERROR`	Check for errors	`ISERROR(expression)`

Examples:

// Simple status label
IF({probability} >= 0.75, "Likely", "Needs Work")
 
// Multiple conditions with IFS
IFS(
  {score} >= 90, "A",
  {score} >= 80, "B",
  {score} >= 70, "C",
  {score} >= 60, "D",
  true, "F"
)
 
// Map status to priority
SWITCH(
  {status},
  "Urgent", "High",
  "Normal", "Medium",
  "Low Priority", "Low",
  "Medium"
)
 
// Check multiple conditions
IF(AND({is_active}, {is_verified}, NOT(ISBLANK({email}))), "Ready", "Incomplete")
 
// Handle potential null values
IF(ISBLANK({discount}), {price}, {price} * (1 - {discount}))
 
// Safely handle division by zero
IF({total} == 0, 0, {count} / {total})

Cross-Record References

Formula fields can reference fields from related records through reference fields.

Syntax

Use dot notation to access fields from linked records:

{reference_field.field_name}

Examples

Access company data from a contact:

// Get the company name from a linked company
{company.name}
 
// Get the company's industry
{company.industry}
 
// Combine contact name with company
CONCAT({first_name}, " ", {last_name}, " - ", {company.name})

Access data through multiple levels:

// Get the account manager of the linked company
{company.account_manager.name}
 
// Get the region of the company's headquarters
{company.headquarters.region}

Cross-record references only work with single-value reference fields. For multi-select references, use rollup fields instead.

Example Formulas

Full Name from First and Last

Combine name parts into a display name.

// Basic concatenation
CONCAT({first_name}, " ", {last_name})
 
// Handle missing middle name
IF(
  ISBLANK({middle_name}),
  CONCAT({first_name}, " ", {last_name}),
  CONCAT({first_name}, " ", {middle_name}, " ", {last_name})
)
 
// With title prefix
IF(
  ISBLANK({title}),
  CONCAT({first_name}, " ", {last_name}),
  CONCAT({title}, " ", {first_name}, " ", {last_name})
)

Days Until Due Date

Calculate urgency based on approaching deadlines.

// Simple days remaining
DATEDIFF({due_date}, TODAY(), "days")
 
// With urgency label
IF(
  DATEDIFF({due_date}, TODAY(), "days") < 0,
  "Overdue",
  IF(
    DATEDIFF({due_date}, TODAY(), "days") <= 3,
    "Due Soon",
    "On Track"
  )
)
 
// Business days remaining
WORKDAYS(TODAY(), {due_date})

Calculated Pricing

Build pricing formulas for quotes and orders.

// Line item total
{quantity} * {unit_price}
 
// Subtotal with discount
{quantity} * {unit_price} * (1 - {discount_percent} / 100)
 
// Total with tax
{subtotal} * (1 + {tax_rate} / 100)
 
// Full pricing calculation
ROUND(
  {quantity} * {unit_price} * (1 - {discount_percent} / 100) * (1 + {tax_rate} / 100),
  2
)
 
// Margin percentage
ROUND(({price} - {cost}) / {price} * 100, 1)

Status Based on Conditions

Derive status from multiple field values.

// Lead scoring label
IFS(
  {score} >= 80, "Hot Lead",
  {score} >= 50, "Warm Lead",
  {score} >= 20, "Cold Lead",
  true, "New Lead"
)
 
// Deal stage health
IF(
  AND({days_in_stage} > 30, {probability} < 0.5),
  "At Risk",
  IF(
    AND({days_in_stage} <= 14, {probability} >= 0.7),
    "Healthy",
    "Monitor"
  )
)
 
// Task priority assignment
SWITCH(
  {urgency},
  "Critical", 1,
  "High", 2,
  "Medium", 3,
  "Low", 4,
  5
)
 
// Approval status
IF(
  ISBLANK({approved_by}),
  IF(ISBLANK({submitted_at}), "Draft", "Pending"),
  IF(ISBLANK({rejected_at}), "Approved", "Rejected")
)

Formatted Display Values

Create human-readable formats.

// Currency display
CONCAT("$", FORMAT({amount}, "#,##0.00"))
 
// Percentage display
CONCAT(ROUND({rate} * 100, 1), "%")
 
// Phone number formatting
CONCAT(
  "(",
  LEFT({phone}, 3),
  ") ",
  MID({phone}, 4, 3),
  "-",
  RIGHT({phone}, 4)
)
 
// Address single line
CONCAT(
  {street},
  ", ",
  {city},
  ", ",
  {state},
  " ",
  {zip}
)

Performance Considerations

Formula fields are evaluated in real-time when records are loaded or saved. Keep these best practices in mind for optimal performance.

Keep Formulas Simple

Break complex calculations into multiple formula fields rather than one large formula. This improves readability and makes debugging easier.

// Instead of one complex formula:
ROUND(({quantity} * {unit_price} * (1 - {discount_percent} / 100)) * (1 + {tax_rate} / 100), 2)
 
// Use intermediate fields:
// subtotal_field: {quantity} * {unit_price}
// discounted_field: {subtotal} * (1 - {discount_percent} / 100)
// total_field: ROUND({discounted} * (1 + {tax_rate} / 100), 2)

Minimize Cross-Record References

Each cross-record reference requires fetching data from related records. Limit the depth and number of references.

// Good - single reference
{company.name}
 
// Use cautiously - multiple references
{company.account_manager.team.region}

Avoid Recursive Dependencies

Formula fields cannot reference themselves or create circular dependencies. The system will detect and prevent these configurations.

// Invalid - circular reference
// field_a: {field_b} + 1
// field_b: {field_a} + 1

Use Appropriate Output Types

Choose the correct output type to avoid unnecessary type conversions:

Use Number for calculations you need to sum or average
Use Text for display-only values
Use Boolean for fields you will filter on
Use Date for timeline and scheduling logic

Error Handling

Formulas may encounter errors during evaluation. Use error handling functions to provide fallback values.

Common Error Scenarios

Error	Cause	Solution
Division by zero	Dividing by a field that is 0	Use `IF({divisor} == 0, 0, {value} / {divisor})`
Null reference	Field is blank	Use `IF(ISBLANK({field}), default, calculation)`
Type mismatch	Operating on incompatible types	Ensure operands match expected types
Invalid date	Date calculation with null	Check `ISBLANK` before date functions

Using ISERROR

Wrap potentially failing expressions with ISERROR:

// Safe division with error handling
IF(ISERROR({revenue} / {cost}), 0, {revenue} / {cost})
 
// Fallback for any calculation error
IF(ISERROR({complex_calculation}), "Error", {complex_calculation})

Null Handling Patterns

// Coalesce to default value
IF(ISBLANK({value}), 0, {value})
 
// Only calculate if all inputs present
IF(
  OR(ISBLANK({a}), ISBLANK({b}), ISBLANK({c})),
  "Incomplete",
  {a} + {b} + {c}
)
 
// Safe string concatenation
CONCAT(
  IF(ISBLANK({first_name}), "", {first_name}),
  IF(ISBLANK({last_name}), "", CONCAT(" ", {last_name}))
)

Test your formulas with edge cases including blank fields, zero values, and extreme numbers to ensure robust error handling.