Welcome to another “How I Solved This.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Michael Kolodner was able to beat the dreaded “compiled formula is too big to execute” error. Read more details in the post below.
Formula fields in Salesforce are incredibly powerful. You can bring several text fields together, calculate percentages, compare dates, and do so much more. And the beauty of a formula is that it’s calculated dynamically whenever referenced, so it’s never out of date due to a related record being edited or the like. But there are limits, primarily meant to ensure that the system runs smoothly for all the users in your org and in our shared cloud environment. There’s a 5,000 character limit on the length of your formula and any underlying formulas it might rely on. What does that mean? To prevent formulas from slowing down the server, you have to keep them contained. They can’t reference too many other formulas or do too much complicated processing. The platform will prevent you from saving a formula that’s going to be larger than the limit.
We all run into it at some point. We’re working on a formula field and suddenly get the scary red text when we try to save:
Womp womp. What do we do now?
Giving up isn’t an option. We were building this formula to meet a need for our colleagues or clients. So, we’re going to have to find some other way to get the value we were hoping to display.
Step one is to try to simplify the formula. If the error puts you within just a few characters of the limit, you might be able to make a small adjustment that will simplify things.
The most common example here is switching from multiple nested IF statements to a single CASE formula. That reduces the compiled complexity and also makes it easier for humans to read. This formula of four nested IF statements, for example, won’t compile. The four nested IFs are checking if the month value in the date Big__c is 3, 4, 5, or 6 and then displaying the month name for those months:
IF( MONTH(Big__c) = 3, "March", IF( MONTH(Big__c) = 4, "April", IF( MONTH(Big__c) = 5, "May", IF( MONTH(Big__c) = 6, "June", "Outside Month Range"))))
As you can see in the screenshot below, it’s 7,214 characters, way outside the limit.
But rewrite it like this and it’s both easier to read and stays within the limit:
CASE( MONTH(Big__c), 3, "March", 4, "April", 5, "May", 6, "June", "Outside Month Range")
And it does exactly the same thing as it did when it was nested IF statements.
The main reason that worked is that now the formula only calls on the Big__c field once. And—spoiler alert!—that field is itself a formula that already has a pretty high compiled size.
Sometimes, simplifying the formula reduces the compiled size a little, but maybe it’s not enough. Here, for example, I took out a rounding function from a formula and it dropped from 7,204 characters to 7,184. That’s not enough.
Before - the Percent_of_Period_Given__c field with ROUND() two times
After - the Percent_of_Period_Given__c with no ROUND()
So, simplifying your formula might only get you so far. I still need that Percent_of_Period_Given__c field to solve my business requirement. And, ultimately, I need to round it as well.
If the formula simply can’t get any smaller, then it’s time to make it not a formula! I teased before that the Big__c field is the real problem. Even though it will save, it’s already 1,741 characters when compiled. So, any formula that calls Big__c more than once is going to struggle with the limit. I solved my problem by having a number field instead of a formula field and filling that value with a flow.
Everywhere you see in Big__c above is actually the Effective_Ended_Date_1__c field. Formulas based on Effective_Ended_Date_1__c don’t have any problems because Effective_Ended_Date_1__c isn’t a formula—it’s a number.
My record-triggered flow has just one element on the canvas and one flow resource:
It’s an after save record-triggered flow that runs every time a record is created or edited. All it does when it fires is update the record that launched it to fill the Effective_Ended_Date_1__c field with a value calculated by a formula.
This is the formula element used to fill the field:
The formula in that element is the same as the formula in my Big__c field, which has a compile size of 1,741 characters:
IF( NOT(ISBLANK(Ended_Date__c)) && Ended_Date__c < Period_Ending_Date__c, Ended_Date__c, IF(Ended_Date__c > Period_Ending_Date__c, Period_Ending_Date__c, Period_Ending_Date__c ))
As long as the record is updated like this every time it changes, this is pretty much the equivalent of that field being a formula field. And since it’s built as a flow, it fires really fast and relatively early in the order of operations. You’re going to be able to count on it.
Now, instead of using Big__c in the formulas above, I can reference Effective_Ended_Date_1__c and my formulas compile without complaint. Users never know there’s anything different going on backstage!
At some point in your Salesforce journey, you’re going to find a formula field that gets too big for its britches. I hope you’ve now got the tools to tackle it. I’d love to hear from you on the Trailblazer Community about your struggles and solutions.
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one Awesome Admin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Deanne Walters uses Flow to create […]
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one Awesome Admin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Dee Ervin searched unsearchable field data […]
Welcome to another “How I Solved It.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Rachel Park Brayboy used her sewing (and […]
