Compiled formula too big error (Was: Need help with a Formula) [ Edited ]

[AMartin]

Hi,

I've created the following formula that references a Grand Total field and returns this error: Compiled formula is too big to execute (23,322 characters). Maximum size is 4,000 characters.

IF(AND ({!Grand_Total__c}  >= 0, {!Grand_Total__c}  <= 33),"Small",
IF(AND ({!Grand_Total__c}  >= 34, {!Grand_Total__c}  <=66 ),"Medium",
IF(AND ({!Grand_Total__c}  >= 67, {!Grand_Total__c}  <= 100),"Large","")))

The Grand Total field is the sum of 4 other formulas and has the compiled size of 3857.  I'm pretty sure I'm getting the error because my new formula references the Grand Total field 6 times.  Can anyone suggest a better formula that will return the "Small", "Medium" or "Large" depending on the value of Grand Total.

Thanks in advance.

Aiden

Message Edited by Kingsley on 06-08-2006 10:35 AM

[daroz]

I didn't syntax check this, but off the cuff...


If( GT > 0,
  if ( GT <= 33, "Small",
    if( GT <= 66, "Medium",
      if( GT <= 100, "Large", ">0 and Larger then Large")
    )
  )
, "< 0")


[AidenM]

Hi Daroz,

Thanks but referencing Grand Total more than once in the formula pushes the compiled size over the 4000 character limit.  Your formula is15,608 characters.  While smaller than mine, it's still too large.  I've been trying to use the CASE function but so far I'm SOL.

Aiden

[daroz]

Sorry I missed the part where the Grand Total formula was almost 4k by itself.

I don't see a way to do what you want in this forumla. I suggest you try to dig into your Grand Total formula and see if you can cut it back instead.

[EricB]

You may be out of luck, since your Grand Total field is so close to the 4k limit, but here is a more efficient formula:

CASE (TRUE,
{!Grand_Total__c}  <= 33, "Small",
{!Grand_Total__c}  <=66,  "Medium",
{!Grand_Total__c}  <=100, "Large",
"")

Good luck,
Eric

Eric Bezar
AppExchange Platform Product Management

[dcouch]

I've been trying to use a formula similar to this, but it appears that you cannot use "<=" in the arguments of the case function... is that correct?

[EricB]

My apologies.  My formula was completely wrong.  (Note to self: always test these things before posting)
Here is a formula that does work.  Not sure if it will save that much space, though...

CASE (1,
IF( {!Amount} <= 33, 1, 0), "Small",
IF( {!Amount} <= 66, 1, 0), "Medium",
IF( {!Amount} <=100, 1, 0), "Large",
"Huge")

Cheers,
Eric

[Peter Yao]

Has anyone else hit this 4k cumulative limit? With a couple hyperlink formulas, I'm easily over.

I just talked to a support rep on the phone and he said that it's not on the roadmap - maybe I'm using these formulas completely wrong?

Does anyone have any ideas on reducing the compiled size of formulas? I've already shortened some field names which seems to work. I'm also using "contains" instead of "equals" for text comparison. Case is nice to read, but does take up a little more room.

Thanks,
Peter

[Erite11]

Since the ealier formulas are so close to the cap you may want to post them for help shrinking them a bit.  I have hit that limit myself on two occasions.  On one occasion, the field wasnt truly needed so I just used a report when someone wanted the number.

On the other occasion, I found that hardcoding any fields that did not change was helpful.  Wouldnt know if this applies to you in any way.

[sfdcfox]

For static values (values that do not change between edits), you might consider a workflow rule with a field update. This allows the formula to evaluate once during a save, and then your other formulas only have to reference a normal integer or text field instead of another formula. This can have drastic space savings for formulas, while only slightly increasing the save times (usually on the magnitude of less than one second).