Dynamics CRM and Address Validation

Last year I was involved in a couple of CRM projects for which we had heavy requirements for address validation. The main scenarios that we encountered are the following:

  • All existing addresses must be validated and/or corrected (existing records)
  • New addresses saved in the system should be validated and/or corrected (synchronous validation)
  • When an address is entered, there should be an auto-complete functionality to help users enter the correct address

That was content for great research. First I want to share some facts about what you usually get with address validation solutions and then I will talk about the different solutions we used for the scenarios mentioned above.

About Address Validation Tools

An address validation solution is a tool that helps make sure an address is valid according to a standard (post service, government, other). It does so by taking an address as input, parsing it, comparing it to addresses in a proprietary database using match algorithms and returning a corrected/validated when it is found. There are plenty of address validation tools out there. Most of them have some common points.

First, they are sold as web services (cloud based or on premise) or as local APIs (.NET Libraries). If you are going with a local installation, it usually includes one or more address databases along with a web service. That means you will require little space on a server to install your address validation tool. Most vendors also offer a batch address validation/correction module.

Second, most of them function the same way: you send an address as an input and you get the validated/corrected address as an output. When an address is corrected, you get the changes that have been made (e.g. postal code changed, street type added etc.). In the same logic, when an address is not validated, you also get a validation status and sometimes a reason why the address is not validated. The batch processes usually provide a way to upload an address spreadsheet and get an output spreadsheet with the validation results.

Lastly, the pricing models are similar. You get can buy a certain amount of transactions (monthly, yearly or other). The price will vary based on the set of tools selected.

Obviously, there are other things to take into account. Some vendors only offer validation for specific countries and regions, some provide more than just address validation, for example address standardization, geocoding, multi-language support and more.

  • Correcting Existing Addresses

There are many ways to tackle this. The easier way is to use the validator’s batch processing module. When using Dynamics CRM, you can easily export your address using the “Export to re-import” functionality as shown here. Once you have your spreadsheet, you can easily import it to your address validation tool and get the corrected rows back into your CRM. There are two issues with this approach:

  • There is the 10000 records limit when you export data to Excel spreadsheets. If you have more rows to correct, then you need to consider an alternative solution
  • When an address is sent for validation, you may want to indicate a validation status in CRM which you can’t do by simply reimporting the corrected rows

In our case, we decided to write an application that reads all the rows for which we need to validate an address. For each row, a web service call is made to the address validation service provider, once we get the result, we store the corrected address as well as the correction details. When an address is not corrected, we store the details (if available) and we send the record through a manual processing for someone to perform the validation manually.

  • Address must be validated when saved to CRM

If you are adding this feature when building your system (before there is any address in your CRM), then you should never need the logic described in the point above.

The design here is very simple:

  1. Create a plugin on the address save or update (could be a custom entity or OOB such as Account or Contact)
  2. The plugin calls the address validation web service
  3. The plugin handles the business logic that is executed post validation (e.g. overwrite input address or creates a manual validation activity/task linked to the address being processed

Keep in mind that in point b, we call an external web service. This design works well for on premise installations. For CRM Online, you may not be able to reach those external web services if there are not hosted in Azure.

  • Address Auto-Complete Feature

For this client request, my colleague had experience with a CRM Add-On that does just that. It is called PostCodeAnywhere. It is a light weight solution for CRM (it only contains a few web resources). Here are a few screenshots.

It is worth noting that they support a lot of different countries so it makes it a very handy tool if you work for a company that stores international addresses. You can configure it to work on OOB address fields as well as custom fields.

I will also mention that using this tool is great to help system users entering correct addresses. However, there is no way to know (from an auditing perspective) that an address was entered using the add-on. Anyone could overwrite it manually.

A few tools

Here are some of the tools that we tried, used and/or tested for address validation:

  • Service Object : Great overall experience. The product is very good and the support and sales team are very responsive, efficient and flexible. They offer validation web services for US and Canada only.
  • Melissa Data : The product list is impressive (on prem, hosted web services, SSIS integrated tools etc.). They offer a global address validation web service.
  • nCode : The product has a lot of search algorithms for address validation. It comes as an on premise web service installation. It only support Canadian addresses.

The Google MAP and Bing APIs are also worth taking a look at.

Have fun!

Advertisements

5 thoughts on “Dynamics CRM and Address Validation

  1. I have also used a product called SmartyStreets to implement address validation in Dynamics CRM 2011. We combined this with Bing mapping to display the address on a map. They only support US addresses. I have used Melissa Data’s web site to troubleshoot some address issues in the past and to debug issues during the SmartyStreets implementation.

  2. Thank you for this information — so clear and well laid out. Your details on the different scenarios you worked through helped me conceptualize how my company can work to a) validate addresses currently existing in our CRM, and b) implement validation on contact creation.

  3. Hi, i’m currently using Ms BCM 2010 yet some of the addresses are either incomplete or wrong, some mail comes back as moved or missing info, is there a utility that integrates with BCM or Ms Dynamics CRM which can do this? Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s