Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Gary AllenGary Allen 

How do I get a formula field to work when either statement 1 or statement 2 meets the criteria color is red else green?

If either Statement 1 or Statement 2 is true then red, else green

Statement 1
IF( 
AND( 
Owner.LastName <> "Partners", RecordType.DeveloperName = "Phase 4" ),

OR

Statement 2
IF( 
AND( 
NOT(ISBLANK(Under_Current_Contract_Until__c)), Under_Current_Contract_Until__c>=TODAY(), 
Owner.LastName <> "Partners" ),

IMAGE("/img/samples/color_red.gif", "red", 30, 30),
IMAGE("/img/samples/color_green.gif", "green", 30, 30)
Best Answer chosen by Gary Allen
UC InnovationUC Innovation
Ok then this is my last suggestion. Try changing the logic for the second case in the or to check for <= today

IF(
    OR(
          AND(
                  Owner.LastName <> 'Partners',
                  RecordType.DeveloperName = 'Phase 4'
          ),
          AND(
                  NOT(ISBLANK(Under_Current_Contract_Until__c)),
                  Under_Current_Contract_Until__c <= TODAY(), 
                  Owner.LastName <> 'Partners'
          )
    ),
    IMAGE('/img/samples/color_red.gif', 'red', 30, 30),
    IMAGE('/img/samples/color_green.gif', 'green', 30, 30)
)

All Answers

UC InnovationUC Innovation
Hi Gary,

Try something like this out and let me knwo if it works for you. you might need to modify the result if true and for false.

IF(
    OR(
          AND(
                  Owner.LastName <> "Partners",
                  RecordType.DeveloperName = "Phase 4"
          ),
          AND(
                  NOT(ISBLANK(Under_Current_Contract_Until__c)),
                  Under_Current_Contract_Until__c>=TODAY(), 
                  Owner.LastName <> "Partners"
          )
    ),
    IMAGE("/img/samples/color_red.gif", "red", 30, 30),
    IMAGE("/img/samples/color_green.gif", "green", 30, 30)
)

Hope this helps!

AM
Keyur ModiKeyur Modi
Hi Gary,

as mentioned above you can try with this way.
  
IF(((owner.LastName<>"Partners" && RecordType.DeveloperName="Phase 4") || (NOT(ISBLANK(Under_Current_Contract_Until__c)) && Under_Current_Contract_Until__c>=TODAY() && Owner.LastName <> "Partners")),IMAGE("/img/samples/color_red.gif", "red", 30, 30),
IMAGE("/img/samples/color_green.gif", "green", 30, 30))

Please let me know if this one will help you.

Thanks,
Keyur Modi
 
Gary AllenGary Allen
Thanks but this didn’t work. [cid:image002.jpg@01D1A78E.91357790]
Gary AllenGary Allen
Thanks but the color was still green when I changed Under contract until to < today [cid:image002.jpg@01D1A78E.91357790]
UC InnovationUC Innovation
can you provide the values for the following fields:

Owner.LastName
RecordType.DeveloperName
Under_Current_Contract_Until__c
Gary AllenGary Allen
Owner.LastName is just not "Partners"
RecordType.DevelperName = "Phase 4"

Under_Curret_Contract_Until__c  : This is the field I am testing the formula by. When I change the field to = today 
the color is red. When I change the field to yesterday the field is green. The intent is to have it stay red because if 
the first two criterias above are met the status color needs to be red regardless of any other criteria in the rest of the 
formula.
UC InnovationUC Innovation
If your data is as you say then give this a try. You mention that you want to stay red in the event that Under_Curret_Contract_Until__c  is set to yesterday. I replaced quotes with single quotes.

IF(
    OR(
          AND(
                  Owner.LastName <> 'Partners',
                  RecordType.DeveloperName = 'Phase 4'
          ),
          AND(
                  NOT(ISBLANK(Under_Current_Contract_Until__c)),
                  Under_Current_Contract_Until__c >= TODAY(), 
                  Owner.LastName <> 'Partners'
          )
    ),
    IMAGE('/img/samples/color_red.gif', 'red', 30, 30),
    IMAGE('/img/samples/color_green.gif', 'green', 30, 30)
)

If still green then check your RecordType.DeveloperName and the comparison for Under_Current_Contract_Until__c.

Hope this helps!

AM
Gary AllenGary Allen
I am certain that the RecordType.DeveloperName is correct. I just tested your last suggestion by changing 
the Under contract until date to yesterday and color was green. When changed to = today it was red.

I appreciate your efforts though. This problem is driving me crazy.

Thanks!
GA
UC InnovationUC Innovation
Ok then this is my last suggestion. Try changing the logic for the second case in the or to check for <= today

IF(
    OR(
          AND(
                  Owner.LastName <> 'Partners',
                  RecordType.DeveloperName = 'Phase 4'
          ),
          AND(
                  NOT(ISBLANK(Under_Current_Contract_Until__c)),
                  Under_Current_Contract_Until__c <= TODAY(), 
                  Owner.LastName <> 'Partners'
          )
    ),
    IMAGE('/img/samples/color_red.gif', 'red', 30, 30),
    IMAGE('/img/samples/color_green.gif', 'green', 30, 30)
)
This was selected as the best answer
Gary AllenGary Allen
OK, the result was as close as I've come yet. I need to do some more testing and if this actually 
works I need to understand why this works.

One other question. Is OR boolean? If it is that doesn't make sense to me and why should your last 
suggestion work or even pass a syntax error? I am still trying to adjust from sql statements to this.
Gary AllenGary Allen
I am embarrassed to say this but you were correct when asked about the value of the fields. I said I was sure about RecordType.DeveloperName but I should have known that the developer Name would have an underscore (Phase_4). This means your last suggestion works!!! Thanks for all your help. I am still in the learning phase with Salesforce development so it’s Good to know there are people like you out there who are willing to help. Thanks! GA [cid:image002.jpg@01D1A78E.91357790]