2

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

Improve this question
  • The logic looks correct to me, though is the field Is_Closed__c a checkbox or a picklist/text? If it's a checkbox you should be testing for False and True without the quotes. – Matt Lacey♦ Nov 17 '14 at 23:52
  • BTW Rochelle - a good way to debug complex, misbehaving, formulas like this is to break it up into individual formula fields that you display on your page layout (perhaps one only you can see) where each formula field is one expression (like 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. – cropredy Nov 18 '14 at 3:12
  • @crop1645 Thanks for the tip! It turns out that the problem was with one of the fields I was using, not with the formation of the formula. This is resolved now. – Rochelle Nov 18 '14 at 14:35
  • @Rochelle Could you please answer your own question, to take this question from the list of unanswered ones? Thank you! – Sander de Jong Apr 15 '15 at 8:29
0

It turns out that the problem was with one of the fields I was using, not with the formation of the formula. This is resolved now.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.