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.

ADVERTISEMENTS

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.

    SharePoint Tasks list to be used in InfoPath
    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.

    Secondary data source for the SharePoint Tasks list in InfoPath
    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:

    InfoPath form template with repeating table for the SharePoint Tasks list and date picker controls to select a time period
    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.

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

 
 Subscribe for updates 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 InfoPath Articles:

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