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:
- How to represent such data in CRM?
- 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?
- 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.
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.
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
Apply the following Filter for the City lookup
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.
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.
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
After Saving the record