Pages

Saturday, November 2, 2013

Salesforce formula size

There are 2 limitations on Salesforce formula:
1. Character length limit, contain up to 3,900 characters, including spaces and line breaks.
2. Compile size limit, formula fields are calculated using generated SQL on the backend. This SQL must not exceed 5,000 characters. This includes other referenced formula fields.

Formulas are limited to 3,900 characters or 4,000 bytes, including spaces, return characters, and comments, and can’t exceed 5,000 bytes when compiled. It’s important to understand the differences between these size restrictions and how to work around or within the constraints.


Character length limit
For this limitation, you can easily count number of characters including spaces and line breaks, with Ms Word, it is easy to count it, so you can make sure do not over it.

You can shorten long formulas in several ways. Replacing AND() with &&, for example, saves a few characters with each use, as does replacing nested IF() statements with a CASE() statement. Shorter field names and comments also make a small but significant difference in the length of your formula.

If your formula field is significantly longer than 3,900 characters, use a helper formula field.

Options for this error:
1. Move parts of the formula into other formula fields, and reference those in your main formula. The new formula fields don’t need to appear on the page layout.
Example: create new date formula called "Short__c" with formula DATE( YEAR( Membership_Start_Date__c ), use Short__c in the formula field rather than the long formula, this will reduce number the characters, but this will NOT reduce compile size for the original formula (it will even increase it, compile size do not impact by the the length of API field name)

2. Shorter API field name, example: you can have label name 'This is a long field name', by default API name will be This_is_a_long_field_name__c, but you can modify the API name to Long_field__c


Compile size limit
A formula that is less than 3,900 characters can still exceed the 5,000-byte compile size limit. When a formula is over the compile size limit, creating helper fields and shortening field names or comments doesn’t make a difference. When you reference a helper field, its compile size is added to the compile size of the formula that references it. 

Here are few option to reduce formula size issue:
  • Minimize the number of references to other fields
  • Minimize the number of times formula functions are called
  • Rethink your picklist
  • Think about the problem another way
  • If all else fails, use a workflow field update

Minimize the number of references to other fields
The most important thing you can do to reduce your formula compile size is reduce the references to other formula fields. Each time you reference a field, the compile size of that field is added to your current formula. 

Minimize the number of times formula functions are called
Try to limit use of formula fields within a formula, you can try to change from using IF() to CASE() as possible.

Rethink your picklist
You can consider of making the picklist a lookup to a  custom object with the name as the picklist value, then create a custom field on that object for the value you need to set in the formula.

Think about the problem another way
This about to simplify the formula as possible, example: introducing CASE(), MOD(), NOT(), etc

Use a workflow field update
This maybe the last option, workflow will not update value for all existing records, unless you do mass update (which this will effect Last Modified By information). But, formula in a field update have bigger compile size limitation.


Reference: