Using the method described in this post, you will be able to join multiple lists together and create a combined view. For example, assume that you are managing a list for some events. And you are having a separate participants list. You want to join the two lists and create a composite view.
SharePoint 2010 allows you to create this kind of view using a Linked Data Source. In this approach, you can create your own custom list in SharePoint.
How to Create a Linked Data source
Go to SharePoint Designer and go to the link called Data Sources.
Click the Linked Data Source button in the ribbon. SharePoint Designer will show this dialog:
In there, add the two lists that you wan to link together.
I'm adding Airline schedule and Booking list. Those are the two list that I want to merge. Click Next and it will guide you through another screen. It will ask you to select either:
- Merge
- To combine lists which have the same column definition. This is like a Union operation in a database.
- Join
- Join operation is used to link two or more lists that need not have the same column definition. This is like a join operation in a database.
I need to join the two lists to get the passengers for a particular schedule. So I'm using the join option, and click Finish.
Switch to the General tab and give some name to identify the data source.
Now you are done with the linked data source.
How to create a custom view using the linked data source in the SharePoint Designer
In here, I'm going to create a custom ASPX file in the SharePoint Designer and create a view on it. For that, go to SitePages and create a new ASPX file.
In the designer, you can see an empty aspc file as follows:
Now you want to add the data source into the page. There are many ways to do this. The best thing is to go to Insert and then select your data source to add the data source to the page. But in this way, I found that it is not always visible in the data source task pane. You can then go to Data View and add your data source.
This action will generate a table in the page. But we don't need it. Do delete it. We will want the data source in the data source task pane (Select all and press Delete in the keyboard).
Now go to the task data source pane and select the columns you want to add to the page. Click Add as a multiple view item.
This will generate a table for you. Now click the right most column and go to the table and add a new column to the right in the table, as follows:
Now place the mouse cursor on the column and select the second list in the data source pane and select the columns you want, and click Join Sub View.
Then it will ask for the relation between the columns to Join (like the foreign key). In here, link data using the ID of the Airline schedule.
Note:– Some times Join Sub view does not appear. Then click two or more columns and then the view will appear. I don't know why this happens but it happened to me several times. This way, I manage to solve the issue.
Now you can see a composite list view in the page ..
