# Formula that shows images [stars]

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,
IF(Formula_SUM_ALL__c >=60,
IF(Formula_SUM_ALL__c >=90,
``````

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

• Nov 24 '14 at 20:27

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 `IF`s (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.

• brilliant -- i should have thought of the FLOOR function to coerce the division into tiers. CASE is much more compact than multiple IFs Nov 25 '14 at 1:51
• Can someone please explain the newbie what does this actually mean? Because its quite confusing for me :( 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. Nov 28 '14 at 15:53
• @DarkoTodorovski Please let me know if my edits helped. Dec 1 '14 at 18:02
• @CharlesKoppelman thanks buddy your edits explained it way better how the Floor formula works. :) Dec 2 '14 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

``````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 Nov 26 '14 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 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? Nov 26 '14 at 15:18
• i solved it and post my answer. Thanks for the help. Nov 26 '14 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. 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. Nov 24 '14 at 17:25
• 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. Nov 24 '14 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 ,