2

A field(Example__c, Text 255 char) can have a value of alphanumeric(numbers and/or letters) type and character length should ONLY be either:

  1. 3 character value
  2. 7 character value
  3. 12 character value

I have written the below validation rule:

AND(
    NOT(
        REGEX(Example__c,"[A-Za-z0-9])
    ),
    OR(
        LEN(Example__c) <> 3,
        LEN(Example__c) <> 7,
        LEN(Example__c) <> 12
    )
)

However while testing I see that although it's checking the alphanumeric validation but it's not checking the length at all. All values are getting saved no matter what the length is. I am unable to find what mistake I have made in the formula, please help.

1 Answer 1

2

The correct version of the validation rule in your question is :

AND(
    NOT(
        REGEX(Example__c, "[A-Za-z0-9]")
    ),
    OR(
        LEN(Example__c) <> 3,
        LEN(Example__c) <> 7,
        LEN(Example__c) <> 12
    )
)

Notice the " at the end of Regex and an extra ) removed.

But I think for the requirement which is described , we need to have OR operator instead of outermost AND. This is because error needs to be thrown if either the field does not match the regex or its length is not valid. Also , the length check needs to be an AND. Based on above logic , validation rule becomes :

OR(
    NOT(
        REGEX(Example__c, "[A-Za-z0-9]")
    ),
    AND(
        LEN(Example__c) <> 3,
        LEN(Example__c) <> 7,
        LEN(Example__c) <> 12
    )
)
8
  • 2
    Yep, this looks spot-on. Hope you don't mind my edit. For others: The general "rules" with validation rules are: 1) They describe when your data is invalid (instead of describing when data is valid). 2) If you're comparing a single field against multiple values, you need to use OR() for equality and AND() for inequality (because a single field can only hold 1 value at a time, it cannot be both value1 AND value2 and vice-versa for an inequality check).
    – Derek F
    Mar 14, 2023 at 13:17
  • @DerekF : Thank you for the edit. I will take care of the formatting next time.
    – Rohit
    Mar 14, 2023 at 13:21
  • Thanks for the reply. Firstly can you explain why we are enclosing the LEN part inside AND()? Value inside a field can only be 3 char or 7 char or 12 char, so I think we need OR() right? Secondly I tried this rule but now it is not accepting any value irrespective of the length. @Rohit Mar 14, 2023 at 14:49
  • @Hatake_Kakashi : 1. We need to add formula for error , So , if the length is anything other than 3 , 7 ,12 we need to throw error. Let's consider , we put OR in place of AND , If the field length is 3 ,we satisfy condition for 7 and error gets thrown which is incorrect. 2. Formula is not working since regex appears to be incorrect. If you remove regex condition , it works for the lengths. You need to correct the regex as per your exact requirement.
    – Rohit
    Mar 14, 2023 at 15:05
  • @Rohit - Can you please tell me my mistake in REGEX? Since you did not make any changes earlier I thought it was fine. Mar 14, 2023 at 15:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .