Dynamics CRM: Considerations when customising address fields

The perceived issue on customising address fields and how to store addresses in computer systems always fascinated me. Not because of any apparent complexity one might believe that exists when handling addresses, but mostly because of people’s undeserved anxiety around the subject. And in Dynamics CRM it is no different.

The issue tends to surround the myth that countries and regions with different subdivisions would require complex requirements for address handling. I came across a couple of clients who believe that they require such a complex solution that their Dynamics CRM deployments almost came onto a halt because of such hurdle. I the end, the solution relies not on complex customisation, but on standards compliance, a little bit of compromising, but most importantly: common sense.

Please note that while this post relates to address fields in Dynamics CRM perspective, the data management considerations presented here should be taken into account regardless of the applications.

What is the issue?

Well if you ask me, there is no issue. But here is the source of discord: Most countries and regions in the world (with the exception of some microstates and dependencies) have first-level administrative divisions (i.e. top-level administrative subdivisions), which in different countries tend to carry different names. Here are four examples of countries and the name of their respective first-level administrative division:

• Germany – Federated States
• Uruguay – Departments
• Sweden – Counties
• Switzerland – Cantons

By default Dynamics CRM presents the following fields to store address information:

• Address Line 1
• Address Line 2
• Address Line 3
• City
• State/Province
• Country/Region

With these fields in mind, let’s now consider the chart below which is a rough representation of how addresses are hierarchically defined:

Many customers tend to see as an issue that in Dynamics CRM the first-level administrative division of a country or region is named as State/Province, gripping on arguments such as “but what if here in country X we use counties rather than states or provinces?” As a result, customers tend to add custom fields for specific subdivisions in hope to use them with the relevant countries/regions. However, consider the following cut-out (i.e. resumed) diagram of client records in a database:

If we add fields for every possible subdivision we want to use, we will end up with a a plethora of unused fields for every account record we have. From the example above, although ACME is a company based in Germany (a country with federal states), even though we only need to use the state field for this account, the account record still has the fields for all other possible subdivisions we created (Cantons, Department etc).

From an technical perspective this is a mess which would make database designers and technical architects twitch their face with disgust. But from the end-user perspective this could also be a nightmare for reports, mail merge and other tasks that rely on data manipulation of account records.

If you still doubt this is a bad idea, then consider countries that have more than just one type of subdivision. In the United Kingdom for instance, England has 27 two-tier counties, 32 London boroughs, 36 metropolitan districts, 55 unitary authorities and 1 city corporation. Northern Ireland has 26 districts. Scotland has 32 council areas and Wales has 22 unitary authorities. Following the logic of creating a field for each subdivision (and being finicky about it), shouldn’t we create a field for each of these possible subdivisions? And for the record, the UK is far from being an exception. There are several other countries (Russia and China, being other notorious examples) that has more than one type of first-level administrative division.

If this was really an issue, every single logistics company out there would be in shambles. Your FedEx post for London England would end-up in the Faroe Islands, or the Amazon order you placed to be delivered in Moscow would end up in Guam. How come these companies are still standing?

Recommendations

My solution is plain simple. As a general rule when adding addresses, consider the State/Province field the first-level administrative division, regardless if for the address in question is for a state, province, department, canton, oblast or emirate. The City field should then be used by city, town or village.

It is just a label

With a little bit of common sense a user will understand that when filling an address, Bern being a Swiss Canton and therefore the first-level administrative division of a Swiss address, should be filled in the State/Province field. However if the “State/Province” label really bothers you, you could use a JScript bound to the Country/Region field to change the “State/Province” label according to the selected country/region. This will work best if you use an option set for the country/region field, but it can also work if using a lookup field like my Country/Region solution for Dynamics CRM.

City field vs State/Province field

In some cases it might be unnecessary to have both City and State/Province fields filled in. The first question you address is what sort of address information should be included in correspondences to this address.

This is often the case in the UK since only the address along with the city (or town, or village) is required along with the post code. For example when filling an address for London, typing London as the city should suffice for postal records. However, the second question you also need to consider is if State/Province is required for any of your business processes such as workflows, or reports. If that is the case then again the solution relies on common sense, and also some user training. Some solutions could be:

• Type London for both City and State/Region
• Type the London Borough (or City of London) in City, and London in State/Province

Keep in mind the following: When filling addresses, having extra information is better than missing information. One might think it is irrelevant to capture both Gloucester in the City field and Goucestershire in the State/Province field and that such address label would look unsightly. However when dealing with databases I rather have consistency over aesthetics. An address label is a means to an end and it is likely that it will end up in the bin as soon as the letter is received by its recipient.

Beware of the County field

Dynamics CRM does provide a county field (not used by default) for the several entities with address-related fields (e.g.: Accounts, Contacts, Competitors, etc). However, unless your are planning to use Dynamics CRM only with countries or regions that has counties as their administrative divisions, I strongly suggest you NOT to use for the previously mentioned reasons.

Another option would be that when filling an address of a country/region with county divisions, we sync the value of the County and the State/Province fields through the use of a workflow or JScript (in such case, one of these fields would be hidden in the entity form).

In some exceptional cases, the first-level administrative division of a country/region might not be used for address information. The Republic of Ireland is a good example, where its first-level administrative division of “Province” is subdivided into counties (see ISO 3166-2:IE). However in Ireland’s case nobody uses provinces for address purposes. But in some cases provinces might be required for reporting, business processes (such as workflows) or integration with other systems. so a combination of both County and State/Province fields might be required. Again, it is all about common sense and data integrity.

Final thoughts

The idea of this post is to give some guidance when customising Dynamics CRM around business requirements that might arise for capturing address information. It is important that such requirements are realistic considering the advantages and limitations of computer science, which is: computers think through binary operations; not as humans do! Therefore there must be a compromise between usability and system architecture in order to avoid bad data. Bottom line; it is all about common sense.