+ Start a Discussion

Compiled formula is too big to execute

I have encountered this problem and found many similar tips, only to find that the information appears to have been obtained from a Salesforce Help page. In an effort to end that practice I am posting the actual source, which can also be found be searching on "Formula field exceeds maximum number of characters":



Formula field exceeds maximum number of characters

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.