Skip to content

PranavNagrecha/AwesomeSalesforceSkills / skills/admin/formula-fields

formula-fields

Verified

Use when designing, reviewing, or troubleshooting Salesforce formula fields. Triggers: 'formula field', 'cross-object formula', 'null handling', 'compile size', 'HYPERLINK', 'IMAGE', 'why is formula slow', 'formulaTreatBlanksAs', 'BlankAsZero', 'blank field handling', 'field-meta.xml formula', 'CustomField formula deploy', 'formula returns blank', 'formula field read-only on data load'. NOT for a formula field slowing a SOQL query or blocking an index — use apex/formula-field-performance-and-limits. NOT for Formula resources inside a Flow — use flow/flow-formula-and-expression-patterns.

references/gotchas.md

View on GitHub
# Gotchas: Formula Fields

---

## Formula Fields Do Not Preserve History

**What happens:** A business user expects a formula to show the value that was true when the record was closed. Instead, it keeps recalculating based on current parent data and "rewrites history."

**When it bites you:** Closed-won metrics, renewal snapshots, compliance indicators, and lifecycle milestones.

**How to avoid it:** If history matters, store the value in a real field via Flow or Apex at the right lifecycle moment.

**Example:**
```text
Bad fit: Renewal_Risk_Snapshot__c as a formula
Better fit: Renewal_Risk_Snapshot__c populated when Stage changes to Closed Won
```

---

## Cross-Object Convenience Becomes Reporting Debt

**What happens:** A few helpful parent references turn into a network of chained formulas across Account, Contact, Opportunity, and custom objects. Reports become harder to understand and slower to evolve.

**When it bites you:** Mature orgs with years of admin-added helper formulas.

**How to avoid it:** Keep cross-object formulas intentional, shallow, and documented. Move heavy logic to stored fields when usage grows.

**Example:**
```text
Acceptable: Contact -> Account.Owner.Name
Risky: Child -> Parent -> Grandparent -> Great-grandparent decision logic
```

---

## Blank Handling Depends on Field Type

**What happens:** Admins assume blank text, zero numbers, and unchecked checkboxes all behave the same. The formula returns unexpected values.

**When it bites you:** Discount math, scoring, compliance flags, and display labels.

**How to avoid it:** Test each field type explicitly and handle `0`, null, and empty text deliberately.

**Example:**
```text
Revenue__c = 0 is not the same business meaning as Revenue__c being blank
```

---

## Decorative Functions Get Overused

**What happens:** `IMAGE()` and `HYPERLINK()` start as helpful UI enhancements, then become critical to understanding business state. Mobile, reports, and downstream users do not always consume them cleanly.

**When it bites you:** Executive dashboards, integrations, and large report exports.

**How to avoid it:** Keep the underlying business value in a plain formula or real field, and make the decorative formula secondary.

**Example:**
```text
Store Status__c = "At Risk"
Optionally show Risk_Icon__c with IMAGE() for page UX
```

---

## `formulaTreatBlanksAs` Is One Switch For The Whole Formula

**What happens:** An admin sets Blank Field Handling to "Treat blank fields as zeroes" so a missing
discount stops blanking a subtraction. Every other blank reference in the same formula silently
becomes zero too — including the date comparison at the bottom that was relying on a blank date to
mean "never contacted".

**When it bites you:** Any formula that mixes numeric arithmetic with date or text references, and
any formula that grows a second clause months after the blank-handling choice was made.

**How to avoid it:** The Metadata API exposes exactly two values, `BlankAsBlank` and `BlankAsZero`,
on a single `formulaTreatBlanksAs` element per field (Metadata API Developer Guide, *CustomField* →
Fields, api_meta.txt:43424–43426). There is no per-reference override. Pick the setting for the
arithmetic, then guard every non-numeric reference explicitly with `ISBLANK()` or `BLANKVALUE()`
rather than relying on the switch. Write the element into the XML deliberately; a formula deployed
without it inherits a default you did not choose.

**Example:**
```text
BlankAsZero + (TODAY() - Last_Activity_Date__c > 60)
  -> a blank Last_Activity_Date__c does NOT evaluate false; it enters date arithmetic
Guard it: NOT(ISBLANK(Last_Activity_Date__c)) && TODAY() - Last_Activity_Date__c > 60
```

---

## A Formula Field Is Read-Only In The API, So Nothing Can Ever Write To It

**What happens:** A data load, a Flow assignment, or an Apex `insert` includes the formula field in
its column list. The load fails on the field, or the value is dropped and the team spends a day
looking for the automation that "cleared" it.

**When it bites you:** Migrations that map a legacy stored column onto what is now a formula field;
converting an existing stored field into a formula and forgetting the integrations that populate it;
`upsert` templates generated by describing the object and taking every field.

**How to avoid it:** The Object Reference is explicit: "Calculated fields are read-only fields in the
API" (object_reference.txt:2207–2211). Before converting a stored field to a formula, list every
writer — data loads, Flows, Apex, inbound integrations — and retire each one first. Cross-reference
`admin/custom-field-creation` for the conversion path.

**Example:**
```text
Stored Net_Amount__c populated nightly by an ETL job
  -> converted to a formula
  -> the ETL job now errors on an unwriteable field, every night, until someone reads the log
```

---

## Calculated Fields Do Not Replicate, So Downstream Systems Never See The Change

**What happens:** A parent Account value changes. Every child formula that references it shows the
new value in the UI immediately. The replication-based integration that syncs the child object never
picks it up, because the child record itself was never modified.

**When it bites you:** Data warehouse loads, mirroring tools, and anything driven by "records changed
since timestamp" logic. The org and the warehouse disagree and nobody can find a bad record.

**How to avoid it:** The Object Reference states the constraint alongside the SOQL one: "You can
filter on these fields in SOQL, but you don't replicate these fields" (object_reference.txt:2208–2209).
If a downstream system needs the derived value, either have it recompute the value from the source
fields it already replicates, or stop using a formula and stamp a real field. Do not solve it by
adding another formula.

**Example:**
```text
Account.Rating changes
  -> Contact.Account_Rating__c (formula) shows the new value in the UI
  -> Contact.SystemModstamp is unchanged, so the incremental extract skips the Contact
```

---

## A `<` Or `&` In The Formula Breaks The Deploy Before Anything Compiles

**What happens:** A formula that saves perfectly in the Setup editor is pasted into a
`.field-meta.xml` and the deploy fails with an XML parse error rather than a formula error. The
message names the file, not the formula, so the admin re-checks the logic instead of the encoding.

**When it bites you:** Any source-tracked or CI deploy where formulas are edited as files —
which is every deploy once the org is in a DX repo — and especially formulas ported from a Setup
screenshot or a chat message.

**How to avoid it:** `&`, `<`, `"` and `'` are XML metacharacters inside `<formula>`. The Metadata
API guide's own samples escape them: `<formula>Name &amp; &quot;Updated&quot;</formula>`
(api_meta.txt:140599) and `<errorConditionFormula>OR(Name = &apos;Milo&apos;, ...)`
(api_meta.txt:45439–45440). Parse the file locally before deploying — `scripts/check_formula_fields.py`
fails on malformed XML for exactly this reason.

**Example:**
```text
Setup editor:  Amount < 1000 && ISBLANK(Discount__c)
File:          <formula>Amount &lt; 1000 &amp;&amp; ISBLANK(Discount__c)</formula>
```

---

## `CustomField` Takes No Wildcard, And Retrieving One Rewrites Your Profiles

**What happens:** An admin adds `<members>*</members>` under `<name>CustomField</name>` and gets
nothing back, or retrieves one formula field and finds a hundred lines of profile diff in the pull
request.

**When it bites you:** The first CI pipeline on a new repo, and every "just retrieve the one field"
hotfix.

**How to avoid it:** Two separate statements in the guide's `CustomField` section. Wildcards: "This
metadata type doesn't support the wildcard character `*` (asterisk) in the `package.xml` manifest
file" (api_meta.txt:43983–43984) — list every field as `Object.Field__c`. Profiles: "Retrieving a
component of this metadata type in a project makes the component appear in any `Profile` and
`PermissionSet` components that are retrieved in the same package" (api_meta.txt:43251–43252) —
retrieve fields in a manifest that contains no profiles unless you intend to review the FLS diff.

**Example:**
```text
Wrong: <members>*</members>          <name>CustomField</name>
Right: <members>Account.Health_Icon__c</members>   <name>CustomField</name>
```

---

## Text Formula Results Are Truncated At 3,900 Characters

**What happens:** A concatenation formula that assembles a long description, an address block, or a
generated URL returns a value that is silently cut off. Nothing errors; the tail is simply gone.

**When it bites you:** `IMAGE()` and `HYPERLINK()` chains, formulas that build JSON or a query
string for an external system, and formulas that concatenate several long text fields.

**How to avoid it:** "The length of text calculated fields is 3,900 characters or less — anything
longer is truncated" (Object Reference, *Calculated Field Type*, object_reference.txt:2207–2211).
This is a limit on the **result**, and it is a different number from the three formula *size* limits
in `references/llm-anti-patterns.md` (anti-pattern 2), which govern the expression rather than its
output. If the output can exceed it, the value does not belong in a formula field.

**Example:**
```text
Long_Description__c (Long Text, 32,000) & " — " & Notes__c
  -> the formula field returns the first 3,900 characters and no warning
```
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 322kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 118kUpdated Claude CodeCodexVerifiedInstall