Filter and sort a SharePoint list in InfoPath using a DataView

This article is based on concepts discussed in Programmatically add items to a drop-down list box in InfoPath 2007
and is an extension of a solution for sorting and filtering proposed in the InfoPath newsgroup.

Problem

You have a repeating table on an InfoPath form that displays data from a SharePoint list, which you would like to sort and filter.

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 data source or to a repeating node in a secondary data source.

Discussion

You can accomplish this functionality 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 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