2

I am trying to write a simple validation rule and for some reason i am just not getting it right.

Here is what i want to do

If recordtype = Franchise, then i need to allow only the foll. numbers in Royalty Admin field. 15,18,20,25,30 these are the valid values. If the recordtype is anything other than Franchise then any value is allowed.

Here is what i have got 

AND( 
$RecordType.Name = "Franchise", OR( NOT(Royalty_Admin__c = 15) ,NOT(Royalty_Admin__c = 18),NOT(Royalty_Admin__c = 20),NOT(Royalty_Admin__c = 25), NOT(Royalty_Admin__c = 30)) )

This validation rule fires even if i give the right values or wrong values.

UPDATE:@sfdcfox i tried your code and it still fires the validation if enter 18 or 19. Here is the screenshot.

enter image description here enter image description here

| improve this question | |
4

You've confused "OR" with "AND". If the value is 15, for example, then it is not 18. When any of the OR values are true, the entire OR is true. Simply changing this to AND would fix the problem, but there's an easier way:

AND($RecordType.Name = "Franchise", 
  1 = CASE(Royalty_Admin__c, 15, 0, 18, 0, 20, 0, 25, 0, 30, 0, 1)
)

Here, we compare the field to a list of values, returning 0 if it is allowed, 1 if it is not (e.g. this ultimately evaluates to 1 = 1 when no values match, and 1 = 0 when any value matches).

Edit: Percent fields are represented as decimal values (fractions of 1), so you actually need to either use the decimal values (15% = 0.15), or multiply by 100. Here, I multiply by 100:

AND($RecordType.Name = "Franchise", 
  1 = CASE(Royalty_Admin__c * 100, 15, 0, 18, 0, 20, 0, 25, 0, 30, 0, 1)
)
| improve this answer | |
  • Would this perhaps be a little more clear if the CASE() were to emit true and false rather than 0 and 1 with a comparison to 1? – Derek F Jul 27 '18 at 16:40
  • 1
    @DerekF For whatever silly reason, CASE doesn't actually support true/false, hence the need to return a value (in this case, 1 and 0, but could also be text, etc). – sfdcfox Jul 27 '18 at 16:49
  • @sfdcfox, i still fires the validation rule even if i add the correct or wrong value. Updated the question with the screenshot – Prady Jul 27 '18 at 16:57
  • @Prady Percent fields are fractional so that they work properly in math operations. See updated answer. – sfdcfox Jul 27 '18 at 17:20
  • @sfdcfox This makes sense. Its working now – Prady Jul 27 '18 at 17:24
3

you need to change your OR to an AND

if royalty = 15, it doesnt = 18, and therefore the OR is true.

you want it to fire when RecordType = X AND royalty is not equal to 15 and royalty is not equal to 18 and royalty is not equal to 20...

| improve this answer | |
0

AND( $RecordType.Name = "Franchise", NOT(Royalty_Admin__c = 15) , NOT(Royalty_Admin__c = 18), NOT(Royalty_Admin__c = 20), NOT(Royalty_Admin__c = 25), NOT(Royalty_Admin__c = 30) )

Please try this. I think the issue is you are using OR condition instead of AND.

| improve this answer | |
  • I tried this as well. validation rule keeps firing if i enter the right or wrong values – Prady Jul 27 '18 at 17:16
  • Is the record type name correct? Assuming you are checking with values other than the ones in the formula should throw you an error theoretically! – codeyinthecloud Jul 27 '18 at 17:21
  • Ahh its a percentile. I think @sfdxFox answer should work in this case them. – codeyinthecloud Jul 27 '18 at 17:23
  • Yup sfdcfox answer resolved the issue – Prady Jul 27 '18 at 17:25

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.