Skip to main content

How to Link Two List and Create a combine view in the SharePoint 2010

In this way you can join multiple list together and can create a combine view. for an example assume that you are managing a list for some events. And also you are having a participants in separate list. Thus you want to join the two list and create a composite view.

SharePoint 2010 allows you to create this kind of view using  Linked Data Source. in this approach you can create your own custom list in the SharePoint.

How to Create a Linked Data source

Go to SharePoint designer and  go to the link called data sources.

image

And Click the Linked Data Source button in the ribbon. Then SharePoint designer will prompt following kind of a dialog.

In there add two list, that you wan to linked together.

imageimageI'm adding airline schedule and booking list. those are the two list that i wan to merge. then click next. And it will guide you another screen. it will ask you to select either

  • Merge
    • Merge use to combine list which are having same columns definition. for and example we can say this is like a Union operation in the database.
  • Join
    • Join operation use to link two or more lists. this do not need to having a same column definition in the lists. this is equal to join operation in the database.

I need to join the two list to get the passengers for particular schedule. thus  I'm using join option and click finish.

image 

Then switch to the general tab and give some name to identify the data source.

image

Now you are done with the linked data source.

How to create a custom view using linked data source in the SharePoint designer

In here I'm going to create a custom aspx in the SharePoint designer and create a view on it. For that go to SitePages and create new aspx file.

image 

In the designer now you can see a empty aspc file as follows.

image

Now you want to add the data source into the page. There are many ways. Best thing is go to Insert and then select you data source to add data source to the page, But in this way i found that it is not always visible in the data source task pane, Thus you can go to Data View and add your data source.

image

This action will generate a table in the page. But we don't need it. therefore delete it . we will want the data source in the data source task pane. (Select all and press delete in the key board.)

image

now Go to the task data source pane and select the columns you want to add to the page. and click add as a multiple view item.

image

This will generate a table for you. now click the right most column and go to table and add a new column to the right in the table as follows.

image Now place the mouse cursor to the column and select the second list in the data source pane and select the columns you want and click Join Sub View.

image

Then it will ask relation between columns to Join (like foreign key ). In here in link data using ID of the AirLine Schedule.

image

Note : – Some times this Join Sub view not coming. then click two or more columns then this view will come. I don't know why it is happening but it happened to me several times. And in this way i manage to solved the issue.

Now you can see a composite list view in the page that you can use for ..

image

Comments

Michelle L. said…
How would you accomplish this if the list was stored in a different site (in the same site collection)?
b said…
in simply U can have a BCS connectivity to the site (U can user sharepoint list web services) and create a List and performe the same operation
Beck said…
Hi
Thanks for this - has helped me tremendously! Only problem I am having is when new data is entered into one of the joined lists, it is not updating in the combined list (even when doing a manual refresh).

What can I do to have this happen?
b said…
Hi BECK,

I did this for many things it is updating , please check the paging it may display first records by misguiding you as not updating ...
Anonymous said…
in the following picture:
http://lh4.ggpht.com/_TufE6r6woBg/TPTG7lfNPeI/AAAAAAAAAdY/_1VSIx1Av2A/s1600-h/image%5B71%5D.png

I get empty box, any help ????
b said…
Hi,

Check the data source. If u could recreate the data source.
Anonymous said…
Very good article,it help me a lot,is there a way i can export that combined view of two list to excel,like the Export to Excel option on the ribbon
b said…
Hi,
I didn't found any out of the box method to export the combined view to excel. If i found i will post it surely.

Thank you
Anonymous said…
hi
I am Abdul Satar Zamanpanjshiri
from Afghanistan I have a question about workflows that how to add One workflow in tow lists.

regards
Anonymous said…
hi

well you please gave me information that how tow add One workflow in Tow lists in SharePoint 2010
b said…
hi Abdul Satar Zamanpanjshiri,

I dint actually get what u want, default work flow can be triggered either in item creation or modification in a one list. but u can access other list information from that workflow also.

What do u mean by access two list from one workflow ?
butterwise said…
Thanks, Melick. This was a great help!
Anonymous said…
Hi,
Thank you so much for your sharing, now I have a question: if I want to make the new table(named Table1) as a new template, and put an "add" button(just as the "add new item" button in one library),if the user clicks the add button, then an overlay can appear which allows users to input some information such as date,etc.(but must have the same fields as the Table1)
Anonymous said…
Hi,
Thank you so much for your sharing, now I have a question: if I want to make the new table(named Table1) as a new template, and put an "add" button(just as the "add new item" button in one library),if the user clicks the add button, then an overlay can appear which allows users to input some information such as date,etc.(but must have the same fields as the Table1)
How can I make this done? Thank you in advance.
FDFS said…
Good article. I'm attempting to merge 20 custom lists all having different lengths. I'm able to to go through the entire procedure, but my final merged list contains only 10 entries. How do I expand this number such that I can view all of the data from the merged list on my aspx page?
FDFS said…
Clarification. Trying to merge the data from just two custom lists initially (20 eventually). One custom list has 12 entries, the second one has 6 entries. I performed the link data sources, then created a new aspx page as specified, then edit the page in Advanced mode, and insert the linked source through the data view button of the insert tab. When I select my columns to be included an insert into multiple fields, only 10 items appear in the window. Is there a way to increase this to include everything?
b said…
HI FDFS,

Actually this works as one to many relationship. For an example if we have a merged list should have some value which can related to the source list.
If we have Customer list with 3 entries and we are going to link it with order list which is having 8 entries(one customer can have many orders); final list will have eight entries.
b said…
@ Anonymous said...

Hi, I actually didn't get your point. Which i get is you can change the field type to make overlay like that. But there is a problem when you are going to insert data in to combined list. because which list are u going to insert since it is merged (there can be one to many relationship as well)
FDFS said…
This still doesn't really help me. Let's take a different approach. Say, I have two custom lists and both have the same values: Company, Location, Data, Value. List 1 has 12 entries and List 2 has 6 entries. The merged list should then contain 18 entries, right? Linking these two lists into a single data source is no problem, however when I attempt to merge this into a singular list by inserting data view of the linked data source, highlight the four columns, then insert items into multiple fields, my final list only has 10 entries (not 18). Also, if I just try opening list 1 that has 12 entries through insert, data view, my final list in a new aspx file contains 10 entries.

If I can send you some screen shots to help me on the matter, let me know.
Fer Martz said…
This post was very useful until I got stuck with a blank join subview dialog. It took me hours to figured it out. The key is to put your cursor on the second row of your new column, that way you will get that pop up dialog with columns to match.
Hope this helps...

Fernando
Xopher said…
Fer Martz, thank you I was stuck as well. Melick, cool post! you may want to incorporate the cursor in the second row comment from fers.
Cheers!
Xopher
b said…
@xophr,

Yeh,true , big thanks to Fer Martz.

Thanks xophr!
Unknown said…
I have multiple lists that I wanted to combine into one list for a few people to view. Is there a way to do this while keeping the attached files and links live?
Unknown said…
I am trying to make a page for legal to store their contracts and they want seperate permissions for each department so we were trying to make a contract list (with metadata) for each department but have a view for legal that shows everything. I thought this might have been it but it wasn't. Is there an easy way to do something like that?
Anonymous said…
Good Article.
b said…
Thanks ! Anonymous
Anonymous said…
Hello Melick
Is this feature available in Sharepoint 3.0 Unfortunately that the only version available with me.
Regards
Prakash
Unknown said…
How to Link Two List in different sites in SharePoint 2010


leave request
emp : fill the form in em login

manager : see the form in his login and approve /reject

displyed in emp request site


please help me

Vee said…
Hey Melick's,
Any suggestions on how to do the exact same thing in Designer 2013?
Anonymous said…
This blog worked perfectly for me to set up an aspx page pulling data from 4 SharePoint Lists.

However I am finging an issue with trying to filter and sort the datasource. I have edited the properties of my datasource adding filter and sort criteria but the page is still returning all of the data. I'm using SharePoint 2010 designer. Any ideas?
b said…
Hi you can click the data source and filter using SPD
Unknown said…
This comment has been removed by the author.

Popular posts from this blog

Motion Eye Docker compose File

Docker compose files are comes in handy when considering container orchestration. Below example shows my docker compose files and folder structure. ---- Your Folder (motioneye)   -- etc   -- lib   -- docker-compose.yaml You can run the docker compose file using docker-compose -d , and etc and lib folder will be automatically populated in the initiation. --- Below shows the content of the docker-compose.yaml file. version: '3' services:   nodered:    image: "ccrisan/motioneye:master-amd64"    container_name: motioneye    restart: always    user: root    ports:      - 8765:8765    volumes:      - "/etc/localtime:/etc/localtime:ro"      - "./etc:/etc/motioneye"      - "./lib:/var/lib/motioneye"

How to get Username , UserID in CAML Query

If you are want to get the userID you can simple use following code. <Where>    <Eq>       <FieldRef Name='userfieldname' />       <Value Type='Integer'>            <UserID Type='Integer' />       </Value>    </Eq> </Where> in here you should declare the UserID variable before it uses. normally If you make a user filter in the SharePoint designer it will automatically create the parameter in parameter binding section in the web part. <ParameterBinding Name="UserID" Location="CAMLVariable" DefaultValue="CurrentUserName"/> But assume you want to filter using a user name and the field is not a persongroup feild and it is just a text field. then you can use following query to archive it. <Where>    <Eq>       <FieldRef Name='userfieldname' />       <Value Type=’Text’>            <UserID Type=’Text’/>       </Value>    </Eq> </Wh