Displaying Multi-Select Column Values in CrossList DVWPs

This is one of those things that I’m amazed I haven’t run into before. Perhaps I did in the past and wrote it off to my own lack of understanding. It turns out that this is a known, if rarely mentioned, limitation not only for Data View Web Parts (DVWPs) but also Content Query Web Parts (CQWPs).

You can easily reproduce this issue as follows:

  • Create a new Custom List (or any type of list you choose)
  • Add a Person or Group column called Project Manager. Do not allow multiple values.
  • Add a new item to the list with whatever values you want
  • Create a page with a DVWP on it with your list as its DataSource
  • Display some columns from the list, including Project Manager
  • Add a filter so that you are sure you’ll only get the item(s) you’ve added to your list
  • Convert the DVWP to DataSourceMode=”CrossList”

At this point, you should see the item(s) in your DVWP just as you would expect.

Now go back into the list settings and change the Project Manager column settings so that it allows multiple values. Next go back into your page with the DVWP and Refresh the data view. You should now see no items at all.

This simple test should prove that the issue is only the multiple value selection which essentially acts as a “filter” which you didn’t ask for. You can probably pretty easily think of reasons you’d want to display data like this. In my little example, you might have multiple Project Managers in Projects lists in sites across the Site Collection. If you wanted to show all of the projects rolled up somehow, you might well want to display the Project Manager(s).

Unfortunately, in my testing, this works exactly the same in both SharePoint 2007 and 2010.

Once I realized this was what was going on, I turned to the Interwebs and found some old posts from Waldek Mastykarz and others that mentioned the limitation. I could only find a few posts, but when the people who’ve done the posts are as smart as Waldek, I take their word for it – it’s not me this time, it’s a SharePoint limitation.

This is truly one of those “features” which feel an awfully lot like a “bug”.

I did find one trick to at least allow the items to be displayed, even though the multi-select column values will not be displayed. If we add Nullable=”TRUE” to the ViewFields settings in the CAML in the SelectCommand, then we do get the items to display, albeit with blank values for the multi-select columns.

This ends up looking something like this. Note that I have added the Nullable attribute to the Project Manager FieldRef.

<ViewFields><FieldRef Name="Title"/><FieldRef Name="Project_x0020_Manager" Nullable="TRUE"/><FieldRef Name="ID"/><FieldRef Name="PermMask"/></ViewFields>

Now I can see all of the items, but the Project Manager is simply blank. A step forward, but not far enough.

image

Time for a kludgy fix, don’t you think? Well, I think I’ve got one for you. We can use script and my SPServices jQuery library to “fill in” the values after the page loads. Since we can display the items, but not the values for the multi-select column, we can use the Lists Web Service and specifically GetListItems, to go and grab the items, parse out the multi-select column values, and place them into the DOM where they belong.

This isn’t the type of thing that I like to use jQuery for, really, as it really feels like a kludge. On the other hand, if it plugs a hole in SharePoint’s functionality, maybe that’s not so bad?

To make this work, you’ll want to create good “hooks” in the markup you render for the items in your DVWP or CQWP. I always try to do this, anyway, if I’m going to use script on the page so that my selectors can be very “tight” and efficient.

In the DVWP, I simply add three new attributes for the table detail cell (TD):

  • id – This is a unique id for the TD element, which I create by concatenating the string “ProjectManager_” and the current item’s ID. I’ll use these ids to find the empty cells in the DOM.
  • ListId – This is the GUID for the list which contains the item. The @ListId “column” contains this value. (This “column” only exists after you switch to CrossList.)
  • ItemId – This is the ID for the item itself. We could parse it out from the id above, but it’s easier to store it as its own attribute.

You may not realize that you can create any attributes that you want for HTML elements. They aren’t standards compliant, of course, but by adding your own attributes you can store any values you might need.

<td class="ms-vb" id="ProjectManager_{@ID}" ListId="{@ListId}" ItemId="{@ID}">
  <xsl:value-of select="@Project_x0020_Manager" disable-output-escaping="yes"/>
</td>

Now that I have markup which makes it pretty easy to both select the right DOM elements as well as the data I need to make the Web Services call, I can use this script:

$(document).ready(function() {

  var projectManager;

  // For each empty Project Manager column value...
  $("td[id^='ProjectManager']").each(function() {

    // ...call GetListItems to get that item
    $().SPServices({
      operation: "GetListItems",
      listName: $(this).attr("ListId"),
      CAMLQuery: "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>" + $(this).attr("ItemId") + "</Value></Eq></Where></Query>",
      async: false,
      completefunc: function(xData, Status) {

        // Parse out the Project Manager value
        projectManager = $(xData.responseXML).find("[nodeName='z:row']").attr("ows_Project_x0020_Manager");
      }
    });

    // Create the links and the column value into the DOM
    $(this).html(userLinks(projectManager));

  });

});

function userLinks(columnValue) {

  var userArray = columnValue.split(";#");
  var numUsers = userArray.length / 2;
  var out="";
  for(var i=0; i < numUsers; i++) {
    out += "<a href='/_layouts/userdisp.aspx?ID=" + userArray[i*2] + "'>" + userArray[(i*2)+1] + "</a>";
    out += i < numUsers ? "<br/>" : "";
  }
  return out;
}

Using this simple bit of script fills in the values for the Project Manager by getting the right items and plugging the values into the DOM, like so.

image

I decided to simply show the names as links to the userdisp.aspx page, with each one on a new line. This link will show either the information for that user in the User Information List or their My Site profile, depending on how the environment is configured.

Depending on what your data looks like (how many items you are displaying, how many multi-select columns you have, etc.), there are obviously some inefficiencies in my example, because I’m calling GetListItems once per item. You could also batch your calls together per list to get all of the items from that list, or whatever made sense in your situation.

Finally, if using script like this gives you a bad feeling, then you could try using a third party Web Part like the Bamboo List Rollup Web Part or just develop your own custom Web Part. But it seems that if you’ve gotten this far, you’re probably trying to stick to the Middle Tier, so the script approach might make sense.

References:

Similar Posts

17 Comments

  1. The Nullable=”TRUE” in the SelectCommand seems to be what I’d want but when I add it to my SelectCommand, it’s still returning nothing instead of something.

    …FieldRef Name="Tags" Nullable="TRUE"/><…

    1. Kathy:

      If you read the full post, you’ll see that setting Nullable=”TRUE” only solves part of the problem. The DVWP will not return values for multi-select columns. That’s what the kludgy script helps to solve.

      M.

  2. Marc

    I am getting stuck on

    listName: $(this).attr(“ListId”),
    CAMLQuery: “” + $(this).attr(“ItemId”) + “”,
    async: false,
    debug: true,
    completefunc: function(xData, Status) {

    // Parse out the Assigned to value
    assignedTo = $(xData.responseXML).find(“[nodeName=’z:row’]”).attr(“AssignedTo”);

    It does not appear to be returning anything

    I know my CQWP list items hae valid properties
    id=”AssignedTo_8″ ListId=”18EE0763-9168-477A-8826-844660E6C871″ ItemId=”8″>danThemanxx

  3. Marc

    That worked.. . I am seeing more than one Assigned to person ( for the first time today!) …

    I notice in my my case the empty AssignedTos are handled inconsequently: some have “undefined” some left blank …. but I will look at that tomorrow morning.

    thanks again …

    Daniel

  4. Hi Marc

    My all sub site tasks (CQWP) dashboard has worked well up to now. Unfortunately, the client has reported script errors due the sheer number of rows being processed. by the above jQuery.

    I guess I have a number of options going forward.

    1) force the CQWP to only return rows for a given subsite – say user selectable from a drop down list., then trigger your JQuery code.
    2) somehow tune the existing approach to run much faster with or without updating to the latest versions of SPServices and JQuery.
    3) any other suggestions

    I would welcome your thoughts.

    Daniel

    1. Daniel:

      This method is truly a stop-gap, but it works. If you’re putting a large number of items on the page, maybe you should consider some sort of paging to reduce the number of Web Services calls. you could also fiddle with making things asynchronous so that the pagfe isn’t hung up.

      What type of errors are the user’s seeing? Even if there is a lot of data, you shouldn’t be getting script errors unless it’s the “long running script” sort.

      M.

  5. Marc

    Thanks for getting back to me. Yes, I did warn my client that this dashboard will get slower and slower as more content is added to the sub sites.
    Yep, it is the ….”xwy.com not responding due to long running script” I am using a CQWP so I am not sure how easy paging is to implement. Neither, if memory serves is it possible to make this a connected web part.

    Pan B: I wonder if if I could follow your SPDataSource fix – cross site DVWP and implement paging there. However, I think CQWP is much more elegant.

    Plan C: would be building a cross content rollup visual Web Part – not my fave choice as I am a bit Visual Studioed out!

    In the interim I have set the SPservices GetListItems call to async and upgraded to jQuery 1.6.4. In which case the page renders sometimes without any error – however the page is now too slow to be viable.

    Daniel

  6. Marc

    Any thoughts on my options before embark on a re-engineering my CQWP ;-(. BTW I do think your approach is viable given this so obvious failing in SharePoint – Probably ensure “Group By” always works in rolled up content.

    Daniel

    1. Daniel:

      Another thought would be to run the script for each item only “on demand”. Put a button on each item that, when clicked, fetches the info for that item. It would speed up the whole page and in most cases, they may not want to see the values for every item, anyway. In any case, it may make sense to rethink the requirements.

      M.

      1. Marc

        Yep, I like that as I can pretty much keep my CQWP intact, I get to play with jQuery and I don’t need to code up a feature receiver in VS2010 ( my other thought). Ironically, very few of the sub sites tasks have assigned users to your approach makes a lot of sense.

        I will let you know how I get on .

        Daniel

  7. Just in case anyone else tuns into this issue-

    If you are using jQuery 1.7+, the
    find(“[nodeName=’z:row’]”)
    syntax is no longer valid you have to use something like:
    SPFilterNode(“z:row”)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.