SharePoint – How to Display Related data joined from 2 Lists in a Data View web part
Posted by: Ravi Keshwani
in Category SharePoint
Abstract: In this article, I will explain you how to display data from 2 lists, based on joins associated with it. In short, we will display master-detail records in SharePoint
Continuing my beginner series on SharePoint, in this article I will explain you how to display data from 2 lists, based on joins associated with it. In short, we will display master-detail records.
There is no coding involved for this task and can be achieved using the inbuilt mechanism provided by SharePoint. Taking a real life example, let us say we have 2 lists - Teams and Projects. ‘Projects’ are assigned to the ‘teams’ which can be one-to-many. We have to display data in a form, to display all the projects assigned team wise.
Follow the below steps to implement this functionality….
Step 1: Create a list named ‘teams’ which stores team related information.
Step 2: Create a list name ‘Projects’ and add a lookup column related to the above created ‘team’ list and insert some sample data as shown below:
Step 3: Open your website in SharePoint designer and create a new page inheriting master page provided by SharePoint via File > New which will display a screen as shown below.
Step 4: Add Content Placeholders as shown below for providing the heading of the page and a PlaceHolderMain ,where our data view webpart will reside as shown below.
<asp:Content id="Content2" runat="server" contentplaceholderid="PlaceHolderPageTitleInTitleArea">
Team Wise Project Assignments
<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">
Step 5: Now click on ‘Dataview Menu and select Manage Data source item from it. We have to add a linked source so click on ‘Create a new Linked source…’ link which is placed below the Linked source drill down item, from the data source library pane.
Step 6: Clicking on ‘Create a new Linked Source…’ will open a window as shown below:
Step 7: Now click on ‘Configure Linked Source…’ button which will pop a window where you can select lists from where you want to grab linked data.
Step 8: Select ‘Projects’ and ‘Teams’ lists and click next. You should now see a window where you have to set how the data is to be grabbed from the selected lists. Select the second option ‘Join the contents…’ as shown below and click Finish.
Step 9: Now in the general tab, give appropriate name to this linked data source as ‘Team-Projects’ and click OK. This will add a new linked data source item in ‘Linked Data Sources’ in Data source library.
Step 10: Put the cursor on next line of ‘PlaceHolderMain’ and insert a data view web part from ‘insert-> Dataview’ menu. You will see that the current data source in the data view webpart will be the linked data source you created recently.
Step 11: Right click on the linked data source and click on ‘show data’. Select ‘Title’ field from the ‘Teams’ data source and click on ‘Insert Selected Fields as’ > Multiple Item View to insert button to insert the names of the Team in data view webpart.
Step 12: Now the data view web part will show all the team names. Place the mouse cursor on any of the row of teams, in the data view web part
And now select ‘Projects’ folder from the data source details pane and select field ‘Title’ from it. After this select ‘Insert Selected field as..’ > ‘Joined Subview’.
Step 13: The next screen will ask for the columns which are to be joined to get related data. Select ‘Title’ field from the left section and ‘Team’ Field from right section as shown below.
Step 14: Click OK and right click on the page from the ‘Folder list’ pane and click ‘Preview in browser’ option which will now show you the linked data of Projects allocated to all the teams.
That’s it! This is how you can show related data (Master-detail) in SharePoint using its inbuilt capabilities.