function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Norman GilbertNorman Gilbert 

Need Formula Help for IMAGE & CASE

I have posted this on the Trailhead Forum and received no solutions. I am stuck on the following challenge in the Advanced Formula Section of Trailhead:

Create a formula field that returns an image to indicate data quality.
Sales Managers have asked for an at-a-glance solution to see completeness on leads. Create a helper formula field that looks at 5 key fields on the Lead object and evaluates their completeness, then a second formula field that references the helper formula and returns an image.
The helper formula field should be on the Lead object with a name of 'Lead Quality Helper' and a resulting API name of 'Lead_Quality_Helper__c'.
The helper formula should be of type Number.
The helper formula should evaluate the following 5 fields: Email, Phone, Company, Title, and Industry and return 0 if blank and 1 if not blank. The formula should then add all the values together to return a total value.
The ima
ge formula should be on the Lead object with a name of 'Lead Quality' and a resulting API name of 'Lead_Quality__c'.
The image formula should reference the helper formula, and return an image based on the number returned by the helper formula. The image formula should be of type Text. Note: All of these images are already available in your Developer Edition.
1 = /img/samples/stars_100.gif with alternate text '1 star'
2 = /img/samples/stars_200.gif with alternate text '2 stars'
3 = /img/samples/stars_300.gif with alternate text '3 stars'
4 = /img/samples/stars_400.gif with alternate text '4 stars'
5 = /img/samples/stars_500.gif with alternate text '5 stars'
If none of the fields are filled out, the default should be /img/samples/stars_000.gif with alternate text '0 stars'.
The 'Lead Quality' formula must be added to the Lead Layout page layout.


The helper formula was easy.

I cannot pass syntax with the formula below and tring to edit it doesn't lead anywhere except to more errors.

IMAGE
(CASE(Lead_Quality_Helper__c ,
5, ("/img/samples/stars_500.gif","5 Stars"),
4, ("/img/samples/stars_400.gif","4 Stars"),
3, ("/img/samples/stars_300.gif","3 Stars"),
2, ("/img/samples/stars_200.gif","2 Stars"),
1, ("/img/samples/stars_100.gif","1 Stars"),
0, ("/img/samples/stars_000.gif","0 Stars"),
"/img/samples/stars_000.gif","Unknown")

Can someone with more experience please help me troubleshoot? The error using this formula produces the following error text: Syntax Error. Missing ')' at the comma between the url for the image and the trext string on this line:. 5, ("/img/samples/stars_500.gif","5 Stars"),
Malni Chandrasekaran 2Malni Chandrasekaran 2
Please try,
CASE( Lead_Quality_Helper__c ,
5, IMAGE("/img/samples/stars_500.gif", '5 star'),
4, IMAGE("/img/samples/stars_400.gif", '4 star'),
3, IMAGE("/img/samples/stars_300.gif", '3 star'),
2, IMAGE("/img/samples/stars_200.gif", '2 star'),
1, IMAGE("/img/samples/stars_100.gif", '1 star'),
IMAGE("/img/samples/stars_000.gif",'0 stars')
)


Please mark it as resolved if it helps complete your challenge.
Norman GilbertNorman Gilbert
I receieved a simiar solution on a different web site, using the IMAGE statement inside the CASE statement and repeating it. This despite the fact that Salesforce Trailhead used CASE as an argument for IMAGE in the tutorial. So apparently someone at Salesforce who wrote the training beieves it is possible. You show only single quotes bracketing the text string. Was that intentional? '5 stars' instead of "5 stars"?
Malni Chandrasekaran 2Malni Chandrasekaran 2
Norman,
I generally try multiple possible syntax. This one I pasted from my code, worked fine. Single quotes was intentional :) and it works in formula.

Here is my helper formula -
 if( Email <> Null, 1, 0) +
if( Phone <> Null, 1, 0) + 
If( ISNULL( Company ), 0, 1) +
if( ISPICKVAL( Industry , ""), 0, 1) + 
if(isnull( Title ), 0,1)

is it not working for you? please let me know.

 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Norman,
If you want to pass CASE as an argument, yes you can

please try,
IMAGE
(CASE(Lead_Quality_Helper__c ,
5, "/img/samples/stars_500.gif",
4, "/img/samples/stars_400.gif",
3, "/img/samples/stars_300.gif",
2, "/img/samples/stars_200.gif",
1, "/img/samples/stars_100.gif",
"/img/samples/stars_000.gif"
), TEXT(Lead_Quality_Helper__c) + " Stars"
)

It works too and I have tried it. 

Please mark it solved if it helps to complete your challenge
Malni Chandrasekaran 2Malni Chandrasekaran 2
Norman,
Did this work for you?
Please update the thread to help other viewers.
Jim ThompsonJim Thompson
The only problem I see with your suggested approach @Maini is that it will output "1 stars" and it should be "1 star" (no s at the end). If you do CASE first, you can accomodate that.