There is no parent-child relationship support in MOSS 2007 lists by default. But as a developer, we all know it is the most common functionality while storing and displaying the data. Following is the technique that I discovered somewhere on the internet to display the data using DataView Control in SharePoint Designer(while developing reports for my last project), but unfortunatily I lost the original source and thought to reproduce it in my blog with some enhancements.
Implementation:
Following are the steps to perform Inner Join on two lists using DataView Control in SharePoint Designer:
1. We need following two lists(with data in them):
2. Open SharePoint Designer and create a new ASPX page.
3. Create a linked datasource by following steps:
- Select Create a new Linked Source... from Data Source Library tab.
- In Data Source Properties dialog, select Configure Linked Source... button.
- From Link Data Sources Wizard, select Students and Department Lists and hit Next.
- Choose Join the contents... option and select Finish.
- Then select OK to get the New Data Source.
- Expand the New Data Source and select Show Data option.
- Under Data Source Details tab, you should see both lists (Department and student).
4. Insert a Data View control into the ASPX page.
5. Under Data Source Details tab, expand the Students list and drag and drop the Title column to Data View control and it will display all the students.
6. Now place your cursur in Title column and select Table -> Insert -> Column to Right. In this column we will display Department.Title.
7. Place your cursor on the first row of new column, select Title Column from Department list and select the Insert selected field as... button and choose Joined Subview opion.
8. In Join Subview dialog, select Department_x0020_ID = ID and hit OK. (Pic 12)
9. Thats it :o).
10. Play with the HTML to make it look like a report.
Pardon my (extreme) ignorance, but how is this different from creating a regular lookup?
ReplyDeleteRegular lookup is based on the lookup Column which can have multiple entries of same name :o)(unless you specifically apply some rule).
ReplyDeleteAnd we know every list has IDs so use one list's ID in other list to make a child parent relationship and above technique to display the records.
how to do the same in SharePoint 2010. Any idea pl share it
ReplyDeleteI haven't tried in SPD 2010 but this concept/technique should be the same.
ReplyDeleteDo the joined columns need to be "site" columns?
ReplyDeleteNope. It can be list columns too.
ReplyDeleteHow could it do a Data View with 3 list, like:
ReplyDelete* Student (ID, Department_x0020_ID, Title)
* Department (ID, Title, Area_x0020_ID, Title)
* Area (ID, Title)
I need to show the join between them.
Is it possible do that (a two level join based on the ID like step 8)?
The query should be STUDENT > DEPARTMENT > AREA.
I tried but the results I've got were:
Student | Department | Area
A | D1 | A1
B | D1 | A1
C | D2 | A1
When D1 has A1 and D2 has no Area. So the right view should be:
Student | Department | Area
A | D1 | A1
B | D1 | A1
C | D2 |
Thanks.
You can use the same above technique however you have to deal hard with the HTML formatting.
ReplyDeleteIt's done!
ReplyDeleteThe key is to do the same steps but in the internal table. I followed the same steps for link Student-Department, then in the internal table just created, I have to repeat the step 6 and forward for link Deparment-Area. :)
Thank you for verifying it :).
ReplyDeleteCan I join two lists but put the resultant data into one of the 2 lists?
ReplyDeleteI dont think that is possible, I am assuming that two lists will have different schema.
ReplyDeleteHi Taher, Many Thanks For your post which is looking great, but i tried to follow each step on the article but it didn't work fine with Once i working with step 8 "In Join Subview dialog, select Department_x0020_ID = ID and hit OK" ... popup that's appear with no fields..!!?? any help please
ReplyDeleteSomething does not look right with your SPD. Only thing I can think of is, to make sure you have the Service Pack 2 installed for SPD 2007.
DeleteA while back we tried to do something similar in SP2010 but going the other way. The above example is essentially trying to view the parent from the child, which department is a student in. We were looking to do the equivalent of which students are in a department. Never really got it to work. Not the first time, as a former DBA, I've wanted to be able to tell sharepoint to let me treat lists like DB tables and just write a SQL query.
ReplyDeleteSharePoint Lists dont support SQL. However LinQ and CAML Queries in C# could achieve this. But I have written exact same post for SharePoint 2010 version.
DeleteURL: http://mysplist.blogspot.com/2012/07/sharepoint-2010-join-two-lists-child.html
thanks for your help ... helps me a lot!
ReplyDeletehello is it possible to view the data in a datasheet view.?
ReplyDeleteNot really.
Delete