Friday, January 27, 2012

Setting Multi-Level Categories Against a Record Codelessly

This is a bit of a variation on a theme I did a while ago when I talked about using filtered views for address population. In this case I wanted to explore if we could set up a subject-like hierarchy and then use filtered lookups to enter category levels easily against an account. It turns out it works quite nicely.


Essentially, you set the first category from you list of top level categories. Then, you set level 2 which will automatically adjust to only the valid values, based on the category 1 value. Finally, you set the category 3 value, based on the value in category 2.

So How Do I Set This Up?

First of all, we create a new Category entity.


In this case I have stripped everything out; there are no notes, activities etc. and the record is organization owned.

Next I set up a recursive 1:N relationship so that for a given category I can set an infinite number of category levels, each with an infinite number of categories.


The main points of interest here are the changing of the display name to ‘Parent Category’ and the changing of the display option to ‘Use Custom Label’ so I can refer to the next level down as ‘Sub Categories’.

On the category form I also add the parent category lookup, created as a result of this relationship.


Finally, I add three lookups to the account form so I can add three levels of category to my account record.


Finally, I populate my category hierarchy. Unfortunately, there is no nice tree view to employ so I fill it up in pretty much the same way I would if I was populating, for example, an account hierarchy. My advice would be to do it via a data import.

Where’s The Magic?

So far this will just let us pick three category values from all category values and add them to an account; not exactly exciting.

The trick comes in adjusting the filters on the lookups.

For category 1, we set up a new system view called Level 1 Categories. This just shows those categories with no parent category i.e. they are at the top of the tree.


We then go to our first lookup and force it to only use this view for its values.


The result is when we click the lookup only the values in the top level appear.


For the other two lookups, we use the Related Records Filtering properties of the lookup. For the category 2 lookup, we set it as follows:


The setup for the category 3 lookup is identical, except we replace ‘Category 1 (Accounts)’ with ‘Category 2 (Accounts)’.

This means, clicking on, say, the category 2 lookup shows only the valid values, based on the selection for category 1.


And that is it. With all that set up, all the user has to do is pick the category 1 value and the category 2 values will be auto-filtered. Once the category 2 value is selected, the category 3 values will filter.

Why Not Use Subjects?

Subjects are nice in that they can be linked to an account with a 1:N relationship and they have a tree view lookup. However, they do not, at this time, play nicely with lookup filters. If you add a subject lookup to an account form and try to filter its values, based on the selection in another subject lookup, CRM throws an error. This has been reported and, I am sure, will be addressed in a future roll-up.


If you have a multi-level hierarchy you wish to apply to a record, such as an account, and you are looking for a reasonably user-friendly way to capture the information this is not such a bad solution and does not require code or Silverlight web resources. While my first choice would be the subject entity, because of its friendly tree view, if this is not practical, this solution provides an alternative approach which supports any number of category levels.

1 comment:

Adam Vero said...

You've prompted me to write a blog post about how I did something similar recently using an option set and a lookup, and I'll definitely be referring people here to consider this alternative.

Lesser mortals would have created three custom entities for the natural hierarchy of this, but a single entity with a self-referential relationship so you can actually use this for any number of levels iteratively is brilliant!

I've used self-references for other things such as creating projects and follow-on projects, or memberships or licences and their renewal periods, or simply to 'clone' service cases. Never considered them for this kind of use though.
Definitely one for the toolkit.

Keep up the great work.