We recently had a bit of a challenge with one of our internal forms - we wanted to easily be able to re-order some repeating rows, and the approaches we'd tried thus far (using the out of the box InfoPath Move Up and Move Down menu options, little arrow buttons that used qRules to change the value of a field for order and re-sorting, etc.) were not exactly making our collective hearts sing. A bit of a re-think was in order!
I like the way SharePoint lets me sort columns in a view:
I can indicate the position I want, and the other columns automatically get their new order number based off the position I've selected:
Now, I confess - I'm not crazy about a dropdown for this, so I didn't bother with that in this implementation - however, I will address some basic error handling around invalid data entry. The form samples attached to this blog post use a trial of qRules that expires 3/15/2013 - if you are trying this after that date, you'll need to re-inject with a new trial - here's a link to the trial download. Also, the form samples are InfoPath 2007 and are not browser forms - you can try this in IPFS if you'd like, but I am using attributes to hold some data to help with re-ordering, and if you decide to try to implement this in the browser, I'd recommend using elements instead of attributes, since I have seen some event bubbling in IPFS with attributes in the past (i.e. - a changed attribute causing logic on its parent element to execute).
I'm starting with this form. I've added default data so that I don't have to bother filling it out as I test it:
The initial data source is relatively simple:
I like to use an XML secondary data source for logic fields when possible (that is, fields needed for rules, but not really relevant to the form's data) - so here's the XML:
Add a secondary data connection to your form that uses this XML:
Here's the form with that added, in case you need it.
Okay - I just said above that I like to use secondary data sources for fields that are just for logic and here I go breaking my own little rule - but sometimes you have to make exceptions for repeating data because you have logic that needs to be executed on this instance of the group. In this case, I am adding 3 attributes to my Order element to help me in my endeavors:
Here's the form again with those attributes added.
Since I'm using default data, I've set the initialOrder for each of my existing rows manually. However, in order to handle for newly added rows, we need a rule to set the Order and the initialOrder when a new row is added. I've added a rule to the repeating Book node for this:
All the pieces are in place, so let's take a look at the rules and test it out. Our goal is to be able to change the order number for a row and:
- Have the other rows Order numbers update as needed
- Resort the rows to put them in the desired order
The first rule I'm going to add is on the Order element - I've named it Trigger Increment, and I'm adding a condition on it - I only want it to run when the EditingOrderPosition field in my secondary FormLogic data source is blank:
I want this rule to execute only when I change the Order field myself - I'm using the EditingOrderPosition condition to prevent creating a loop, because I know that my rules are going to cause other instances of the Order field to change. So, the first action I'll add to my rule is to set that field's value:
I am setting it to the count of preceding-sibling Book nodes - I'll use this in another condition later. If your repeating data has a unique identifier, you could also leverage that here - set EditingOrderPosition to the field with the unique identifier and then, in the condition check on the editing attribute, ensure the row does not have the same unique identifier.
Next, I set the increment attribute to the Order field minus the initialOrder attribute - the initialOrder attribute contains the original value of the Order field, and after the user changes the Order field, the difference between the two will let us know how we need to increment our other fields.
When the form is running, this is the point where the rule on the increment attribute will fire, so let's go take a look at those and we'll come back to the rules on Order after we walk through the rest of the logic.
There are two rules on the increment field - one if increment is less than zero and one if it is greater than zero. I've also included a 2ds field named Sorting in my condition - I use that when I sort the data to prevent rules from firing during the sort (however, I've done a little additional testing, and it appears it is not necessary at this level - only at the Book level if I wanted to prevent that logic from firing).
The Negative Increment ruleset (runs if the value of increment is negative) looks like this:
First, I'm setting a helper field in my 2ds to the correct amount to increment the field. Then, since our Order field is less than its initialOrder (i.e., the increment attribute is negative), I need to add one to all Order fields that are greater than or equal to the current value of the Order field I just modified and less than the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 4 (with the initialOrder also being 4) and I change Order to 1, any field with a value greater than or equal to 1 and less than 4 needs to have 1 added to it. Whew.
In the rule above, I am setting the editing attribute for all fields that meet that condition to true, to execute another rule that increments the row order:
This adds the current OrderIncrement to the Order - but note the condition to that will prevent the rule from executing if the field is the same that initiated the sequence (mentioned above - if you used a unique identifier instead of a count of preceding-sibling, you'd use that for the condition instead of the count):
A second rule re-sets the editing field back to false if it is true:
Back on the increment field, the rules for Positive Increment are similar to the ones for Negative Increment:
The condition in this case is that the increment attribute is greater than 0 (along with Sorting being blank), and I set the OrderIncrement helper field to -1. In this case, since our Order field is greater than its initialOrder (i.e., the increment attribute is positive), I need to subtract one from all Order fields that are less than or equal to the current value of the Order field I just modified and greater that the initialOrder attribute of the Order field I just modified. So, if my Order had the value of 1 (with the initialOrder also being 1) and I change Order to 4, any field with a value less than or equal to 4 and greater than 1 needs to have 1 subtracted from it.
Guess what? We are finally back to the rules on the Order field! The remaining actions in the ruleset are pretty straight-forward:
I set my helper node, EditingOrderPosition back to blank. I set my Sorting node to true (to prevent rules firing during sorting that I don't want to have fire), and then execute a qRules command to sort the rows based on Order:
Finally, prior to my Trigger Increment ruleset, I have a rule to handle for invalid data. The condition ensures that the user has entered a positive number:
If they have not, a message is displayed and the Order is set back to the initialOrder:
Note the use of the "Don't run remaining rules if the condition of this rule is met" checkbox - we don't want to execute the Trigger Increment rule unnecessarily. Here's the final form - I encourage you to download it to walk through the rules, since this blog post is not so much a step-by-step how-to (that would have been mind-numbingly long) and more of a guideline.
A few more points of interest on this form - so far, I am also able to use the InfoPath Move Up and Move Down right-click menu widgets with this (due to the re-numbering logic on the Book node) and the right-click Insert options also work well - adding a new row in-between existing rows allows all Order nodes to update their values. However, I don't have anything to handle for delete / cut. The logic is somewhat self-correcting - if you delete a row the numbers after it will be off only until a new row is added or the rows are re-sorted, but it is a weakness I wanted to highlight. If you can think of a good way to handle for this, leave a comment and let me know!
When we use qRules SortTable, the nodes that are being sorted are actually deleted and re-added. I could have used my Sorting helper node to prevent the rules on the Book node from firing - but then I realized I could leverage this behavior to reset my initialOrder attribute as well as handle for user entries that are greater than the number of items in the table - that is, if a user enters a too-high number, the rules on Book that fire during the sorting will correct the number.
Also - I think this could be implemented without qRules (although I haven't tried) using this method to set the correct editing attributes and manually modifying the view to include xsl:sort and a preserve code block (this won't be browser compatible).