in implementation guides ~ read.
Salesforce Formula Size Tipsheet

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.

Download

Tips for Reducing Formula Size
Salesforce Spring

salesforcedocs
Last updated February

Copyright salesforcecom inc All rights reserved Salesforce is a registered trademark of salesforcecom 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 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 new 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

Replacing SomeDatec in the above 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

Reducing Your Formulas Compile Size

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 a lot of cases with the same value use it as the default

to reduce the number of checks Lets take another 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 to 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 very 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

Not only is this easier to read the formula compiles to only characters for Datec and characters for Datec
and it now 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 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 try this with our business days formula
SomeDatec

CASE MOD SomeDatec DATE

Sunday
Thursday
Friday
Saturday
Default MonTuesWed

We have 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 are not compiled equally Daterelated functions in particularlike DATE and MONTHgenerate large

queries The fewer times you need to 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 just once

CASE Airlinec

Airline httpairlinecom
Airline httpairlinecom
Dozens of other airlines

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 above CASE is used to check each value in a picklist to set a formula field value If

you have 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 you are 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 need to 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

Added 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 and 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 big 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 and 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 is not 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 returnednot the formula
that created it Heres how to do it
Create a custom field of the type your field update formula will return 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

Another way we could have solved this is 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 Developers 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

***