SharePoint List Data Integration using SharePoint Designer 2010

Microsoft Office SharePoint Designer 2010 is a powerful and wonderful invention from Microsoft Corporation for creating and customizing SharePoint 2010 sites. In this article we will explore how to integrate SharePoint List data into a SharePoint page using Data Integration functionality provided by SharePoint Designer 2010.

What is Office SharePoint Designer 2010?

SharePoint Designer 2010 is built specifically for those users who are creating and customizing SharePoint sites and build workflow enabled applications in Microsoft SharePoint 2010. SharePoint Designer 2010 provides a variety of tools for producing more interactive Web pages that also contain data from various sources like List, Document Libraries, and Picture Library. SharePoint Designer 2010 has a powerful reporting tool for the SharePoint platform. Also, it offers the ability to create workflows  - an important part of the SharePoint Designer 2010 feature set.

As we have powerful Visual Studios, then why use SharePoint Designer 2010? The answer is, SharePoint Designer 2010 is ASP.NET 2.0 compliant and is solely targeted to work with SharePoint technology. SharePoint Designer 2010 is used to build no-code solutions. The following are the cases when SharePoint Designer 2010 is useful:

1. Tracking and reporting scenarios
2. Designing lists and libraries to store data
3. Making forms and views for presenting data

4. Building application logic in the form of workflows
5. Extending the SharePoint security system to define rights and roles for SharePoint Designer 2010.

Integrating SharePoint List Data using SharePoint Designer

The Data access layer of SharePoint Designer is so enriched that you can have data from SharePoint, SQL Server, Simple Object Access Protocol and XML as well. The data source controls available in SharePoint Designer 2010 are developed for the same data sources as used in previous version data access layer, but the key difference is that now they have developer supported model. Means, you can create your own custom data source controls and use them to fetch data and to bind them to the data bound controls such as data view and data form.

SharePoint Designer 2010 has now support for the data views which have read as well as write actions. Generally, Data Views are used for data presentation in read only mode, but now they also support write operations to update the data sources. Also, Data Forms are supported by SharePoint Designer 2010 as single record forms and multiple record forms. Using data forms, we can update the data stored in SharePoint, SQL Server as well as in XML files.

Aggregate data source feature provided by SharePoint Designer 2010, now allows the developers as well as designers to fetch data from to data sources and aggregate them into a single data view. Also, SharePoint Designer 2010 data integration provides new ways for passing parameters to the data view. With traditional parameter passing like, using Web Part connection and Query string parameters, now SharePoint Designer 2010 gives the new UI that allows you to create parameters from the ASP.NET controls placed on the page, cookies, forms, query strings or server variables.

Using the data integration functionalities of SharePoint Designer 2010, designers or developers can create overviews and simple Web forms applications without writing a single line of code.

Data Source Library

Data Source Library is the central collection of all the data sources used by SharePoint Designer 2010. From there the data sources are used and accessed. Following is the list that we can have access using Data Source Library in SharePoint Designer 2010.

• SharePoint Lists: - All the lists which are defined and created in SharePoint site with the option to create new list also.
• SharePoint Libraries: - All the libraries that are created in your SharePoint site
• Database Connections: All the data based connection for the SharePoint Site here.
• XML files: All the XML files that are in SharePoint site.
• Server-side Scripts: All the server-side scripts and Real Simple Syndication (RSS) feeds that are used in SharePoint site.
• XML Web Services: All the Web services that are used in the SharePoint site.
• Business Data Catalog (BDC): All the Business Data Catalog (BDC) views that are created and you will have the option to create new view.
• Linked Sources: All the linked sources that are used in SharePoint site.

When you select "Data Sources" from Site Objects task pane, SharePoint Designer 2010 opens a new tab in the main view and list outs all the Lists and Document Libraries provisioned in your site as Data Sources.

When you select "Data Source", the top Ribbon bar also adjusts it self to provide data sources related operation only. You can see that the Ribbon bar has only one tab "Data Sources" and it groups different types of data soruces like Linked Data Source, Database Connection, SOAP Service Connection, REST Service Connection and XML File Connection. See the below image.

For our example now we will create two new Custom Lists using SharePoint Designer 2010 and add some columns as well to the lists.

Add a SharePoint List or Library

Using SharePoint Designer 2010, you can create a new list or library for your data source. You can also specify columns, sorting, filtering and grouping. By using the following steps you can create a new SharePoint list as a data source. I insist you that first you create a Blank Site or Team Site using SharePoint Central Administration.

1. In SharePoint Designer 2010 open your test site.

2. In SP Designer 2010, from the Site Objects task pane, select "Lists and Libraris". This will open the "Lists and Libraries" tag in the main design view and show you the list of all Lists and Document Libraries provisioned in your site. Also notice that the top Ribbon bar adjusts it self to provide Lists and Libraries related operations. For your reference see the below image.

3. Now to create the Custom List, click "Custom List" button provided in the Ribbon bar.

4. This will open a dialog box to create new list or document library.

5. Provide appropriate name for the list. For our example, I'm providing Departments. Next, click OK to create the list.

6. Ok. We will not create any column to this list as we need only Department name, we can use it's default column named "Title" for our use.

7. Now, create one more Custom List with name "Employees", by repeating steps 3 to 5.

8. Now we will create some columns for this Employees list.

9. To create columns for Employees list, first select it from the list. When you select it, the designer changes the view accordingly to show details of the list. Also, the top Ribbon bar chagnes accordingly with list related operations. See the below image.

10. Click on "Edit Columns" button from the Ribbon bar.

11. This will load all the columns of the list in the Employees tab.

12. Next, click Add New Column button from the Ribbon bar and from its context menu, select "Single Line of Text" option.

13. This will add a new column in the list and allows you to rename the column name. So, rename that column to Full Name.

14. By following steps 12 and 13, create some more columns like City, Zip, Phone no, Email Address.

15. Now, we will add a lookup column to the Employees list which will refere the Title column of the Departments list.

16. So, to do that click on "Add New Column" button and at this time select "Lookup (Information Already on This Site)" option from the menu. This will bring you to the "Column Editor" dialog box.

17. In the Column Editor dialog box, select "Departments" for Lists or document library drop down list and for the field select "Title".

18. Click OK to create the column.

19. Now, rename this column to "Department".

Now, navigate to your site in browser and add some data to the Departments list. See below image for sample.

Next, add some sample data to the Employees list as well. See below image.

Now, switch back to the SharePoint Designer 2007 and refresh the site, by selecting the site URL in Folder List task pane and hit F5, to get changes that we did in last few steps.

Now we will customize the DispForm.aspx page for Departments list to include the employees in that department. For this we will use Data View Web Part.

So, to complete this follow the below given steps:

1. Switch back to SharePonit Designer. There select Departments list to open its details.

2. From the "Forms" group in list details, click on "DispForm.aspx" page.

3. So, this will open the DispForm.aspx in designer.

4. Now split the design view that you can see HTML part of the page as well as design of the page.

5. In the design view, select “Departments” list view web part.

6. This will select the <WebPartPages:ListFormWebPart> tag in the HTML view. Now in HTML view, go to the ending of the tag </WebPartPages:ListFormWebPart>.

7. Place you cursor next to the above told tag and hit Enter.

8. Now, select Insert tab from the top ribbon bar. There select “Data View” and from the menu select “Empty Data Source”. This will add empty data form web part to the page.

9. Now, in the design view, click the link with “Click here to select a data source”, in the data view web part.

10. This will open Data Source Picker dialog box.

11. Select the Employees list from the Lists group and click OK.

12. This will open the Data Source Details task pane in the right hand side.

13. Select the columns which you want to display them in the data view web part. Then click the button “Insert Selected Fields as…” and select “Multiple Item View”.

14. Now, save the changes.

15. Our page is ready for displaying.

16. Navigate to your site, open the Departments list. Click on one of the item to go to the display page.

17. Here you will find that all the employees are listed instead of only related to the selected department.

18. To achieve this we need some changes in the query generated by the SharePoint designer for the DataFormWebPart that we added to the page in last pages.

19. So, again switch over to your SharePoint Designer. In the Design view select your DataFormWebPart that we added in last couple steps.

20. If you see, the Options tab is activated in the Ribbon bar. From there click on Filter button. This will open the Filter Criteria dialog.

21. Next, for the Field Name select Department and then for the Value column select "Create a new parameter..." option from drop down list. This will open the "Data View Paramters" dialog box.

22. For that, provide the values as shown in the below image and click OK to close that dialog box.

23. Click OK to close the Filter Criteria dialog box.

24. Save the changes and try to see what is the effect of your changes.

25. If you see the department details page, you will not get any Employees items. Right.

26. The reason behind this is that our Department column in Employees list is Lookup and our criteria try to get the Employees related to Department by matching the ID of the Department item. So, to get the Employees list items related to the Department item we have to amend the query used by the Data Form Web part.

27. For this, switch back to SharePoint Designer and in the HTML view, find the word "selectcommand".

28. You can see that this "selectcommand" is the attribute of the SPDataSource object which is bound to our Data Form Web part.

29. For the "selectcommand" value replace "<FieldRef Name="Department"/><Value Type=&quotText">{DeptID}</Value>" with "<FieldRef Name="Department" LookupId="TRUE" /><Value Type="Lookup">{DeptID}</Value>"

30. Save your changes. Now, try to see what is the effect of your changes.

31. Now at this time you will find that the Employees list items related to your selected Department item are listed.

So, this way we can use the SharePoint Designer for customizing the SharePoint page and without writing a single line of code, we can achieve this type of functionality.

Hope you have enjoyed the article. As, this article does not have any coding part so you are thinking that there is no download. This time no sample code but you can download the site template which I've used for this example from here.

By Jatin Prajapati   Popularity  (9535 Views)