2

I've developed a formula that was functional for my Leads. The formula was giving which lead is cold,warm or hot.

Formula below:

CASE( Formula_SUM_ALL__c , 
-30, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48), 
-25, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48), 
-20, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48), 
-15, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
-10, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
-5, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
  0, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
  5, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
 10, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
 15, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
 20, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
 25, IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
 30, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 35, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 40, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 45, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 50, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 55, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 60, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 65, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
 70, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
 75, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
 80, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
 85, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
 90, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
 95, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
100, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48),
null)

Before: enter image description here

After new edits: enter image description here

With Amit suggestion: enter image description here Now what i want is to make it smaller . Can I somehow go with the range of numbers? For example use case as well but not to exceed the compiling ?

Formula_SUM_ALL__c = Formula_Video__c + Formula_Monetization__c + Formula_OVP__c + Formula_Social_Media_Popularity__c + Formula_Status__c

Formula_Video__c = IF( ISPICKVAL( Has_Video_Type__c , "No") , 0, 25)

Formula_Monetization__c = IF (ISBLANK(Video_Monetizing_tool__c), NULL, IF(OR(INCLUDES(Video_Monetizing_tool__c,"No"), INCLUDES(Video_Monetizing_tool__c,"N/A")), 0, 10 ) )

Formula_OVP__c = CASE( OVP_used__c , "Brightcove", 10, "Flow Player",10, "Haivision",10, "JW Player", 10, "Kaltura",10, "Piksel",10, "Qbrick", 10, "StreamUK",10, "thePlatform",10, "Vimeo",10, "Vyoo",10, "Youtube",10, "N/A",0, "None",0, 0)

Formula_Social_Media_Popularity__c = IF( Formula_SUM_Social__c > 0 && Formula_SUM_Social__c < 5000, 0, IF( Formula_SUM_Social__c >= 5000 && Formula_SUM_Social__c < 10000 , 5, IF( Formula_SUM_Social__c >= 10000, 10, null) ) )

Formula_SUM_Social__c = Facebook_Fans__c + Twitter_Fans__c + Linkedin_Followers__c

Formula_Status__c = CASE( Status , "None",0, "Contacted/Campaign in Progress", 10, "Not Responding", -30, "Prospect in communication", 35, "Not Interested", -30, "Close to Converting",45, "May Convert One Day",10 ,null)

  • Its not hitting compile size. its good. Why do you want to change it ? – Amit Bangad Dec 4 '14 at 15:18
  • It's hitting the compiled size because that formula field that i have is made from many small formulas and if i use If statement, for every time i call that field it doubles the values in the compiling. – Darko Todorovski Dec 4 '14 at 15:38
  • 1
    If you show the formula for the Formula_SUM_ALL__c field we may be able to help to reduce the size of that. – BarCotter Dec 4 '14 at 15:50
  • Can you include Formula_SUM_Social__c as well? – BarCotter Dec 4 '14 at 16:48
  • @BarCotter done. – Darko Todorovski Dec 4 '14 at 18:30
4

This might help, Compile size if far less(371):

IF(Formula_SUM_ALL__c < 30,IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "COLD", 16,48),
IF(Formula_SUM_ALL__c < 70,IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,48),
IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3FP", "HOT", 16,48)))
| improve this answer | |
  • This doesn't help because the values go way up... Error: Compiled formula is too big to execute (5,427 characters). Maximum size is 5,000 characters – Darko Todorovski Dec 4 '14 at 15:29
  • How is that formula too big to execute? It's a lot shorter than your original formula. – BarCotter Dec 4 '14 at 15:32
  • Because in this example, Formula_SUM_ALL__c is referenced twice, which means that its compile size is multiplied by 2. See help.salesforce.com/help/pdfs/en/… to understand the difference between compile size and formula length. – shannonsans Dec 9 '14 at 19:39
3

@Amit Bangads solution, which uses IF rather than CASE, would greatly reduce the amount of characters being used and allows you to use number ranges.

If you wanted to use more formula fields you could add a new field for the Image URL

Formula_WeatherImage__c:

IF(Formula_SUM_ALL__c <= 25, "/servlet/servlet.FileDownload?file=01520000002XTrg",
IF(Formula_SUM_ALL__c <= 65,"/servlet/servlet.FileDownload?file=01520000002Y3F0",
IF(Formula_SUM_ALL__c <= 100,"/servlet/servlet.FileDownload?file=01520000002Y3FP",
null)))

And something similar for the text

Formula_WeatherText__c:

IF(Formula_SUM_ALL__c <= 25, "COLD",
IF(Formula_SUM_ALL__c <= 65,"WARM",
IF(Formula_SUM_ALL__c <= 100,"HOT",
null)))

Your formula for the image then becomes:

IMAGE(Formula_WeatherImage__c, Formula_WeatherText__c, 16,48)

BUT bare in mind that SalesForce will compile the formula back into one larger formula. e.g. in the formula above it will substitute the Formula_WeatherImage__c field name for all the formula text from within that field. Which would end up being compiled on the backend to something like:

IMAGE(IF(Formula_SUM_ALL__c <= 25, "/servlet/servlet.FileDownload?file=01520000002XTrg",
IF(Formula_SUM_ALL__c <= 65,"/servlet/servlet.FileDownload?file=01520000002Y3F0",
IF(Formula_SUM_ALL__c <= 100,"/servlet/servlet.FileDownload?file=01520000002Y3FP",
null))), IF(Formula_SUM_ALL__c <= 25, "COLD",
IF(Formula_SUM_ALL__c <= 65,"WARM",
IF(Formula_SUM_ALL__c <= 100,"HOT",
null))), 16,48)
| improve this answer | |
  • its true that Amit formula reduce the characters in that specific case but not on global compile level when I have 10 other small formulas that I call every time I use Formula_SUM_ALL__c. With each calling of the Formula_SUM_ALL__c i get around 2600 characters multiply by 3 its more than 7500 characters specified in that if statement. – Darko Todorovski Dec 4 '14 at 15:44
  • It sounds like you may have to fine tune your other formula fields as well – BarCotter Dec 4 '14 at 15:47
  • I've made edits in the question you can see the problem specified @BarCotter – Darko Todorovski Dec 4 '14 at 15:50
0

Which edition of Salesforce do you have? If you have Enterprise Edition, you can use a workflow field update to set a regular Number field value to be equal to the same formula you are using for Formula_SUM_ALL__c. This would allow you to use Amit's example and not worry about the compile size, because it would use the compile size of the number field (marginal), rather than the compile size of the formula (large).

The steps are (from the Tips for Reducing Formula Size tip sheet):

  1. Create a custom field of the type your field update formula will return, such as Date or Number. Don’t add it to any page layouts.
  2. Create a workflow rule on the object. Set it to execute whenever a record is created or updated.
  3. Create a Field Update workflow action. Choose the custom field you created in Step 1 as the target, and the large formula into the formula that sets the value in that field.
  4. In your formula field, reference your custom field instead of the formula.

All of your smaller formulas look pretty optimized at this point, so I don't think there's a lot you can do to reduce the size of Formula_SUM_ALL__c.

| improve this answer | |

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.