Do you need a custom county field in CRM 2011?

Pedro Innecco posted an article on his blog recently about considerations for customising handling of addresses in Dynamics CRM. This had some great advice clearly based on real-world experience and you should go read it now, then come back here for some more titbits on this topic.

I agree with Pedro’s view that some users get unnecessarily knotted up over labels sometimes because they are creatures of habit rather than purely logical data processing machines (which is probably a good thing most of the time). But if you re-label state as province or canton for their ‘convenience’ and they want to add an Account which is in another country they can get all flustered rather than using common sense.

The choices to make can also depend on the context of the business. If you only really do business in one US state then you might feasibly want to divide up records by the next level of hierarchy for sales territories, or service visits using counties even if they are not used for postal addressing. If your business is nationwide, then it is far less likely that anything below the first level state/region/province would be necessary.

So is the built-in “stateorprovince” field the best option?

One big thing to sway in favour of using the built-in state field (even if renamed to localise it for the business location) is that when you do a mail merge, the built-in stateorprovince field is included in the list of address fields, whereas the county field is not, and nor will any custom fields you add. Asking users to remember to add the right fields every time they start a new mail merge is a bit nuts and time consuming when changing the display name and form labels achieves the same thing more easily.

What is against it is that if you do rename it you need to change the display name, and the label on every form where it appears – but as you will see below although you should change the display name, I suggest taking it off the form in many cases anyway

Using an Option Set to make life easier

If your country has only a few top-level regions then setting up a global option set in a second field can help users avoid spelling errors. This is great where there are very few “states”such as in Canada or Ireland, reasonable enough for the 16 Lände in Germany, maybe OK for 26 cantons in Switzerland but starts to become a horribly cumbersome picklist in France, US or UK.

But I said you should use the built-in stateorprovince field for state for things like mail merging, didn’t I, so how can you get the best of both worlds? Use both!

I would suggest to use an option set for data entry then copy this value into the ‘real’ field using workflow so it is there when needed for a mail merge (yes, you can copy the value from an option set into a text field in workflow, but not vice versa). Make sure you do this when the records are created and when the option set field value is changed.

I would be tempted to only display the option set on the form, and keep the built-in text field hidden from users, and similarly make only the option set searchable (see below) since that lends itself well to use in Advanced Find for doing implicit “OR” queries simply by choosing multiple values (equals “Texas” or “Kansas”, for example).

Add the same option set and same workflow to Leads, Contacts and Accounts. Of course in CRM 2011 you can use a global option set so you guarantee to get the same items in all three entities. Make sure to add mappings for the option set and the text field to copy values from Lead > Account, Lead > Contact and Account > Contact.

Using a lookup as an alternative to an option set

If you have a few too many values for a usable option set, consider creating a custom entity to hold the “state”. This approach at its simplest would use a set of records (pre-populated ideally by data import) each of which has a single text field to hold the name of the state. Again, use workflow to copy the resulting value to the built-in stateorprovince field and make sure to map the lookup between Leads, Accounts and Contacts.

The lookup approach has a few extra possibilities that can add levels of convenience for users, such as:

  • You can add more than one text field to the “state” custom entity, for example to add the state abbreviation and make this available as a find field (by editing the Quick Find view). Now users can simply type the abbreviation in the lookup field, hit tab and it will resolve to the right record. This may not be perfect in all cases but it is a damn good start – eg in the US typing “MA” will result in a list to choose between Maine, Maryland or Massachusetts since they all begin with that pair of letters. Likewise for “NE”, but in both cases three letters for Mas or Neb is unambiguous, for others use the regular abbreviations.
  • You could add another field on the state record for any additional information or disambiguation to help users make the intended choices, which may be according to your own set of rules rather than some “ideal” perfectly accurate reflection of reality. Maybe there is a city which sits across two states, or some degree of ambiguity whether to use more or less precision (eg should you be looking for “London Borough of…” or just lump everything together as “Greater London”? Is Bristol in Avon or South Gloucestershire, or North Somerset, or is it a separate unitary authority in its own right?). To see this, from the lookup dialog simply select the “possible” state record and click “properties” to open the record to view (you could also include the disambiguation text in the lookup view, but if it is lengthy that might not be ideal)
  • Once you have a lookup, you also have a “look down”! Opening a state record would give you instant access to the Accounts (or Contacts, or Leads etc) which are in that area from the left navigation. Make sure you don’t give most users the privileges to create new state records otherwise you loose the element of control which is part of the point here. Users will need to have the rights to read and “append to” on your custom entity (as well as “append” on the Account entity, which they probably already have, hopefully). Including this in a baseline security role which you give to all users will be a good way to ensure this works.
  • The downside of a lookup is that it does make advanced find or other reporting more clunky compared to an option set, and to do an “OR” you have to do the long-hand method of grouping clauses and so on. It’s a trade off between functionality on the form, and in the query – which do your users interact with most?

Tidy up the loose ends

Whatever solution you use to the problem, make sure you help users further by tidying up the loose ends such as:

  • fields you are not expecting people to use, such as the built-in county field should be marked as not searchable so they don’t appear confusingly in Advanced Find (edit the field under the entity in your solution or the default solution, and change “Searchable” value to “no”, you can multi-select fields and click edit to change this property for several fields at once if you want to).
  • rename these unused fields as well so they are obvious to future system customisers and to users when they show up in other contexts (such as mail merge field choices, Excel export list). a suffix of "DO NOT USE" on the end ought to be obvious enough
  • personally I would not like to remove built-in address fields since they are not really on the Account and Contact records but in the addresses table. Hide and rename them, but I would probably avoid actually deleting them from the schema.

What do you use for states, counties and everything else? Are options sets better than lookups for this? Start a discussion in the comments below

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

4 Responses to Do you need a custom county field in CRM 2011?

  1. NateOne says:

    I like the lookups for the reasons you stated as well as the ability to use Filtered Lookups. i.e. your State/Province entity can have a relationship to Country and then the State/Provice lookup can be filtered by the Country, with no code.

    I also have had trouble using Picklists in Workflows i.e. dynamically setting picklists x = to picklist y. I’m not sure if that is resolved with Global Option sets.

    Lookups are perhaps a little more tedious to fill in than picklists, but in general I prefer them for the reasons mentioned above.

    • Adam Vero says:

      Thanks for your viewpoint.
      I also like the ability to filter lookups, either using native functionality as you suggest or other methods (which I will be looking at in a later post). I also hate all the propopsed solutions for dynamically filtering picklists except in very simple cases. There’s almost always a better and more sustainable way.
      Moving away from the specific example of country / state and looking at this more generally, there are also situations where you want some specific set of users to be able to maintain the “list” for themselves – things like industry sectors might be a prime example. You don’t want anyone changing it, but equally getting users in the marketing department to edit a picklist is way outside their comfort zone (and mine, giving them that level of access). To also edit JScripts to dynamically filter these is a definite no-no for them, and using a custom entity with a lookup to another one gives them an easy way to deal with this without breaking anything.

      Global option sets make workflows a dream – when you go to set the properties for a create or update of a record and click into a field, it always filters the available list of fields to use a value from (dates, for example, or a lookup of the appropriate type). This means that in many cases it filters to only allow you to choose the one field using the same option set. For our example of using a global option set for something like state, this is perfect and means filling in these kind of things is very quick and easy, and much less prone to errors. You could have more than one field on an entity using the same option set – perhaps for “which of our products / services is this prospect interested in”, storing perhaps three values for the three things they are most likely to buy from you.

      Well designed lookups and getting users to use type + Tab to auto-resolve them makes them at least as easy to fill in as a picklist.
      From an admin point of view, lookups are easier to populate simply by using data import of course.

      But advanced find uses picklists so much more effectively for “OR” situations, which can be quite common for geographical queries. Maybe you want to invite clients in a handful of states to an event. Adding lots of lines to an OR clause soon gets tedious, compared to the equivalent with a picklist. Like so many design decisions, there may be a trade-off to make and you have to listen carefully to the business needs and how they expect to use things on a daily basis to decide which way to go for different situations.

  2. pmdci says:

    Adam,

    Great article!

    For country/regions, I almost always use a global option set, which I made available for download at my site. I added all country/regions based on the ISO 3166-1 specification, and the value of the country/regions are also based in their ISO 3166-1 numerical value. The solution also contain some useful jScripts. I have an article about this here: http://www.pedroinnecco.com/2011/06/dynamics-crm-adding-a-country-region-option-set-using-iso-3166-1/ but you can also download the full solution under “Projects”.

    But for one given customer I am considering using referential lookups in a solution, where depending on the country/region a user selects, the options for the country/region top level constituency will change. For example, if a user selects USA as Country/Region, the label for the top level constituency will become “State” and only the 50 USA states will be available (New Work, California, etc).

    The caveat with this solution is that I can’t find a way to make the text inside the lookup be localised. So if I have a multilingual Dynamics CRM deployment this can be an issue. If there could be a way to change the field displayed inside a lookup box, I could come up with an easy fix. For example, we could have a fields like name_EN, name_ES, name_FR, name_DE, name_PT etc for the localised names. Then based on the user’s Dynamics CRM language pack, the lookup field should show name_EN or name_ES, etc.

    Also, administrators and customisers should always ensure that the address fields in CRM are populated. Perhaps not directly, but with the use of jScript and workflows. So for example if you’re using an option set for country/region, make sure that you parse the country/region name into the country/region text field. This is important to maintain compatibility with other solutions (such as Bing/Google maps integration) and also for reports. I have a sample script on how to do that in my country/region option set (mentioned above).

  3. pmdci says:

    Hi Adam,

    Yesterday I wrote another post on Addresses in Dynamics CRM 2011, this time focusing on how to handle the Address entity, including some jScript and workflow suggestions. You can find it here: http://www.pedroinnecco.com/2012/04/dynamics-crm-the-importance-of-the-address-entity/

    Just my extra 2p to keep the discussion going! 🙂

    P.

Please feel free to join in the conversation below...

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

%d bloggers like this: