Cascading drop-down list boxes in InfoPath 2010 – the non-traditional way
Learn about a non-traditional way of setting up cascading drop-down list boxes in InfoPath 2010 and how you can avoid infinite loops when running rules on fields that set each other's values.
This article is based on a suggestion/improvement/extension to one of the recipes in my InfoPath 2010 Cookbook that was submitted to me by one of my Greek readers who wanted to remain anonymous, but asked me to share this information with you.
Much has already been written about cascading drop-down list boxes and one of the main improvements of InfoPath 2010 compared to InfoPath 2007 is that you can now create cascading drop-down list boxes for browser forms without having to resort to code or web services. You can see how this can be done in this video of the InfoPath Team.
Traditionally, cascading drop-down list boxes work in a linear way in that you select an item from a first drop-down list box, which populates a second drop-down list box with only those items that are related to the item selected in the first drop-down list box. You can continue this linear populating of drop-down list boxes using filters on the data source that supplies the entries for the drop-down list boxes.
The type of cascading drop-down list boxes I’m writing about in this article are slightly different in that the selection and filling of the drop-down list boxes are non-linear as follows:
- The first drop-down list box is populated with employee numbers, and each employee number is unique and belongs to one and only one employee.
- You select an employee number from the first drop-down list box upon which the second and third drop-down list boxes are not only populated with first and last names, but also their values are immediately set to the correct first and last name of the employee whose number you selected in the first drop-down list box.
But here comes the twist to this solution: There are people in companies who have the same first or last name, for example: John Doe, John Kettle, Jane Doe, Jane Boss, etc. Where this solution deviates from the traditional cascading drop-down list boxes, is that when you select an employee number from the first drop-down list box, and "John Doe" appears, when you open the second drop-down list box that has a current value of "John", you will also see that there is a "Jane Doe" in the company. And when you open the third drop-down list box that has a current value of "Doe", you will also see that there is a "John Kettle" in the company.
Since it is a bit difficult to explain how exactly this works, I created the following demo video to show you:
How this solution works is when you select an item from any one of the drop-down list boxes, it sets the values of the other two drop-down list boxes, and then the first and last name drop-down list boxes are populated based on the values that they were set to. For example, the last name drop-down list box is populated and filtered based on the value of the first name selected in the second drop-down list box, and the first name drop-down list box is populated and filtered based on the value of the last name selected in the third drop-down list box. All values are set using rules.
As you may already know – and if you’ve gone through recipe 67 of InfoPath 2010 Cookbook, you may have already read – that an action rule on a field is triggered whenever the value of that field changes. So in the solution described above you could wind up in a loop where one field is trying to set another which is trying to set the one that is trying to set it. And when this happens, InfoPath will eventually show you an error saying:
Some rules were not applied
An error occurred in the form’s rules or code. The number of rule actions or the number of calls to the OnAfterChange event for a single update in the data exceeded the maximum limit.
The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or event handler does not update the data which causes the same rule action or event handler to execute.
This problem is not inherent to cascading drop-down list boxes alone, but to all fields in InfoPath in general. Whenever a value change occurs, the rules on a field get fired. And if you have fields setting each other’s values, you can wind up in an endless loop.
The solution proposed by my Greek reader was to make use of a "controller" field; a field that says something like: "Hey, there is a field that is currently running a rule, so all other fields must wait until I say that it is safe to run rules again". This way, the rules on fields do not get in each other’s way.
The "controller" field solution works as follows:
- The value of the controller is equal to 0.
- The first drop-down list box is populated with employee numbers.
- A user selects an employee number from the first drop-down list box.
- The rule on the first drop-down list box only runs if the value of the controller is 0, which in this case it is, so the rule runs.
- The first action on the rule sets the value of the controller field to 1 as an indication that a rule is currently running and that all other fields must wait to run their rules.
- An action looks up the first name of the employee in a data source and sets the value of the second drop-down list box.
- An action looks up the last name of the employee in a data source and sets the value of the third drop-down list box.
- An action sets the value of the controller field to 0 as an indication that the rule is done running and that either the second or third drop-down list box can run their rules.
You must define similar logic as you have for the first drop-down list box ( steps 4 through 8 ) for the other 2 drop-down list boxes to restrict their rules from running depending on the value of the controller field. This way, each rule (and field) can do its work without getting in the way of another rule (or field).
A special thanks goes to my Greek reader for suggesting and wanting to share this solution.
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.