Filed under: Administration October 1, 2009

Occasionally Salesforce.com’s formula compile size limit is hit when creating Formula fields on Salesforce.com objects. A message similar to, “Compiled formula is too big to execute (13,831 characters). Maximum size is 5,000 characters,” will occur. (Please note the example formulas below are for illustration purposes and will not compile correctly in Salesforce.com.)

How Large Can A Formula Be?

Formula fields start off as a semi-human-readable formula with a maximum of 3900 characters. The formula is then converted into machine-readable code in a process called compiling. The result cannot exceed 5000 characters. We do not know the exact process Salesforce.com uses, but other systems consider lexical analysis, preprocessing, parsing, semantic analysis, code generation, and code optimization. Therefore, it is very difficult to predict the resulting size and the only practical way is to use the “Check Syntax” button and have the system calculate it.

Several reasons this error occurs along with several solutions are discussed below, going from the simplest to the complex.

Use Algebra

Many times a formula will reference other formulas. Salesforce.com actually pulls in all the sub-formulas into one big statement before processing. Therefore, multiple nesting of formulas can cause an explosion in size.

One effective solution is to use algebra if a sub-formula is referenced more than once. For example, a 25% discount can be calculated by:

Final Price = Sales Price - Discount * Sales Price

where

Sales Price = 90% * List Price, and
Discount = 25%

Sales Price is used twice in the Final Price formula. Algebra can be used to rewrite the formula so that it is referenced once:

Final Price = Sales Price * (100% - Discount)

This is a simple example, but it could easily become a large formula if Sales Price took into account Customer Type, Region, and other factors.

This approach is very effective, however, there are situations where this will not be sufficient.

Use CASE Instead Of Nested IFs

There are situations where a value is dependent on a text value, such as discounts based on customer type. Many times IF statements are used multiple times for this and these are commonly called “nested IFs.” For example:

Discount = IF( Type = "Consumer", 10%, IF( Type = "Reseller", 30%, 0 ) )

Nested IFs generally result in large compiled sizes. Salesforce.com provides a CASE statement to accomplish the same thing but with smaller resulting sizes.

Discount = CASE( Type, "Consumer", 10%, "Reseller", 30%, 0 )

Look up CASE in Help to learn more.

A significant limitation of CASE is that it cannot return Boolean (TRUE, FALSE) values, so return 0 or 1 and then wrap a single IF around it to return TRUE or FALSE.

Use Workflow Field Update

When CASE statements are used with large picklists, it can still overwhelm the limit. A typical scenario would be to translate all the countries in the world to five regions (e.g., North America, Europe, etc.).

In situations where the formula is simply too large, Workflow Field Updates can be used (Enterprise and Unlimited Editions only). The formula field for Field Updates has a much larger limit. There is actually no documentation on what the limit is.

The approach is to:

  1. Create a non-formula field instead of what would normally have been a Formula field on the object
    • On Page Layouts set this field as Read Only since users should not manually update this field.
    • Field Level Security can also be used if the Default Workflow User has System Administrator privileges
  2. Create a Workflow Rule that will always fire
    • For the Evaluation Criteria, choose “Every time a record is created or edited”
    • For the Rule Criteria, select “formula evaluates to true”
    • Enter “true” in the formula box
  3. Create a Field Update with a formula to update the field on the object
  4. Any subsequent formulas can reference the populated field

The main drawback to this solution is when an object has multiple Workflows. Salesforce.com does not guarantee the order in which Workflows are evaluated. Therefore, it is possible for a Workflow to fire based on a field that has not been updated yet. It may be possible to adjust the Evaluation and Rule Criteria for some situations. Validation Rules might manage the situation as well.

Use an Apex Trigger

If all else fails, an Apex Trigger could be implemented (Enterprise and Unlimited Editions only). Programming skills are required to implement Triggers. Triggers are Apex code that run based on the state of a transaction. Typically the state is right after manual updates to the record have been saved to the database.

The approach is similar to the outline for Workflow Field Update except substitute a Trigger for the Workflow.

The main advantage in this situation is that Triggers are evaluated before Workflows, therefore ensuring that Workflows do not fire before a criteria field has been updated. However, make sure that the Trigger is not dependent on a Workflow firing first.

The disadvantages are the programming skills required to create and then maintain the code as well as the effort it takes to create a Trigger.

Summary

While 5000 characters is fairly generous, the compile size limit is hit occasionally.  These four solutions are the most common ones.  I would be interested in hearing of any additional solutions.

  1. Pingback: Writing Your First Apex Trigger Overview « Vision Into Reality

  2. Natalie says:


    Thanks for your post, it was very helpful. My CASE formula was 7 chars over the limit so I went with a workflow update instead.

  3. Pingback: Copying Multi-Select Picklist Values With A Formula Field

  4. Alden says:


    Thanks so much! I can’t tell you how useful this post was. I was hitting a problem trying to do a calculation with 50 custom calculation fields, each which was near the 5k limit per field. The workflow method works great, even with a combined “character amount” of ~250,000 characters.

    Thanks so much for the great idea!

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2009-2015 Hayata Takeshita