Friday, December 11, 2009

Join Lists Using SharePoint Designer 2007

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.

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):
  • Department (ID, Title)
  • Student (ID,Department_x0020_ID, Title)

  • 2. Open SharePoint Designer and create a new ASPX page.

    3. Create a linked datasource by following steps:
    1. Select Create a new Linked Source... from Data Source Library tab.

    2. In Data Source Properties dialog, select Configure Linked Source... button.

    3. From Link Data Sources Wizard, select Students and Department Lists and hit Next.

    4. Choose Join the contents... option and select Finish.

    5. Then select OK to get the New Data Source.

    6. Expand the New Data Source and select Show Data option.

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

    Official SharePoint Documentation

    I have recently contributed to the official SharePoint documentation for developement. Check it out here: