ShowAll Questionssorted byDate Posted
jjvdev

# Reducing Formula Compile Sizes

I've noticed that using multiple operators with CONTAIN functions ( : ) instead of OR functions and using CASE statements instead of nested IF statements can have a big impact on reducing formula compile sizes.

What are some other things for reducing compile sizes?

Navatar_DbSup

Hi,

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 )

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:

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
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

Create a Field Update with a formula to update the field on the object
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.

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

Siddharth Birari.ax1164
Hi Pal,

I went through what you have described about all the possible use cases.
Right now, I have a similar requriement where I  need to populate one custom field say Zone (Picklist), which would be dependent on one of my another custom field say Suburb (Picklist field).

One foremost approach is to opt for dependent picklist, however my field 'Suburb' has more than 300 values hence I cannot make use of dependent picklist.

The next three options are

1) Formula Field - Here I cannot afford to change the field type to formula. In fact you cannot change the field type to formula from any other field type.
2) Workflow Rule  - Workflow rule could have worked, however I am updating a picklist value through workflow, hence cannot have the formula field in it. Workflow again doesn't solves my problem.
3) Trigger - Yes, Trigger makes my job simpler but comes up with one overhead. When I have my picklist values altered, I need to make the code changes. Here in order to prevent the recursive code changes, I may end up with creating the custom settings. In that case, while altering the picklist values, I need to be sure that I make necessary changes in my custom settings too.

As of now, I am solved with my problem with the help of a Workflow rule.

Regards,