+ Start a Discussion
abccabcc 

case expansion in formula

why does the following compile to 14K:

 

CASE(FLOOR(Case_Age_by_Business_Hours__c),
 0,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 1,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 2,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010v6", "Green Flag"),
 3,
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010vB", "Yellow Flag"),
 /* 4 hours and above */
  IMAGE("/servlet/servlet.FileDownload?file=015P000000010vG", "Red Flag")
)

 

Case_Age_by_Business_Hours__c is s formula that compiles to 4K.

Shannon HaleShannon Hale

What's the compile size of FLOOR(Case_Age_By_Business_Hours__c)? The FLOOR() function can be computationally expensive.

abccabcc

TEXT(FLOOR(Case_Age_by_Business_Hours__c))

results in 13,999.

I tried to use CASE to avoid the IF statement and referencing the Case_Age_by_Business_Hours__c multiple times.

What options do I have besided creating another field that calculates the floor of Case_Age_by_Business_Hours__c?

Shannon HaleShannon Hale

You were right to use CASE() instead of IF() -- referencing Case_Age_by_Business_Hours__c multiple times is just going to pull your 4K formula in that many times, so as soon as you reference it twice you're still going to be over. And creating another formula containing FLOOR(Case_Age_by_Business_Hours__c) is going to run into the same issue - that formula field is going to be 14K.

 

I don't know which formula you are using to calculate business hours, but someone on Stack Exchange came up with one that appears to be pretty efficient -- if you're not using that one for Case_Age_by_Business_Hours__c, try it and see if it reduces your compile size sufficiently. Here's his post: http://stackoverflow.com/questions/11126378/salesforce-formula-calculate-business-hours-between-two-dates

 

Otherwise you can use a workflow field update action or trigger to copy the value of the formula you are using to generate Case_Age_by_Business_Hours__c to another field, rather than using a formula field. Then you can use that field in this formula and it will probably reduce the compile size to under 5K, because you'll just be running FLOOR() on a simple number and the CASE() and IMAGE() functions shouldn't add that much. The instructions for field updates are in this tip sheet: Tips for Reducing Formula Size.