formulaEver created a formula you were happy with, only to have it run up against the formula size limits? Did it have too many characters, or was its compile size too big?

If you need help streamlining your formulas, we’re pleased to announce our newest tip sheet for Summer ’13:

Tips for Reducing Formula Size

Inside, you’ll find tips and tricks for slimming down your formulas, both in number of characters and compile size, as well as example code, including:

  • how to minimize the number of references to other formula fields
  • how to minimize the number of times formula functions are called
  • suggestions for rearranging your formula logic….and more!

We’ve got more formulas-related doc enhancements planned for the future, so stay tuned!

Here’s an excerpt from the tip sheet:

Suppose you have a Membership Start Date field, and you’re using that date to calculate a Membership End Date (in the interest of space, we’ll keep it short, but pretend it exceeds 3,900 characters):

IF(
 MONTH( Membership_Start_Date__c ) < 10,
 DATE( YEAR( Membership_Start_Date__c ), 12, 31),
 DATE( YEAR( Membership_Start_Date__c ) + 1, 12, 31)
)

Because you repeatedly reference YEAR(Membership_Start_Date__c), you can move that into a formula field called Start Year. You can also move MONTH(Membership_Start_Date__c) into its own formula field.

Then the main formula becomes:

IF(
 Start_Month__c < 10,
 DATE( Start_Year__c, 12, 31),
 DATE( Start_Year__c + 1, 12, 31)
)

You’re now down to 89 characters from 146. Since there are edition-based 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 Start_Year__c in the example above.

Related Resources

For more formula-related documentation, visit the Formulas page on DeveloperForce.

tagged , , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • http://www.tgerm.com/ Abhinav Gupta

    Nice tip @Michelle, but one needs to be careful when clipping parts of formula here. Creating such new clipped/sub formula fields is more fruitful if they could be referenced across more than one formulas. This is because its easy to enhance and fix a complex formula if things are usually at one place.

    • Shannon Hale

      Good point, thanks! We’ll pull that into the section on reducing formula length.

      • http://www.tgerm.com/ Abhinav Gupta

        Thanks !

      • http://www.tgerm.com/ Abhinav Gupta

        Thanks !

  • http://www.appirio.com/company/leadership.php#glenn Glenn Weinstein

    Ideally, the formula compiler would do all of this hard work, and let us humans write more readable, easily-understood formulas.

    • Shannon Hale

      The formulas team has made a number of improvements to the compiler in the last year, and reduced the compile size of a number of formula functions. We plan to continue this over the coming releases.

      Most of the suggestions in this tip sheet will improve a formula’s readability in addition to making it more efficient.

  • Johan

    Michelle, do you mean in Summer ’13 release, if we use formula field within formula field, compiled size will be smaller? If not, what is the difference with earlier release?

    • Michelle CT

      The suggestions in the new tip sheet aren’t specific to the Summer ’13 release.