A Codeless Address Finder Using Filtered Lookups

A bit of a mouthful of a title but, basically, I am going to show how to set up a codeless set of dynamic picklists (now called option sets) by ignoring option sets and using lookups, which can now be filtered, as a standard feature in CRM 2011.

My example will be an address finder. In this case we will enter a country and this will then restrict the states. This will then restrict the postcodes (zipcodes for our American friends) we can select and then this will restrict the towns and cities we can select.

First Step: Get Some Data

For this example, I am going to use Australian postcodes, but it will work with any postcodes. Australian postcodes are available here and US zipcodes are available here. I cannot find a decent list of UK postcodes so, if you know of one, please add a comment.

The Structure

We will create four new entities:

• Country: We will use the name field to store the name of the country
• State: We will use the name field for the state and create a lookup to country
• Postcode (or Zipcode): We will use the name field to store the code and create a lookup field to state
• City: We will use the name field to store the name of the city and have a lookup field to the postcode entity

I will also add lookup fields to these in a new section on the Account record.

The results are something like this.

The Country record:

The State record:

The Postcode record:

The City record:

and finally the lookups on the Account record:

Getting Data Into the System

Using the downloaded CSV record, we adjust the data so that we have four columns in each file:

• Postcode
• City
• State
• Country

Given we are only dealing with Australia, I added the country record by hand. For the other entities, we can download templates by going to Settings-Data Management-Templates For Data Import. The templates that are downloaded are XML files but they are completely compatible with Excel.

Populate the Postcode.xml, City.xml and State.xml files using the data from the source file. You will have to eliminate the duplicates. Excel 2010 has a ‘Remove Duplicates’ function in the Data tab of its ribbon to clean up the data.

If the xml file is over 8M in size, it will not import. This happened to me so I saved them all as CSVs and all was good. I also noticed that the files had a bunch of extra empty lines at the end of them, where the duplicates had been removed (nice work Excel!). I opened up each file in notepad and stripped out the extra commas.

Now combine the three xml files into one zip file (highlight them, right click and Send To Zip File)

Go to Settings and click ‘Import Data’ in the ribbon. It should just work. In my case the postcode file did not automap but once I mapped the fields for it, within the import wizard, I was back on track. Here are my results.

You will notice that 275 records failed. This confused me but, with a little investigation, it turns out that there are some postcodes in Australia that go across multiple states. Therefore, when I imported the cities and tried to link it to one of these postcodes, there were two records in the system to link to for the two states the postcode traversed. Given the blog is more about filtered lookups than about importing data, I will live with the 2% fail rate.

Creating the Filtered Lookups/Dynamic Picklist

If we ran with our system now, the lookups on the Account record would have no knowledge of each other. I would have to trawl through all the cities in the database to link to the right one. This is where the new filtered lookup feature comes into play. To set up the filtered lookup, go to the Account form, select the State field and click ‘Change Properties’ in the ribbon. Down the bottom of the default tab are the filtered lookup settings.

We want to configure the ‘Related Records Filtering’ because we are matching the Country that the Account is associated to and the Country that the State is associated to. I untick the user’s ability to turn off the filter and turn off the search box.

The result is a list of states just for the country I have selected on the Account record.

For those of you outside of Australia, these are the states and territories of Australia. I rinse and repeat for the Postcode and City

The result is every time I click a lookup, as long the lookup above is filled in, the list will be appropriately adjusted, just like a dynamic picklist (dynamic option set). In the case of the postcode lookup, there is probably limited value (in the database, Australia has eight state values and almost 3,000 postcodes and we have no good concept of ‘county’ like the USA) but the solution shows its value in the city lookup.

Because I have already set the postcode to ‘2089’, CRM knows I must be in one of the three cities in this area.

Conclusions and Takeaways

If you have a nice taxonomy of values and need to dynamically adjust one as you set the value of another, this is not a bad way to do it. One thing to note though is that the solution does not enforce consistency. What I mean by this is, in the above picture, we could go back to country and select ‘USA’, even though the rest of the data are for Australia.

Also, (and this one is more for Microsoft on the off-chance they read this) what would be really great is, as well as the ability to use a common entity to filter the lookup, the use of a global option set. However, at this time, we only have the option of a common linked entity.

Finally, for this specific example, we are replacing the default address fields on the form with new lookup fields. If you are using default reports or have legacy reports which reference the system address fields, you will either need to adjust these reports or keep the system fields in sync via a workflow or plugin.

Enjoy.