Join two Sharepoint Lists together by ID

By | December 17, 2013

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

01 Classrooms

While the student list will look like this

02 Students

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


03 Linked Form

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“.

04 Current Code

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”

05 Link to Another Source

It will warn you regarding inserting data, just click OK we just need a view anyways.

06 Message

Now choose and add the students list


07 Link Student

Then select “Join

08 Join Lists

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

10 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).

11 Remove Attachments

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

13 Your Code here

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.

12 Select Whats needed

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).

14 Linking together

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,

15 Autogenerated Codes

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.

16 Joined View


9 thoughts on “Join two Sharepoint Lists together by ID

  1. Al Karla Saclayan

    Hi Raymund, how do you display the attachment from List #1? Thanks

    Reply
  2. Robin

    Great post but I have one question – is it possible to provide an edit on the linked list items?

    Cheers…

    Reply
  3. Shivaprasad Rao

    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.

    Reply
  4. Barb W.

    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?

    Reply
    1. Leif Frederiksen

      I have the same problem – anybody who can shed some light on why this happens?

      Regards
      Leif

      Reply
  5. Ina

    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 ???

    Reply

Leave a Reply