News Ticker

How to represent Hierarchical Data in Dynamics CRM 2013?


One of the requirement comes often is to store and use hierarchical data like business process hierarchy, address (City, State, Country) hierarchy, portfolio hierarchy etc… In this post, we would discuss how to represent such data in CRM 2013 with out-of-the-box features.

For discussion, let us take the example of Country-State-City hierarchy.

I would discuss three important aspect of such requirement:

  1. How to represent such data in CRM?
  2. How to filter child data based on parent selection in the hierarchy? For example, how to filter State based on Country and City based on State?
  3. How to auto-fill the parent hierarchy when a child field is selected? For example, when City is selected how to auto-fill State and Country fields.

Step 1: Representing the hierarchical data in CRM

A country has many states and each state has many cities. Hence, it is evident that Country has 1: N relationship with State and State has 1: N relationship with City. After creating custom entities for Country, State and City, we must create the relationships.

City-State-Country Relationship

 

Step 2: Filtering child data based on parent selection in the hierarchy

Now we need to add these address fields to the entity where they would be used, for example Contact entity. A contact record would have address information.

Add Lookup fields (N: 1 relationships) City, State and Country in the Contact entity form.

City-State-Country in Contact Form

Use filtered lookups

When a user has selected Country, the system should filter the state records and display those which are within the selected country. Similarly, when use has selected the State the system system should filter the cities and display those within the selected city.

We can achieve this by using filtered lookup as shown below.

Apply the following Filter for the State lookup

State Lookup Filter

Apply the following Filter for the City lookup

City Lookup Filter

When these filters are applied on the State and City lookups, the result would be like the following. When Country is selected as Australia, only Australian states are displayed in the State lookup for selection.

State-Country Hierarchy Data

Step 3: Auto-fill the parent hierarchy when a child field is selected

To automatically fill-up the Country and State fields with appropriate values when City field is selected, we need to develop some Synchronous workflows. We would need two workflows in our scenario.

  • Auto-fill State when City is populated
  • Auto-fill Country when State is populated

Register these Synchronous Workflows against the Contact entity i.e. the entity where these lookup fields have been added.

The following is the “Populate Country from State” workflow which would auto-fill Country when State is populated. Similarly “Populate State from City” can also be designed which would auto-fill State when City is populated.

Populate Country from State Workflow

Once both the workflows are activated, then check the result. Navigate to a Contact record and only select City data. For example, select City as “Sydney” and save the record (Ctrl + S). The page refreshes and wow…the State and Country is auto-filled.

Before Saving the record

City-State-Country auto-population before saving

After Saving the record

City-State-Country auto-population after saving

About Dipankar Bhattacharya (59 Articles)
A multi-skilled Dynamics 365 Professional with strong experience in delivering IT projects especially across multiple industries. A Microsoft technology evangelist, a regular speaker at tech events, blogger and avid reader. Certified IT Architect and well versed in Solution Architecture of Business Applications using Microsoft platforms like Dynamics 365, Azure and Office 365.

Leave a Reply

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: