+ Start a Discussion
Philip Essien 8Philip Essien 8 

Using Text Formulas


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 helper 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.Check Challenge


ANS:
Lead Quality (Text) =
IF(Lead_Quality_Helper__c=1, 
IMAGE("/img/samples/stars_100.gif","1 star"), IF(Lead_Quality_Helper__c=2, IMAGE("/img/samples/stars_200.gif","2 stars"), IF(Lead_Quality_Helper__c=3, 
IMAGE("/img/samples/stars_300.gif","3 stars"), 
IF(Lead_Quality_Helper__c=4, 
IMAGE("/img/samples/stars_400.gif","4 stars"), 
IF(Lead_Quality_Helper__c=5, 
IMAGE("/img/samples/stars_500.gif","5 stars"), 
IMAGE("/img/samples/stars_000.gif","0 stars"))))))


Lead Quality Helper (Number) =
IF(ISBLANK(Email),0,1)+ 
IF(ISBLANK(Phone),0,1)+ 
IF(ISBLANK(Company),0,1)+ 
IF(ISBLANK(Title),0,1)+ 
IF(ISBLANK(TEXT(Industry)),0,1)

I can't see what I am doing wrong. Everything works just fine but I keep getting this error. 
Challenge Not yet complete... here's what's wrong: 
The Lead object does not display the formula fields correctly. Tip: check the requirements again and make sure you have the correctly forumlas and their values.


Any help will be appreciated. Thanks.
pconpcon
I would first recommend that you change your Lead Quality formula to use a CASE [1] instead of a bunch of IF statements.
 
CASE(Lead_Quality_Helper__c,
1, IMAGE("/img/samples/stars_100.gif", "1 star"),
2, IMAGE("/img/samples/stars_200.gif", "2 stars"),
3, IMAGE("/img/samples/stars_300.gif", "3 stars"),
4, IMAGE("/img/samples/stars_400.gif", "4 stars"),
5, IMAGE("/img/samples/stars_500.gif", "5 stars"),
IMAGE("/img/samples/stars_000.gif", "0 stars"))

Try that and see if it passes

NOTE: When adding code please use the "Add a code sample" button (icon <>) to increase readability and make it easier to reference.
Pierre RognionPierre Rognion
Hi, 

I have the same issue with the following aswers: 

Lead Quality Helper :

IF(ISBLANK(Email),0,1)+ 
IF(ISBLANK(Phone),0,1)+ 
IF(ISBLANK(Company),0,1)+ 
IF(ISBLANK(Title),0,1)+ 
IF(ISBLANK(TEXT(Industry)),0,1)

Lead Quality : 

IMAGE( 
CASE(Lead_Quality_Helper__c, 
1, "/img/samples/stars_100.gif", 
2, "/img/samples/stars_200.gif", 
3, "/img/samples/stars_300.gif", 
4, "/img/samples/stars_400.gif",
5, "/img/samples/stars_500.gif", 
"/img/samples/stars_000.gif"), 
"Unknown")
pconpcon
Your lead quality you need to set the alt text correctly.

Lead Quality Helper
IF(ISBLANK(Email), 0, 1) + 
IF(ISBLANK(Phone), 0, 1) + 
IF(ISBLANK(Company), 0, 1) + 
IF(ISBLANK(Title), 0, 1) + 
IF(ISPICKVAL(Industry, ""), 0, 1)

Lead Quality
IMAGE( 
    CASE( 
        Lead_Quality_Helper__c, 
        1, "/img/samples/stars_100.gif", 
        2, "/img/samples/stars_200.gif", 
        3, "/img/samples/stars_300.gif", 
        4, "/img/samples/stars_400.gif", 
        5, "/img/samples/stars_500.gif", 
        "/img/samples/stars_000.gif" 
    ), 
    CASE( 
        Lead_Quality_Helper__c, 
        1, "1 star", 
        2, "2 stars", 
        3, "3 stars", 
        4, "4 stars", 
        5, "5 stars", 
        "0 stars"
    ) 
)
pconpcon
@Philip

What does your lead layout look like after adding the fields to your object and to the page layout?  What I have is as follows:

User-added image
Philip Essien 8Philip Essien 8
I have the the same output as well. But the challenge won't still go through. User-added image
pconpcon
Just to verify, can you save your Lead Quality formula elsewhere and then copy the one I have above using the CASE statement and see if that works?
Philip Essien 8Philip Essien 8
I'm already using that and it returns the desired output. But the challenge still returns the same error. I don't have any validation rule active on my lead object as well as any trigger.
Akbar HussainAkbar Hussain
Hi All,

Please try with below ones.
Lead_Quality_Helper:

IF( AND( ISBLANK( Email ), ISBLANK( Phone ), ISBLANK( Company ), ISBLANK( Title ), ISPICKVAL(Industry, "") ) , 0, IF(ISBLANK(Email),0,1)+ 
IF(ISBLANK(Phone),0,1)+ 
IF(ISBLANK(Company),0,1)+ 
IF(ISBLANK(Title),0,1)+ 
IF(ISBLANK(TEXT(Industry)),0,1))

Lead Quality:

IF(Lead_Quality_Helper__c=1, 
IMAGE("/img/samples/stars_100.gif","1 star"), IF(Lead_Quality_Helper__c=2, IMAGE("/img/samples/stars_200.gif","2 stars"), IF(Lead_Quality_Helper__c=3, 
IMAGE("/img/samples/stars_300.gif","3 stars"), 
IF(Lead_Quality_Helper__c=4, 
IMAGE("/img/samples/stars_400.gif","4 stars"), 
IF(Lead_Quality_Helper__c=5, 
IMAGE("/img/samples/stars_500.gif","5 stars"), 
IMAGE("/img/samples/stars_000.gif","0 stars"))))))
 
Suzi Simmons 30Suzi Simmons 30
I did get a response from Salesforce, and was informed that the problem is the custom domain we have to use with this module that affects the challenger checker.
So, there appears to be no way around it, and prior modules require the custom domain, otherwise you can't do lightning stuff.
Has this been resolved yet?  It's keeping me from my challenge points!!
Thank you
pconpcon
There are some modules that conflict with others including custom domains and namespacing.  You can however use multiple developer orgs to do you work and still have all of the badges associated with a single account.  If you follow the instructions here [1] and watch this video [2] you can see how to login with your primary account (the one with the badges) and then login to a secondary developer account that is a new account to do and check your work.

[1] https://developer.salesforce.com/forums/?id=906F00000005JciIAE
[2] https://www.youtube.com/watch?v=1rKrBR5qbTg
Andrew Hart (SFDC)Andrew Hart (SFDC)
I just got stuck on here too... it didn't like my CASE statement in the image formula field, it had to be repeated IFs. Not ideal, and certainly not as easy to read, but even with a custom domain it was fine.
Peter Nielsen 8Peter Nielsen 8
You might not be able to complete the challenge for Using Text Formulas if you made your helper formula a hidden field when you first created it even though the formula works well as a hidden field, it is just that Trailhead cannot check it if it is hidden
Peter Timmer 8Peter Timmer 8
Help needed

When I use the formula from above the next error pops up
The object
Name is Lead Quality Helper
Type is Number
API Name is Lead_Quality_Helper__c

I am now trying to create the Lead Quality Object
Name: Lead Quality
API Name: Lead_Quality__c
Type: Text
Lenght 10
Formula:
Lead Quality
01IMAGE(
02    CASE(
03        Lead_Quality_Helper__c,
04        1, "/img/samples/stars_100.gif",
05        2, "/img/samples/stars_200.gif",
06        3, "/img/samples/stars_300.gif",
07        4, "/img/samples/stars_400.gif",
08        5, "/img/samples/stars_500.gif",
09        "/img/samples/stars_000.gif"
10    ),
11    CASE(
12        Lead_Quality_Helper__c,
13        1, "1 star",
14        2, "2 stars",
15        3, "3 stars",
16        4, "4 stars",
17        5, "5 stars",
18        "0 stars"
19    )
20)
Thanks PCON
But still
What am I overlooking?

Error: Field Lead_Quality_Helper__c may not be used in this type of formula
Frank MoscatelloFrank Moscatello
Hey Peter,

Lead Quality Helper is asking you to chek to see if values are Blank/NULL then return 0, else return 1 and then add regardless of the number to the next IF Statment. So it should look something like this...
Lead Quality Helper:
IF(ISBLANK(Company), 0, 1)+ 
IF(ISBLANK(Title), 0, 1)+ 
IF(ISBLANK(Email), 0, 1)+ 
IF(ISBLANK(Phone), 0, 1)+ 
IF(ISBLANK(TEXT(Industry)), 0, 1)
For Industry you dont need to check the picklist, you only need to check if a option is selected.

For Lead Quality a Case is best so you good there and all you need to do for this one is simply create a Case which references Lead_Quality_Helper and then continues to check the "Number of Stars".  At the end of the Case you have to create something called a default case which if all of the Cases fail it goes to the last one no matter what. So it should look something like this...
Lead Helper
CASE( Lead_Quality_Helper__c, 
1, "/img/samples/stars_100.gif", 
2, "/img/samples/stars_200.gif", 
3, "/img/samples/stars_300.gif", 
4, "/img/samples/stars_400.gif", 
5, "/img/samples/stars_500.gif", 
"/img/samples/stars_000.gif")

Hopefully this helps you out!
Good luck!

Best regards,
Frank
Lucy MonkLucy Monk
Thanks Pcon, I was struggling how to include the alternate text but your snippet variations really helped, also turned out I'd quoted a space in the industry picklist instead of a blank quote.
Suraj Tripathi 47Suraj Tripathi 47
Hi Philip,
Greetings!

Please use CASE inside the formula field.
Because if you want to implement the IF-ELSE ladder then CASE is the best option provided by Salesforce.

If you find your Solution then mark this as the best answer. 

Thank you!

Regards,
Suraj Tripathi