InfoPath repeating table validation check for duplicate records

Learn how to perform repeating table validation in InfoPath 2010 to check for duplicate records in a repeating table on an InfoPath 2010 form.

ADVERTISEMENTS

Problem

You have a repeating table on an InfoPath 2010 form and would like to check for duplicate records in the InfoPath repeating table through validation, that is, whether there is more than one row in the repeating table that has a column with the same data. You do not want to have to write code to get such functionality.

Solution

Use sum(), count(), preceding-sibling, and following-sibling in formulas for the Default Value of fields to check whether duplicate values exist in a field in the rows of a Repeating Table.

Discussion

You can check for duplicate records in an InfoPath repeating table through validation as follows:

  1. In InfoPath 2010, create a new Blank Form template.
  2. On the Home tab in the Controls group, click Repeating Table to add a repeating table to the form template. Create a repeating table that has at least 2 columns.
  3. In this example, you will check whether the first field in the repeating table (field1) contains duplicate data. You will use field2 as a helper field to detect duplication. And because field2 will be used as a helper field, you don't really want to display it on the form, so must make it a hidden field in the repeating table.
  4. On the Fields pane, click the down arrow behind field2 and select Properties from the drop-down menu that appears.
  5. On the Field or Group Properties dialog box on the Data tab in the Default Value section, click the formula button behind the Value text box.
  6. On the Insert Formula dialog box, select the Edit XPath (advanced) check box.
  7. On the Insert Formula dialog box, type the following formula into the Formula text box:

    count(../my:field1[text() = ../preceding-sibling::*/my:field1/text()]) + count(../my:field1[text() = ../following-sibling::*/my:field1/text()])

  8. On the Insert Formula dialog box, click OK.
  9. On the Field or Group Properties dialog box, ensure that the Refresh value when formula is recalculated check box is selected, and then click OK.
  10. On the Fields pane, select myFields, and then under Actions at the bottom of the Fields pane click Add Field.
  11. On the Add Field or Group dialog box, type a name for the field (e.g. duplicatesCheck), and click OK.
  12. On the Fields pane, double-click on the field you just added.
  13. On the Field or Group Properties dialog box on the Data tab in the Default Value section, click the formula button behind the Value text box.
  14. On the Insert Formula dialog box, select the Edit XPath (advanced) check box.
  15. On the Insert Formula dialog box, type the following formula into the Formula text box:

    sum(../my:group1/my:group2/my:field2)

    This sums up all of the results for the count() functions performed in step 7. If the result of this sum is zero, you can conclude that there are no duplicates in field1.

  16. On the Insert Formula dialog box, click OK.
  17. On the Field or Group Properties dialog box, ensure that the Refresh value when formula is recalculated check box is selected, and then click OK.

To let the user know that there are duplicates in field1, you can display a message. In this example, you will add data validation on field1, but you could use any other method to alert the user that there are duplicate records in the InfoPath repeating table.

To use data validation to let the user know that there duplicate records in the InfoPath repeating table:

  1. On the Fields pane, click field1.
  2. On the Home tab under Rules, click Manage Rules.
  3. On the Rules pane, click New, and then select Validation.
  4. On the Rules pane under Condition, click the text None.
  5. On the Condition dialog box, select Select a field or group from the first drop-down list box.
  6. On the Select a Field or Group dialog box, select the duplicatesCheck field, and click OK.
  7. On the Condition dialog box, select is not equal to from the second drop-down list box.
  8. On the Condition dialog box, select Type a number from the third drop-down list box.
  9. On the Condition dialog box, type 0 in the text box, and click OK.
  10. On the Rules pane under ScreenTip, type a message such as Field1 contains duplicate values.
  11. On the Home tab, click Preview.

Now whenever you add a row to the Repeating Table and type a value that already exists in field1 in another row of the Repeating Table, a red dashed border will appear around all of the text boxes bound to field1.

This solution works for both InfoPath Filler forms and InfoPath Browser forms.

 
 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