Take Control of Your Salesforce Data: State and Country Picklists

Are you frustrated with the incomplete and inconsistent data in your Salesforce records? Tired of having to report off of different variations of country and state spellings and abbreviations? (i.e. typing United States of America, USA, U.S.A, America, US) You can now help yourself and your users by activating State and Country Picklists, previously in beta, now generally available to everyone with Salesforce.com Winter ’14. While this is a great new release of Salesforce, it should not be underestimated as being as simple as activating the feature…

Salesforce provides an interface that allows you to select multiple values and map them to standardized values. The interface finds values in all State and Country fields across all objects, so that you only have to map once. What a time saver! After you decide which Countries to allow your users to select and standardize the field values, you will map the States.

Before Activating State and Country Picklists …

To start: Scan your data. Salesforce was gracious enough to provide a scan that returns areas in your Salesforce instance with customizations that contain references to State or Country fields. The scan looks across Apex Classes, Visualforce Pages, Apex Triggers, Assignment Rules, Auto Response Rules, Email Templates, Escalation Rules, Field Sets, List Views, Reports, Validation Rules, Custom Buttons or Links, and Workflow Rules. Once you identify the areas where you will need to manually update, you must understand how these changes will affect your data.

We’re writing this post to highlight some key areas to watch out for as you begin this process. The easiest and most straightforward are misspelled or variant records. Currently, this is not case sensitive, meaning you will need to match up values such as “UNITED STATES” and “UNITED states” (Yes, this was a value I found in the mix of my own dirty data!) In the example below, there are also some counties in the UK that do not match to any picklist values. You can either choose to make these unknown or add each value manually.

You may also see many areas that have misspelled values. We’ve all seen it before; when someone quickly types something in a rush and mistypes a word, such as “Unitd Kindom” or “Barzil.” While these are honest mistakes, they can cause a nightmare in trying to decipher what your Users were trying to enter. For records like these, you may need to drill into the record to try and find more detail, such as the address or Zip Code, and update the field manually.

One of the biggest issues with State and Country Picklists occurs when States are entered as Countries. Many times I have seen users get lazy and enter Delaware as both the State and Country, or make an honest mistake such as entering Puerto Rico as the Country and not USA. These pose a problem, because you cannot map a value in a Country field to the corresponding State field. Hopefully there are fewer of these errors and are best handled through running a Report or using the Data Loader, and manually fixing.

After Activating State and Country Picklists …

When the feature is activated, the existing data in State and Country fields become Read Only fields. They retain their API names (e.g. BillingCountry), which is great for any integrations or Apex that look to those fields. New Fields are also added to your org to store the standardized picklist values. These fields have the same name as the original fields, appended with “Code,” such as BillingCountryCode. Salesforce has also made it very easy to track existing values before State and Country Picklists were activated. The screen shot below shows a Report with the old values shown in the “text only” columns and the new standardized data.

Note for example, that if you change the value for Marketing Science Institute from Massachusetts to New York, the Billing State/Province (text only) field with change to New York as well.

Gotchas …

The state conversion process only contains states for the US, Canada, Australia, Brazil, China, India, Ireland, Italy, and Mexico. This means if you want to store values for a country such as the UK, you cannot map those values to a standard picklist and instead have to manually key in those State values.

Two areas to investigate and cleanup are your Salesforce Web-to-Lead and Web-to-Case forms. You will want to standardize these form submits to match the values that are available in Salesforce. Otherwise, you are opening up the doors again to flood your system with dirty data.

For programmers, there are some distinctions to be aware of. When querying off of these new fields, you are either going to be returned with the text version or the ISO Code depending on field you query off. The field that contains the text “Code” will return the ISO Code, whereas the field without “Code” appended will give you the text value.

If you are weary to make the leap, I recommend first testing this activation in a Sandbox or Development Org. This is a great new tool, but nobody like surprises when it comes to their CRM. It’s always best practice to fully understand how new functionality will affect your system before deployment.

If you have any concerns about this or other related features, feel free to leave a comment below! Also, don’t hesitate to reach out to our team — we can help you figure out the best way to establish a routine to implement this and other release features!