4

I'm getting a compile size error on a formula, yet none of it makes sense. The formula only references non-formula fields so there shouldn't be any issues with it adding formula sizes, which is what I've read in other posts.

Here's the formula without CEILING(). Clicking [Check Syntax] returns "(Compiled size: 1,855 characters)".

Moves_Per_Month__c * 
  ( 
    CASE(Channel__c, 
      "Mortgage", 0.50,
      "Traveling", 0.25,
      "Brokerage", 0.25,
      "Team", 0.10,
      "Title", 0.10,
      "Property Management", 0.35,
      "University", 0.35,
      "Relocation Services", 0.35,
      "Employee Relocation", 0.30,
    0) 
  ) * ( 
    CASE(Data_Capture_Method__c,
      "API", 0.55,
      "Supported Integration Partner", 0.55,
      "Automated Export", 0.40,
      "Manual Data Export", 0.20,
      "Manual Data Retrieval", 0.40,
      "Data Feed", 0.50,
      "Other", 0.35,
      "Manual Data Process", 0.1,
      "", 0.05, 
    0) 
  ) * 
  IF(ISBLANK(TEXT(Estimated_Usable_Range__C)), 0.35, Estimated_Useable_Data__c)

If I wrap the entire formula in CEILING() and click [Check Syntax] the result is "Error: Compiled formula is too big to execute (5,627 characters). Maximum size is 5,000 characters".

Can't figure out why that's happening.

  • Interesting. Does changing your CASE else values to 1 maybe make a difference? – Thomas Taylor Apr 1 '15 at 23:16
  • Are other formulas on this object? What is their total combined compile size? – sfdcfox Apr 2 '15 at 5:23
  • Are there possible picklist values besides the ones in your CASE statements? If your current formula accounts for all values, you could trim the first by dropping Mortgage, Brokerage, Team and Title out and using 0.30 as your "else" value. And similarly with one of the pairs in the second. – Thomas Taylor Apr 2 '15 at 17:05
  • @ThomasTaylor Changing the CASE values doesn't have an affect. – Force2b_Mike Apr 3 '15 at 11:57
  • @sfdcfox No other formulas are referenced. – Force2b_Mike Apr 3 '15 at 11:57
3

Try using ROUND(x+0.499,0) instead of CEILING(x).

For an explanation of the +0.499 part, see the comments below.

Long explanation: yes, CEILING does something funny (well, not so funny actually). I created a very simple NUMBER formula, which only references another NUMBER field. Compiled size: 32 characters.

Then I wrapped it in CEILING. Compiled size: 158 characters.

I created a more complicated formula, not referencing any other formulas. Compiled size: 732 characters. Wrapped in CEILING: 2258 characters.

Apparently, applying CEILING does not simply add a number of characters to the total size, it seems to multiply the original size with some factor.

In fact, the size = 3 * original size + 62!

Would using ROUND(x,0) instead of CEILING(x) solve your problem? ROUND seems to have a better sizing behavior than CEILING and FLOOR.

| improve this answer | |
  • Very interesting! Nice to see that you've replicated the bizarre size issues I observed. Unfortunately, round behaves differently from ceiling. Round does the following: "Returns the nearest number to a number you specify, constraining the new number by a specified number of digits." On the other hand, Ceiling will "Rounds a number up to the nearest integer." It's the "up" part that's important here. Round won't do that. – Force2b_Mike Apr 3 '15 at 11:58
  • I'm not sure about that. I haven't tried it myself. But should it do that, the solution is: ROUND(x+0.5,0). – Sander de Jong Apr 7 '15 at 6:40
  • Great point @Sander. Round does return the nearest 'lower' integer (different from Ceiling), but adding .5 should force Round to return a similar result. I'll run some tests to confirm. – Force2b_Mike Apr 7 '15 at 12:08
  • After testing, I had to add 0.499 to the result so that it did not round results of 0 up to 1 (after adding 0.5). Otherwise it works perfectly. If you edit your answer to include the new details I'll mark it as Best Answer. – Force2b_Mike Apr 7 '15 at 12:33
  • Strange that it should be 0.499. I edited my answer and I anxiously await your Best Answer mark :-) – Sander de Jong Apr 7 '15 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.