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:

Roll Up Blog Posts in a Site Collection

I got a quick question today from a client asking how to roll up blog posts.  He wanted to display the latest blog posts from all of the blogs which executives have in the Site Collection, which is used as the organization’s Intranet.

He had the idea right, using a Data View Web Part (DVWP) with the DataSourceMode=”Crosslist” and the Webs Scope=”SiteCollection”.  However, he got trapped by a cut and paste gotcha.

Since he wanted to only show the latest blog posts, he needed to set the correct value for ServerTemplate.  The CAML he had copied had <Lists ServerTemplate=’104′></Lists>, which specifies lists which are based on the ‘Announcements list’ template.  The value for the ‘Blog posts’ template is <Lists ServerTemplate=’301′></Lists>.

The possible values for ServerTemplate can be found in this MSDN support article, and here they are listed for ease of use:

  • 100   Generic list (this means any Custom List)
  • 101   Document library
  • 102   Survey
  • 103   Links list
  • 104   Announcements list
  • 105   Contacts list
  • 106   Events list
  • 107   Tasks list
  • 108   Discussion board
  • 109   Picture library
  • 110   Data sources
  • 111   Site template gallery
  • 112   User Information list
  • 113   Web Part gallery
  • 114   List template gallery
  • 115   XML Form library
  • 116   Master pages gallery
  • 117   No-Code Workflows
  • 118   Custom Workflow Process
  • 119   Wiki Page library
  • 120   Custom grid for a list
  • 130   Data Connection library
  • 140   Workflow History
  • 150   Gantt Tasks list
  • 200   Meeting Series list
  • 201   Meeting Agenda list
  • 202   Meeting Attendees list
  • 204   Meeting Decisions list
  • 207   Meeting Objectives list
  • 210   Meeting text box
  • 211   Meeting Things To Bring list
  • 212   Meeting Workspace Pages list
  • 301   Blog Posts list
  • 302   Blog Comments list
  • 303   Blog Categories list
  • 1100   Issue tracking
  • 1200   Administrator tasks list

Displaying Columns in a Crosslist DVWP

I’ve alluded to this in the past, but I had a client situation in the last few days that brought it top of mind again. One of the quirks of a Crosslist DVWP is that you *must* specify all columns you would like to display in the CAML for the Data Source. You can add any column in the XSL you want, but if they aren’t explicitly specified in the CAML, you will get blank results.

Here’s an example. Say you want to gather the latest announcements from across the Site Collection for display on the Home Page of your Intranet. You’re displaying the latest 5 announcements, but you realize that annoucements that are expired are still showing up. So, you add a filter to the row select, as below, and you’re not seeing what you’d expect.

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row&#91;
    (ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') >=
    ddwrt:FormatDateTime(string(@Expires), 1033, 'yyyyMMdd')) or
    string-length(@Expires) = 0
  ]"/>

You’ll need to add an explicit FieldRef for the Expires column in your CAML, as below.  (I’ve added carriage returns and white space into the CAML above for readability, but you can’t have them in your DVWP.)

<SharePointWebControls:SPDataSource
  runat="server"
  DataSourceMode="CrossList"
  SelectCommand="
    <View>
      <Webs Scope='SiteCollection'></Webs>
      <Lists ServerTemplate='104'></Lists>
      <ViewFields>
        <FieldRef Name='ID'/>
        <FieldRef Name='Title'/>
        <FieldRef Name='Body'/>
        <FieldRef Name='FileDirRef'/>
        <FieldRef Name='Created'/>
        <FieldRef Name='Author'/>
        <FieldRef Name='Expires'/>
        <FieldRef Name='PermMask'/>
      </ViewFields>
    </View>"
  UseInternalName="true"
  ID="dataformwebpart5">
</SharePointWebControls:SPDataSource>

Once you add the FieldRef to the CAML, the Expires column values are available for use in your filter.

Keep in mind that you need to be careful what you add into your <Viewfields> section. If you add a FieldRef for a column which doesn’t occur in *all* of the items which you want to retrieve, you will, in effect, be adding a filter to the CAML. Only items which have all of the columns specified in the FieldRefs will be returned.

Rollup Data View Web Parts Revisited

I posted back in February about using the Data View Web Part to do cross list rollups (See Rolling Up Content in SharePoint Using the Data View Web Part (DVWP)).  Since then, I’ve done quite a few of these, and I’ve been meaning for some time to post this update.  Not everything that I believed to be true in that earlier post is right all of the time.

It turns out that the CAML is the key to success with this type of DVWP.  What it amounts to is something like the following in the selectcommand.  (Note that you cannot have any of the white space or comments I show below to describe it if you want it to work!).  This is from a client example, but you should be able to get the gist of it for your situation.

<View>
  // This says to grab from all child webs recursively.
  <Webs Scope='Recursive'></Webs>
  // ServerTemplate 100 is 'General List', i.e., Custom Lists.
  // See the 'Type' attribute on the page at http://msdn.microsoft.com/en-us/library/ms462947.aspx
  // for descriptions of the various values that are available.
  <Lists ServerTemplate='100'></Lists>
  <View>
  <Query>
    <Where>
      <Eq>
        // Only grab items which have ContentType of 'Finding'. This isn't required,
        // but if you do not make it explicit, you will also get any items
        // which have all of the columns below in the <ViewFields>.
        <FieldRef Name='ContentType'/><Value Type='Text'>Finding</Value>
      </Eq>
    </Where>
  </Query>
  // In a CrossList Data Source, the <ViewFields> are what matters
  // when it comes to specifying columns.
  // <DataFields> has no effect whatsoever, and can even be removed.
  <ViewFields>
    <FieldRef Name='ID'/>
    <FieldRef Name='Title'/>
    <FieldRef Name='AuditTitleCT'/>
    <FieldRef Name='AuditorCT'/>
    <FieldRef Name='CompleteDateCT'/>
    <FieldRef Name='RiskCT'/>
    <FieldRef Name='FileDirRef'/>
   </ViewFields>
  </View>
</View>

The DataSources section will end up looking something like below:

<DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="CrossList" UseInternalName="true" selectcommand="<View><Webs Scope=&quot;Recursive&quot;></Webs><Lists ServerTemplate=&quot;100&quot;></Lists><View><Query><Where><Eq><FieldRef Name=&quot;ContentType&quot;/><Value Type=&quot;Text&quot;>Finding</Value></Eq></Where></Query><ViewFields><FieldRef Name=&quot;ID&quot;/><FieldRef Name=&quot;Title&quot;/><FieldRef Name=&quot;AuditTitleCT&quot;/><FieldRef Name=&quot;AuditorCT&quot;/><FieldRef Name=&quot;CompleteDateCT&quot;/><FieldRef Name=&quot;RiskCT&quot;/><FieldRef Name=&quot;FileDirRef&quot;/></ViewFields></View></View>" id="dataformwebpart2"></SharePoint:SPDataSource></DataSources>

One other thing to note: It didn’t make one bit of difference in my testing what the WebURL was set to or even if it was there.  All items in the current Web (Site) and any below it in the Site Collection topology are returned.  You could obviously control this further by either adapting your topology or adding more constraints in your Where clause in the CAML.

Note added 23 September 2008: When you add your columns to the ViewFields section, you may see that the first underscore character is encoded as _x005F_.  In other words, a column named Expiration Date, which would normally have the internal name of @Expiration_x0020_Date is shown as @Expiration_x005F_x0020_Date.  You actually will need to change the column references in your XML to reflect this oddity.