0

I want to express this condition with my new formula type field that returns text

Green Flag: is still valid

Orange Flag: 1 year approaching its end.

Red Flag: no longer valid so exceeds the "End date"

Example :

Start date: 01/01/2022
End date: 31/12/2024
  if the date 01/01/2022 my fields will be Green Flag ,
If the date 31/12/2023 my fields will be Orange Flag
If the date 31/12/2024 my field is Red Flag

==> My formula :

IF( OR( Start_Date__c = TODAY(),IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagGreen.png")) , 
OR( Start_Date__c  < TODAY() > End_Date__c),IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagOrange.png" )),
OR(  End_Date__c  = TODAY(),IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagRed.png" )
))

I am not sure if the condition is correct or not .

2
  • In your OR() statements, you are checking: whether a date field has a certain value to an image insertion. I don't think that is what you are intending to do. From Salesforce help: OR "Determines if expressions are true or false. Returns TRUE if any expression is true. Returns FALSE if all expressions are false."
    – Moonpie
    Mar 28, 2022 at 15:35
  • @Moonpie , should I Use just IF() ? any suggestion from you ? Mar 28, 2022 at 15:38

1 Answer 1

1

Firstly, if you write your formula more like code it is usually much easier to follow. (You may have it that way on your end and it just copied/pasted into your question differently.) Something like this:

IF( 
  OR( 
    Start_Date__c = TODAY(),
    IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagGreen.png")
  ), 
  OR( 
    Start_Date__c  < TODAY() > End_Date__c),
    IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagOrange.png" )
  ),
  OR(  
    End_Date__c  = TODAY(),
    IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagRed.png" )
  )
)

Secondly, here is the documentation for Salesforce Formula Operators and Functions: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/customize_functions.htm


OR(logical1, logical2...): Checks whether any of the arguments are true and returns TRUE or FALSE. Returns FALSE only if all arguments are false

IMAGE: Inserts an image

IF(logical_test, value_if_true, value_if_false): Determines if expressions are true or false. Returns a given value if true and another value if false.


Your OR statements are checking whether either a certain date comparison is TRUE OR an image insertion is TRUE.

What you want is to test that date comparison, and then insert an image if that comparison is true; otherwise, make another comparison. For this you can use nested IF statements (like can be seen in an example in the linked Salesforce documentation).

In your first IF:

  • logical_test: date comparison
  • value_if_true: image to insert
  • value_if_false: next check - which will be another IF statement
  • etc.

UPDATE: As @DerekF pointed out in a comment, your comparison of Start_Date__c < TODAY() > End_Date__c) is invalid. That could be changed to AND(Start_Date__c > TODAY(), End_Date__c < TODAY()).

Taking your code (plus the UPDATE change) would give this:

IF(
  Start_Date__c = TODAY(),
  IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagGreen.png"),
  IF(
    AND(
      Start_Date__c  > TODAY(),
      End_Date__c < TODAY()
    ),
    IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagOrange.png" ),
    IF(
      End_Date__c  = TODAY(),
      IMAGE($Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c &"/Icons/FlagRed.png" ),
      // YOU NEED SOME VALUE HERE IF THIS IS FALSE
    )
  )
)

But note that if Start_Date__c is prior to (earlier than) today, OR if End_Date__c has passed, then whatever you put in // YOU NEED SOME VALUE HERE IF THIS IS FALSE will be used. If you want it to be a red flag in those cases, you can leave out the last IF and just use the red flag image.

3
  • The Start_Date__c < TODAY() > End_Date__c that you used from the OPs question (the second IF) isn't valid. There's probably a way to extract $Setup.CORE_Static_Resource_URL__c.CORE_Relative_URL_Path__c too. Maybe it's possible to finagle CASE into working.
    – Derek F
    Mar 28, 2022 at 16:11
  • Hmmm... I guess I should have looked more closely. I was more worried with/focused on the IF/OR logic issues.
    – Moonpie
    Mar 28, 2022 at 16:15
  • Edited for the invalid date check. I'll let the OP do that CASE finagling, if so desired, as "homework". :)
    – Moonpie
    Mar 28, 2022 at 16:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .