The "Right" Way to Join Two Lists In a DVWP

ByMarc D Anderson

As long as I’ve been working with Data View Web Parts (DVWPs), I’ve pretty much always done my “joins” manually by diving into the XSL.  When I started working with DVWPs, there wasn’t much available to tell me how to do things, so I just made it up based on what worked.

Today I saw this video from Laura Rogers that showed the way you are probably “supposed” to do a “join” in your DVWP.  Take a look.  I may have once followed this route, but it would have been long ago!

A few observations on all of this:

  • When you create a Linked Source, what actually happens is that the information about the sources is saved into an XML file in the _catalogs/fpdatasources Document Library in the current site.  What this XML file contains is information about the SharePoint:AggregateDataSource in a udc:DataSource wrapper.   Each time you use the Linked Source, this information is copied in as the SharePoint:AggregateDataSource for your DVWP.  Think of it as a SharePoint:AggregateDataSource template.  If you can make the CAML more efficient, and you expect to reuse the Linked Source, you can do it here.
  • Upon initial creation of the DVWP from the Linked Source, there are templates in the DVWP named dvt_1, dvt_1.body, and dvt_1.rowview.  (Depending upon the other options you choose from there, you may end up with more dvt_1.* templates.)
  • Note that both Data Sources (in this case; there can be many) have the selectcommand=”<View></View>” by default, so there is no efficiency in the “join”.  I haven’t seen issues with this, even with thousands of rows, but folks often [virtually] look at me askance when I suggest doing the filtering after the items have been retrieved.  (I often reply with “I learned to program in 16k of memory, so this makes me blanch, too, but I just haven’t seen performance problems.)  If you expect that you will have large numbers of items in your lists, think about how you can add WHERE clauses into your CAML to reduce the number of items retrieved initially as I mention above.
  • When you add the first Joined Subview, what happens is that a second set of templates called dvt_2, dvt_2.body, and dvt_2.rowview are created in your DVWP. This set of templates is for the second Data Source (thus the names).
  • The “join” isn’t really a join at all.  The dvt_1.rowview template calls the dvt_2 template and the values of the columns which you specify in the Joined Subview dialog are used to filter the second Data Source, something like this: 
    <xsl:call-template name="dvt_2">       
<xsl:variable name="dvt_ParentRow" select="current()" />         
<xsl:variable name="Rows" select="../../../Assignees/Rows/Row&#91;@Title=$dvt_ParentRow/@Title&#93;" />

You can watch all of these things happen if you follow along with Laura’s video and do the same things that she is doing in Split mode in SharePoint Designer.  At this point, you’re sort of where I end up when I do most of this manually.  However, there are some best practices which I follow which I thought I’d pass along:

  • Rename the templates from dvt_1.* and dvt_2.* to match your list names.  So, given the code above, I’d rename dvt_2 to Assignees, dvt_2.body to Assignees.body, and dvt_2.rowview to Assignees.rowview.  This makes following even your own code much easier, and it certainly ought to make more sense to someone else who gets stuck with it.
  • Rather than using the dvt_ParentRow logic above, call the dvt_2 template (which is called something like Assignees now, right?) with the columns you want to “join” on as parameters, something like this: 
    <xsl:call-template name="dvt_2">       
    <xsl:with-param name="Title" select="@Title"/>        
</xsl:call-template>

    and then the top of your dvt_2 template looks like this:

    <xsl:template name="dvt_2">        
  <xsl:param name="Title""/>        
  <xsl:variable name="Rows" select="../../../Assignees/Rows/Row&#91;@Title=$Title&#93;" />
  • This way, if you need to “join” (notice how I keep putting “join” in quotes) based on multiple columns or a calculation, you’re positioned for it.  Simply pass in the additional parameter(s) and add the logic to the filter in dvt_2.

Once you’re as familiar with DVWPs as I am, you may forego the dialogs, too, but hopefully this gives you some better ideas about what’s going on “under the hood”.

Similar Posts

65 Comments

  1. Hi Marc,

    Is it possible to send values from an OOTB filter webpart and filter the secondary list?

    If so, what is the best way to do this?

    I’ve managed to use your example above to show the data from the 2nd list, but am having trouble getting the syntax right to allow someone to choose an option from a drop down or filter webpart and have the DVWP return only items that match that.

    If I was going to filter on the primary list I would use @Field=$Parameter. Is there a way to extend this to the secondary list? e.g. /dsQueryResponse/List2/Rows/Row/@Field=$Parameter

    Many thanks

    Reply

    1. I usually skip the Filter Web Parts and just build the filtering functionality in a DVWP (or DVWPs). Then I pass the selected value(s) back to the same page on the Query String, pick up those values with the main DVWP and filter the results.

      Now that I’m a jQuery hound as well as a DVWP hound, I often forego the postback and just do the filtering with jQuery.

      M.

      Reply

  2. Brilliant! It never occurred to me to modify the template names to reflect the actual lists that they represent! Definitely walking away with this! You are definitely the “Lord of XSLT.”

    I have a related/unrelated question for you- Have you tinkerd with AJAX/jQuery to pull list data and display with XSLT?

    Reply

  3. Hi Marc,

    I have created a joined list but am not able to include sort functionality for the second list field so am not able to export the joined list . If you send me some code with example it will be greatful.

    Thanks,
    Gomathy

    Reply

  4. Hi Marc,

    Thanks for your article. I am using a joined DVWP on a page layout. The DVWP uses a linked source with three lists. In the end, the first list will exist on sub-site X and the other two lists on the top level. I have changed all ListID to ListName. By using the parameter WebURL set to ‘sitecollectionroot’ * for the third list, the third list does not need to exist on the sub-site, but can exist on the top level. I get an error though when I do the same for the second list and the webpart is not displayed. Any thoughts as to what the problem could be?

    * I add this bit of code after

    Reply

    1. iKT:

      I think what you describe ought to work. Try just specifying “/” (or whatever the sitecollectionroot works out to) for the WebURL and see if that works.

      M.

      Reply

      1. Thanks for the quick response. I tried specifying the actual link for the second list and then since that didn’t work I tried it for all three lists – and any other variation that I could think of doing. Also, with out success. Stranglely using “/” generated an error in SP Designer. Strange.

        Reply

  5. hi marc,

    have you experienced any problem with paging when u use aggregate datasource ? when I create aggregatedataSource navigation links dissapper. I see dvt_nextpagedata parameter is not set correctly, at least at the beginning. do u have any idea ?

    thanks..

    Reply

    1. When I switch to an AggregateDataSource, I generally build my own navigation, as Designer rarely does what anyone’s looking for, especially with paging.

      N.

      Reply

  6. Hi Mark,

    Can a multiple list DVWP be created in a development environment and then moved to production in the same way as a single list DVWP?

    I am not having any luck displaying my multiple DVWP in production; I get the “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator” error and unfortunately designer is disabled on production so there is no way to trouble shoot.

    Reply

      1. Right, I thought so. I did just that following your other post’s instructions. Everything worked well with single list DVWPs but with multiple lists DVWPs I am getting the following error in production:

        “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.”

        Any clue about what might be triggering this?

        Reply

  7. Hi Mark,

    Do you know if date sorting works fine with a linked source in a DVWP?

    I am trying to sort a linked source by the modified date (most recent on top) but the date is treated like a string and the result is …

    4/9/2012
    4/4/2012
    4/3/2012
    4/10/2012

    This only seems to happen for linked source and works fine for single list in a DVWP. Did you ever come this with linked source in DVWP?

    Thanks.

    Reply

    1. Nicolas:

      To reliably sort by date, you’ll usually want to convert to YYYYMMDD format. Here’s an example:

      <xsl:sort select="ddwrt:FormatDate(string(@Modified), 1033, 'yyyyMMdd')">

      M.

      Reply

      1. Thanks Marc. I meant sorting using the column headers. The xsl template dvt.headerfield is called and sorting works fine on all columns except date column.

        Reply

        1. Nicolas:

          Yes, you’ll need to do some hocus pocus there, too. I know I solved this once. As I remember, there is some conditional logic for the sorting that you need to expand upon so that it works properly for dates.

          M.

          Reply
  8. Pingback: Display item counts in a Data View Web Part « Blah-de-blah-blog

  9. Hello Marc,
    Thank you for your post. I’m trying to also get the sum of one of the columns which I have added to the Dataview via the Join. Each time I try to refer to the column , I get nothing. Any suggestions?

    Reply

  10. Hi Marc,

    Is it possible to combine data from two lists, when the second one is in a subsite of first?

    I’m having 2 lists and I was looking to create a combined view in a DVWP on subsite with fields from first list which is in the root site collection and second on a subsite where I have my DVWP.

    Reply

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.