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?




6 Answers 6


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.

  • 1
    brilliant -- i should have thought of the FLOOR function to coerce the division into tiers. CASE is much more compact than multiple IFs
    – cropredy
    Commented Nov 25, 2014 at 1:51
  • Can someone please explain the newbie what does this actually mean? Because its quite confusing for me :( Commented Nov 26, 2014 at 9:40
  • It works like this: FLOOR(Formula_SUM_ALL__c / 30) divides your formula field by 30 and then rounds the result down to the nearest integer. This will be 0 if Formula_SUM_ALL__c is less than 30; 1 if it's >= 30 but < 60, and so on. CASE() takes that resulting integer and for 0 returns a blank gif (no image), for 1 returns a single star, and so on. The last row is the image used when the integer is not 0-3. Kudos to Charles for also calling out the importance of alt text on the images. Commented Nov 28, 2014 at 15:53
  • @DarkoTodorovski Please let me know if my edits helped. Commented Dec 1, 2014 at 18:02
  • @CharlesKoppelman thanks buddy your edits explained it way better how the Floor formula works. :) Commented Dec 2, 2014 at 11:45

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


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


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

  • can you please explain it more in details b) because its kinda confusing for me. Thanks Commented Nov 26, 2014 at 12:49

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.

  • Hi @Jenny can you point it out what criteria i need to place in the workflow rule? Thanks Commented Nov 26, 2014 at 12:39
  • @DarkoTodorovski when do you want it to run, when a specific field is changed or everytime the record is edited?
    – Jenny B
    Commented Nov 26, 2014 at 15:18
  • i solved it and post my answer. Thanks for the help. Commented Nov 26, 2014 at 15:48

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.

  • Rather than suggesting that they use a CASE statement, your answer should also provide the formula. As it stands, this is more of a comment, then an answer. Commented Nov 24, 2014 at 17:25
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. Commented Nov 24, 2014 at 17:25
  • 1
    My apologies. Until I have the pre-req rating, I can't comment, but I assumed that the suggestion of using a CASE would be enough. I'll add a formula to the edit. Thanks for the help.
    – HomerJ
    Commented Nov 24, 2014 at 18:07

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), 

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.

  • Darko - this is a good solution as it only references Formula_SUM_ALL__c once and hence fits in the compiled size; the other solutions assumed that Formula_sum__c was a continuous variable with values anywhere from 0 to infinity and hence attempted to provide an intermediate step to 'bucket' the values into three tiers in a way to work around the 5000 character formula size limit
    – cropredy
    Commented Nov 26, 2014 at 17:34
  • @crop1645 thank you i just needed few hours to figure this out the way i wanted. Thanks to all the comments that were given. Commented Nov 27, 2014 at 11:13

You must log in to answer this question.

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