Join two Sharepoint Lists together by ID

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.