Partager via

Cascading Dropdowns in Browser Forms

  • Article

If you are building an InfoPath client-only solution and you need to filter drop-down list boxes, you can simply use the “Filter Data” feature when you set the Entries property for the control. However, since filters are not supported in browser-compatible form templates, how can you accomplish the same functionality?

 

This is where .NET web services can “save the day!” By creating web methods that accept parameters, you can add those web methods as data connections and then pass the selected value from one drop-down list box to the appropriate data connection “queryField”. Once the queryField has been set, simply execute that data connection to retrieve the associated values.

 

To setup this sample, you will need to have access to the SQL Server Northwind sample database and Visual Studio installed on your server.

 

First, let’s create the web service and the two web methods we will use in this sample:

 

Step 1: Open the appropriate web site

 

  1. Launch Visual Studio
  2. From the File menu, select Open and choose Web Site
  3. Select File System and then navigate to: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS

 

NOTE: By choosing to open the LAYOUTS folder, your web service will be available from all provisioned sites. If you want the web service only to be available from a specific site (i.e. the default site) you would want to open: C:\Inetpub\wwwroot\wss\VirtualDirectories\80

 

  1. Click Open
  2. In the Solution Explorer, right-click on the web site and choose New Folder

 

 

  1. Rename this folder to: WebServices
  2. Because you may have multiple web services, let’s add a sub folder here that is specific to our web service:
    1. Right-click on WebServices and choose New Folder
    2. Rename this folder to: NorthwindTables

 

 

Step 2: Create the web service

 

  1. Right-click on NorthwindTables and choose Add New Item
  2. From the Visual Studio installed templates list choose Web Service
  3. In the Name box, rename this to: NorthwindTable.asmx

 

 

  1. Uncheck the option “Place code in a separate file” and click Add

 

 

Step 3: Add the web methods

 

NOTE: For this sample, it is assumed the SQL Server database is installed on the same Microsoft Office SharePoint Server. 

 

  1. Add the following “using” declarations at the top of your code page: 

using System.Data;

using System.Data.SqlClient;

 

  1. Add the following web method to retrieve the CustomerID values from the Customers table in the Northwind database:

 

[WebMethod]

public DataSet GetCustomers() {

            // Create a SQL connection to the Northwind sample database

            SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");

 

            // Create data adapter object passing it the SELECT

            // statement to retrieve the customer ID values

            SqlDataAdapter da = new SqlDataAdapter("SELECT Customers.CustomerID FROM Customers Order By CustomerID", cn);

 

            // Create a dataset object to store the data

            DataSet ds = new DataSet();

 

            // Open the connection

            cn.Open();

 

            // Fill the dataset

            da.Fill(ds, "Customers");

 

            // Clean up

            cn.Close();

            cn = null;

            da = null;

            

            return ds;

            }

 

  1. Add the following web method to retrieve the associated orders for the selected customer:

[WebMethod]

public DataSet GetOrdersForSelectedCustomer(string strCustID) {

            // Create a SQL connection to the Northwind sample database

            SqlConnection cn = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind");

 

            // Create a string variable for the modified SQL statement

            string strOrdersSQL = "";

 

            // Create a string variable for the default SQL statement

            string strOrdersOrigSQL = "SELECT * FROM Orders";

 

            // Some of the customer ID values contain apostrophe's - we need

            // to replace them with two single quotation marks so that all

            // single quotation marks in the CustomerID are parsed correctly.

            strCustID = strCustID.Replace("'", "''");

 

            // Concatenate the default SQL statement with the "Where" clause

            // and add an OrderBy clause

            strOrdersSQL = strOrdersOrigSQL + " Where CustomerID Like '%" + strCustID + "%' Order By OrderID";

 

            // Create data adapter object passing it the SELECT statement

            // to retrieve the OrderID values

            SqlDataAdapter daOrders = new SqlDataAdapter(strOrdersSQL, cn);

 

            // Create a dataset object to store the data

            DataSet Ds = new DataSet();

 

            // Open the connection

            cn.Open();

 

            // Fill the DataSet

            daOrders.Fill(Ds, "Orders");

 

            // Clean up

            cn.Close();

            cn = null;

            daOrders = null;

                 

            return Ds;

}

 

  1. Build and save the project

 

 

Step 4: Test the web methods

 

NOTE: The Identity account of the Application Pool for the web site where this web service is published will need to have access to the SQL Server database.

 

  1. Open a browser and navigate to: http://<server>/_layouts/WebServices/NorthwindTables/NorthwindTables.asmx (replace <server> with the name of your server)
  2. You should see the two web methods created above along with the default HelloWorld web method:

 

 

  1. Click the GetCustomers link and then click Invoke – this should return a list of the CustomerID values
  2. Click the GetOrdersForSelectedCustomer link, in the strCustID box enter: BERGS and then click Invoke – this should return a list of only those OrderID values for BERGS

 

 

Step 5: Create the InfoPath form

 

  1. Design a new, blank, browser-compatible InfoPath Form Template
  2. Add a drop-down list box to the view and modify the name to: SelectCustomer
  3. Add another drop-down list box to the view and modify the name to: SelectOrder

 

 

 

  1. Add a new “receive data” data connection to the NorthwindTables web service for each of the web methods created above as follows:
    1. GetCustomers:
      • Enable the option “Automatically retrieve data when the form is opened”
    2. GetOrdersForSelectedCustomer:
      • Use ALFKI as the sample value for the strCustID parameter when prompted in the Data Connection Wizard
      • Uncheck the option “Automatically retrieve data when the form is opened”
  2. Set the Data source for SelectCustomer to the GetCustomers data connection and use the CustomerID field for both the Value and Display name properties
  3. Set the Data source for SelectOrder to the GetOrdersForSelectedCustomer data connection and use the OrderID field for both the Value and Display name properties
  4. Create a Rule on SelectCustomer with the following actions:
    1. Set a field’s value: Set the SelectOrder field to nothing (e.g. leave the Value blank)
    2. Set a field’s value: Set the parameter value (strCustID) for the GetOrdersForSelectedCustomer data connection to the SelectCustomer field
    3. Query the GetOrdersForSelectedCustomer data connection

 

 

  1. Save the form locally as FilteredDrop-downs_IPFS.XSN

 

 

Step 6: Publish the form

 

  1. Publish the form to a server running InfoPath Form Services
  2. Navigate to the form library where the form was published and click the New button
  3. From SelectCustomer choose BERGS
  4. Click SelectOrder – only those orders for BERGS are displayed
  5. Select a different customer – notice the orders have also changed

 

Scott Heim

Support Engineer

Comments

  • Anonymous
    December 20, 2006
    Encontrei num post do blog do JOPX , este conjunto de recursos sobre InfoPath 2007. General resources

  • Anonymous
    December 25, 2006
    InfoPath 2007 resources General resources InfoPath General Overview InfoPath team Blog Designing Form

  • Anonymous
    December 27, 2006
    I am a newbie in infopath and I have surfed almost every article to get my dependent dropdowns working! Most of the sites ask us to refer this URL.. This is a great article. I would like to add my comments for populating cascading dropdowns. You can also use repeating tables for this purpose. There is no need to call a webservice on change of each and every dropdown. Just write your logic in the codebehind and populate a repeating table onchange of a dropdown. Bind the dependent dropdown to this repeating table. Ensure to clear the reapeating table on next onchange event of your first dropdown! -Bhavana Bhat

  • Anonymous
    January 02, 2007
    Hi Bhavana, Thank you for your suggestion as this is certainly another option! One of the main reasons I used a web service in this manner is that the InfoPath form template (for this functionality) requires no code - as such, the form template does not need to be "Administrator" deployed. Scott Heim

  • Anonymous
    January 16, 2007
    The comment has been removed

  • Anonymous
    January 16, 2007
    When designing Microsoft Office InfoPath form templates, filtering can be used to limit the options that are displayed to users in certain controls. However, if you are designing an Office InfoPath 2007 form template for a browser scenario, it should

  • Anonymous
    February 20, 2007
    One of our customers here asked us to develop a simple InfoPath form, including dependant dropdown functionality.

  • Anonymous
    April 20, 2007
    This is a great example, but what do you do if the controls are in a repeating table? The dependant control (SelectOrder) is "filtered" for every row in the repeating table based on the selected value of SelectCustomer in the current row.

  • Anonymous
    April 24, 2007
    The comment has been removed

  • Anonymous
    May 22, 2007
    Hi, I have to admit that this post is long overdue. In the last two weeks, I came across a lot of people

  • Anonymous
    May 22, 2007
    Hi, I have to admit that this post is long overdue. In the last two weeks, I came across a lot of people

  • Anonymous
    August 23, 2007
    The InfoPath Team Blog has a great article on how to implement cascading dropdowns in InfoPath Forms

  • Anonymous
    August 23, 2007
    Using managed code, it is possible to set the dropdown values dynamically even in the repeating context. The above URL contains an article on how to do it with a sample.

  • Anonymous
    September 06, 2007
    Hello You are the hero, i was trin to do that from three days without filters but .... You solved the problem. Thanks alot.

  • Anonymous
    September 06, 2007
    Hello I have q question. What if i have 3 dropdown lists. I applied same rule to 1est and 2nd as u did at "SelectCustomer " but its not working, it shows data in first ddl, but no data is selected in 2nd ddl. Pleaser help Thanks

  • Anonymous
    September 06, 2007
    I solved the above problem it was my mistek. Thanks agin for this article

  • Anonymous
    October 10, 2008
    u How to do custom themes for MOSS http://www.sharepointblogs.com/tigirry/archive/2007/07/03/custom-site-theme-for-sharepoint-2007-moss-2007-and-wss-3-0.aspx

  • Anonymous
    February 10, 2009
    The InfoPath Team Blog has a great article on how to implement cascading dropdowns in InfoPath Forms

  • Anonymous
    June 25, 2009
    Excellent article!  Thanks so much.  I needed to populate cascading dropdowns from SharePoint lists in the main site collection, so I just replaced the web service file with this: <code> <%@ WebService Language="C#" Class="ListAccess.ListAccessService" %> <%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%> using System; using System.Web; using System.Web.Services; using System.Web.Services.Protocols; using Microsoft.SharePoint; using System.Xml; namespace ListAccess {    [WebService(Namespace = "http://localhost/ListAccess", Description = "Provides access to SharePoint Lists")]    class ListAccessService : WebService    {        [WebMethod(Description = @"Gets filtered items from the specified list.   Be sure to use the internal name for the filterField.")]        public XmlDocument GetListItems(string listName, string filterField, string filterValue)        {            XmlDocument returnDoc = new XmlDocument();            using (SPSite site = SPContext.Current.Site)            {                using (SPWeb web = site.RootWeb)                {                    SPList list = web.Lists[listName];                    string queryText = String.Format(@"<Where> <Eq> <FieldRef Name=""{0}"" /> <Value Type=""Text"">{1}</Value> </Eq> </Where>", filterField, filterValue);                    SPQuery query = new SPQuery();                    query.Query = queryText;                    SPListItemCollection items = list.GetItems(query);                    returnDoc.LoadXml(items.Xml);                }            }            return returnDoc;        }    } } </code>

  • Anonymous
    June 26, 2009
    Thanks Elliott!

  • Anonymous
    July 14, 2009
    Hi, Is there anywhere a walkthrough for this procedure? I'm quite a newbe on this, an on step 2 visual web developpers 2008 express already giving the error: does not support opening Sharepoint Web sites.

  • Anonymous
    July 16, 2009
    Hi pbakker_67, I don't believe the "express" edition will provide the functionality you will need. I believe you will need a "Professional" level or greater to get the features for this type of functionality. Scott

  • Anonymous
    November 02, 2010
    Is it possible to have rules or seperate xml's without creating a webservice to make this cascading work for browser enabled forms? I am able to set the second dropdown value based on the first, BUT it only takes the first value of the "new" xml list??

  • Anonymous
    November 27, 2010
    anyone tell me what version of VB to use with SP 2007 server 3.0 and form services 07?

  • Anonymous
    December 08, 2010
    The comment has been removed

  • Anonymous
    May 23, 2012
    great article. it works in default website generated from VS2005/08. but not in under (IIS)localhost. it throws error when clicking invoke btn for getcustomers as (The user is not associated with a trusted sql server connection.) i am having sql server seperately (but in same domain)and calling name of that server in data source.. pls help me..as iam in the halfway

  • Anonymous
    July 19, 2012
    Hi Scott, Nice article! Thanks for posting it! :)

  • Anonymous
    July 17, 2013
    May i know what are the basic tasks in Microsoft InfoPath 2010? <a href="www.sampleforms.net/">Sample Forms</a>