Sort and filter a SharePoint list in InfoPath using a DataView

Learn how you can use C# code and the DataView class of ADO.NET to sort and filter SharePoint list items displayed in a repeating table in InfoPath.

ADVERTISEMENTS

This InfoPath SharePoint article is based on concepts discussed in Populate InfoPath drop-down programmatically from code
and is an extension of a solution for sorting and filtering proposed in the InfoPath newsgroup.

Problem

You would like to filter an InfoPath repeating table that displays data from a SharePoint list.

Solution

You can use an DataView object to sort and filter data from the SharePoint list, and then transfer the filtered and sorted data to a repeating node in the Main InfoPath data source or to a repeating node in a secondary data source.

Discussion

You can sort and filter a SharePoint list in InfoPath as follows:

  1. In Microsoft Office InfoPath 2007, create a new blank form template.
  2. Add a Text Box control named filter and a Button control to the InfoPath form template.
  3. Open the Properties dialog box for the button and change the text for the label of the button to Filter.
  4. Add a data connection to a SharePoint list you want to filter and sort. Here we’ll use a simple SharePoint list that has a Title column, which contains the names of fruits.
  5. Create an items XML file with the following content:

    <?xml version="1.0" encoding="UTF-8" ?>
    <items>
      <item><title/></item>
      <item><title/></item>
    </items>

    This XML file will serve as a secondary data source that will contain the sorted and filtered items from the SharePoint list.

  6. Create a Receive InfoPath data connection to an XML document for the XML file in the previous step. Name the data connection items.
  7. Drag the repeating node for items secondary data source from the Data Source task pane to the InfoPath form template. Select Repeating Table from the context menu that appears when you drop the repeating node. Your InfoPath form template should now resemble the following figure.
    The InfoPath form template in Design mode
    Figure 1. The InfoPath form template in Design mode.
  8. Add the following C# methods to the code file of the InfoPath form template:

    private void RemoveFirstItem()
    {
      XPathNavigator DOM = DataSources["items"].CreateNavigator();
      XPathNavigator group1 = DOM.SelectSingleNode("//items", NamespaceManager);
      XPathNavigator field1 = DOM.SelectSingleNode("//items/item", NamespaceManager);
      field1.DeleteSelf();
    }

    private void AddItem(string itemTitle)
    {
      XPathNavigator DOM = DataSources["items"].CreateNavigator();
      XPathNavigator group1 = DOM.SelectSingleNode("//items", NamespaceManager);
      XPathNavigator field1 = DOM.SelectSingleNode("//items/item", NamespaceManager);
      XPathNavigator newNode = field1.Clone();
      newNode.SelectSingleNode("title").SetValue(itemTitle);
      group1.AppendChild(newNode);
    }

    private void ResetItems()
    {
      // Delete all of the item nodes
      XPathNavigator DOM = DataSources["items"].CreateNavigator();
      XPathNodeIterator iter = DOM.Select("//item", NamespaceManager);
      XPathNavigator firstSibling = DOM.SelectSingleNode("//item[1]", NamespaceManager);
      XPathNavigator lastSibling = DOM.SelectSingleNode("//item[" + iter.Count.ToString() + "]", NamespaceManager);
      firstSibling.DeleteRange(lastSibling);

      // Add two empty item nodes
      DOM.SelectSingleNode("//items", NamespaceManager).AppendChild("<item><title/></item>");
      DOM.SelectSingleNode("//items", NamespaceManager).AppendChild("<item><title/></item>");
    }

    The RemoveFirstItem() method deletes the first item in the items secondary data source, the AddItem() method adds an item to the items secondary data source, and the ResetItems() method resets the entire items secondary data source and restores its contents to the way it was when you first added it manually to the InfoPath form template.

  9. Add a reference to the System.Data assembly and a using statement for the System.Data namespace to the InfoPath form code file.
  10. Add the following code to the Clicked event handler of the button:

    ResetItems();
    RemoveFirstItem();

    XPathNodeIterator sharepointlistitems = DataSources["Fruits"].CreateNavigator().Select(
      "/dfs:myFields/dfs:dataFields/dfs:Fruits", NamespaceManager);

    // Create a DataTable to hold the SharePoint list items
    DataTable dt = new DataTable("SharePointListData");
    dt.Columns.Add("title");

    // Loop through all of the SharePoint list items and fill the DataTable
    DataRow dr;
    foreach (XPathNavigator sharepointlistitem in sharepointlistitems)
    {
      string title = sharepointlistitem.SelectSingleNode("@Title", NamespaceManager).Value;

      dr = dt.NewRow();
      dr["title"] = title;
      dt.Rows.Add(dr);
    }

    // Retrieve the value to filter the SharePoint list items on
    XPathNavigator root = MainDataSource.CreateNavigator();
    string filterValue = root.SelectSingleNode("//my:filter", NamespaceManager).Value;

    // Sort and filter the SharePoint list items in the DataTable
    DataView dv = new DataView(dt);
    dv.Sort = "title";
    dv.RowFilter = "title LIKE '" + filterValue + "*'";

    // Fill the secondary data source with the sorted and filtered SharePoint list items
    foreach (DataRowView drv in dv)
    {
      string title = drv["title"].ToString();
      AddItem(title);
    }

    RemoveFirstItem();

    For the list of filter expressions you can use, see DataColumn.Expression Property on MSDN.

  11. Save your work, build the project, and test the form.

You should now have a fully functional InfoPath form so that when you click the Filter button, the repeating table is populated with the SharePoint list items ordered by Title and filtered on the value specified in the filter text box.

Unfiltered SharePoint List data in a Repeating Table in InfoPath
Figure 2. Unfiltered (but sorted) SharePoint List data in a Repeating Table in InfoPath.

Filtered and sorted SharePoint List data in a Repeating Table in InfoPath
Figure 3. Filtered and sorted SharePoint List data in a Repeating Table in InfoPath.

Also See

Recipe 75 of InfoPath 2010 Cookbook 3 (for C#) and InfoPath 2010 Cookbook 4 (for VB) teaches you step-by-step how to sort rows of a repeating table in an InfoPath 2010 form by writing code, and recipe 76 in the two aforementioned books teaches you how to filter rows of a repeating in an InfoPath 2010 form. The technique used is similar to the technique discussed above in this article, but then with an extensive explanation of what the code does.

 
 Subscribe 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 Posts

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