You need to sign in to do that
Don't have an account?
Larry Van Cantfort
problem with regex and picklist as criteria in validation rule.
I am trying to make a credit card field required when a picklist value is NOT Checking (as in checking account. The other picklist items are credit cards) and then check to make sure the credit card is numbers and a length of 16 for Visa and Mastercard or 15 if AMEX. I have two problems with my validation rule. Problem one, does not make the credit card field required when the picklist is Checking. Problem two, the credit card field accepts letters and numbers. I am new to validation rules and even newer to regex. Any ideas?
AND(
NOT(ISPICKVAL( Method__c , "Checking")),
( Not(REGEX( Credit_Card__c , "(\\d{4}-){3}\\d{3}|\\d{15}" ))
&& NOT( REGEX( Credit_Card__c ,"(\\d{4}-){3}\\d{4}|\\d{16}"))
)
)
AND(
NOT(ISPICKVAL( Method__c , "Checking")),
( Not(REGEX( Credit_Card__c , "(\\d{4}-){3}\\d{3}|\\d{15}" ))
&& NOT( REGEX( Credit_Card__c ,"(\\d{4}-){3}\\d{4}|\\d{16}"))
)
)
You can try this condition:
AND(
NOT(TEXT(Method__c) = "Checking"),
(NOT(REGEX( Credit_Card__c , "^((4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\d{4}-?\\d{4}-?\\d{4}|3[4,7]\\d{13}$" ))
))
The trick for the regular expression is to use this site: http://regexlib.com/
40 expressions just for the credit card: http://regexlib.com/Search.aspx?k=credit%20card&AspxAutoDetectCookieSupport=1
Example of expression:
^((4\d{3})|(5[1-5]\d{2})|(6011))-?\d{4}-?\d{4}-?\d{4}|3[4,7]\d{13}$ (raw)
^((4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\d{4}-?\\d{4}-?\\d{4}|3[4,7]\\d{13}$ (just doubling \ for a formula as you have already done)
Description: Matches major credit cards including:
Matches
6011-1111-1111-1111 | 5423-1111-1111-1111 | 341111111111111
Non-Matches
4111-111-111-111 | 3411-1111-1111-111 | Visa
Author: Steven Smith
http://regexlib.com/REDetails.aspx?regexp_id=49
All Answers
You can try this condition:
AND(
NOT(TEXT(Method__c) = "Checking"),
(NOT(REGEX( Credit_Card__c , "^((4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\d{4}-?\\d{4}-?\\d{4}|3[4,7]\\d{13}$" ))
))
The trick for the regular expression is to use this site: http://regexlib.com/
40 expressions just for the credit card: http://regexlib.com/Search.aspx?k=credit%20card&AspxAutoDetectCookieSupport=1
Example of expression:
^((4\d{3})|(5[1-5]\d{2})|(6011))-?\d{4}-?\d{4}-?\d{4}|3[4,7]\d{13}$ (raw)
^((4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\d{4}-?\\d{4}-?\\d{4}|3[4,7]\\d{13}$ (just doubling \ for a formula as you have already done)
Description: Matches major credit cards including:
Matches
6011-1111-1111-1111 | 5423-1111-1111-1111 | 341111111111111
Non-Matches
4111-111-111-111 | 3411-1111-1111-111 | Visa
Author: Steven Smith
http://regexlib.com/REDetails.aspx?regexp_id=49
Thanks so much for the help and the info on regex. I am going to keep that one handy as I continue to learn about regex.
Thanks again,
Larry
You could close your question by the way if a solution was particularly suited to your problem and helped you.
Thanks
Alain