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.

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.
   
   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.

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

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

Related Posts

• Submit repeating table rows from InfoPath to a SharePoint list using the SharePoint Object Model
• How to copy data from a secondary data source (SharePoint list) to a repeating table in the Main data source of an InfoPath form
• InfoPath Video Tutorial: Copy SharePoint tasks to InfoPath form fields
• How to add, update, and delete SharePoint list items via InfoPath
• Display SharePoint Tasks assigned to the current user in InfoPath

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. Usage of 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.