There is a text character and byte size limit of 3,900 characters and a limit of 5,000 characters for the compiled characters for formulas.
When this limit is reached, you will be unable to save the formula field and will be presented with the following error:
Compiled formula is too big to execute (7,085 characters). Maximum size is 5,000 characters.
It is common to encounter these limits when building complicated formula field calculations, particularly when building formulas that reference other formula fields. While there is no way to increase this limit, there are some methods to help avoid and work around these limitations; they are listed as follows:
- Use the
CASEfunction for branch conditions - Use algebra
For formulas that use multiple branch conditions to derive the values, as in the preceding example formula, check whether the market is US and the state is California, Nevada, or Utah. You can replace the nested IF statements and use the CASE statement instead.
Nested IF statements often result in larger compiled sizes where the IF function is used multiple times, as shown in our example:
IF(TEXT(Account.State__c) = "California", 0.0925,
IF(TEXT(Account.State__c) = "Nevada", 0.081,
IF(TEXT(Account.State__c) = "Utah", 0.0835, 0) )) Using the CASE statement can provide better logic and often results in a smaller compiled size for the formula:
IF( TEXT(Account.Market__c) = "US",
CASE(Account.State__c,
"California", 0.0925,
"Nevada", 0.0685,
"Utah", 0.0475, 0) ,
0)The compiled size of formula fields increases as you increase the number of fields that are referenced. This is compounded when you are referencing fields that are themselves formula fields. A way to reduce the overall size is to use algebra to avoid the need to reference fields wherever possible. The following example shows you how the Item_Price__c and Support_Price__c fields are used multiple times:
Total Price = (Item_Price__c + (Item_Price__c * Sales_Tax__c)) + (Support_Price__c + (Support_Price__c * Sales_Tax__c))
To reduce the compiled size, use simple algebra to avoid multiple uses of the Item_Price__c and Support_Price__c fields, as shown in the following example:
Total Price = (Item_Price__c * (1 + Sales_Tax__c)) + (Support_Price__c * (1 + Sales_Tax__c))
There might be situations where the logic that is required for a formula is simply too complex for the current size limitations in formula fields. The proven methods to overcome this are to implement a solution using either of the following:
- Workflow field updates
- Apex trigger updates
There are two ways in which workflow field updates can help provide a formula logic workaround. Firstly, larger and more complex formulas can be saved using the formula-building function within the workflow mechanism. Secondly, large formula logic can be decomposed into smaller functions of resulting data. For example, you could create simple formulas that get the data fed from fields that have been updated by multiple workflow field updates.
Workflows are covered in detail later in this book. However, the general approach to implementing a workflow field update to provide a solution to the formula field limit is to do as follows:
- Create a nonformula field on the object, such as a currency or number field, in place of the desired formula field. Administrators often identify this field with a suffix to indicate that it is a workflow field—for example, Total Price (workflow). This field is then set as read-only on page layouts, as the field can be considered a system field (as it should not be available for manual updating).
- Create a workflow rule that will always fire.
- Create a field update with an appropriate formula to update the workflow field—Total Price (workflow) in our preceding example.
Any subsequent formulas can reference the populated field. The disadvantages to this workaround are that creating many workflows can add to the complexity of the application and might eventually introduce performance issues. Also, whenever an object has multiple complex workflows assigned, the order in which the workflows are evaluated cannot always be guaranteed, which if not properly maintained, can lead to subtle data discrepancies.