Salesforce Formula Size Tipsheet
DOWNLOAD
First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.
Tips for Reducing Formula Size
Salesforce Spring
salesforcedocs
Last updated November
Copyright Salesforce Inc All rights reserved Salesforce is a registered trademark of Salesforce Inc as are other
names and marks Other marks appearing herein may be trademarks of their respective owners
CONTENTS
Tips for Reducing Formula Size
Reducing the Length of Your Formula
Reducing Your Formulas Compile Size
Need more help
TIPS FOR REDUCING FORMULA SIZE
In Salesforce formulas are used in many types of business logic including formula fields default values for fields workflow rules and
validation rules Use formulas to calculate something combine data from multiple fields check for a condition or show information
from one record on a related record Formulas can be simple or complex ranging from basic strings or numbers to complex structures
with layers of nested logic extracting data from multiple fields
Formula size is made up of two parts The first is the formula syntax that you write in the formula editor and save as a formula field
validation rule and so on The second is the database query thats compiled from your formula syntax and executed using your data at
runtime The query can be much larger than the formula syntax that generates it because it requests data from all the fields involved
performs operations and in many cases performs error checking on resulting values Think of a formula like an iceberg the visible part
is your formula syntax but there is a big piece below the surface that you cant see
Formula size problems come in two flavors the formula is too long or its compiled size is too big Lets look at some tips for preventing
these issues
REDUCING THE LENGTH OF YOUR FORMULA
Formula syntax is limited by the maximum size of the database field that stores it For any Salesforce edition youll get an error message
if you exceed these limits
Maximum number of characters characters
Maximum formula size when saved bytes
When your formula exceeds one of these limits the error message either says Formula is too long characters Maximum length
is characters or The size of your string is bytes but may not exceed bytes This means the text that you typed into
the formula editor has more characters than the database can store in one formula field Character count includes spaces carriage returns
and comments If your formula includes multibyte characters such as in Japanese or other multibyte languages the number of bytes
is different than the number of characters
To correct formula limit errors move parts of the formula into one or more secondary formula fields and reference those in your main
formula The secondary fields dont need to appear on the page layout
To figure out which bits of your formula to break out look for large blocks of text or expressions that are repeated multiple times Breaking
up a formula can also make it easier to understand if the secondary formula fields are named well
For example suppose you have a Membership Start Date field and youre using that date to calculate a Membership End Date in the
interest of space well keep it short but pretend it exceeds characters
IF
MONTH MembershipStartDatec
DATE YEAR MembershipStartDatec
DATE YEAR MembershipStartDatec
Because you repeatedly reference YEARMembershipStartDatec you can move that into a formula field called Start
Year You can also move MONTHMembershipStartDatec into its own formula field
Then the main formula becomes
IF
StartMonthc
DATE StartYearc
DATE StartYearc
Youre now down to characters from Since there are editionbased limits on how many fields you can create for an object take
them into account when figuring out how to break up a long formula You might only want to create StartYearc in the example
above
Keeping comments and field names as short as possible while still being descriptive can also help You might want Membership Start
Date to be the label for your users but you could conserve space by overriding the default API name MembershipStartDate to
StartDate or MemStartDate Override the default name when you create a custom field rather than trying to change it later
You can remove blank spaces and carriage returns but this makes complex formulas harder to read so consider that a last resort
REDUCING YOUR FORMULAS COMPILE SIZE
The query thats compiled from your formula syntax is limited by the maximum query size that the database can execute This limit is
the same for all Salesforce editions Maximum formula size in bytes when compiled bytes
When your formula exceeds this limit the error message says Compiled formula is too big to execute characters Maximum
size is characters This error message means that the query generated by your formula syntax is too big for the database to handle
all at once
Reducing the number of characters in your formula doesnt help comments white space and field name length make no difference on
the compile size Breaking the formula into multiple fields doesnt help either because the compile size of each field is included in the
main formulas compile size
Fortunately there are ways to work around this limit and you can avoid it in many cases by making your formulas more efficient
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 A simple field type like a Date is small but for other
formula fields the size can add up
Consider two fields
Datec is a Date field
Datec is a formula field that creates a date from Datec
DATE YEAR Datec MONTH Datec DAY Datec
Using Datec in a formula adds characters to the compile size each time its referenced Datec adds characters each
time Lets look at the impact on this formula that generates a deadline two business days after a given date
CASE MOD SomeDatec SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
DATE
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Default
Reducing Your Formulas Compile Size
Replacing SomeDatec in the formula with Datec gives us a formula compile size of characters But replacing
SomeDatec with Datec compiles to characters Most of that is the nine references to Datec
characters
So how can we reduce the number of times we reference other fields
Leverage the default value in CASE
The last argument in the CASE function is the default value If you have many cases with the same value use it as the default
to reduce the number of checks Lets look at the deadline formula
CASE MOD SomeDatec SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
DATE
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Default
The default value in this formula is SomeDatec But the value of MOD Datec DATE
is always so the default value is never used This formula could be rewritten as
CASE MOD SomeDatec SomeDatec
SomeDatec
SomeDatec
SomeDatec
SomeDatec
DATE
Sunday
Thursday
Friday
Saturday
Default MonTuesWed
By making the MondayTuesdayWednesday case the default date days we reduce the compile size to characters for
Datec and to for Datec
Use CASE instead of nested OR statements
The following formula returns the date of the last day of the month for a given date assume February always has days
DATE
YEAR SomeDatec
MONTH SomeDatec
IF
OR
MONTH SomeDatec
MONTH SomeDatec
MONTH SomeDatec
MONTH SomeDatec
IF
MONTH SomeDatec
Reducing Your Formulas Compile Size
The formula first checks for months with days then February and the remaining months are days It requires a nested IF
function which isnt readable and compiles to characters for Datec and a whopping characters for Datec
Why Because the formula references the date seven times Compare that to this revised version of the formula
DATE
YEAR SomeDatec
MONTH SomeDatec
CASE
MONTH SomeDatec
The revised version is easier to read and the formula compiles to only characters for Datec and characters for
Datec It also references the date three times instead of seven
Rearrange the logic
This example came from the Salesforce Answers community A picklist stores the name of an agent responsible for an opportunity
The formula calculates a commission based on Base Commission value and a multiplier But because BaseCommissionc
is referenced in each condition of the CASE statement the formula exceeds the compile size
CASE Agentc
John BaseCommissionc
Jane BaseCommissionc
Repeat for many other agents
BaseCommissionc
To fix this issue move BaseCommissionc outside the CASE function The formula can be rewritten as
BaseCommissionc CASE Agentc
John
Jane
Repeat for many other agents
Even if Base Commission is only a Currency field and not a formula itself referencing it once instead of multiple times greatly reduces
the formula compile size
As another example lets use our business days formula
SomeDatec
CASE MOD SomeDatec DATE
Sunday
Thursday
Friday
Saturday
Default MonTuesWed
Weve now further reduced the size to characters for Datec and to characters for Datec which is almost a
quarter of the original formula size
Reducing Your Formulas Compile Size
Minimize the Number of Times Formula Functions Are Called
All formula functions arent compiled equally Daterelated functions in particular like DATE and MONTHgenerate large
queries The fewer times you must reference functions the smaller your compile size is likely to be
We just saw an example where we rearranged the logic in a CASE statement to reduce the number of times a field was referenced
This strategy also works for formula functions
Heres another example from the Answers community A picklist contained a list of airlines The formula returned a link to the airlines
website and looked something like this
CASE Airlinec
Airline HYPERLINKhttpairlinecom Airline
Airline HYPERLINKhttpairlinecom Airline
Dozens of other airlines
There are so many options in the picklist the formula hits the compile size limit But since the friendly name for the link is the same as
the picklist text moving HYPERLINK outside the CASE statement allows it to be referenced one time
HYPERLINK
CASE Airlinec
Airline httpairlinecom
Airline httpairlinecom
Dozens of other airlines
CASE generates the URL for the hyperlink
TEXTAirlinec TEXT generates the friendly name
The revised formula adds only characters to the compile size for each picklist option instead of
Rethink Your Picklist
In the airline and agent commission examples CASE is used to check each value in a picklist to set a formula field value If youve
more than picklist options and you plan to check each possible condition in a CASE statement to determine another value
consider a lookup instead of a picklist
When choosing between picklist and lookup think about
How many objects your Salesforce edition supports
Whether you have dependencies on other field values
The experience for the end user
How frequently you change the picklist values
How close youre to hitting the crossobject reference limit
If you cant reduce the compile size of this type of formula using other methods Consider 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 must set in the formula
In the commission example we looked at earlier if the Agent field is a lookup to an Agent object with a numeric field called
Commission Multiplier the formula becomes a simple calculation using a crossobject reference
BaseCommissionc AgentrCommissionMultiplierc
Reducing Your Formulas Compile Size
Bonus when you add new agents you dont have to remember to change the formula It just works
Think About the Problem Another Way
A common formula to format the elapsed time between two DateTime fields to days hours and minutes looks like this where Diffc
is one DateTime subtracted from another to get the difference in days
IF
Diffc
TEXTFLOORDiffc days
TEXTFLOOR Diffc FLOORDiffc hours
TEXTROUND ROUND Diffc FLOORDiffc
FLOORROUND Diffc FLOORDiffc
minutes
This formula checks if the difference is a positive value If it is several operations calculate values for days hours and minutes in Diffc
and return a string such as days hours minutes This formula compiles to characters And it works fine when Diffc
subtracts two simple DateTime fields like this
LastModifiedDate CreatedDate
However if Diffc is a more complex formula the elapsed time formula becomes too large to compile Why Count the number
of times Diffc is referenced Also all those nested FLOOR and ROUND functions add up
Theres a simpler way to think about the problem
Number of days DateTime DateTime
Number of hours Number of days
Number of minutes Number of hours
The MOD function can really help The modulus of the number of hours divided by is the number of hours not accounted for by
days The modulus of number of minutes divided by is the number of minutes not accounted for by hours So we can change the
formula to
IF
Diffc
TEXTFLOORDiffc days
TEXTFLOORMODDiffc hours
TEXTROUNDMODDiffc minutes
The new version of the formula compiles to characters down from Because it only includes Diffc four times it has
room to accommodate more complex formulas in that field
Heres another example This validation rule formula returns true if an opportunitys Close Date isnt in the current month
OR
CloseDate DATE YEARTODAY MONTHTODAY
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY MONTHTODAY
true
Reducing Your Formulas Compile Size
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
true
IF
AND
MONTH TODAY
CloseDate DATE YEARTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
MONTHTODAY
Reducing Your Formulas Compile Size
true false
This complex check determines whether the Close Date is earlier than the first day of the current month or later than the last day of this
month But actually the day doesnt matter at all if the month and year of the Close Date are the same as the month and year of the
current month then its the same month So we can rewrite the formula as
NOT
AND
MONTH CloseDate MONTH TODAY
YEAR CloseDate YEAR TODAY
This new version is much more readable and only compiles to characters compared to more than for the original formula
If All Else Fails Use a Workflow Field Update
Sometimes theres just no way to rework a formula to make it compile small enough For example on the Answers community someone
asked for help with a formula that compiled to more than characters Reducing references to other formula fields and reducing
the size of those referenced formulas only got the size down to about characters
If you have Enterprise Edition Unlimited or Performance Edition you can use a workflow rule with a field update action to set the value
of a regular custom field using a formula When you reference that custom field in a formula only the value is returned not the formula
that created it Heres how to do it
Create a custom field of the type your field update formula returns such as Date or Number Dont add it to any page layouts
Create a workflow rule on the object Set it to execute whenever a record is created or updated
Create a Field Update workflow action Choose the custom field you created in Step as the target and copy part of your large
formula into the formula that sets the value in that field
In your formula field replace the part of the formula you copied to the field update action with a reference to your custom field
Remember the formula that returned the last day of the month for a given date and how when the formula field Datec was
substituted for SomeDatec the formula was over the compile size limit
DATE
YEAR SomeDatec
MONTH SomeDatec
IF
OR
MONTH SomeDatec
MONTH SomeDatec
MONTH SomeDatec
MONTH SomeDatec
IF
MONTH SomeDatec
Reducing Your Formulas Compile Size
We could have solved this issue by making Datec a custom Date field instead of a formula field and creating a workflow rule and
field update to set Datec to the value of the formula it previously contained
DATE YEAR Datec MONTH Datec DAY Datec
Now the size of Datec is the same as Datec because it contains a simple date and the compile size of the
lastdayofthemonth formula is characters instead of over
NEED MORE HELP
We hope these examples help when you hit one of the formula size limits If you get stuck try posting your question to the very smart
people on the Salesforce Answers community the Formulas Validation Rules Discussion on the Salesforce discussion boards or the
Salesforce Stack Exchange Chances are someone else has run into the same thing and often it just takes another pair of eyes looking
at a problem to solve it
