Complex Data Validation
How do you test more than 5 parameters? How do you group parameters? One answer to both questions, is to have multiple validations in one statement. We'll look into these problems in detail in the case studies below.
Case Study #1
Problem: a form designer wants to use this logic:
IF (State="Ohio" or State="Alabama" or State="Arizona" or State="Georgia" or State="Utah" or State="Idaho" or State="Iowa") THEN (fail...)
Since the Validation UI only supports 5 statements, you run out of room before entering all of the tests.
Solution: Put more than one test in a statement. If you aren't sure of the syntax, follow along.
Enter the first three tests.
Click [OK] to close the dialog. Notice that the syntax is displayed in this dialog:
Click [Modify] and use this syntax.
When you change the first column to "The expression", you will see how to refer to the field you are checking. This differs depending on if you are checking the current field or a different field. In this case, State is the selected field, so we can enter the validation like this:
If you OK this and then come back to it, InfoPath will automatically break out the last 4 tests into separate statements. This makes it easier to see more of the conditions that are being evaluated.
Case Study #2
Problem: a form designer wants to use this logic:
IF (State="Ohio" or State="Alabama") and TaxRate is blank THEN (fail...)
Using just the default Validation UI, you can't group tests like this.
Solution: Put more than one test in a statement. Again, if you aren't certain of the syntax, enter the tests in the Validation dialog:
Click [OK] to close. Note the syntax in this dialog:
Click [Modify], and use the syntax shown in the Validation statements.
Alternative Approach
Another way to handle both of these scenarios is to use multiple validations:
This will work and in some cases would be easier to follow (the 50 states could be listed and you could scroll the Validations).
Performance may be better in one or the other, but that would depend on the logic you are validating.
All of these statements are evaluated. There is not an implied "and" or "or" combining them. In this last example, if you were checking the "State = ... OR TaxRate is blank", both would fail. You could check the number of errors and would get 2, even though only 1 error message would be shown (the first one).
Jerry Thomas
Software Design Engineer in Test
Comments
Anonymous
March 23, 2007
Hello, I have a question regarding Case Study #2, the first screenshot. You want to achieve this logic: IF (State="Ohio" or State="Alabama") and TaxRate is blank THEN (fail...) I have built this exact data validation rule (in Infopath 2007) and I have a suspicion that behind the scenes Infopath is interpretting it as: IF State="Ohio" or (State="Alabama" and TaxRate is blank) THEN (fail...) by giving the "AND" preference in the order of operations. This means that it throws a validation error whenver Ohio is selected, regardless of what Tax Rate is. However, the Alternative Approach is exactly what I'm looking for and I will use that going forward. Thanks for the info! -AndyAnonymous
July 05, 2010
Looks like I'm about 4 years late with this comment, but it would make a lot more sense to use regular expressions for these specific examples. Something like... State matches expression "Alabama|Arizona|Georgia|Utah" would cover all 4 tests in one rule and is much easier to follow/debug. While I'm certainly no master of regular expressions, I know enough to know that they're powerful and handy.Anonymous
October 13, 2010
How to create validation rule to accommodate the following: "Account number must be between pattern 40ddddd? and pattern 50ddddd?" Does a numerical range ruleset require VB?Anonymous
October 13, 2010
p.s. Clarification, my last comment is in the context of InfoPath Designer 2010. Thanks.