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:
- Create a plugin on the address save or update (could be a custom entity or OOB such as Account or Contact)
- The plugin calls the address validation web service
- 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.