I'm having a hard time with the following custom formula. I use this field in reports to determine if there are any data errors in our records. I can get the second scenario to work, but the first one is returning thousands of records that don't meet the criteria. I think that the issue has to do with how the IF statements are constructed, but basically, I want this formula to check for these two separate scenarios and display the error message for whichever one is true. Here's a little information about the terms used in the formula:
Needs: Custom Object
Desk: Account on Need or Case object
Hospital: Parent.Account on Need or Case object
/* Scenario 1: If a Need is open, then the Need's Desk must match the Case's Desk. */ IF( AND(Is_Closed__c = 'False', Desk_Location__c <> TEXT(Case__r.Desk_Location__c )), "ERROR: Need's desk must match the Case's desk", /* Scenario 2: If a Need is closed, then the Need's Hospital must match the Desk's Hospital */ IF( AND(Is_Closed__c = 'True', Desk_Location__r.Parent_Account_Name__c <> Case__r.Account.Parent.Name), "ERROR: Need's hospital must match Case's hospital", "") )
Is_Closed__ca checkbox or a picklist/text? If it's a checkbox you should be testing for
Truewithout the quotes.
is_closed__c = 'False'). Then, by going to a record that you know will be an error, you can see which expression is not evaluating as expected.