+ Start a Discussion
Dorel Nasso 9Dorel Nasso 9 

Help with a conditional image formula field

I am trying to create a image field flagging when an opportunity is behind schudule.  I am using the stage field and a custom field of "# of days in Process" in the formula.  The Days field is also a formula from when the opportunity is received.  I can get the formula to work with the first condition but I can not add other stages to the formula.

IF(AND (ISPICKVAL ( StageName, "Received"), of_Days_in_Process__c  <= 2), IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(AND (ISPICKVAL ( StageName, "Strategy"), of_Days_in_Process__c  <= 3), IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(AND (ISPICKVAL ( StageName, "Devlopment"), of_Days_in_Process__c  <= 5), IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(AND (ISPICKVAL ( StageName, "Assembly"), of_Days_in_Process__c  <= 6), IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)))     

I keep getting error message for ",", IF, And.  Please advise what I am doing wrong.

Thank you,
Dorel    
Best Answer chosen by Dorel Nasso 9
Dorel Nasso 9Dorel Nasso 9
This is the formula I got to work.  I add an image at the end instead of a blank.   Thank you everyone for your help.
CASE(StageName, 
'Received', IF(of_Days_in_Process__c <= 2 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Strategy', IF(of_Days_in_Process__c <= 3 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Devlopment', IF(of_Days_in_Process__c <= 5 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Assembly', IF(of_Days_in_Process__c <= 6 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30))

All Answers

SarisfdcSarisfdc
Try THIS CODE

IF(ISPICKVAL ( StageName, "Received"),

If(of_Days_in_Process__c  <= 2, IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(ISPICKVAL ( StageName, "Strategy"), 

IF(of_Days_in_Process__c  <= 3, IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(ISPICKVAL ( StageName, "Devlopment"),

IF(of_Days_in_Process__c  <= 5,
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)),

IF(ISPICKVAL ( StageName, "Assembly"),

IF(of_Days_in_Process__c  <= 6,
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30))
))))


Thanks!
Raj VakatiRaj Vakati
Use this formula .. 
 
Data Type	Formula	 	 
CASE(StageName, 
'Received', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Strategy', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'Devlopment', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'Assembly', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'')

 
Dorel Nasso 9Dorel Nasso 9
@Sarisfdc, Thank you but I get the following error message:
Error: Invalid Data. 
Review all error messages below to correct your data.
Incorrect number of parameters for function 'IF()'. Expected 3, received 2 (Related field: Formula)
Raj VakatiRaj Vakati
This will work 
CASE(StageName, 
'Received', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Strategy', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'Devlopment', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'Assembly', IF(of_Days_in_Process__c <= 2 ,IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30,30), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)) , 
'')

 
Dorel Nasso 9Dorel Nasso 9

Raj V, Thank you for your formula.  The formula saves but no images show in the field.
Raj VakatiRaj Vakati

Try this ..  
 
CASE(StageName,
'Received',  IF(of_Days_in_Process__c  <= 2 ,IMAGE("/img/samples/flag_green.gif",'Green', 30,30),
IMAGE("/img/samples/flag_red.gif",'Red', 30, 30)),
'Strategy',  IF(of_Days_in_Process__c  <= 2 ,IMAGE("/img/samples/flag_green.gif",'Green', 30,30),
IMAGE("/img/samples/flag_red.gif",'Red', 30, 30)) ,
'Devlopment',  IF(of_Days_in_Process__c  <= 2 ,IMAGE("/img/samples/flag_green.gif",'Green', 30,30),
IMAGE("/img/samples/flag_red.gif",'Red', 30, 30)) ,
'Assembly',  IF(of_Days_in_Process__c  <= 2 ,IMAGE("/img/samples/flag_green.gif",'Green', 30,30),
IMAGE("/img/samples/flag_red.gif",'Red', 30, 30)) ,
'')




 
Raj VakatiRaj Vakati
Make sure your image file is externally accessable 
Dorel Nasso 9Dorel Nasso 9
Thank you but it is still a blank field.
Raj VakatiRaj Vakati
from where you are getting these icons? static resource ? 
Dorel Nasso 9Dorel Nasso 9
The image worked in my formula when I used only the first condition.  The images are from the app exchange download.
 
Raj VakatiRaj Vakati
Looks like your URL is incorrrect .  Steps to create a Formula Field that displays an image in Salesforce Classic

Refer this line ..

https://help.salesforce.com/articleView?id=000007604&type=1

 
Obtain the URL to the files you uploaded
Click the Documents tab.
From the "Folder" picklist, select the folder you created in step 1.
Click Go.
Click View next to the name of the file you want to use.
- A new browser window or browser tab opens.
In the browser's address bar, select and copy the URL.

 
Dorel Nasso 9Dorel Nasso 9
I am using Lightning not Classic, does that matter?  thank you
Dorel Nasso 9Dorel Nasso 9
I forgot, I am using the image in another image field and it works
 
Raj VakatiRaj Vakati
ok .. Close the thread! 
Dorel Nasso 9Dorel Nasso 9
Sorry, I did not explain myself correctly.  I was trying to say I know I have the correct URL for the image because I am using it in another field.  None of the formulas work.
Narender Singh(Nads)Narender Singh(Nads)

Hi Dorel,

Please hit this url and please verify if you are seeing that image.

https://PutYourOrgDomainNameHere.com/servlet/servlet.FileDownload?file=0151F000000A3BF

Dorel Nasso 9Dorel Nasso 9
Narender, when I click the link I see the image.  Do you know what I am doing wrong?  Thank you for your help
Narender Singh(Nads)Narender Singh(Nads)

Hi Dorel,
I see you are using a same image for all the TRUE Conditions and another for FALSE Condition, so I took the liberty to shorten your formula.

You can use this formula:

IF( OR(ISPICKVAL(Stagename, "Received") &&  TotalOpportunityQuantity  <=  2,
       ISPICKVAL(Stagename, "Strategy") &&  TotalOpportunityQuantity  <= 3,
       ISPICKVAL(Stagename, "Devlopment") &&  TotalOpportunityQuantity  <= 5,
       ISPICKVAL(Stagename, "Assembly") &&  TotalOpportunityQuantity  <= 6
      ), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30) 
)

*Replace TotalOpportunityQuantity  with your custom field name i.e 'of_Days_in_Process__c'

Note: If you want to have a different image for each opportunity stage then formula will be different.

Let me know if it helps
Thanks!

Dorel Nasso 9Dorel Nasso 9
This is the formula I got to work.  I add an image at the end instead of a blank.   Thank you everyone for your help.
CASE(StageName, 
'Received', IF(of_Days_in_Process__c <= 2 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Strategy', IF(of_Days_in_Process__c <= 3 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Devlopment', IF(of_Days_in_Process__c <= 5 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
'Assembly', IF(of_Days_in_Process__c <= 6 , IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30),IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BN",'Red', 30, 30)), 
IMAGE("/servlet/servlet.FileDownload?file=0151F000000A3BF",'Green', 30, 30))
This was selected as the best answer