The dark side of 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.

1. Since formula fields are calculated, and not actually stored in the database, they are not available in before-save record-triggered automation.
2. The compiled size has a 15,000-character limit. This means you need efficient logic, as it can quickly grow past this limit
3. 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. It’s a regular text field and the system copies Receipt Status to Receipt Status Indexed at various moments.

The resulting SOQL statement is below and is incredibly fast.

SELECT Id FROM Opportunity 
WHERE Receipt_Status_Indexed__c = 'Eligible'

The takeaway
Don’t use formula fields in report filters or SOQL WHERE clauses.