Filtering a list in InfoPath on the current month and day period

Use rules and filter conditions in InfoPath to filter a list containing dates first on the current month and then on a specific period between a minimum and maximum day number.

ADVERTISEMENTS

Problem

You have a list of tasks that are due on a certain date. You want to show only those tasks that are due in the current month. In addition, when you select a period, which can run either from the first of the month to the 15th (period 1) or from the 16th to the end of the month (period 2), you want the list to be filtered further to only show those tasks that are due in the selected period.

If you are looking to filter a repeating table bound to a SharePoint tasks list, refer to this InfoPath 2010 video tutorial.

Solution

Use the Filter Data option and the today(), number(), and substring() functions to filter on the current month, and use helper fields in InfoPath to filter on the minimum and maximum day numbers of a period.

Discussion

You have the following XML file containing tasks with their corresponding due dates:

<?xml version="1.0" encoding="utf-8" ?>
<tasks>
<task>
<name>Task 1</name>
<date>2006-02-02</date>
</task>
<task>
<name>Task 2</name>
<date>2006-02-28</date>
</task>
<task>
<name>Task 3</name>
<date>2006-02-14</date>
</task>
<task>
<name>Task 4</name>
<date>2006-02-23</date>
</task>
<task>
<name>Task 5</name>
<date>2006-03-02</date>
</task>
</tasks>
  1. Design an InfoPath form and select New from XML Document or Schema....
  2. Select the XML file as the Data Source.
  3. Add a field with the name period and data type Text (string) to the Main data source.
  4. Add a field with the name fromDay and data type Whole Number (integer) to the Main data source.
  5. Add a field with the name toDay and data type Whole Number (integer) to the Main data source. Your Main data source should now resemble the following figure:
    Main data source of the InfoPath form
    Figure 1. Main data source of the InfoPath form.
  6. Add a Table with Title to the InfoPath form and create an extra row in the table.
  7. Drag the period field from the Data Source pane and drop it into the first row as a Drop-Down List Box.
  8. Drag the task repeating group from the Data Source pane and drop it into the second row as a Repeating Table.
  9. Modify the table and controls to resemble the following figure:
    The InfoPath form in design mode
    Figure 2. The InfoPath form in design mode.
  10. Double-click on the drop-down list box for the period to open its Properties dialog box.
  11. Select Enter list box entries manually under List box entries on the Data tab.
  12. Select the first entry, click on the Modify... button, type 0 into the Value field, change the Display name into -- Choose a Period --, and click on OK to save the changes.
  13. Back in the Properties dialog box: Click on the Add... button, type 1 into the Value field, type From 1 To 15 into the Display name field, and click on OK to save the changes.
  14. Back in the Properties dialog box: Click on the Add... button, type 2 into the Value field, type From 16 To 31 into the Display name field, and click on OK to save the changes.
  15. Back in the Properties dialog box: Click on the Rules... button.
  16. Add the following 3 rules to the period drop-down list box:
    Condition on the first rule:
    period = 0
    Actions for the first rule:
    Set a field's value: fromDay = 1
    Set a field's value: toDay = 31
    Condition on the second rule:
    period = 1
    Actions for the second rule:
    Set a field's value: fromDay = 1
    Set a field's value: toDay = 15
    Condition on the third rule:
    period = 2
    Actions for the third rule:
    Set a field's value: fromDay = 16
    Set a field's value: toDay = 31
  17. Double-click on the Repeating Table to open its Properties dialog box.
  18. Click on the Display tab.
  19. Click on the Filter Data... button to open the Filter Data dialog box.
  20. Click on the Add... button to open the Specify Filter Conditions dialog box.
  21. Add the following 3 filter conditions joining them with an And:
    number(substring(date, 6, 2)) = number(substring(xdDate:Today(), 6, 2))
    And
    number(substring(date, 9, 2)) >= number(../my:fromDay)
    And
    number(substring(date, 9, 2)) <= number(../my:toDay)

    Note: You must select The expression from the first drop-down list box on the Specify Filter Conditions dialog box to be able to enter the expressions above.
  22. Click on OK when closing all the open dialog boxes.
  23. Go to Tools > Default Values... to open the Edit Default Values dialog box.
  24. Click on the fromDay node and type 1 into the Default value field.
  25. Click on the toDay node and type 31 into the Default value field.
  26. Click on OK to save the changes.

You should now be able to select a period from the drop-down list box and retrieve the tasks for the selected period in the current month.

 
 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