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 

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
Best Answer chosen by Glenn Nyhan 54
Travis Lee 1Travis Lee 1
Hey 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

Travis Lee 1Travis Lee 1
Hey 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
This was selected as the best answer
Glenn Nyhan 54Glenn Nyhan 54
Thanks Travis. I was unaware of the State and Country/Territory Picklists. I will look into this. 
Travis Lee 1Travis Lee 1
Hey Glenn,

Did this solve your issue? If so, would you mind marking my solution as the best answer? Thanks!

Travis
Glenn Nyhan 54Glenn Nyhan 54
Hi 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
Ken S (OLD ACCOUNT)Ken S (OLD ACCOUNT)
Everyone on this thread, please take a minute to Vote Up the idea of displaying 2 digit US state abbreivations, rather than the full spelled-out state name, by default (or at least a one-click setting) after enabling State and Country Picklists! Absolutely silly SFDC doesn't do this out of the box!! Thanks :-)

--> https://success.salesforce.com/ideaView?id=08730000000kwZ8AAI