2

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

10

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 Nov 25 '14 at 1:51
  • Can someone please explain the newbie what does this actually mean? Because its quite confusing for me :( – Darko Todorovski Nov 26 '14 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. – shannonsans Nov 28 '14 at 15:53
  • @DarkoTodorovski Please let me know if my edits helped. – Charles Koppelman Dec 1 '14 at 18:02
  • @CharlesKoppelman thanks buddy your edits explained it way better how the Floor formula works. :) – Darko Todorovski Dec 2 '14 at 11:45
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

1

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

  • can you please explain it more in details b) because its kinda confusing for me. Thanks – Darko Todorovski Nov 26 '14 at 12:49
0

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 – Darko Todorovski Nov 26 '14 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 Nov 26 '14 at 15:18
  • i solved it and post my answer. Thanks for the help. – Darko Todorovski Nov 26 '14 at 15:48
0

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. – Daniel Hoechst Nov 24 '14 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. – Daniel Hoechst Nov 24 '14 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 Nov 24 '14 at 18:07
0

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.

  • 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 Nov 26 '14 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. – Darko Todorovski Nov 27 '14 at 11:13

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.