How to create a Dutch address lookup

WRITTEN BY Duco Boer - 29 July 2014

Many Dutch organizations have a subscription to the Dutch zipcode table from Cendris:

http://www.postcodeshop.nl/

In this case we will demonstrate how you can auto-fill the city and street on the account or contact form upon change of the zipcode or house number.

This example is valid for : CRM 4.0, 2011 and 2013 On Premise. (not for CRM Online)

For example purposes we use the following fields:

1. address1_line1 = Street

2. address1_line2 = House number

Furthermore we have created a custom entity named new_TPGpostcode where we import the Cendris zipcode table frequently. In this example we assume you import the fields without any modification.

Cendris zipcode entity

To accomplish the autofill you need to create two calculated attributes. One for the street and one for the city. Also you need to add two client side triggers to the calculated attributes which will fire upon change of the zipcode and house number (address1_line2).

The target entity for the calculated attributes is either account or contact. The target attribute is either address1_city or street (address1_line1) depending on your configuration. The type is 'Single entity calculation'. The Calculation operator is 'Custom SQL'.

And now comes the most important part : the custom calculation syntax. For the city retrieval use the following:

case
   when [address1_line2] = '0' then
           isnull((select top 1 new_WoonplaatsnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 0 and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_city]) > 0 then [address1_city] else 'ONBEKEND' end)   
when CAST([address1_line2] AS int) % 2 = 0 then
       isnull((select top 1 new_WoonplaatsnaamNEN  from new_TPGpostcode where new_Huisnummerreeks = 2 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_city]) > 0 then [address1_city] else 'ONBEKEND' end)      
   else
           isnull((select top 1 new_WoonplaatsnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 1 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode) , case when len([address1_city]) > 0 then[address1_city] else 'ONBEKEND' end)   

end

For the second calculation 'the street' please use the following:

case
   when [address1_line2] = 0 then
           isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 0 and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)   
when [address1_line2] % 2 = 0 then
       isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 2 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)  
   else
           isnull((select top 1 new_StraatnaamNEN from new_TPGpostcode where new_Huisnummerreeks = 1 and new_nummervan <= CAST([target].[address1_line2] as INT) and new_nummertot >= CAST([target].[address1_line2] as INT) and upper(replace([target].[address1_postalcode], ' ', ''))= new_postcode), case when len([address1_line1]) > 0 then [address1_line1] else 'ONBEKEND' end)

end

Of course if you used other field names, you should change them accordingly.

Now the only thing that remains is to publish your triggers and you are good to go.