The example below will demonstrate how to join two Sharepoint Lists similar to how Database Tables are joined using Foreign Key relationship between two objects. It will also show how you display this joined List items in a custom display form in Sharepoint
Lets say we have 2 lists named Classroom and Students. The classroom list contains all classrooms in a school with their respective names and teacher while student will contain student information including which classroom will they be placed, So in this case the foreign key relationship is in the classroom id.
The structure of classroom list will look like this
While the student list will look like this
Now lets start creating our custom form and fire Sharepoint Designer 2013, go to your list and create a new Display Form, in this case we name it Display Form Linked
Once created edit it in advanced mode and you should see something like this auto-generated for you. On the right hand side is the data source details if its not showing you can enable by selecting any of the table row on the code then the tabs on top should show “Data View Tools“, from there choose options then “Data Source Details“.
Since you have the Classroom Details all you need are the students on that classroom, so you now need to link it by using the related data sources then “Link to another data source”
It will warn you regarding inserting data, just click OK we just need a view anyways.
Now choose and add the students list
Then select “Join”
Once finished your data source details will have extra information shown, it is not yet linked at this point.
When you try to view the form it a this point you will have this error
and if you look at the Sharepoint logs it will tell you this error:
The variable or parameter ‘Pos’ is either not defined or it is out of scope.
I am not sure why this happens but looking at the HTML codes it related to the attachment section (if you are using one).
Removing them will make it work.
Now lets do the linking, first lets place a table where you want your data to show. Look for the closing table tab of the dvt_1 then start your new table from there
Now select all the columns you want to show then insert the selected fields as a “Joined Subview“. If it does not show which happens a lot, just click the refresh icon on top of the Data Source Details.
Now it will ask you where it will be linking in, you select the ID from the classroom (the list on the left side) then select the Classroom Id without the dot (.) (the list on the right side).
Now they are linked, and it will add the codes for you, on the table place holder you created there is an xsl template which you can see on the bottom,
If you want to do some changes feel free to do so and in this example I just made some minor modification to the formats, so it looked like this initially
<xsl:template name="dvt_2.rowview"> <tr> <xsl:if test="position() mod 2 = 1"> <xsl:attribute name="class">ms-alternating</xsl:attribute> </xsl:if> <xsl:if test="$dvt_2_automode = '1'" ddwrt:cf_ignore="1"> <td class="ms-vb" width="1%" nowrap="nowrap"> <span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span> </td> </xsl:if> <td class="ms-vb"> <xsl:value-of select="@Title" /> </td> <td class="ms-vb"> <xsl:value-of select="@Student_x0020_Id" /> </td> <td class="ms-vb"> <xsl:value-of select="ddwrt:FormatDate(string(@Birthday), 1033, 5)" /> </td> <td class="ms-vb"> <xsl:value-of select="@Notes" /> </td> </tr></xsl:template> <xsl:template name="dvt_2.empty">
I changed the date format (for more formats have a look here http://blogs.msdn.com/b/joshuag/archive/2009/03/25/custom-date-formats-in-sharepoint-xsl.aspx) as well as word wrapping the Notes column by setting the disable-output-escaping to Yes.
<xsl:template name="dvt_2.rowview"> <tr> <xsl:if test="position() mod 2 = 1"> <xsl:attribute name="class">ms-alternating</xsl:attribute> </xsl:if> <xsl:if test="$dvt_2_automode = '1'" ddwrt:cf_ignore="1"> <td class="ms-vb" width="1%" nowrap="nowrap"> <span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span> </td> </xsl:if> <td class="ms-vb"> <xsl:value-of select="@Title" /> </td> <td class="ms-vb"> <xsl:value-of select="@Student_x0020_Id" /> </td> <td class="ms-vb"> <xsl:value-of select="ddwrt:FormatDate(string(@Birthday), 2057, 3)" /> </td> <td class="ms-vb"> <xsl:value-of select="@Notes" disable-output-escaping="yes"/> </td> </tr></xsl:template> <xsl:template name="dvt_2.empty">
And after those changes your linked list items should now show like this.
I am glad to stumble upon a Filipino blogger like me. I am a fan of yours, Kuya. More power to you.
Salamat!
Hi Raymund, how do you display the attachment from List #1? Thanks
Great post but I have one question – is it possible to provide an edit on the linked list items?
Cheers…
I am afraid theres no quick way
From the above solution, any idea how to create Allitems view (combined view of both list) so that I can view items together and export to excel.
I want this too, did you get the solution for this?
Thanks!
Thanks for the info. I went to do the insert and am unable to insert the selected fields – items is the only option under the insert field list and it’s greyed out along with the data source name. Any ideas why this would happen?
I have the same problem – anybody who can shed some light on why this happens?
Regards
Leif
Hi Raymund, thanks for this which was recommended to me by MS support :). But indeed like you, I only get the first room displayed and don’t find a way to display the others ???