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

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