You need to sign in to do that
Don't have an account?
Norman 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 image 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"),
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 image 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"),
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.
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.
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
Did this work for you?
Please update the thread to help other viewers.