Filtered cascading drop-downs in InfoPath browser forms using SharePoint lists and WSS owssvr.dll

Learn how you can use Windows SharePoint Services' (WSS) owssvr.dll and InfoPath's FileQueryConnection to write code that filters SharePoint list data displayed in dependent or cascading drop-down list boxes on an InfoPath browser form in SharePoint.

ADVERTISEMENTS

Problem

You have 3 drop-down list boxes on an InfoPath browser compatible form template.

You would like the second drop-down list box to be filled with only those items that are related to the item selected in the first drop-down list box, and you'd like the third drop-down list box to be filled with only those items that are related to the item selected in the second drop-down list box.

In essence, you want to create cascading drop-down list boxes on an InfoPath browser form.

Solution

Use the owssvr.dll of Windows SharePoint Services (WSS) to filter SharePoint list data and populate drop-down list boxes on an InfoPath browser form, thereby creating cascading lists.

Discussion

There are several ways to create cascading drop-down list boxes in InfoPath, some of which include using the Filter Data option in InfoPath if you're not creating a browser-compatible form template, using a web service, or writing code to filter items and then programmatically populate drop-down list boxes.

In a previous post, I mentioned using the owssvr.dll to pre-filter large SharePoint lists in InfoPath. You can also use the owssvr.dll to return filtered data from a SharePoint list and then use this data to populate a drop-down list box.

You can achieve this functionality as follows:

  1. In SharePoint, create 3 custom lists: Department, Managers, and Employees. Link the Managers list to the Department list through a Department column, and link the Employees list to the Managers list through a Manager column.

    Custom lists in SharePoint
    Figure 1. Custom lists in SharePoint.

  2. Create a new InfoPath browser compatible form template.
  3. Add 3 Drop-Down List Box controls to the InfoPath form template and name them department, manager, and employee respectively.

    Filtered cascading drop-down list boxes on an InfoPath form template
    Figure 2. Filtered cascading drop-down list boxes on an InfoPath form template.

  4. On the Tools menu, click Data Connections.
  5. On the Data Connections dialog box, click Add.
  6. Add a Receive data connection to the Department SharePoint list.
  7. Add a Receive data connection to an XML document, and enter the URL to the owssvr.dll for the Managers SharePoint list. For detailed instructions, see Populating form data from SharePoint List Views. Do the same thing for the Employees SharePoint list.
  8. Bind the manager drop-down list box to the Manager data connection and bind the employee drop-down list box to the Employees data connection. For the sake of simplicity, select the Title field from each SharePoint list and set it as the Value and Display name of each drop-down list box.
  9. On the Properties dialog boxes of the department and manager drop-down list boxes, select Always on the Browser forms tab.
  10. Right-click the department drop-down list box, select Programming, and then Changed Event from the context menu that appears.
  11. Add the following C# code to the event handler you just created:

    XPathNavigator root = MainDataSource.CreateNavigator();

    // Get the value for the selected department
    string department = root.SelectSingleNode("//my:department",
    NamespaceManager).Value;

    // Clear the last selected value in the Manager drop-down list box
    root.SelectSingleNode("//my:manager", NamespaceManager).SetValue("");

    // Retrieve the data connection bound to the Manager drop-down list box
    FileQueryConnection con = (FileQueryConnection)DataConnections["Managers"];

    // Change the location of the data connection to point the XML file
    // returned by the owssvr.dll with a filter on department
    con.FileLocation = @"http://<Server>/<Site>/_vti_bin/owssvr.dll"
    + "?Cmd=Display&List={A6C7784B-C08B-456E-9DC0-1E94C9BE40BC}"
    + "&XMLDATA=TRUE&FilterField1=Department&FilterValue1="
    + department;

    // Query the data connection to fill the Manager drop-down list box with items
    con.Execute();

  12. Right-click the manager drop-down list box, select Programming, and then Changed Event from the context menu that appears.
  13. Add the following C# code to the event handler you just created:

    XPathNavigator root = MainDataSource.CreateNavigator();

    // Get the value for the selected manager
    string manager = root.SelectSingleNode("//my:manager",
    NamespaceManager).Value;

    // Clear the last selected value in the Employee drop-down list box
    root.SelectSingleNode("//my:employee", NamespaceManager).SetValue("");

    // Retrieve the data connection bound to the Employee drop-down list box
    FileQueryConnection con = (FileQueryConnection)DataConnections["Employees"];

    // Change the location of the data connection to point the XML file
    // returned by the owssvr.dll with a filter on manager
    con.FileLocation = @"http://<Server>/<Site>/_vti_bin/owssvr.dll"
    + "?Cmd=Display&List={5E648E54-D8CC-4C0B-83DF-B74D6FEB80A3}"
    + "&XMLDATA=TRUE&FilterField1=Manager&FilterValue1="
    + manager;

    // Query the data connection to fill the Employee drop-down list box with items
    con.Execute();

  14. Publish the InfoPath form template to a SharePoint server running InfoPath Forms Services. Because the InfoPath form template contains managed code, you'll have to perform an administrator-approved InfoPath form template deployment.
  15. Test the InfoPath form.

You should now have a fully functional InfoPath form, so that when you select an item from the first drop-down list box, the second drop-down list box is filled with only those items that are related to the item you selected in the first drop-down list box, and when you select an item from the second drop-down list box, the third drop-down list box is filled with only those items that are related to the item you selected in the second drop-down list box.

Filtered cascading drop-down list boxes on an InfoPath browser form in SharePoint
Figure 3. Filtered cascading drop-down list boxes on an InfoPath browser form in SharePoint.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services