Formula that shows images [stars]

Question

I've developed many small formulas and sum their results into number. Now i am trying to use that number for another formula field that will give me lead quality with star images. The formula field that i am playing with is Formula_SUM_ALL__c .

And this is where I am with the code :

IF(Formula_SUM_ALL__c >= 30, 
   IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "",16,16) +
   IF(Formula_SUM_ALL__c >=60, 
       IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "", 16, 16)+ 
       IF(Formula_SUM_ALL__c >=90, 
          IMAGE("/servlet/servlet.FileDownload?file=01520000002XTrg", "", 16, 16) ,"") ,"") ,"")

Note:It points out that i am way over the character limit. Is it possible to shrink this code into smaller, or what is the solution for this type of case?

Thanks,

Darko

Answer 1

If you're not near capacity on static resources, I'd suggest the following:

1. Create three Static Resources with 1, 2, and 3 stars. These can be gif's, jpg's, png's or any other image file format. Give them names that describe what they mean, not what they look like.
2. Use IMAGE(URLFOR($Resource.Ok), 'one star') to access these image files.
3. Use CASE to consolidate your IFs (read the documentation on Visualforce functions for more information on CASE).
4. Since CASE statements don't allow ranges, you need to figure out a way to deal with that.
   1. Formula_SUM_All__c / 30 divides Formula_SUM_All__c by 30. It will give you a number between 0 and (presumably 4) based on the value of Formula_SUM_All__c. For any number between 0-29 it will give you a number between 0-1. For 30-59 it will give a number between 1-2. For 60-89, it will give you 2-3. For 90-119, it will give you 3-4. For anything greater, it will give you a number greater than 4. (e.g., if Formula_SUM_All__c is 53, Formula_SUM_All__c / 30 yields 53 / 30 which is 1.76667)
   2. To get the integer values, we can use FLOOR to round down (see the documentation for help on Visualforce functions).
   3. So FLOOR(Formula_SUM_All__c / 30) will give you:
      • 0 for values 0-29
      • 1 for values 30-59
      • 2 for values 60-89
      • 3 for values 90-119
      • 4 or greater for anything higher.
5. So now we can use those 0-4+ values in our CASE statement.

So together the final result would be something like:

CASE(FLOOR(Formula_SUM_ALL__c / 30), 
     0, IMAGE('/s.gif', 'no stars'),
     1, IMAGE(URLFOR($Resource.Ok), 'one star'),
     2, IMAGE(URLFOR($Resource.Fair), 'two stars'),
     3, IMAGE(URLFOR($Resource.Good), 'three stars'),
        IMAGE(URLFOR($Resource.Excellent), 'over three stars'))

The advantages with this are that you can set whatever "kerning" between the stars you want (and not rely on Salesforce to do it for you), that your alt text actually helps hard-of-seeing users, and that you can easily change your star system to a traffic light system (or anything else). You also get to use some of that Static Resource space instead of using Documents.

If your formula is still too long, you can probably save some space by changing URLFOR($Resource.Excellent) to the actual url, but the method above is clearer from a maintenance perspective.

Answer 2

If you use other formulas inside formulas, the character limits add up. You will need to optimize all the nesting formulas together to stay within the limits.

You can find some best practices here: https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

Answer 3

Most likely, Formula_SUM_ALL__c is the cause of your issues - because you reference it three times, the number of compiled characters in Formula_SUM_ALL__c is multiplied by three (3) when calculating the size of the image generation formula field. And of course, the size of Formula_Sum_All__c depends on any formula fields it references! This leaves you with two options:

a) Optimize Formula_SUM_ALL__c so when its size * 3 is added to the rest of the IF statements and IMAGE functions, it does not exceed the 5000 limit or

b) Create a parallel field called Formula_Sum_All_Tier__c with possible values = '<30', '30-59', '60-89', and '90+'. Compute this via a workflow field update as Jenny B suggested. Then the image formula references the Formula_Sum_All_Tier__c using a CASE statement

CASE(Formula_Sum_All_Tier__c,
          "30-59",IMAGE(...),
          "60-89",IMAGE(...),
          "90+",IMAGE(...),
          NULL)

Note that the workflow should be setup to fire on 'every time it is edited'.

The CASE option will not directly work with inequalities, only IF statements do

Answer 4

One way to work around this is to set your Formula_SUM_ALL__c as a number field and use a workflow rule to calculate the sum and update the value. This way you are referencing a number in your calculation vs compounding the characters from using another formula field.

Answer 5

EDIT** You may be able to shorten the formula by using a CASE statement instead of nested IF statements.**

Correction, since you cannot use a CASE to evaluate logical statements (equals-comparisons only) then the option would be to use a secondary field updated with formula or workflow to a value that can be evaluated by the CASE.

Additionally, if you were to upload additional IMAGE files (of 1-, 2-, and 3-stars) rather than concatenate the 1-star image multiple times, you'll save some characters as well in the IF statement.

Answer 6

Working solution without the need to adapt the code.

First thing I did is made the values to be like the following: 0, 5, 10, 15, 20, 25, 30.... 100. Second thing I did was make a formula with the following cases:

CASE( Formula_SUM_ALL__c , 
5, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y2UT", "COLD", 16,16), 
10, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y2UT", "COLD", 16,16), 
15, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y2UT", "COLD", 16,16), 
20, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y2UT", "COLD", 16,16), 
25, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y2UT", "COLD", 16,16), 
30, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
35, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
40, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
45, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
50, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
55, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
60, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
65, IMAGE("/servlet/servlet.FileDownload?file=01520000002Y3F0", "WARM", 16,32), 
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)

This way I am calling the Formula_SUM_ALL__c only once and it gives what I want to see perfectly and rounds the value as it should.