Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Multiple listing controls with a single datasource - InfoPath


By:   |   Read Comments   |   Related Tips: > Sharepoint

Problem

The scope of this article is to explain how to populate multiple InfoPath listing controls like a Drop-Down List and a List Box with only one datasource. The scenario can be like this:

Assume we have 2 to 3 SharePoint lists with data on our SharePoint site and we have some listing controls on our InfoPath form. The aim is to populate all these listing controls using only a single InfoPath datasource.

Solution

By default we do have four "Receive Data" options for InfoPath datasource like an XML document, DataBase,Web Service, and SharePoint Lists & Libraries. As mentioned in the "Problem", our aim is to populate data from different SharePoint lists to different listing controls on an InfoPath form. Usually, everyone will create different datasources from SharePoint lists for each listing control, but we can utilize the "Web service" option for populating many listing controls with a single datasource.

Prior to the development of this sample, we have to create 2 SharePoint lists inside a SharePoint Site. Name one SharePoint list "Names" and the other "Products". Fill these lists with some sample data. After populating these lists with data we can begin development.

First, we will create a webservice with a web method returning a dataset and deploy this to a SharePoint site. For SharePoint component development I use WSPBuilder to a large extent. You can download it from this location WSPBuilder. It’s a very easy and handy tool for the SharePoint development.

Follow the below steps to develop a webservice that's going to deploy to the SharePoint site.

  • Open the Visual Studio 2008/2005. Go to File Menu --> New-->Project -->WSPBuilder --> WSPBuilder Project. Name the Project as "SharePointDataServiceWSP".
  • From the Solution Explorer, right click on the Project and choose the "Add new item" option. From the window, select the option "WebService" under the WSPBuilder category and name the webservice "GetData".
  • Switch to the code behind the "GetData.asmx" webservice file, which we can see under the "WebServiceCode" folder of the WSP Project.
  • Add the below WebMethod (GetDataSet) to the code behind.
  • [WebMethod]
    public DataSet GetDataSet()
    {
    //Creation of the Dataset
    DataSet ds = new DataSet();
    ds.DataSetName = "ParentDS";
    
    //Creation Datatables for Names and Products
    DataTable dtNames = new DataTable();
    DataTable dtProducts = new DataTable();
    
    SPSite site = new SPSite("http://servername:port/");
    SPWeb web = site.OpenWeb();
    SPQuery namesQuery = new SPQuery();
    namesQuery.Query = "";
    SPList listNames = web.Lists["Names"];
    SPListItemCollection itemNamesCollections = listNames.GetItems(namesQuery);
    //assigning SharePoint list item collections to the datatable - Names
    dtNames = itemNamesCollections.GetDataTable();
    
    SPQuery queryProducts = new SPQuery();
    queryProducts.Query = "";
    SPList listProducts = web.Lists["Products"];
    SPListItemCollection itemProductCollections = listProducts.GetItems(queryProducts);
    //assigning SharePoint list item collections to the datatable - Products
    dtProducts = itemProductCollections.GetDataTable();
    
    //Adding datatables to the DataSet
    ds.Tables.Add(dtNames);
    ds.Tables.Add(dtProducts);
    
    //returning DataSet
    return ds;
    
    }
    
  • Build the code.
  • Build the WSP and Deploy the WSP to your site
  • After the deployment we can see the webservice under the _layouts folder of the SharePoint site. It will be like this:

    http://servername:port/_layouts/GetData.asmx

InfoPath Form Development - creation of datasource with webservice and data population of the dropdownlists.

  • Open Microsoft InfoPath 2007 .
  • Create a new blank InfoPath Form Template and save the InfoPath form with a name.
  • Add 2 dropdownlists into the form from the Controls. Name these dropdownlists, one as "Names" and the other as "Products". See image below.

    infopath view

  • From the "Design Tasks" of the InfoPath form, Click on "Data Source" Link thiswill make the "Data Source" section appear. Click on the "Manage Data Connections" link from the "Data Source" Section.  From the "Data Connections" window, click on the "Add" button and it will open a "Data Connection Wizard". On this wizard select the option to receive data [see image below] and click Next.

    receive Data, Data connection wizard

  • On this section it will ask for the "Source of the Data". Select the "Web Service" Option and click Next.
  • On this section we have to give our Webservice [which was created above] URL and click Next.

    WebService URL

  • On this section we need to select an "operation", which means the WebMethod that we have created. In our case it is "GetDataSet". Select "GetDataSet" and click Next.
  • On this section, we need to decide whether data needs to be locally saved or not. Leave the checkbox as blank and click Next.
  • The checkbox should be checked in order to automatically populate the data when the form is opened. Also name the data connection on the name field as "GetDataSet" and click Finish.
  • From the above steps we have added a datasource and 2 dropdownlists. Now we have to bind this datasource with the 2 dropdownlists .
  • Right Click on the "Names" dropdown and select the DropDownList Properties.
  • On the properties window, under the tab "Data" and from "List box entries" section, select the third option which is "Look up values from an external datasource."
  • After the above step, we can select the datasource from the "Data Source" dropdown. Here we can select the datasource name "GetDataSet".
  • After selecting the datasource, we have to select the "Entries" Xpath for the "Names" dropdown. Please see the image for adding the XPath.


  • From the window that appeared after clicking the highlighted button of the above image, here you can expand the tree available and select the Group. See the image below.


  • After the above step we can select the "Value" and "Display name" of the Dropdown. Please see the image below.


  • Repeat the same steps for the "Products" dropdown for the data binding.
  • After the data binding of these dropdowns, just preview the InfoPath form and you should see the dropdowns populated with the proper data.

Now we have an InfoPath form with 2 dropdowns that are populated from a single datasource.

Next Steps
  • Check out MSSQLTips.com for great information about Microsoft SQL Server.


Last Update:






About the author
MSSQLTips author Abin Jaik Antony Abin Jaik Antony

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools