function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Glenn Nyhan 54Glenn Nyhan 54 

Need to Add European States to US/Canada State Abbreviation Validation Rule

I created a validation rule to make sure State field can only contain 2 letters (can also be blank), and must be in CAPS for US/Canada records. This rule also supports Canadian Provinces, which are also 2 letters and must also be in CAPS. But, we get membership requests from Europe where the State abbreviation is 3 letters like the one we got from Germany today which was NRW (North Rhein Westphalia), and so this rule in that case doesn't work:
AND( 
NOT(ISBLANK(MailingState)), 
OR( 
LEN(MailingState) <> 2, 
NOT( 
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY:PR:" & "AB:BC:MB:NB:NL:NT:NU:ON:PE:QC:SK:YT", MailingState) 
)))

So I'm stuck as to what to do. Should I add this to the current rule?:
OR( 
LEN(MailingState) <> 3 

Let me know if anyone has a solution for this issue.

Thanks in advance.

Glenn
Best Answer chosen by Glenn Nyhan 54
Manish BhatiManish Bhati
Hi Glenn,

You can use the below Validation rule:- 
AND( 
NOT(ISBLANK(MailingState)), 
OR( 
LEN(MailingState) <= 3, LEN(MailingState) >1,
NOT( 
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY:PR:" & "AB:BC:MB:NB:NL:NT:NU:ON:PE:QC:SK:YT", MailingState) 
)))

Mark it as answer if it works for you.

All Answers

Manish BhatiManish Bhati
Hi Glenn,

You can use the below Validation rule:- 
AND( 
NOT(ISBLANK(MailingState)), 
OR( 
LEN(MailingState) <= 3, LEN(MailingState) >1,
NOT( 
CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY:PR:" & "AB:BC:MB:NB:NL:NT:NU:ON:PE:QC:SK:YT", MailingState) 
)))

Mark it as answer if it works for you.
This was selected as the best answer
Glenn Nyhan 54Glenn Nyhan 54
Hi Manish,

When I replaced the current formula with this and ran Syntax check everything was fine, but when the person doing data entry tried it it did not work. I made a few modifications and now it works. Thanks for the help. 

Glenn
Glenn Nyhan 54Glenn Nyhan 54
Well I was incorrect. When I made a few modifications it worked for add a 3 letter abbreviation but then was failing in 2 letter abbreviations. Any ideas. 
Manish BhatiManish Bhati
Hi Glenn,

Just tell me your requirements. See if below requirememnts are correct:-
1.The MailingState should not be blank.
2.Length of Mailing state should be 2 or 3, nothing else.
3.Mailing state should not contain any of these :- AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY:PR:" & "AB:BC:MB:NB:NL:NT:NU:ON:PE:QC:SK:YT

Do the corrections if needed and also add something which is missed.
 
Glenn Nyhan 54Glenn Nyhan 54
I think thats the issue, "The MailingState should not be blank.", my original formula allows for the Mailing State field to be blank. We get memberships where the person filling out the form does not enter the State Abbreviation, and being that its a validation rule the State Abbreviation field then because a required field. In my original formula I add code that allows the Validation Rule to fire on save, and if the State Abbreviation field is blank, the record still saves, and the Mailing States field does contain all of thise - AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & 
"IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" &, and will need to also contain 3 letter European State fields, Germany for instance, which has abbreviations such as North Rhine Westfalia ie. abbreviated as NRW. 
"NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & 
"WA:WV:WI:WY:PR:" & "AB:BC:MB:NB:NL:NT:NU:ON:PE:QC:SK:YT