+ Start a Discussion
Regina200Regina200 

Help with text formula to show an image when two conditions are met

Hi, I've never studied coding or developing, so I'm having a bit of trouble figuring this out. Basically what I want my formula text to do is, if the Days Since Last Update field > 90 AND if Stage Name does not equal Closed Won or Closed Lost, show an image of a red exclamation point so that users know this opportunity needs to be reviewed. 

Here is the formula I am using: IF(AND(Days_Since_Last_Update__c > 90), IMAGE ("/servlet/servlet.FileDownload?file=015j0000000PsCZ","Red:High Importance"),null) 

The only issue is, I'm not sure how to insert the piece about if the Stage Name (which is a picklist) equals Closed Won / Closed Lost, the red image does not have to be shown and the opportunity does not have to be reviewed. 

Thank you so much for your help!!
Best Answer chosen by Regina200
Prem Anandh 1Prem Anandh 1
Hi Rlee,

I have created the formula field with mentioned criteria and its works fine for me. 
 
IF(
    AND
    (
      Days_Since_Last_Update__c  > 90, 
      NOT
      (
       OR
       (
        ISPICKVAL(StageName, 'Closed Lost'),
        ISPICKVAL(StageName, 'Closed Won')
       )
      )
    ),
    IMAGE("/servlet/servlet.FileDownload?file=015280000018x5E", 'red banner'),
    IMAGE("/servlet/servlet.FileDownload?file=015280000018x5n", 'Yellow banner')
  )

Below are the screen-shots for Valid and Invalid criteria:

Valid Criteria (If our formula field meets our criteria), it will show red banner

Red

Invalid criteria, It will show yellow color banner:

Yellow


I belive this helps you! Please let me know if you have any queries or help.

Thanks,
Prem Anandh

All Answers

Jordan FrankJordan Frank
IF(AND(Days_Since_Last_Update__c > 90,
OR( ISPICKVAL(Stage_Name,"Closed Lost),ISPICKVAL(Stage_Name,"Closed Won")), IMAGE("/servlet/servlet.FileDownload?file=015j0000000PsCZ","Red:High Importance"), 
null)

Try the above, basically it is saying IF Days > 90 & Stage Name is not equal to Closed Lost OR Closed Won then show the picture. Otherwise null.
Jordan FrankJordan Frank
Correction:
 
IF(AND(Days_Since_Last_Update__c > 90,
OR( NOT(ISPICKVAL(Stage_Name,"Closed Lost)),NOT(ISPICKVAL(Stage_Name,"Closed Won"))), IMAGE("/servlet/servlet.FileDownload?file=015j0000000PsCZ","Red:High Importance"),
null)

 
Regina200Regina200
Jordan, Thanks so much for your response. I tried that but I receive this error:  Error: Incorrect parameter type for function 'AND()'. Expected Boolean, received Text

 
Jordan FrankJordan Frank
Yea I forgot you cannot use an OR value within an IF statement, this returns true or false. Try this. 
 
IF(Days_Since_Last_Update__c > 90, 
IF(NOT(ISPICKVAL(Stage_Name, "Closed Lost)), 
IF(NOT(ISPICKVAL(Stage_Name, "Closed Won)),
IMAGE("/servlet/servlet.FileDownload?file=015j0000000PsCZ","Red:High Importance"),
null)))

 
Jordan FrankJordan Frank
Nevermind that won't work, let me try again!
Jordan FrankJordan Frank
IF(Days_Since_Last_Update__c > 90, 
IF(OR(NOT(ISPICKVAL(Stage_Name, "Closed Lost)), 
NOT(ISPICKVAL(Stage_Name, "Closed Won))), 
IMAGE("/servlet/servlet.FileDownload?file=015j0000000PsCZ","Red:High Importance"), null)))


Try this, you cannot have an IF statement inside of an OR but I think you can have an OR statement inside of an IF. 
Regina200Regina200
Ugh I am so sorry being a pain, but I received another error: Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2. 
I am so grateful for your help!!
Jordan FrankJordan Frank
No problem! Let's try this a different way. 
 
IF( TEXT(Stage_Name) = "Closed Won" || 
TEXT(Stage_Name) = "Closed Lost", 
null, 
IF(  Days_Since_Last_Update__c > 90, 
IMAGE("/servlet/servlet.FileDownload?file=015j0000000PsCZ", "Red:High Importance"), null))

 
Prem Anandh 1Prem Anandh 1
Hi Rlee,

I have created the formula field with mentioned criteria and its works fine for me. 
 
IF(
    AND
    (
      Days_Since_Last_Update__c  > 90, 
      NOT
      (
       OR
       (
        ISPICKVAL(StageName, 'Closed Lost'),
        ISPICKVAL(StageName, 'Closed Won')
       )
      )
    ),
    IMAGE("/servlet/servlet.FileDownload?file=015280000018x5E", 'red banner'),
    IMAGE("/servlet/servlet.FileDownload?file=015280000018x5n", 'Yellow banner')
  )

Below are the screen-shots for Valid and Invalid criteria:

Valid Criteria (If our formula field meets our criteria), it will show red banner

Red

Invalid criteria, It will show yellow color banner:

Yellow


I belive this helps you! Please let me know if you have any queries or help.

Thanks,
Prem Anandh
This was selected as the best answer
Regina200Regina200
Jordan-- Thank you for your continued help! Really appreciate it.

Prem--Thank you for the formula!! Totally got what I needed.
Prem Anandh 1Prem Anandh 1
Hi Regina, 

FYI, We can use static resource to populate image in formula field. So We no need to hardcode the Id's 

Thanks, 
Prem Anandh

 
Sagar SinghalSagar Singhal
IF(AND(ISPICKVAL(Stage__c,'Dismantling'),IMAGE('/resource/Dismantling','Red',80,80)))
show in error
Sagar SinghalSagar Singhal
when my stage is dismatling then  selected my photo