The dark side of formula fields
Summary
Formula fields offer quick, dynamic business logic but come with limitations like unavailability in before-save automation and compiled size constraints. They also cause significant performance issues when used in report filters or SOQL WHERE clauses. The best practice is to avoid filtering queries on formula fields and instead create shadow indexed fields that replicate the formula’s value for efficient querying.
Takeaways
- Avoid using formula fields in report filters and SOQL WHERE clauses.
- Formula fields aren’t available in before-save record-triggered automation.
- Keep formula field logic efficient to stay under the 15,000-character compiled size limit.
- Create shadow indexed fields to replicate formula values for better SOQL performance.
- Querying on regular indexed text fields is much faster than on formula fields.
Formula fields are great because they allow you to place business logic in a field that can be quickly referenced. However there are some drawbacks you should be aware of. Since formula fields are calculated, and not actually stored in the database, they are not available in before-save record-triggered automation. The compiled size has a 15,000-character limit. This means you need efficient logic, as it can quickly grow past this limit It’s incredibly inefficient in queries. Using a formula field in a report filter or in SOQL results in poor performance. As of last week, the last point was new to me. I was constructing the SOQL statement SELECT Id FROM Opportunity WHERE Receipt_Status__c = 'Eligible' According to AI, Receipt Status is a “God Formula”. It’s impressive, but a nightmare for the Salesforce Query Optimizer. LOL. So I had to create a shadow field called Receipt Status Indexed.