Formula field is too big to execute (5,480 characters)

Question

I got an error when i am trying to add fomrula in formula field.

Error: Compiled formula is too big to execute (5,480 characters). Maximum size is 5,000 characters

My formula is

(ED__c+Service_Tax__c+Total_Site_Work_Price1__c+Total_Selling_Price_Based_on1__c )*WCTper__c

In this formula ED__c, Total_Site_Work_Price1__c, Total_Selling_Price_Based_on1__c are also Formula fields. And these formulas are also contain fomrula fields.

How can i calculate the formula..?

Answer 1

As others have pointed out, the compile size for a given formula includes the compile sizes for other formulas that it has referenced. The additive nature of compile size is causing you to exceed the limit with this one.

Without seeing the content in your other formulas, it's hard to say exactly what's causing the large compile size. Before you leap to Apex, it's worth a look to see if your formulas are written as efficiently as possible (which is a good practice anyway). There is a doc from Salesforce on this very topic, Tips for Reducing Formula Size, which has a number of suggestions and examples:

1. Minimize the number of references to other fields
2. Minimize the number of times formula functions are called
3. Rethink your picklist
4. Think about the problem another way
5. If all else fails, use a workflow field update (or trigger)

Your biggest win will be reducing formulas that are referenced multiple times.

Answer 2

Formulas are compiled, in that they include up all the refernced other formulas as well.

That makes for a very large formula in your case. You can calculate the exact size by replacing the references by the actual formulas they reference and check the total length.

Now, instead of doing that, it's probably a better idea to rethink your approach, since these huge formulas are most often a symptom of 'bad' design, i.e. there are probably other more efficiënt options to achieve what you want.

Answer 3

As other have pointed out, the length of a formula is the sum of the length of the formulas it references.

If you're unable to reduce the size of these formulas, and you're unable to change your design, a way around this is to replace one (or more) of the formulas with a workflow rule update. It's a hack, but it gets around this specific problem.

For example, if you have a formula field total_price__c that is essentially qty__c * item_price__c, you can create a workflow rule that fires on either qty__c or item_price__c being updated, with the total_price__c field updated with the formula.

This is a very simplistic example, but what it means is that total_price__c is no longer a formula, so it can be referenced without adding significantly to your formula length.

I think it's worthwhile evaluating your design first though, as this approach introduces maintenance issues going forward.

Answer 4

The compile size exceeds the limit because of the three formula fieldsyou have used in the current formula. You can write a trigger to populate these fields.

ED__c, Total_Site_Work_Price1__c, Total_Selling_Price_Based_on1__c

and then you can use these fields in your formula field then.An alternate solution would be create a custom field and write a trigger to implement the business logic using the existing formula fields.

Answer 5

I recently published an app named Unleash Your Formula. https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3u00000MS8T1EAL

The app does something simple, it copies and syncs formula fields to a specified non-formula field (of the same type). This is an effective way to handle character limits for your situation.

First create 3 new (non formula) fields that you will use to sync the ED__c, Total_Site_Work_Price1__c, Total_Selling_Price_Based_on1__c formulas to, then reference these new fields in your formula instead. The character limits of those formulas will no longer be counted as part of your formula.