Filter a SharePoint list in a repeating table in InfoPath on a date range

Use conditional formatting on a repeating table in InfoPath to filter a SharePoint list on a date range using two date picker controls.

Problem

You are displaying the items from a SharePoint Tasks list in an InfoPath repeating table on a form and you want to use two InfoPath date picker controls to filter the SharePoint list and only return those tasks of which the due date falls between the two dates specified.

Solution

Use conditional formatting to filter the rows of a repeating table that contain items from a SharePoint list.

Discussion

You can accomplish this functionality as follows:

1. In SharePoint, create a new Tasks list or use an existing one.

   
   Figure 1. SharePoint Tasks list to be used in InfoPath.

2. In InfoPath, add a data connection to the SharePoint list you created or chose in step 1.

3. In InfoPath, on the Data Source task pane, switch to the Secondary data source that represents the SharePoint list, expand the nodes, drag the repeating node to the InfoPath form template, drop it, and select Repeating Table from the context menu that appears.

   
   Figure 2. Secondary data source for the SharePoint Tasks list in InfoPath.

4. Add two Date Picker controls of data type Date (date) to the InfoPath form template and name them periodStartDate and periodEndDate. Your InfoPath form template should now resemble the following:

   
   Figure 3. InfoPath form template with repeating table for the SharePoint Tasks list and date picker controls to select a time period.

   Note: I've deleted many of the columns in the repeating table and have only left the Title and Due Date columns.

5. Double-click the Repeating Table to open its Properties dialog box.
6. On the Repeating Table Properties dialog box, click Display, and then Conditional Formatting.
7. On the Conditional Formatting dialog box, click Add.

8. On the Conditional Format dialog box, add the following 2 conditions:

   Due_Date is less than periodStartDate

   or

   Due_Date is greater than periodEndDate

9. On the Conditional Format dialog box, select the Hide this control check box, and click OK.
10. Click OK on all open dialog boxes.
11. Test the InfoPath form template.

You should now have a fully functional InfoPath form so that when you enter a start date and an end date, only the tasks from the SharePoint list that have a due date between the start and end dates are displayed. This solution also works for InfoPath browser forms.

Figure 4. Filtered SharePoint Tasks list on dates in an InfoPath repeating table.

Related InfoPath Articles:

• Filter a repeating table on a date range using rules and filters
• Video Tutorial: Filter SharePoint list data in an InfoPath repeating table on dates
• Filter SharePoint list on dates in InfoPath 2010
• Filtering a list in InfoPath on the current month and day period
• Sort and filter a SharePoint list in InfoPath using a DataView

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