Display information from multiple data sources is an almost obligatory to make a SharePoint application. In this article we'll create a view with this technique using SharePoint Designer 2007.
Sample
Suppose we have two separate SharePoint lists, the first is called NewsCategories and contains two columns: Title (default column list) and Category Picture (type column Picture). The second list is called News and contains two columns: Title (default column list) and News Category (column of type Lookup to the column Title from the NewsCategories list).
In our view, we want to display the title of the news and image of the corresponding category.
Solution
In SharePoint Designer 2007, we are creating a Linked sources by clicking on the link Create a new Linked source... under the section Linked sources of task pane Data Source Library.
The Data Source Properties window opens. Click Configure Linked Source....
In the Link Data Sources Wizard that opens, select the desired two lists, here News and NewsCategories then click the Next button.
Then select Join the contents of the data sources by using the Data Source Details to insert data views and joined Subviews. and click the Finish button.
In the Data Source Properties window, under the tab Source, you can now see your two lists.
Click the General tab for naming your Linked sources (News-NewsCategories) then click the OK button to close the dialog box.
Our Linked sources is now in the task pane Data Source Library. Clicking on opens the shortcut menu. Select Show Data.
In the task pane Data Source Details, click on the column Title from the News list and then click Insert Selected Fields as... and the option Multiple Item View from the menu.
Data from the News are now displayed on the page. Position the cursor before or after a field Title (here, the cursor is before Orlando ...).
In the task pane Data Source Details, click on the column Category Picture then click the button Insert Selected Fields as... and Joined Subview option from the menu.
In the dialog Join Subview, select the two columns News Category and Title which is carried on the juncture between the two lists, then click the OK button.
Go to the SharePoint Designer mode Code and locate the line displaying the tag below:
Add attribute disable-output-escaping with the value yes. This will display the picture itself and not HTML tags.
Save and view your page in your browser to get the following result (after formatting the XSL):
Final word
Merge or join the data?
When you link data sources, you have two choices: merge or join.
Merge: You merge the data when all data sources have exactly the same set of fields. For example, if you link the tables of products from several different databases, and each table has exactly the same fields as all the other tables. The merge is the SQL union.
Join: You attach the data when two different data sources have a common field. For example, if you link a products table with a table of categories, and tables are linked by a field called CategoryID, then you attach the data. The join is the SQL inner join.
SharePoint does not support cascade delete natively, to maintain data integrity, we must therefore use code-behind to delete child records when deleting their parent.