Recently, in one of my Dynamics AX implementations there was a need to validate the addresses on existing records and to ensure that the address is valid for any records created in the future. Had explored quite a few options during the implementation that I would like to share here.These can be used in a CRM implementation as well.

Third Party Solutions (ISV)

There are a number of Address Validation products available that can do bulk cleaning and real-time validation. While these Solutions can save some time and provide additional functionality they come at a cost ($$). Generally, the cost depends on the number of features in the solution, number of countries supported and the transaction volume. Below are some of the popular solutions

In-house development

With the help of a developer, building Address validation isn’t all that difficult. There are two main options in doing this

  1. Using APIs

    Here are some 80+ APIs that you could explore and use based on the requirement. There are also some free APIs that are used to validate addresses. However, be sure to read the terms of service as most of them want the API to be used for their own business and could sometimes have a downtime that could challenge your business processes. For example, USPS limits to only US addresses and has terms of  service to that require API usage for USPS shipping only. Another most used API is that of Google which limits the number of transactions per day on a free account and requires to be used with GMaps only.

    • Pros: Latest addresses, no maintenance required
    • Cons: Possible downtime, network issues
  2. Using Address Database
  • Download the address database from GeoNames and restore it on the local SQL Server to use for validations. It consumes some space and needs data refresh at regular intervals. With a little search you would discover some Shell scripts that you could use to download the database and restore it locally.
    • Pros: No external dependency, Works well for bulk/list validations
    • Cons: Needs regular updates to avoid stale data, doesn’t provide street level details

Note: There could be other sources to get the database especially the commercial ones. However, GeoNames is from Creative Commons and is free.

Evaluate these solution options and choose the best fit for your situation. After all, no man’s land has no place in enterprise applications.

Leave a Reply

Your email address will not be published. Required fields are marked *