You need to sign in to do that
Don't have an account?

State Abbreviation Rule: Two letter abbreviation and must be an abbreviation relative to a US State or Canada i.e. CA, WA, BC, QC etc.
I created a Validation Rule. The validation rule (set on Billing_State field), was created to insure if a lead record contact is listing an address in the US or Canada their State can only contain a two letter abbreviation and must be an abbreviation relative to a US State i.e. CA, WA, NY etc. But we get some foreign records coming in that are precluded from Salesforce because in some countries the state/province is more than 2 letters ex: North Rhine-Westphalia Germany = NRW. This is the rule:
AND(
NOT(ISBLANK(BillingState)),
OR(
LEN(BillingState) <> 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", BillingState)
)))
But here is my question, how can I write a Validation Rule that will allow for us to maintain data integrity yet still meet the need of allowing records into Salesforce where its a foreign country with longer state abbreviations?
Thanks in advance for any help,
Glenn
AND(
NOT(ISBLANK(BillingState)),
OR(
LEN(BillingState) <> 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", BillingState)
)))
But here is my question, how can I write a Validation Rule that will allow for us to maintain data integrity yet still meet the need of allowing records into Salesforce where its a foreign country with longer state abbreviations?
Thanks in advance for any help,
Glenn
Have you looked into the feature for State and Country/Territory Picklists? It can be found under Data Management and would allow you to specify these values globally while maintaining data integrity. Or (if you've looked into this already), what was the use case that prevented you from using it?
Travis
All Answers
Have you looked into the feature for State and Country/Territory Picklists? It can be found under Data Management and would allow you to specify these values globally while maintaining data integrity. Or (if you've looked into this already), what was the use case that prevented you from using it?
Travis
Did this solve your issue? If so, would you mind marking my solution as the best answer? Thanks!
Travis
This got pushed to the back burner for a bit. Just reviewing this now, and yes this does work. Thanks for the help, much appreciated.
Warmest Regards,
Glenn
--> https://success.salesforce.com/ideaView?id=08730000000kwZ8AAI