Help with 'Compiled formula is too big to execute'. - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Steve HuseSteve Huse 

Help with 'Compiled formula is too big to execute'.

Hi All,

I hope someone can help me with this please.

In CONTRACTS we have a custom field called 'Invoicing schedule' which is a picklist (values: Annually, 6 Monthly, 4 Monthly and Quarterly) where we record each customer's preferred invoicing schedule.

We also have some custom formula fields that 'add months' to the Contract StartDate.
Here's the 'Add 6 months' (SDAdd6M__c) one:

DATE( Year(StartDate)+floor((MONTH(StartDate) + 6 - 1) / 12) ,
mod(MONTH(StartDate) + 6 - 1, 12) + 1 ,
day(StartDate))

I'm trying to create some custom fields that provide the dates each invoice is due.

The first invoice is always due on Contract StartDate ... easy!

The date the second invoice is due depends on the chosen payment schedule. 
If 'Invoicing Schedule'='6 Monthly', then the 2nd invoice is due on StartDate + 6 months
If 'Invoicing Schedule'='4 Monthly', then the 2nd invoice is due on StartDate + 4 months
If 'Invoicing Schedule'='Quarterly', then the 2nd invoice is due on StartDate + 3 months

Our custom field formular to achieve this looks like this:

IF(ISPICKVAL(Invoicing_Schedule__c, "6 Monthly"), SDAdd6M__c,
IF(ISPICKVAL(Invoicing_Schedule__c, "4 Monthly"), SDAdd4M__c,
IF(ISPICKVAL(Invoicing_Schedule__c, "Quarterly"), SDAdd3M__c, null)))

Which is correctly written but generates the error 'Compiled formula is too big to execute' (6,251 characters).

Can anyone see a way that I can streamline these formulas to get the above formula below 5,000 characters?

I've heard of triggers, but not sure how to implement.

Thanks in advance.

Steve



Jayant JoshiJayant Joshi
I think you should break the formula into pieces. You can create another formula field and break out some part of the code into it.

You can refer to the SFDC Knowledge Article Number-105091 for this.

Jayant JoshiJayant Joshi
For your reference:


Knowledge Article Number: 105091

Formula field exceeds maximum number of characters
Description

Resolution

The code in a formula field can exceed maximum number of characters allowed in two ways:

  • Directly in the formula field's characters (3900), or
  • In the overall size of the formula after other included formula fields are factored in (5000)

The latter of those problems can result from the formulas (code) of other formulas being inserted where those other formula fields are included. For example:

  • Field A says "Total_Amount * .9"
  • Field B says "IF (Field_A__c = 0)..."
  • Then behind the scenes, B is expanded to say "IF ((Total_Amount * .9) = 0)..." because Formula_A's formula is put into Formula_B, not Formula_A's result

So while building or modifying a formula field if you run into either of these limits, what to do?

1. The smaller character limit (3900) can be remedied by splitting the formula into 2 fields. Create another formula field and break out some part of the code into that one. Then call Formula_A within Formula_B. Each can have up to 3900 characters.

2. The trickier case is when a formula field that refers to other formula fields (that may in turn refer to even more formula fields) results in compounded code which exceeds the 5000 character limit. In any edition, the whole combination of formula can be scrutinized to see if there is anything that can be reduced or eliminated from the string of code. Any small part might have a ripple effect and significantly reduce the overall size.

2b) In Enterprise or Unlimited Edition workflow can be used to help out. Create a new hidden field (not on any page layout). Create a new Workflow Rule for the object which triggers when record is Created or Edited and make the Criteria something that is always true, like Owner Name <not equals> NULL. Then make an Immediate Workflow Action which does a Field Update to the newly created (hidden) field, click on "Use a formula to set new value", then for the formula, just enter the name of the first formula field (Formula_A). Any time a record in this object is created or edited, Formula_A's results will be calculated and copied into this new regular field. Then refer to this field instead of Formula_A within Formula_B.

For best results on multi-level nested Formula field references, pick a spot near the middle of the chain of formula fields to split the overall code in half. This will reduce the size the quickest.

2c) Apex code could also be used in (EE, UE) to trigger an update to a field on the record. This provides even more powerful and flexible options, including the capability to assign values based on procedural algorithms (e.g., a double-declining balance) and to reference values beyond the record/object that is being evaluated in a formula or workflow, similar to the VLOOKUP() function--that can be employed on a validation rule but not in a custom formula field.


Formula field exceeds maximum number of characters