Tuesday, November 30, 2010

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

32 comments:

Michelle L. said...

How would you accomplish this if the list was stored in a different site (in the same site collection)?

Melick Rajee 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?

Melick Rajee 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 ????

Melick Rajee 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

Melick Rajee 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

Melick Rajee 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?

Melick Rajee 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.

Melick Rajee 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

Melick Rajee said...

@xophr,

Yeh,true , big thanks to Fer Martz.

Thanks xophr!

Richard Behmer 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?

Richard Behmer 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.

melick baranasooriya 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

athmananda reddy 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?

melick baranasooriya said...

Hi you can click the data source and filter using SPD

xi xiao said...
This comment has been removed by the author.