Selecting the minimum date in an InfoPath repeating table using formulas (no code)

Applies to: InfoPath 2007

Learn how you can use a formula (no code) in InfoPath to find and highlight the rows in a repeating table that contain the date that falls before all other dates in one of the repeating table's columns.

ADVERTISEMENTS

Problem

You have an InfoPath repeating table that contains one column in which an InfoPath date picker displays dates and you want to find and highlight the row that contains the minimum date in this column.

Solution

Use formulas and Conditional Formatting to find and select the minimum date in a repeating table in InfoPath.

Discussion

You can achieve this functionality as follows:

  1. In InfoPath 2007, create a new Blank form template.
  2. Add a Repeating Table control with a hidden column to the InfoPath form template.
  3. Convert the text box in the first column of the repeating table into a Date Picker control. Make its Date type Date (date).
  4. On the Data Source task pane, right-click the myFields node and add a field with the Name minDate and Data type Date (date). Your InfoPath form template and Main data source should now resemble the following figure:
    Repeating table with date and hidden fields and the Main data source in InfoPath Design mode
    Figure 1. Repeating table with date and hidden fields and the Main data source in InfoPath Design mode.

  5. On the Data Source task pane, double-click the InfoPath hidden field in the repeating table to open its Properties dialog box. Here we'll assume that you made field2 in the repeating table hidden.
  6. On the Field or Group Properties dialog box, click the formula button behind the Value text box under the Default Value section.
  7. On the Insert Formula dialog box, select the Edit XPath (advanced) check box.
  8. On the Insert Formula dialog box, copy and paste the following formula into the Formula text box:

    substring(../my:field1, 9, 2) + ((153 * (substring(../my:field1, 6, 2) + 12 * ((14 - substring(../my:field1, 6, 2)) div 12) - 3) + 2) div 5) + 365 * (substring(../my:field1, 1, 4) + 4800 - ((14 - substring(../my:field1, 6, 2)) div 12)) + ((substring(../my:field1, 1, 4) + 4800 - ((14 - substring(../my:field1, 6, 2)) div 12)) div 4) - 32083

    Here field1 represents a date picker control in the first column of the repeating table and the field for which you want to find the minimum date.

  9. Click OK to close all open dialog boxes.
  10. On the Data Source task pane, double-click the minDate field to open its Properties dialog box.
  11. On the Field or Group Properties dialog box, click the formula button behind the Value text box under the Default Value section.
  12. On the Insert Formula dialog box, select the Edit XPath (advanced) check box.
  13. On the Insert Formula dialog box, copy and paste the following formula into the Formula text box:

    ../my:group1/my:group2/my:field1[../my:field2 = xdMath:Min(/my:myFields/my:group1/my:group2/my:field2)]

  14. Click OK to close all open dialog boxes.
  15. Double-click the Date Picker control in the first column of the repeating table to open its Properties dialog box.
  16. On the Date Picker Properties dialog box, select the Display tab, and then click Conditional Formatting.
  17. On the Conditional Formatting dialog box, click Add.
  18. On the Conditional Format dialog box, add two conditions that say:

    field1 is equal to minDate

    and

    minDate is not blank

  19. On the Conditional Format dialog box, select a color for the Shading, and click OK.
  20. Click OK to close all open dialog boxes.

Selecting the minimum date in a column of a repeating table in InfoPath
Figure 2. Selecting the minimum date in a column of a repeating table in InfoPath.

You should now have a fully functional form so that whenever you add a new row to the repeating table, the date column is evaluated and the minimum date is selected. This solution also works for InfoPath 2007 browser forms.

 


Related InfoPath Articles:

 

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.

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