Community Sites have some great features including the categories and the ability to sort and filter on that information. I had recorded a tutorial on how to create custom views a while back, but it only let you choose the Category name from the filter dropdown.
Given our propensity for changing department names, creating custom views using the category field wasn’t really the most useful. The Category ID would have been so much more helpful. However, Microsoft in its infinite wisdom [note the sarcasm] decided not to provide the Category ID as an additional column within the Category Lookup column, and because it’s a sealed column (which means you cannot modify it), there was no way of attaching the Category ID to the Category column within the Discussion List.
So here is my puzzle: How do I get the Category ID into the Discussion List without making the modifications to fields I cannot change?
Because the Category column in the discussion list is a required field, and because it’s tied behind the scenes to a number of things, I couldn’t just replace it with a new lookup column to which I could tie the Category ID.
I knew the answer had to involve SharePoint Designer and Workflows one way or another.
After a little trial and error, I found a solution that worked.
In the discussion list, add a column called CatID or CategoryID – something obvious. Make sure you uncheck Add to Content Types. Whether it’s a text field or a number field may or may not matter.
From within SharePoint Designer, create a list workflow that looks at the Category in the Discussion List, compares it to the Category Name in the Categories list, and returns the ID field from the Categories list and updates the CatID or CategoryID field.
Set that workflow to run on all types: manual, any time a new item is added, and any time an item is changed.
So why go through all that trouble?
The reason we need this workaround is so that you can create custom views and instead of using the Category Name to filter by, you can use the Category ID, so that when department names are changed your custom views will still work. Without that Category ID, you would have needed to adjust your custom views anytime the Category Names are updated. I consider that to not be a great user experience, and so decided you would appreciate it being fixed.