Skip to main content

Formula Field Patterns: The Priority Cascade

Overview​

When data comes from multiple sources -- manual entry, integrations, overrides -- you often need a formula field that picks the "best available" value from a prioritized list. The priority cascade pattern uses nested IF/ISBLANK logic (similar to SQL's COALESCE) to check each source in order and return the first non-empty value.

This article covers the basic cascade pattern for text, numeric, and date fields, simpler alternatives like BLANKVALUE and NULLVALUE, and strategies for managing formula size limits. It is a fundamental technique for maintaining clean, reliable data across pricing, addresses, dates, and any scenario where fallback logic is needed.

Formula Field Patterns: The Priority Cascade

A common need in Salesforce formula fields is selecting the first non-null (or non-zero) value from a priority-ordered list of fields. This is the formula equivalent of SQL's COALESCE function, but with more explicit control over the fallback logic.

The Basic Pattern​

For text fields, the cascade uses nested IF and ISBLANK checks:

IF(
    NOT(ISBLANK(Primary_Source__c)),
    Primary_Source__c,
    IF(
        NOT(ISBLANK(Secondary_Source__c)),
        Secondary_Source__c,
        IF(
            NOT(ISBLANK(Tertiary_Source__c)),
            Tertiary_Source__c,
            "No value available"
        )
    )
)

Evaluation proceeds top-down: if Primary_Source__c has a value, it is used. Otherwise, Secondary_Source__c is checked, and so on. The final branch provides a default value.

Numeric Cascade: Checking for Both Null and Zero​

Numeric fields in Salesforce default to 0 rather than null in many contexts, so ISBLANK alone may not be sufficient. When zero is not a valid value, check for both:

IF(
    AND(NOT(ISBLANK(Contract_Price__c)), Contract_Price__c <> 0),
    Contract_Price__c,
    IF(
        AND(NOT(ISBLANK(Quoted_Price__c)), Quoted_Price__c <> 0),
        Quoted_Price__c,
        IF(
            AND(NOT(ISBLANK(List_Price__c)), List_Price__c <> 0),
            List_Price__c,
            0
        )
    )
)

This pattern selects the best available price: contract price takes priority over quoted price, which takes priority over list price. If none are populated, the formula returns 0.

Practical Use Cases​

Best Available Address Component​

When data arrives from multiple sources, addresses may be partially populated across different fields:

IF(
    NOT(ISBLANK(Override_City__c)),
    Override_City__c,
    IF(
        NOT(ISBLANK(Integration_City__c)),
        Integration_City__c,
        BillingCity
    )
)

Effective Date Selection​

IF(
    NOT(ISBLANK(Revised_Date__c)),
    Revised_Date__c,
    IF(
        NOT(ISBLANK(Confirmed_Date__c)),
        Confirmed_Date__c,
        Requested_Date__c
    )
)

Simpler Alternatives for Two-Field Scenarios​

When the cascade is only two levels deep, Salesforce provides built-in functions that are more concise:

BLANKVALUE (for text, date, and lookup fields)​

BLANKVALUE(Primary_Source__c, Secondary_Source__c)

Returns Primary_Source__c if it is not blank; otherwise returns Secondary_Source__c. You can nest them for three fields:

BLANKVALUE(Primary_Source__c, BLANKVALUE(Secondary_Source__c, "Default"))

NULLVALUE (for numeric fields)​

NULLVALUE(Contract_Price__c, List_Price__c)

Returns Contract_Price__c if it is not null; otherwise returns List_Price__c. Note that NULLVALUE treats 0 as a valid value -- it only falls through on actual null. Use BLANKVALUE if you want 0 to trigger the fallback.

Formula Size Limits​

Deeply nested cascades consume compiled formula size quickly. Salesforce enforces a 5,000-character compiled size limit (or 3,900 for some field types). Strategies to stay within limits:

  • Reduce nesting depth. If you have more than four or five levels, consider whether a workflow field update, flow, or Apex trigger would be more maintainable.
  • Use BLANKVALUE for chaining. BLANKVALUE(A, BLANKVALUE(B, C)) compiles smaller than the equivalent nested IF structure.
  • Simplify conditions. Avoid redundant checks; BLANKVALUE already handles the null check internally.
  • Split across multiple formula fields. Use an intermediate formula field for the first half of the cascade, then reference it in the final formula.

Readability Tips​

Nested formulas become difficult to read quickly. Follow these conventions:

  1. Indent each level to make the structure visible.
  2. Add comments in the formula description (not inline -- Salesforce formulas do not support inline comments) explaining the priority order.
  3. Name the formula field to reflect the pattern: Effective_Price__c, Best_Available_City__c, Resolved_Date__c.
  4. Document the priority order in the field's help text or description: 
    Priority: 1) Contract Price  2) Quoted Price  3) List Price  4) Zero

Key Takeaways​

  • The priority cascade is a fundamental formula pattern for selecting the best available value from multiple sources.
  • For numeric fields, decide whether zero should be treated as blank or as a valid value -- this changes the formula logic.
  • Use BLANKVALUE for simpler two-field fallbacks and to reduce compiled formula size.
  • Document the priority order explicitly; the formula structure alone is not self-documenting for complex cascades.