The "Right" Way to Join Two Lists In a DVWP
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[@Title=$dvt_ParentRow/@Title]" />
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[@Title=$Title]" />
- 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”.
Marc,
Is it possible for you to provide a full tutorial on this? I am facing hard time with formatting the values. I want to repeat the parent table values for each child value in same line.
Thanks,
Tahir
Tahir:
Did you watch Laura’s video? I think that’s an excellent starting point if you haven’t. If that isn’t helping, why don’t you post back with specific questions?
M.
Hi Marc,
Thank you for the post! I was struggling on how to filter the joined recordset based upon a query string. I failed to make it work via the DVWP interface. After reading your post, I manually applied the filter at the parent row data source and it worked!!! Just share it with the others trying to pull their hair out for solutions.
So glad you found it useful!
M.
Hi Mark
Very interesting article.
I’m an old rusty C++ programmer that have just startedworking with Sharepoint and DVWPs.
I watched the video, but was already a little bit past the state of what it acomplishes when I came across it. Wish I had seen it about a week ago. As this point for what she is demonstrating I think she should have used “Insert Selected Fields As”->”Formated”->”Joined Item” in stead of inserting a sub view from which she moved on to delete the headder colomn and it was kind of always implied that there would only ever be one item per sub view anyway. The “Joind Item” approach require fewer steps and far less (cleaner) code generated.
Anyway I am currently struggeling; -not to get my resulting table to display what I want, but rather filters to work on colums that have “Joined Item” fields. I am working with fairly with large lists (My product list has about 7500 items) and without certain filters my “Joined” view’s are completely useless. Am also getting very poor performance, so will defenetely need to follow your advise some WHERE’s.
My (test) “Database” is currently split into 8 sharepoint lists. At the moment I’m just trying to “join” 3 of them:
AvProdLoc
<![CDATA[
]]>
The main “query” am playing around with is on the “Availability” list and the filters work fine for the 5 fields I use from that list.
As an example this is what’s in the “Table body” for a field that works fine.
Another column simply has:
which is obviously from another list, but “joined” in as a linked item, not a sub view, so it’s a nice and neat field in a consistant column of the table!
Works great in the table body. Not so well in filters, but Sharepoint Designer does actually try to put them in when you tell it to, and it doesn’t crash (Which in my expierence it will if you try after putting a sub view in, and I can surely see why). Anyway the resulting code looks like this for a column that works:
@ProductID
Product
Product
1
x:string
The column that doesn’t work looks like this:
../../../Products/Rows/Row[@Title=current()/@ProductID]/@market_x0020_price
market price
market price
1
x:string
Which has the culprit of:
../../../Products/Rows/Row[@Title=current()/@ProductID]/@market_x0020_price
Which from what I can observe completely breaks the logic of dvt.headerfield template for starters. The result is that the filter appears on the page, but it doesn’t populate when you invoke it. Even if I got around that and got it to populate I can’t see how it could possibly work after selecting somthing since the list in questin isn’t even part of the initial query (But is rather selected at a later point), so the entire logic of even trying this falls apart as far as I can see.
It really would help if I could properly join my tables in the original query. I have played around with the IsSynchronous, SeparateRoot and HasSeparateRoot properties of the SharePoint:AggregateDataSource. See some interesting results, but haven’t quite managed to get it to do what I need, possibly because I haven’t got the necessary select commands in place. Not exactly sure. Am possibly barking up the wrong tree (Again) here???
If you know of any articles or hints that might help I would greatly appriciate a pointer in the right direction.
Sorry abot the lengt of this post, which apart form the “Joint Item” hint I guess mainly contain what NOT to try.
Kind regards,
Bent
Actually reading the above is garbled. Most of what I copy pasted into that comment didn’t make it across to your site, making it essentially nonsense to read.
Sorry about that
Bent:
If you want to send me some code to look at, use the Contact link above. Code doesn’t make it into comments in most cases.
Thanks,
M.
Marc,
Do you have any advice or information for how to perform a many to many join on two Sharepoint Lists?
Here is a description of the situation, I am using exercise equipment and muscles as a metaphor for my actual data but the same relationships apply.
In one table I have exercise equipment, each piece of equipment works one or more muscles. For each record in the equipment table, there are other fields of information about it like price, weight, etc. and one field is an Equip_ID which is a unique number as the primary key for the record.
There is a second table with a record for each muscle in the body, with information about the muscle and as with the equipment table there is a field for a primary key Muscle_ID.
Currently in the Equipment Table, there is a field that contains a comma separated list of Muscle_IDs that the equipment is designed to exercise. In the Muscle Table there is a field in each record that contains a similar list of Equip_IDs that can be used to exercise that particular muscle. These IDs will be updated over time as new equipment is added to the Equipment list.
What I am trying to do is to create a view of the data that can show in either direction all of the associated records for that particular ID. So as an example, I want to list for a particular equipment ID, information from the muscle fields for all of the muscle records that can be exercised by that piece of equipment. Alternatively, for a particular muscle, I want to list information from the equipment table for every piece of equipment that can be used to exercise the selected muscle ID.
How would you approach this kind of many to many relationship with two Sharepoint lists?
Thank you,
-Bryan
Bryan:
Well, first off, you shouldn’t think of the SharePoint lists any differently than you would database tables. If you don’t have a good relational structure, then you’ll never be able to do what you want!
If I understand your description right (and exercising is not something with which I’m very familiar), you probably need a third list which contains the relationships between the muscles and the equipment.
Also remember that you can specify compound selections on the rowset, for instance:
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[ (@Column1 = $Value1 and @Column2 = $Value2) or string-length(@Column1) = 0 ]"/>My suggestion is that you try to dummy it up in Excel or Access quickly so that you know you have the right constructs, and *then* build the SharePoint lists.
M.
I did create a join table list, but I could not see a clear way to filter one list through a join table (SP List).
In your example code, what table would this select be made on? Is this the join table to return the row that contain a unique pair ($Value1 and $Value2)? Where Column1 would be the FK of Table A and Column2 the FK of Table B?
I am trying to do something like, return all of the fields from Table B where a particular ID (PK) of Table A is paired with the ID (PK) of Table B on a third join table for A_ID, B_ID pairs. I will then do the same thing in reverse through the join table for fields from Table A for a particular ID of Table B.
If that is what you showed above, I apologize for seeing it yet.
-Bryan
Bryan:
What you describe is certainly possible, but as I said, it’ll take some XSL work. It sounds like you’re on the right track. If you watch the video that Laura did, you’ll see how to get the XSL into the DVWP to “join” the two lists. Then if you look at the resulting XSL, you’ll see what templates are created and how they fit together.
I wish I could give you a clearer answers, but this one sounds like it’ll be a bit of development, albeit with XSL (some would dare to call it a no-code solution).
M.
Ok, thank you, I will see what I can come up with.
I did find a product called SLAM that does this sort of thing.
http://www.codeplex.com/SLAM
But, I would have to install it on the server and I do not have access to that for my site. Maybe I can talk to the admins, but there are implications for security when we stray away from an OTB solution.
-Bryan
Bryan:
SLAM looks interesting, but it’s probably overkill for what you’re trying to do. Based on what you’ve described, I think you’ll be able to get it done with a DVWP.
M.
Informative article as usual Marc. My questions is should I expect that if the user doesn’t have permissions to one of the lists that DVWP will not be displayed for that user? This seems to be the case for me, regardless if they have access to just the first or second list merged by the DVWP. Is there any way around this without having to creating a separate column and then filter on that? I was hoping that the DVWP would just filter the files that the user does not have permission.
Thanks much.
Thanks, Katie.
Usually the DVWP will be displayed, but if the user doesn’t have permission for the items, they will either see the “no items” message (if you’ve customized it) or just a blank DVWP. The basic point is that, yes, the DVWP will honor permissions in what it displays to the user.
It sounds like you are “joining” two lists, to which the user may not have permission? You’ll need to build your DVWP to handle that eventuality.
M.
Thanks for the response Marc. Just to clarify – if the user doesn’t have permission for one of the two libraries being joint nothing will be shown. Could you give me a tip, if that is the case, on how to show the items for which that they do have permissions?
Cheers,
Katie
(for some reason I couldn’t submit the reply when I replied to the note above, the submit button did not even appear.)
The answer isn’t that they won’t see the items they have permissions for. It depends on what you’re doing in your DVWP. If you are showing a joined subview or something then you need to handle things in the XSL. If you’re just showing all of the items from both lists, then they should see what they have permission to.
Have you actually built this yet are are you just noodling on it?
M.
I have built it and I get a message where the DVWP is on the page, see below, if the user doesn’t have permissions for both of the merged lists (showing all items of both lists with my DVWP). Any thoughts why that could be?
message: 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.
Cheers.
K.
You’re only getting the error if the user doesn’t have permissions to both lists? Or are you getting it all the time? That’s the [rather unuseful] generic error message you get with DVWPs if something isn’t right. I’ll email you separately so that you can send me your code if you’d like me to take a look. (Just tried to email you, but the email you used here doesn’t seem to be valid.)
M.
Katie:
Thanks for sending over your DVWP code. I think that the issue lies in the way that SharePoint Designer has written the XSL to combine the two lists. Since you’re sorting by @TechnologyRAE and @FileCatagoryRAE”, it looks like the items are being merged in the XSL in such a way as to cause the error. One thought would be to start afresh with a new DVWP which just displays the items from the two lists with no other changes to the sorting, ListIDs, etc. See how that works, and if it does, try applying the other changes one step at a time. The order may matter. Alternatively, I think you could pare down the XSL quite a bit, as this is one of those circumstances where SPD is leaving “stubs” for possible other changes.
M.
Hi Marc,
Thanks for this very informative article.
I’m currently facing the following problem and I was hoping you would be able to shed some light.
ListA – displaying Title
ListB – creating an Average calculation
The “join” is based off of ListA.ID and ListB.AID.
I’m able to create the view of Title and Avg, but what I want to do is be able to sort the view by the Avg. When attempting to use SPD sort functionality it only shows me the Title as an option. I was hoping you would have some XSL magic that I could use to make it happen.
marc
Marc:
Check out this post. It should help.
M.
Hi Marc,
Thank you for your article. In my situation I have:
List1
List2 with a List1 Lookup column named List1Title
As Lookup columns in SharePoint actually store record IDs of the parent list, I was hoping to be able to build my joins like @List1Title = $dvt_ParentRow/@ID, but these are not returning any records and I’m not sure why. I am able to join the lists based on the Lookup’s displayed value (Title) with @List1Title = $dvt_ParentRow/@Title, but the resulting join is of no use because, in this case, duplicate Titles are permitted in List1, so the join results combine unrelated records from List2 under common Title values.
Would you happen to know how to specify the actual numeric value of a Lookup column in a join?
Thanks again,
Mark
Mark:
Generally the item ID is stored in front of the value for a Lookup column, like this: 1;#textvalue. You can substring the ID out with substring-before(@List1Title, ‘;#’).
On a separate note, if you have duplicate text values in the lookup list, your users are bound to select the “wrong” one regularly, creating a GIGO problem. I would reconsider that architecture decision.
M.
Thanks Marc for your quick and helpful reply.
Though I have not yet been able to make your substring-before suggestion work using:
I have taken your suggestion to heart about eliminating/preventing duplicate Title values.
I guess my next question is if you have any suggestions on how to enforce uniqueness in SharePoint 2007 columns? For after cleaning the existing data in the list, I will need a systematic way to prevent new duplicates from being entered.
Thanks again,
Mark
Mark:
Remote debugging is always fun. ;+) If you eliminate the dupicates, you can find the ID by looking up the value.
I have a function called SPRequireUnique in my SPServices library which can help you enforce uniqueness in your lookup list.
M.