Inconvenient Content Query Web Part vs. Lookup fields with multiple values
Content Query Web Part has been around for a while now and has proven to be a great performing and a very flexible content aggregation solution. In spite of its power it has some shortcomings like for example lack of support for Lookup fields with multiple values.
Almost every Web Content Management (WCM) solution I built on top of Microsoft Office SharePoint Server (MOSS) 2007 required using one or more Lookup fields. Comparing to other alternatives like a Choice field, a Lookup field allows users to maintain the list of value themselves in an intuitive way and all that without modifying any of the existing Site Columns.
Some of the Lookup fields, like Categories or Tags, must allow selecting multiple values. And this is where the problems begin: the Content Query Web Part doesn’t support MultiLookup fields at all. As soon as you include a reference to one in your query, you won’t get any results.
This problem isn’t new and has been mentioned on the Microsoft Enterprise Content Management (ECM) Team Blog nearly three years ago: “SPSiteDataQuery and by extension the Content Query Web Part doesn’t support multi-value lookup fields”.
Considering how often you might want to retrieve the value of a MultiLookup field in a content aggregation the lack of support for it in the CQWP becomes quite problematic: does it mean that we must develop our own alternative for aggregating content in order to use some of the basic functionality of Windows SharePoint Services v3? What about the performance: are you sure that it would perform as good as the standard Content Query Web Part?
If you do want to use the Content Query Web Part and you do want to make this work, there are a couple approaches you might consider.
Custom Multi Lookup Field
One of the first things that could come to your mind might be developing a custom Lookup field which would allow selecting multiple values. Because the LookupMulti field type isn’t supported by the CQWP you could create a totally custom field type deriving from the Text or Note field type. The value of a Lookup field with multiple values is a ;# delimited string, such as id1;#value1;#id2;#value2 which can easily be converted to the SPFieldLookupValueCollection type so that you can work with the stored values in a way comparable to the standard LookupMulti field. You could even go a step further and provide a similar User Interface to the one the LookupMulti field has using the GroupedItemPicker control.
While this is definitely a plausible approach, many people find working with custom field types rather complex. There are quite a few different components you have to consider while creating a custom field type if you want to make it fool-proof. The worst of it all in my opinion is, that a custom field type is available in all Web Applications across the whole farm. In other words: after you deploy your custom field type everyone else is able to use it in their Web Applications as well.
Custom XSLT function
Content Query Web Part uses XSLT for data presentation. The great thing about this combination is that it allows you to extend the standard transform process with your own XSLT parameters and functions.
For the purpose of retrieving the value of a MultiLookup field, you could create a custom function which would take three parameters: ID of the site, ID of the List and ID of the item. Using all these ID’s you would retrieve the item and return the value of the LookupMulti field.
CQWP automatically includes all these ID’s in the results, so you don’t have to do anything extra in order to retrieve them. The downside is however that such function would run in the scope of the item itself meaning: for every single item in the result set you would open a reference to a site, to a list and run a query to retrieve the particular item. And while there are some ways to optimize that process, it’s still quite poor from the scalability and performance point of view.
Depending on your scenario it might or might not be a suitable solution for your situation. The only way to find out about it is to build a proof-of-concept and test it in your solution/environment.
Custom XSLT function++
As I already mentioned the Content Query Web Part includes all different ID’s required to retrieve the SPListItem object for the given result. What the CQWP also does by default, is that it includes all results in the output XML. That gives you yet another approach to retrieve the values of MultiLookup fields.
What if we first gathered all the information about all the items from our result and then use that information to build one query (or a few depending on what kind of results your Content Query Web Part returns). Instead of running one query per item, you could run one query for the whole Web Part, store the results in a variable and retrieve them from the ItemTemplate for every item using the basic XPath selectors.
While this approach is definitely more complex than the previous one, it would definitely prevent you from putting some heavy load on your server. Although I haven’t tried this approach in real-life yet, I’m quite sure it would work and would help you get things done inside the Content Query Web Part without a too big performance penalty.
There is even more to XSLT functions
Using custom XSLT functions has yet one more advantage: you could leverage the same concept as described above to retrieve some related information for every item. Because CAML doesn’t support any JOIN mechanism, using custom XSLT functions might give you a solution for this shortcoming as well.
Summary
Content Query Web Part provided with Microsoft Office SharePoint Server 2007 is a great solution for aggregating content. In spite of its great power it has some serious shortcomings among which the lack of support for Lookup fields with multiple values. Using custom development you can create a solution for this challenge and leverage the power of the Content Query Web Part in your solutions.
Technorati Tags: SharePoint,SharePoint 2007,MOSS 2007