425-882-1032 info@3sharp.com

When designing Microsoft Office InfoPath form templates, filtering can be used to limit the options that are displayed to users in certain controls. This out-of-the-box functionality can be used in list boxes, drop-down list boxes, combo boxes, repeating sections, and repeating tables. However, if you are designing an Office InfoPath 2007 form template for a browser scenario, it should be noted that filtering is not supported.

The InfoPath team posted a workaround for the unsupported filtering feature. That workaround requires a Web service that accepts parameters. In the comments for that post, Bhavana Bhat mentioned an InfoPath code solution that could also work. In this post, I will offer an example of what Bhavana was suggesting.

The scenario for this example is a time-reporting solution. My company, 3Sharp LLC, provides IT consulting services to different companies. Our staff is required to submit time reports each week for projects worked. A simple time report form allows us to distinguish billable projects from non-billable projects, as shown in the following figure.

Our projects are identified in a SharePoint list. Custom columns in the list are used to indicate whether the project is billable or not, who the project manager is, and any other pertinent information.

The form template uses a SharePoint list data connection to retrieve the project names. The drop-down list box control in the Billable Projects section displays only billable project names, while the drop-down list box control in the Non-Billable Projects section displays only non-billable project names. This form template is being used in a browser scenario, so we cannot use filtering functionality in the form designer. Instead, we have a code solution that uses secondary data sources to filter the list values. In addition to the SharePoint list data connection, there are two XML file data connections in the form template. These data connections, BillableProjects and NonBillableProjects, have an identical data source.

We added code to the form’s Loading event that puts the billable project names in the BillableProjects secondary data source and the non-billable project names in the NonBillableProjects secondary data source. The following C# example shows how we first deleted any default Project nodes in the secondary data sources before adding the correct project names received from the SharePoint list.

public void FormEvents_Loading(object sender, LoadingEventArgs e)
    // Delete default billable projects in the secondary data source.
    XPathNavigator billableProjectRoot = DataSources["BillableProjects"].CreateNavigator().SelectSingleNode("//Projects", NamespaceManager);
    XPathNodeIterator defaultBillableProjects = billableProjectRoot.CreateNavigator().Select("//*[local-name() = 'Project']");
    for (int i = defaultBillableProjects.Count - 1; i >= 0; i--)
        XPathNavigator defaultBillableProject = DataSources["BillableProjects"].CreateNavigator().SelectSingleNode("//Project[last()]", NamespaceManager);

    // Delete default non-billable projects in the secondary data source.
    XPathNavigator nonbillableProjectRoot = DataSources["NonBillableProjects"].CreateNavigator().SelectSingleNode("//Projects", NamespaceManager);
    XPathNodeIterator defaultNonbillableProjects = nonbillableProjectRoot.CreateNavigator().Select("//*[local-name() = 'Project']");
    for (int i = defaultNonbillableProjects.Count - 1; i >= 0; i--)
        XPathNavigator defaultNonbillableProject = DataSources["NonBillableProjects"].CreateNavigator().SelectSingleNode("//Project[last()]", NamespaceManager);

    // For each billable project, add the name to the corresponding secondary data source.
    XPathNodeIterator actualBillableProjects = DataSources["ProjectList"].CreateNavigator().Select("//*[local-name() = 'Project_List'][@Billable = '1']");
    foreach (XPathNavigator billableProject in actualBillableProjects)
        billableProjectRoot.AppendChildElement("", "Project", billableProjectRoot.LookupNamespace(billableProjectRoot.Prefix), billableProject.SelectSingleNode("@Title", NamespaceManager).InnerXml);

    // For each non-billable project, add the name to the corresponding secondary data source.
    XPathNodeIterator actualNonbillableProjects = DataSources["ProjectList"].CreateNavigator().Select("//*[local-name() = 'Project_List'][@Billable = '0']");
    foreach (XPathNavigator nonbillableProject in actualNonbillableProjects)
        nonbillableProjectRoot.AppendChildElement("", "Project", nonbillableProjectRoot.LookupNamespace(nonbillableProjectRoot.Prefix), nonbillableProject.SelectSingleNode("@Title", NamespaceManager).InnerXml);

Then, we made sure the drop-down list box controls in the form view were bound to the correct secondary data source. The following figure shows the binding for the drop-down list box control in the Non-Billable Projects section.

NOTE: As Scott Heim correctly pointed out in the comments for the original post, this type of solution would need to be admin-deployed.