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
Formula
/* 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", "")
)
Any ideas?
Thanks,
Rochelle
Is_Closed__c
a checkbox or a picklist/text? If it's a checkbox you should be testing forFalse
andTrue
without the quotes. – Matt Lacey♦ Nov 17 '14 at 23:52is_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. – cropredy Nov 18 '14 at 3:12