Formula Fields - Statisfy

Formula fields automatically compute values based on expressions you define. They update whenever referenced fields change, so they always reflect current data. Example use cases:

Calculate days until renewal from a date field
Sum ARR across all child accounts or related opportunities
Pull the account owner’s email from a related entity
Compute a weighted health score from multiple factors
Concatenate opportunity stages into a single label
Find the median deal size across open opportunities

Creating a Formula Field

You need admin access to create formula fields. The Object Manager is only available to users with the admin role.

Navigate to Settings > Object Manager
Select the entity tab (Accounts, People, Opportunities, etc.)
Click + Create Field
Enter a field name and select a field type that supports formulas (see Return Types for the full list)
Check This is a formula field
Click Save — the formula editor opens automatically

Formulas are not supported on Object, Pill, Lookup, Lookup List, JSON, or URL field types. The This is a formula field checkbox is hidden when one of these types is selected.

Formula Types

The formula editor supports four formula types. Select the one that fits your use case.

Equation

Write a custom expression combining fields, functions, and operators. This is the most flexible formula type.

When to use

Use Equation when you need custom logic — conditional calculations, arithmetic across fields, or text formatting. Examples: weighted scores, tier classifications, concatenated labels.

How to configure:

Select Equation as the formula type
Write your expression in the formula editor (see Using the Formula Editor)
Choose a null handling option (see Null Handling)
Validate and preview your formula
Click Save

Example expressions:

{amount} * 1.1

IF({stage} = "Closed Won", {amount}, 0)

{first_name} & " " & {last_name}

Rollup

Aggregate values from related entities. Rollups traverse a relationship and apply an aggregation function to a field on the related records.

When to use

Use Rollup to summarize data across related records — total revenue from opportunities, count of open tickets, average deal size, median ARR across child accounts, list of unique stages, etc.

How to configure:

Select Rollup as the formula type
Choose an aggregation type (see table below)
Select the related entity and the relationship to follow
Select the field to aggregate from the related entity
Optionally add filter conditions to narrow which related records are included
For CONCAT, optionally set a custom separator (defaults to , )

Aggregation Types

Aggregation	Returns	Description	Example
SUM	Number	Total of all numeric values	Sum of opportunity amounts
COUNT	Number	Number of matching records	Count of open opportunities
MIN	Number	Smallest numeric value	Lowest deal amount
MAX	Number	Largest numeric value	Highest deal amount
AVG	Number	Average of all numeric values	Average deal size
MEDIAN	Number	Middle value (statistical median)	Median ARR across child accounts
FIRST	Any	First non-null value in the collection	First opportunity stage
LAST	Any	Last non-null value in the collection	Most recent opportunity stage
CONCAT	Text	Joins values into a single string with a separator	All stage names: `"Open, Closed, Won"`
COLLECT_UNIQUE	List	Unique values as a deduplicated list	Unique opportunity stages

SUM, MIN, MAX, AVG, and MEDIAN only operate on numeric values. Non-numeric values are silently skipped. If all values are null or non-numeric, the result is null.COUNT counts entities regardless of the source field value — including records with null values.FIRST and LAST skip null values and return the first/last non-null value in the collection. If all values are null, the result is null.

Rollup Filter Conditions

Filters let you narrow which related records are included in the aggregation. For example, you can sum only open opportunities or count only high-priority tasks. Available filter operators:

Operator	Description	Example
equals	Exact match	`stage equals "Open"`
not equals	Does not match	`stage not equals "Closed"`
contains	Text contains substring	`name contains "Enterprise"`
greater than	Numeric comparison	`amount greater than 1000`
less than	Numeric comparison	`amount less than 5000`

Multiple filter conditions are combined with AND logic — all conditions must match for a record to be included.

Rollup Relationships

The relationships available depend on the entity type you’re creating the formula on:

Entity	Available Relationships
Account	`parent`, `children`, `descendants`, `csm`, `owner`, `opportunities`
Opportunity	`account`, `owner`
People	`account`

Children vs. Descendants

Children includes only direct child accounts — accounts whose parent is the current account.Descendants includes the entire hierarchy tree below the current account — children, grandchildren, and so on. Use descendants when you need to roll up values across the full account hierarchy (e.g., total ARR across all sub-accounts).

Rollup Examples

Sum ARR from child accounts

Aggregation: SUM
Related entity: Account
Relationship: descendants
Source field: arr

This computes the total ARR across all accounts in the hierarchy below the parent.

Count open opportunities

Aggregation: COUNT
Related entity: Opportunity
Relationship: opportunities
Source field: name (any field works for COUNT)
Filter: is_closed equals false

Returns the number of open opportunities for the account.

Median deal size

Aggregation: MEDIAN
Related entity: Opportunity
Relationship: opportunities
Source field: amount

Returns the statistical median of all opportunity amounts. With an even number of values, returns the average of the two middle values.

Concatenate opportunity stages

Aggregation: CONCAT
Related entity: Opportunity
Relationship: opportunities
Source field: stage
Separator: |

Produces a string like "Open | Negotiation | Closed Won".

Cross-Object

Reference a field value from a related entity. Cross-object formulas follow a relationship and pull a single field value.

When to use

Use Cross-Object to surface a value from a related record without duplicating data — for example, displaying the account owner’s email on a contact record.

How to configure:

Select Cross-Object as the formula type
Select the source entity and the relationship to follow
Select the source field to reference
Optionally apply a transform:

Transform	Description	Example
None	No transformation	`"John Smith"` → `"John Smith"`
UPPER	Convert to uppercase	`"John Smith"` → `"JOHN SMITH"`
LOWER	Convert to lowercase	`"John Smith"` → `"john smith"`
TRIM	Remove leading/trailing whitespace	`" John "` → `"John"`
SPLIT_FIRST	Extract the first word	`"John Smith"` → `"John"`
SPLIT_LAST	Extract the last word	`"John Smith"` → `"Smith"`

Time Calculation

Calculate time differences between dates. Time calculations work with date fields and return a numeric value in your chosen unit.

When to use

Use Time Calculation for date-based metrics — days until renewal, time since last activity, duration between two milestones, etc.

How to configure:

Select Time Calculation as the formula type
Choose a calculation type:

Calculation Type	Description	Fields Required
Time Between	Duration between two date fields	Start date + End date
Time Since	Time elapsed from a past date to today	Date field
Time Until	Time remaining from today to a future date	Date field

Select the required date field(s)
Choose the time unit:

Unit	Description
Days	Calendar days (exact)
Hours	Hours (exact)
Minutes	Minutes (exact)
Months	Approximate months (30-day)
Years	Approximate years (365-day)

Time Since and Time Until use the current date/time as one endpoint, so they produce different results each day. These formulas are recalculated during each batch evaluation cycle.

Using the Formula Editor

The formula editor is available when creating Equation-type formulas. It provides a rich editing experience with autocomplete, syntax highlighting, and validation.

Inserting Fields

There are two ways to insert a field reference into your formula:

Type @ in the editor to open the autocomplete dropdown. Start typing to filter by field name. Use arrow keys to navigate and press Enter or Tab to insert.
Click the Field button in the toolbar to open the field picker drawer. Browse or search for fields, including related fields from other entities.

Inserted fields appear as styled pills in the editor. In the formula expression, they are represented as {field_name}. Related fields follow the format {relationship_name.field_name} and appear in a separate section in the field picker. For example, {parent.name} references the parent account’s name.

Inserting Functions

Functions are also available through two methods:

Type @ and start typing a function name. Functions appear alongside fields in the autocomplete dropdown.
Click the Function button in the toolbar to open the function picker drawer. Browse by category, view syntax and examples, and click to insert.

The toolbar provides quick-insert buttons for common elements:

Field and Function buttons open their respective picker drawers
Parentheses ( ) for grouping expressions
Arithmetic operators + - * /
Comparison operators = != > < >= <=

Parsed Preview

As you type, a real-time parsed preview appears below the editor. It shows your formula with color-coded syntax highlighting:

Fields — highlighted as primary-colored pills
Functions — highlighted as accent-colored pills
Operators — displayed in muted text
Values (strings and numbers) — displayed in green
Parentheses — displayed in amber

This helps you visually verify that your formula is structured correctly before validating.

Operators

Arithmetic

Operator	Description	Example
`+`	Addition	`{amount} + 100`
`-`	Subtraction	`{total} - {discount}`
`*`	Multiplication	`{price} * {quantity}`
`/`	Division	`{amount} / 12`
`%`	Modulo (remainder)	`{count} % 2`
`^`	Power (exponentiation)	`{base} ^ 2`
`&`	String concatenation	`{first_name} & " " & {last_name}`

Comparison

Operator	Description	Example
`=`	Equal to	`{stage} = "Closed Won"`
`!=`	Not equal to	`{status} != "Inactive"`
`>`	Greater than	`{amount} > 1000`
`<`	Less than	`{score} < 50`
`>=`	Greater than or equal to	`{arr} >= 10000`
`<=`	Less than or equal to	`{days_left} <= 30`

Logical

Operator	Description	Example
`AND`	Both conditions must be true	`{amount} > 100 AND {stage} = "Open"`
`OR`	At least one condition must be true	`{stage} = "Open" OR {stage} = "Won"`
`NOT`	Negates a condition	`NOT {is_closed}`

Operator precedence (highest to lowest):

^ (power)
- (unary negation)
* / %
+ - &
= != > < >= <=
AND
OR

Use parentheses to override default precedence.

Functions Reference

Math

Function	Syntax	Description
`ABS`	`ABS(value)`	Absolute value
`ROUND`	`ROUND(value, decimals)`	Round to N decimal places
`FLOOR`	`FLOOR(value)`	Round down to nearest integer
`CEILING`	`CEILING(value)`	Round up to nearest integer
`MIN`	`MIN(val1, val2, ...)`	Smallest of the given values
`MAX`	`MAX(val1, val2, ...)`	Largest of the given values
`MOD`	`MOD(dividend, divisor)`	Remainder after division
`SQRT`	`SQRT(value)`	Square root
`POWER`	`POWER(base, exponent)`	Raise base to exponent

Text

Function	Syntax	Description
`CONCAT`	`CONCAT(text1, text2, ...)`	Join multiple text values
`LEFT`	`LEFT(text, num_chars)`	Extract leftmost N characters
`RIGHT`	`RIGHT(text, num_chars)`	Extract rightmost N characters
`MID`	`MID(text, start, length)`	Extract substring (1-based start)
`LEN`	`LEN(text)`	Number of characters
`UPPER`	`UPPER(text)`	Convert to uppercase
`LOWER`	`LOWER(text)`	Convert to lowercase
`TRIM`	`TRIM(text)`	Remove leading/trailing whitespace
`CONTAINS`	`CONTAINS(text, search)`	Returns true if text contains search
`SUBSTITUTE`	`SUBSTITUTE(text, old, new)`	Replace all occurrences of old with new
`REGEXP_SUBSTITUTE`	`REGEXP_SUBSTITUTE(text, pattern, replacement)`	Replace text matching a regular expression
`TEXT`	`TEXT(value)`	Convert a value to text
`VALUE`	`VALUE(text)`	Convert text to a number

REGEXP_SUBSTITUTE

Use REGEXP_SUBSTITUTE for pattern-based text replacement that SUBSTITUTE cannot handle — for example, stripping digits, extracting domain parts, or normalizing whitespace.

REGEXP_SUBSTITUTE({email}, "@.*", "")

Extracts the username from an email address (removes everything from @ onward).

REGEXP_SUBSTITUTE({phone}, "[^0-9]", "")

Strips all non-digit characters from a phone number.

Regular expressions are validated for safety before execution. Patterns with nested quantifiers (e.g., (a+)+) are rejected to prevent excessive processing time. Pattern length is limited to 500 characters.

Date

Function	Syntax	Description
`TODAY`	`TODAY()`	Current date
`NOW`	`NOW()`	Current date and time
`DATE`	`DATE(year, month, day)`	Create a date from components
`YEAR`	`YEAR(date)`	Extract the year
`MONTH`	`MONTH(date)`	Extract the month (1-12)
`DAY`	`DAY(date)`	Extract the day of month
`DATE_DIFF`	`DATE_DIFF(start, end, unit)`	Difference between dates in DAYS, MONTHS, or YEARS
`DATE_ADD`	`DATE_ADD(date, amount, unit)`	Add time to a date

TODAY() and NOW() produce different results each day. Formulas using these functions generate a validation warning because their output is not deterministic. This is expected behavior.

Logical

Function	Syntax	Description
`IF`	`IF(condition, true_val, false_val)`	Return one value if true, another if false
`CASE`	`CASE(expr, val1, res1, ..., else)`	Multi-way conditional matching
`AND`	`AND(cond1, cond2, ...)`	True if all conditions are true
`OR`	`OR(cond1, cond2, ...)`	True if any condition is true
`NOT`	`NOT(value)`	Negate a boolean value
`ISBLANK`	`ISBLANK(value)`	True if value is null, empty, or whitespace
`ISNULL`	`ISNULL(value)`	True if value is null

IF Examples

IF({amount} > 10000, "Enterprise", "SMB")

IF({is_closed} = true, "Closed", IF({stage} = "Negotiation", "Late Stage", "Early Stage"))

CASE Examples

CASE({stage}, "Open", 1, "Negotiation", 2, "Closed Won", 3, 0)

This returns 1 for Open, 2 for Negotiation, 3 for Closed Won, and 0 for anything else.

Null Handling

For Equation formulas, you can choose how the formula handles null (empty) field values:

Option	Behavior
Return Null	If any referenced field is null, the formula returns null
Treat as Zero	Null numeric values are treated as `0`; null text values are treated as empty strings
Skip	Records with null values in referenced fields are skipped

For rollup formulas, null handling is automatic. Numeric aggregations (SUM, AVG, etc.) skip null values. COUNT includes all records regardless of null values.

Validation

Click the Validate button to check your formula before saving. Validation checks for:

Syntax errors — Malformed expressions, unmatched parentheses, invalid function usage
Field existence — All referenced fields exist on the entity type
Relationship validity — Cross-object relationships are valid for the entity type
Type compatibility — The formula’s return type matches the expected field type
Function arguments — Functions receive the correct number and type of arguments
Circular dependencies — Formulas cannot reference themselves
Complexity — Rates your formula’s complexity (Low, Medium, or High)

The validation status icon next to the button shows:

Empty circle — Not yet validated
Spinner — Validation in progress
Green checkmark — Validation passed
Red alert — Validation errors found
Orange alert — Validation warnings (e.g., use of TODAY())

Complexity limits:

Metric	Limit
Maximum nesting depth	20 levels
Maximum function calls	50 per formula
Maximum field references	100 per formula

Always validate your formula before saving. This catches syntax errors early and confirms the formula references valid fields.

Preview

After writing your formula, use the Preview section to test it against real records.

Expand the Preview section at the bottom of the editor
Select which entities to test against (up to 50 records)
View the results table showing each entity name and its computed value

Preview results display the actual calculated value, null for empty results, or an error message if the formula fails for a specific record.

Preview evaluates the formula against live data. For rollup formulas, this includes loading related entity collections, so previews accurately reflect what the saved formula will produce.

Supported Entity Types

Formula fields can be created on these entity types:

Entity	Description	Available Relationships
Account	Customer accounts	parent, children, descendants, csm, owner, opportunities
Opportunity	Deals and opportunities	account, owner
People	Contacts and persons	account

Return Types

The return type is derived from the field type you chose when creating the field. It determines how the computed value is stored and displayed:

Return Type	Compatible Field Types	Description
Text	Text, Textarea, Text List	String values
Number	Number, Float, Currency, Percent, Percent Ratio	Numeric values — all numeric field types map to the Number return type
Date	Date	Date values
Boolean	Boolean	True/false values

The following field types cannot be used as the output type of a formula field:

Pill — colored single-value texts
URL — URL with separate display text
Object — nested structured fields
Lookup — single reference to another entity
Lookup List — multi-reference to other entities
JSON — arbitrary JSON payloads

Pill and URL fields can still be referenced as inputs inside another formula — they are read as text when used in an expression.Object, Lookup, Lookup List, and JSON fields cannot be used as inputs either; they are filtered out of the field picker and the @ autocomplete.

Managing Formula Fields

To edit a formula field:

Navigate to Settings > Object Manager
Find the formula field in the field list
Click to edit and modify the formula configuration
Validate, preview, and save

Changing a formula expression recalculates values for all existing records. If the formula references fields that don’t exist on some records, those records will show blank values.

Formula fields are read-only — their values are computed automatically and cannot be manually edited. If you need to override a calculated value, create a separate custom field instead.

Running Computation

Formula fields are recalculated automatically during scheduled batch evaluation cycles. You can also trigger a recompute on demand.

Manual Recompute

When editing a saved formula field, a Run button appears in the editor header. Click it to immediately recompute values for that formula field across all records of the entity type.

Open the formula field in the editor
Click the Run button (play icon) in the top-right corner
A confirmation toast appears and computation starts in the background
Values update shortly — there is no need to stay on the page

If you have unsaved changes to the formula, Statisfy prompts you to save first. This ensures the computation uses the latest formula expression.

Use the Run button after creating a new formula to populate values immediately, or after fixing an expression to refresh stale results without waiting for the next batch cycle.

Batch Evaluation

Formulas are also recalculated automatically in scheduled batch cycles. During each cycle, Statisfy evaluates all active formula fields in dependency order — formulas without dependencies first, then formulas that depend on other formulas. Time-based formulas (using TODAY(), NOW(), or Time Calculation formulas with Time Since / Time Until) produce different results each day and are refreshed during every batch cycle.

Cascading Formulas

Formula fields can reference other formula fields, creating a chain where one formula’s output feeds into another. Statisfy automatically resolves the correct evaluation order using a dependency graph, so you don’t need to worry about which formula runs first.

How It Works

When you reference a formula field inside another formula, Statisfy:

Builds a dependency graph — maps which formulas depend on which fields
Topologically sorts the graph so dependencies are evaluated first
Detects circular references — a formula cannot directly or indirectly depend on itself
Identifies parallel groups — independent formulas at the same level can evaluate simultaneously

You do not need to manually set execution order. Statisfy computes the correct order automatically from your field references. The execution_order field exists for advanced overrides but is rarely needed.

Example: Account Health Scoring Pipeline

This example shows four formula fields on an Account, where each builds on the previous ones. Step 1 — Total ARR (Rollup)

Setting	Value
Formula type	Rollup
Aggregation	SUM
Relationship	descendants
Source field	arr

Creates a total_arr field that sums ARR across all child accounts. Step 2 — Account Tier (Equation)

IF({total_arr} >= 100000, "Enterprise", IF({total_arr} >= 25000, "Mid-Market", "SMB"))

Creates an account_tier field that classifies the account based on total_arr from Step 1. Step 3 — Days Until Renewal (Time Calculation)

Setting	Value
Formula type	Time Calculation
Calculation type	Time Until
Date field	renewal_date
Unit	Days

Creates a days_until_renewal field. Step 4 — Health Score (Equation)

(0.3 * {nps_score}) + (0.2 * {usage_score}) + (0.2 * {support_score}) + IF({days_until_renewal} < 30, -20, IF({days_until_renewal} < 90, -10, 0)) + CASE({account_tier}, "Enterprise", 10, "Mid-Market", 5, 0)

Creates a health_score field that combines NPS, usage, and support scores with adjustments based on days_until_renewal (Step 3) and account_tier (Step 2). Evaluation order: Statisfy automatically evaluates these as:

Order	Field	Depends On
1	`total_arr`	child account `arr` values (no formula dependencies)
1	`days_until_renewal`	`renewal_date` (no formula dependencies)
2	`account_tier`	`total_arr`
3	`health_score`	`account_tier`, `days_until_renewal`, `nps_score`, `usage_score`, `support_score`

total_arr and days_until_renewal have no formula dependencies, so they evaluate in parallel at level 1. account_tier depends on total_arr, so it evaluates at level 2. health_score depends on both account_tier and days_until_renewal, so it evaluates last.

Example: Opportunity Scoring with Cross-Object

Step 1 — Account Tier (on Account)

IF({arr} >= 100000, "Enterprise", IF({arr} >= 25000, "Mid-Market", "SMB"))

Step 2 — Account Tier on Opportunity (Cross-Object, on Opportunity)

Setting	Value
Formula type	Cross-Object
Relationship	account
Source field	account_tier

Pulls the account’s tier onto each opportunity. Step 3 — Opportunity Priority Score (Equation, on Opportunity)

{amount} * CASE({account_tier_from_account}, "Enterprise", 1.5, "Mid-Market", 1.2, 1.0)

Multiplies the deal amount by a tier-based weight.

Example: Cascading Rollups Across Hierarchy

Step 1 — Opportunity Pipeline Value (Rollup, on Account)

Setting	Value
Aggregation	SUM
Relationship	opportunities
Source field	amount
Filter	`is_closed equals false`

Sums open opportunity amounts for each account. Step 2 — Total Hierarchy Pipeline (Rollup, on Account)

Setting	Value
Aggregation	SUM
Relationship	descendants
Source field	opp_pipeline_value

Sums opp_pipeline_value (from Step 1) across all child accounts. This gives the parent account visibility into the total open pipeline across its entire hierarchy. Step 3 — Pipeline Coverage Ratio (Equation, on Account)

IF({total_arr} > 0, {total_hierarchy_pipeline} / {total_arr}, 0)

Computes how much open pipeline exists relative to current ARR.

Circular Dependency Detection

Statisfy prevents circular references at validation time. If formula A depends on formula B and formula B depends on formula A, the validator will reject the formula with a clear error message showing the cycle.

Circular dependencies are checked when you save a formula, not just when you validate. A formula that passes validation individually can still be rejected at save time if it creates a cycle with existing formulas.

Invalidation

When a source field value changes on a record, Statisfy automatically identifies all formula fields affected by that change — including formulas that depend on other formulas that depend on the changed field. Only affected formulas are recalculated, in the correct order.

Common Patterns

Weighted health score

(0.4 * {nps_score}) + (0.3 * {usage_score}) + (0.3 * {support_score})

Combines multiple inputs with custom weights.

Tier classification

IF({arr} >= 100000, "Enterprise", IF({arr} >= 25000, "Mid-Market", "SMB"))

Assigns a tier based on ARR thresholds.

Days until renewal

Use a Time Calculation formula with:

Calculation type: Time Until
Date field: renewal_date
Unit: Days

Total child account ARR

Use a Rollup formula with:

Aggregation: SUM
Related entity: Account
Relationship: descendants
Source field: arr

Full name from first and last

{first_name} & " " & {last_name}

String concatenation using the & operator.

Account owner's first name

Use a Cross-Object formula with:

Relationship: owner
Source field: name
Transform: SPLIT_FIRST

Percentage of closed deals

IF(COUNT({opportunities}) > 0, SUM(IF({opportunities.is_closed} = true, 1, 0)) / COUNT({opportunities}) * 100, 0)

Calculates the close rate as a percentage.

User Management

Settings

Studio

Meetings

Documentation Index

​Creating a Formula Field

​Formula Types

​Equation

​Rollup

​Aggregation Types

​Rollup Filter Conditions

​Rollup Relationships

​Rollup Examples

​Cross-Object

​Time Calculation

​Using the Formula Editor

​Inserting Fields

​Inserting Functions

​Toolbar

​Parsed Preview

​Operators

​Arithmetic

​Comparison

​Logical

​Functions Reference

​Math

​Text

​REGEXP_SUBSTITUTE

​Date

​Logical

​IF Examples

​CASE Examples

​Null Handling

​Validation

​Preview

​Supported Entity Types

​Return Types

​Managing Formula Fields

​Running Computation

​Manual Recompute

​Batch Evaluation

​Cascading Formulas

​How It Works

​Example: Account Health Scoring Pipeline

​Example: Opportunity Scoring with Cross-Object

​Example: Cascading Rollups Across Hierarchy

​Circular Dependency Detection

​Invalidation

​Common Patterns

Creating a Formula Field

Formula Types

Equation

Rollup

Aggregation Types

Rollup Filter Conditions

Rollup Relationships

Rollup Examples

Cross-Object

Time Calculation

Using the Formula Editor

Inserting Fields

Inserting Functions

Toolbar

Parsed Preview

Operators

Arithmetic

Comparison

Logical

Functions Reference

Math

Text

REGEXP_SUBSTITUTE

Date

Logical

IF Examples

CASE Examples

Null Handling

Validation

Preview

Supported Entity Types

Return Types

Managing Formula Fields

Running Computation

Manual Recompute

Batch Evaluation

Cascading Formulas

How It Works

Example: Account Health Scoring Pipeline

Example: Opportunity Scoring with Cross-Object

Example: Cascading Rollups Across Hierarchy

Circular Dependency Detection

Invalidation

Common Patterns