function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nicholas Hale 5Nicholas Hale 5 

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.

ok so im having trouble on this challenege on trailhead. im confused what it means by helper formula, like how would we create a helper formular? also how would we create a image formula?

thanks if anyone answers 
Best Answer chosen by Nicholas Hale 5
James LoghryJames Loghry
A helper formula is just a formula field that is referenced by another formula field.  For instance I create a Formula_A__c field that references Formula_B__c.

For an image formula, you'll create formula field that's actually a Text type, and then in the formula editor you'll use the IMAGE() formula to produce an image.  The image formula is actually discussed in the description or scenario of the same Trailhead challenge you're working on. 

All Answers

James LoghryJames Loghry
A helper formula is just a formula field that is referenced by another formula field.  For instance I create a Formula_A__c field that references Formula_B__c.

For an image formula, you'll create formula field that's actually a Text type, and then in the formula editor you'll use the IMAGE() formula to produce an image.  The image formula is actually discussed in the description or scenario of the same Trailhead challenge you're working on. 
This was selected as the best answer
Nicholas Hale 5Nicholas Hale 5
thanks alot, really helped.
Nida Khan 5Nida Khan 5

Hello All,
I completed this challenge:

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"), 
"0 stars")

Thanks,
Nida
Kate HulsKate Huls
This does indeed work to clear the challenge, but I'm still confused as to why, when it doesn't have any of the alternate text that was requested - "1 star," "2 stars," etc. I know I'm missing something and I'm gonna feel so dumb, but why did this work?
Robin LindemannRobin Lindemann
I was also frustrated by this challenge.  Please review the problem and note that it is asking for things that are not actually part of the solution (and there are typos in the error messages, as well).  I did EXACTLY as the challenge requested.  

The problem states "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".  Doesn't this mean that you should convert the values of the Lead Quality Helper to TEXT???  That is what I did, and the formula worked and produced the desired result  BUT it did not pass the challenge, until I coded it as shown above WITHOUT the TEXT conversion.  

Alternate text is also provided for each image option, leading one to believe that this is also a requirement.  Yet, the challenge is passed, without including these values.  

I spent an extra half hour trying to make this challenge work, until I found this solution and the other comments.  

FYI.
Mayank RajMayank Raj
@ Beppy Huls
You could try this for the precise solution asked....

Lead Quality---

CASE( Lead_Quality_Helper__c ,
1, IMAGE("/img/samples/stars_100.gif","1 star"),
2, IMAGE("/img/samples/stars_200.gif","2 star"),
3, IMAGE("/img/samples/stars_300.gif","3 star"),
4, IMAGE("/img/samples/stars_400.gif","4 star"),
5, IMAGE("/img/samples/stars_500.gif","5 star"),
IMAGE("/img/samples/stars_000.gif", "0 stars"))
Mary Preethi JosephMary Preethi Joseph
Hi,
my formula works absolutely fine. but I coudn't pass my challenge.
It says-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. these are my formulas
On  Lead_Quality_Helper formula field
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)

On Lead Quality formula feild:
IMAGE(CASE( TEXT( 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"),"0 Stars")

Pls help.
Angel OrtegaAngel Ortega
Hi:
My Answer worked and passed the goal. Be carefule evaluating Industry field.
On  Lead_Quality_Helper formula field
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)

On Lead Quality formula feild:
IMAGE(CASE( TEXT( 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"),"0 Stars")
Mary Preethi JosephMary Preethi Joseph
Thank you Angel. 
Kelly BuseKelly Buse
Hi All, 

I have inputted the following into the Lead_Quality_Helper formula field, but it has returned with 'Error: Field Email may not be used in this type of formula'. Would anyone be able to tell me why? Or what I can do about this? 

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)

Thanks!
Matt EckMatt Eck
I am having the same problem as Kelly is above. I keep getting "Error: Field Email may not be used in this type of formula". Does anyone have a solution to this problem?
Bhushan Athale 7Bhushan Athale 7

For Lead_Quality_Helper (Field) following code.

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)

For Lead_Quality_Helper (Lead Quality) following code.

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

Added it on page Layout & my challenge was completed.

Thanks
Peter Nielsen 8Peter Nielsen 8
You might not be able to complete the challenge 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
Barry BaltusBarry Baltus
For me the problem was the Industry. Like I saw other people here doing, there was a space between the brackets --> (Industry," ") 
Removing this space seemed the solution for me.
Paco AlijartePaco Alijarte
Hi All,

For me, it worked using these formulas below

For Lead_Quality_Helper (Field) following code.

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)

For Lead_Quality_Helper (Lead Quality) following code.

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

But is important that you create those fields as formula fields not as a normal field and include the formula.

I hope it helps.

Paco.
Adri SzopianAdri Szopian
In my case it worked only when I have modified the Industry line:
 
IF(ISPICKVAL( Industry,''), 0, 1)

- single quotes and no space.
Jérôme PijpersJérôme Pijpers

I used for the Lead Quality Helper:

IF(ISBLANK(TEXT(Industry)),0,1)

instead of:

IF(ISPICKVAL(Industry , ""), 0, 1)

Both works and pass the challenge , but i was wondering which of the 2 is the better choice and why ?

Im new to platform and really like to learn to create formula's, but also want to learn from start best practises.

Thanks in advance,

eiyu
Sachin Sharma 3937Sachin Sharma 3937
Well... sorry to say every one is wrong here.

I used different approach for

# Lead Quality Helper

If(len(Email)>0,1,0) +
if(len(Phone)>0,1,0)+
if(len(Company )>0,1,0)+
if(len(Title)>0,1,0)+
if(len(text(Industry))>0,1,0)

#  Lead Quality   ( In this use case was to display the total number of stars as a alternative text, if the image is not displayed)

you can check by making any of the image path incorrect, the number of stars will show

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.gifd"
,5,"/img/samples/stars_500.gif"
,"/img/samples/stars_000.gif"),text(Lead_Quality_Helper__c)&" Stars")
 
Benjamin BratcherBenjamin Bratcher
Well, this was frustrating. Barry's suggestion to remove the space between the quotation marks after Industry seemed to do the trick (i.e. Industry, "").Very annoying. Here is what I had for anyone having trouble with it too.

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:
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") 
)
Alexis MASSON 6Alexis MASSON 6
This code works ...
But is there a better way to convert types, to compare numbers ?
It seems that it doesn't work if I let Lead_Quality_Helper__c be a number, and compare it to .. numbers (0, 1,  .., 5) ?
  
IMAGE(CASE(TEXT(Lead_Quality_Helper__c),
           TEXT(1), "/img/samples/stars_100.gif",
           TEXT(2), "/img/samples/stars_200.gif",
           TEXT(3), "/img/samples/stars_300.gif",
           TEXT(4), "/img/samples/stars_400.gif",
           TEXT(5), "/img/samples/stars_500.gif", "/img/samples/stars_" + TEXT(Lead_Quality_Helper__c) + "00.gif" ),
"0 stars")

 
Alexis MASSON 6Alexis MASSON 6

Hummm... It seems that without all the TEXT()s, it works ?

I thought I tried it.

Problem solved ? :)
devashree joshidevashree joshi
I used below formula, and it worked

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 star"), 
IF( Lead_Quality_Helper__c == 3,IMAGE("/img/samples/stars_300.gif", "3 star"), 
IF( Lead_Quality_Helper__c == 4,IMAGE("/img/samples/stars_400.gif", "4 star"), 
IF( Lead_Quality_Helper__c == 5,IMAGE("/img/samples/stars_500.gif", "5 star"), 
IMAGE("/img/samples/stars_000.gif","0 star"))))))
Gopesh VardhanGopesh Vardhan
Hello All,

I am getting error in saving "Lead Quality Helper" field,  

"Field Email may not be used in this type of formula"

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)

It's showing error as 
User-added image

Please any one suggest some solution.
 
Justin StephensJustin Stephens
Hi Gopesh,

Your Email field may be referenced from another module's work that is causing the error, can you try to use a fresh developer org and try the formula again? 
Dana VanBrimmer 6Dana VanBrimmer 6
For the Helper I am getting the Error: Formula result is data type (Number), incompatible with expected data type (true or false).

The Challenge instructions say to set it as a number. What should it be?
Pedro RodriguesPedro Rodrigues
 
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)


Lead Quality (Text) = 
IF( Lead_Quality_Helper__c == 1,IMAGE("/img/samples/stars_100.gif", "1 star"),
IF( Lead_Quality_Helper__c == 1,IMAGE("/img/samples/stars_100.gif", "2 stars"),
IF( Lead_Quality_Helper__c == 1,IMAGE("/img/samples/stars_100.gif", "3 stars"),
IF( Lead_Quality_Helper__c == 1,IMAGE("/img/samples/stars_100.gif", "4 stars"),
IF( Lead_Quality_Helper__c == 1,IMAGE("/img/samples/stars_100.gif", "5 stars"),
IMAGE("/img/samples/stars_000.gif", "0 starts")
)))))
Shivam KanjiyaShivam Kanjiya
For me this worked:

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)        <--- No space between ""
Selma Delalic 10Selma Delalic 10
Hi all,
This challenge is also passed using following formula for Lead Quality (Text), but when I load the Lead Report, column Lead Quality Helper is properly scored, while column Lead Quality(Text) contains only 1 star for all records. Any help highly appreciated! Thanks! 

Lead Quality(Text)=
CASE( Lead_Quality_Helper__c ,
       1,  IMAGE("/img/samples/stars_100.gif", "1 star"), 
       2,  IMAGE("/img/samples/stars_100.gif", "2 stars"),
       3,  IMAGE("/img/samples/stars_100.gif", "3 stars"),
       4,  IMAGE("/img/samples/stars_100.gif", "4 stars"),
       5,  IMAGE("/img/samples/stars_100.gif", "5 stars"),
IMAGE("/img/samples/stars_000.gif", "0 starts"))

Screenshot from the Lead Report
SABRINA HenrySABRINA Henry
Hi Nida

I accidentally tapped on the dislike icon on your answer when I was trying to click on like.  I am unable to undo this action.  Do you know how I can fix  this mistake?
George MelnicGeorge Melnic

Dear all, apart one "If" solution (i think Pedro Rodrigues' solution, the rest do not take into account the "1 star" / "x stars" problem which is a constant headache in programming radable text :) (where x stands for many or zero). If that solution was actually mentioned, i do apologize as i read the post "diagon-alley" after completing the challenge, as i was curious how others have solved the singular/plural problem.

Below my solution, has an extra "" to accomodate no rating (bit redundant as there was already a zero star case but oh well...)
 

Lead Quality Helper - nothing fancy, blatant addition (SUM ain't a thing?!?!)

-- 8< --- >8 --

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

-- 8< --- >8 --

IMAGE(
   CASE( Lead_Quality_Helper__c,
               0, "/img/samples/stars_000.gif",
               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",
               ""),
   If (Lead_Quality_Helper__c=1, text(Lead_Quality_Helper__c) & " star", 
               text(Lead_Quality_Helper__c) & " stars")
)
intergation orgintergation org
Hello everyone,

Just completed the challenge and wanted to post my lines of code here. I was so excited to work on it. Stars look awesome on a Lead page layout.  Hope, this helps! 

For 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)

For Lead Quality:
IMAGE(
CASE(Lead_Quality_Helper__c,
0, "/img/samples/stars_000.gif",
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"),
"Unknown")
 
Manny PiqueroManny Piquero
this worked for me 
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", 25, 100)

 
Jiwesh ChauhanJiwesh Chauhan
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)


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'), TEXT(Lead_Quality_Helper__c) + ' stars')
Poonam SutarPoonam Sutar
I tried the above formula in Lead_Quality . However, getting the below error . 

Error: Field Lead_Quality_Helper__c may not be used in this type of formula  
Tripti LakharaTripti Lakhara
Any one who can help me to pass this challange .I am getting one error .
"
We created a new lead with a value for company, but not email, phone, title, or industry. We expected it to have the '1 star' image but it didn't. If you use a CASE() function, make sure it’s positioned outside the IMAGE() function.
Check Challenge to Earn 500 Points"

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", 25, 100)


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)

Anyone who can help me to clear this challange .
Janice Matthies 7Janice Matthies 7
I see people commenting that they are getting errors like "Field Email may not be used in this type of formula". You have to create a FORMULA field. The return type is where you set Number for the first item and Text for the second.