5

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.

5
  • Interesting. Does changing your CASE else values to 1 maybe make a difference? Commented Apr 1, 2015 at 23:16
  • Are other formulas on this object? What is their total combined compile size?
    – sfdcfox
    Commented Apr 2, 2015 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. Commented Apr 2, 2015 at 17:05
  • @ThomasTaylor Changing the CASE values doesn't have an affect. Commented Apr 3, 2015 at 11:57
  • @sfdcfox No other formulas are referenced. Commented Apr 3, 2015 at 11:57

1 Answer 1

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.

7
  • 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. Commented Apr 3, 2015 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). Commented Apr 7, 2015 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. Commented Apr 7, 2015 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. Commented Apr 7, 2015 at 12:33
  • Strange that it should be 0.499. I edited my answer and I anxiously await your Best Answer mark :-) Commented Apr 7, 2015 at 13:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .