Cascading Dropdowns in Browser Forms

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

Launch Visual Studio
From the File menu, select Open and choose Web Site
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

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

Rename this folder to: WebServices
Because you may have multiple web services, let’s add a sub folder here that is specific to our web service:

Right-click on WebServices and choose New Folder
Rename this folder to: NorthwindTables

Step 2: Create the web service

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

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.

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

using System.Data;

using System.Data.SqlClient;

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;

}

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;

}

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.

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

Click the GetCustomers link and then click Invoke – this should return a list of the CustomerID values
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

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

Add a new “receive data” data connection to the NorthwindTables web service for each of the web methods created above as follows:

GetCustomers:

Enable the option “Automatically retrieve data when the form is opened”

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”

Set the Data source for SelectCustomer to the GetCustomers data connection and use the CustomerID field for both the Value and Display name properties
Set the Data source for SelectOrder to the GetOrdersForSelectedCustomer data connection and use the OrderID field for both the Value and Display name properties
Create a Rule on SelectCustomer with the following actions:

Set a field’s value: Set the SelectOrder field to nothing (e.g. leave the Value blank)
Set a field’s value: Set the parameter value (strCustID) for the GetOrdersForSelectedCustomer data connection to the SelectCustomer field
Query the GetOrdersForSelectedCustomer data connection

Save the form locally as FilteredDrop-downs_IPFS.XSN

Step 6: Publish the form

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

Scott Heim

Support Engineer