SharePoint 2010: How to Link Two Lists and Create a combined view
In this way you can join multiple lists together and can create a combined view. For example assume that you are managing a list for some events. And also you are having participants in separate list. Thus 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.
http://lh5.ggpht.com/_TufE6r6woBg/TPTGYPBEHqI/AAAAAAAAAb8/DVAU9VUFsd8/image_thumb%5B5%5D.png?imgmax=800
Click the **Linked Data Source button **in the ribbon. SharePoint Designer will prompt following kind of a dialog.
In there add two lists that you want to link together.
http://lh5.ggpht.com/_TufE6r6woBg/TPTGaV_X3nI/AAAAAAAAAcE/ik5UUg0FjmU/image_thumb%5B7%5D.png?imgmax=800 http://lh5.ggpht.com/_TufE6r6woBg/TPTGcQ_3MNI/AAAAAAAAAcM/Q1N03WF15WI/image_thumb%5B9%5D.png?imgmax=800
This example adds an airline schedule and booking list. Those are the two lists to be merged. Click Next.
It will guide you another screen and it will ask you to select either:
- Merge: Merge to combine lists which have same columns definition. For example, we can say this is like a Union operation in the database.
- Join: Join operation used to link two or more lists. This does not need to have same column definition in the lists. This is equal to join operation in the database.
We want to join the two lists to get the passengers for a particular schedule. Thus we will use the join option and click Finish.
http://lh6.ggpht.com/_TufE6r6woBg/TPTGep_bEBI/AAAAAAAAAcU/ibBjkBNRa3M/image_thumb%5B11%5D.png?imgmax=800
Switch to the General tab and give a name to identify the data source.
http://lh5.ggpht.com/_TufE6r6woBg/TPTGhFykfsI/AAAAAAAAAcc/sYsVVBb8U3M/image_thumb%5B14%5D.png?imgmax=800
Now you are done with the linked data source.
How to create a custom view using linked data source in SharePoint Designer
Create a custom .aspx in the SharePoint Designer and create a view on it. For that go to SitePages and create new .aspx file.
http://lh3.ggpht.com/_TufE6r6woBg/TPTGkNlKr3I/AAAAAAAAAck/aY51oOXePw4/image_thumb%5B20%5D.png?imgmax=800
In the designer now you can see a empty .aspx file as follows.
http://lh3.ggpht.com/_TufE6r6woBg/TPTGnAz-vgI/AAAAAAAAAcs/5PTWnkD8FU4/image_thumb%5B26%5D.png?imgmax=800
Now you want to add the data source into the page. There are many ways. Best thing is to Insert and then select your data source to add data source to the page, But this way it is not always visible in the data source task pane. Thus you can go to Data View and add your data source.
http://lh5.ggpht.com/_TufE6r6woBg/TPTGpgV2B2I/AAAAAAAAAc0/hc-ejDPVpgM/image_thumb%5B32%5D.png?imgmax=800
This action will generate a table in the page. But we don't need it so delete it (select all and press delete in the keyboard). We will want the data source in the data source task pane.
http://lh3.ggpht.com/_TufE6r6woBg/TPTGvYXtTJI/AAAAAAAAAc8/_nJ9J0-h634/image_thumb%5B39%5D.png?imgmax=800
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.
http://lh6.ggpht.com/_TufE6r6woBg/TPTGxPNRFyI/AAAAAAAAAdE/urKl76RfmRI/image_thumb%5B45%5D.png?imgmax=800
This will generate a table for you. Click the right most column, go to table, and add a new column to the right in the table as follows:
http://lh6.ggpht.com/_TufE6r6woBg/TPTG1IA9ReI/AAAAAAAAAdM/3ifp1hYeH6w/image_thumb%5B59%5D.png?imgmax=800
Place the mouse cursor on the column and select the second list in the data source pane. Select the columns you want and click Join Subview.
http://lh5.ggpht.com/_TufE6r6woBg/TPTG3dZXGuI/AAAAAAAAAdU/KOt1vsTKG8k/image_thumb%5B65%5D.png?imgmax=800
It will ask relation between columns to join (like foreign key ). In here link data uses ID of the airline schedule.
http://lh6.ggpht.com/_TufE6r6woBg/TPTG8xZC72I/AAAAAAAAAdc/nC65tk5CwEs/image_thumb%5B67%5D.png?imgmax=800
Note: Some times this Join Subview does not appear. Click two or more columns then this view will appear. We don't know why it is happening but it happened several times. This way solved the issue.
Now you can see a composite list view in the page.
http://lh6.ggpht.com/_TufE6r6woBg/TPTHA85GZWI/AAAAAAAAAdk/fedren0sqIA/image_thumb%5B74%5D.png?imgmax=800
