Another Way of Sorting Repeating Data
If you have ever attempted to sort repeating node data (i.e. a Repeating Table or Repeating Section) on an InfoPath form, you will find this functionality is not available through the UI. However, using .NET classes you can easily implement a sorting routine that will work in both client and browser scenarios. We discussed one way to make this happen through custom code in a recent article; this post will show a different way to make it happen. We will take a look at how to implement this functionality along with taking advantage of some new features in InfoPath 2007:
- Dynamic button labels
- Complex default value on the Button label to change the caption based on the sort order of the selected field (discussed in this article)
In this sample scenario, let’s assume you are capturing the following data in a Repeating Table:
- Last Name (name: LastName)
- First Name (name: FirstName)
- Age (name: Age)
In addition, you want to allow your users to select the field they want to sort on (using a button in the column header) and clicking the button will toggle the sort between Ascending and Descending. Here is a sample form showing those options:
** NOTE: Notice the “(Asc)” in the Last Name button label? We’ll show you how to do that at the end of this post!
The data structure for the above sample is as follows:
So in this scenario, the user would simply click the button above the field they want to use to sort the data and by default, the first click would sort the data in Ascending order and clicking it again would sort the data in Descending order. Let’s now go ahead and take a look at the code on the click event of the buttons that implements this functionality.
When each button is clicked, the first thing we do is set the value of the SortOrder and SortField nodes. For ease of implementation, we created a “SpecifySortOptions” procedure that is called from the click event of each button:
SpecifySortOptions("LastName",XmlDataType.Text,e.ControlId);
When each button is clicked we call this procedure passing it the field we want to use for sorting (in this case, LastName), the data type of this field (XmlDataType.Text) and the ControlID of the button that was clicked. (The ControlID is used in the Expression for the Default Value property of each button to determine how to change the label.)
Here is the SpecifySortOptions procedure:
public void SpecifySortOptions(string SortField, XmlDataType dataType, string ControlID)
{
//Create Navigator objects for the main DOM and
//for the SortOrder and SortField fields
XPathNavigator xn = this.MainDataSource.CreateNavigator();
XPathNavigator xnSortOrder = xn.SelectSingleNode("/my:myFields/my:SortOrder", this.NamespaceManager);
XPathNavigator xnSortField = xn.SelectSingleNode("/my:myFields/my:SortField", this.NamespaceManager);
//Check to see if the value of the SortField is equal
//to the ControlID that we passed to this procedure. If
//it is the same and the SortOrder field is an SortOrder
//emptry string or is set to "Desc" then set the field to
//"Asc". If the SortField value does not equal the
//ControlID that we passed to this procedure, then that
//would mean either the SortField is an empty string or
//it was set to another field - either way, we will
//then want the SortOrder value to be "Asc"
if (xnSortField.Value == ControlID)
{
if (xnSortOrder.Value == "" || xnSortOrder.Value == "Desc")
xnSortOrder.SetValue("Asc");
else
xnSortOrder.SetValue("Desc");
}
else
xnSortOrder.SetValue("Asc");
//Call the SortTheData() procedure passing in the values
//specified above
SortTheData(SortField, xnSortOrder.Value, dataType);
//Set the SortField value to the current ControlID
xnSortField.SetValue(ControlID);
}
After calling the SpecifySortOptions procedure from the click event of each button, this procedure calls the SortTheData procedure, which accepts a string value for the sort field (strSortField), a string value for the sort order (strSortOrder) and an XmlDataType value (dataType) for the type of data being sorted. This is the code that will actually perform the sorting.
The first thing we need to do is this procedure is create “XPathNavigator” objects for the main DOM:
//Create a Navigator object for the main DOM
XPathNavigator xn = this.MainDataSource.CreateNavigator();
We then will create an XmlSortOrder object so we can specify either an Ascending or Descending sort. In this sample, we will specify an Ascending sort as the default; however, we will check the value of strSortOrder and if this is set to “Desc”, change the XmlSortOrder object accordingly:
XmlSortOrder sortOrder = XmlSortOrder.Ascending;
if (strSortOrder == "Desc")
sortOrder = XmlSortOrder.Descending;
To actually perform the sort, we will be using the “AddSort” method of an XPathExpression object – as such, we need to create an XPathExpression object for the repeating (group) node that we are going sort:
XPathExpression xe = xn.Compile("/my:myFields/my:group1/my:group2");
Now we can use the AddSort method on the Expression object using the field name (strSortField) that we passed into this procedure, the sort order using the sort order object (sortOrder) we created above and the data type using the data type object (dataType) we passed into this procedure:
xe.AddSort("*[local-name()='" + strSortField + "']", sortOrder, XmlCaseOrder.None, "", dataType);
We need to specify a NamespaceManager for the Expression object and for this we will use the SetContext method:
xe.SetContext(this.NamespaceManager);
The next step is to create an XPathNodeIterator object, passing it our XPathExpression object, so we can iterate all the nodes now that they are sorted - in addition, we will use this object (in the lastNode expression below) to get a count of the total nodes in this repeating group:
XPathNodeIterator xi = xn.Select(xe);
In the end, the way this procedure works is to delete the existing “un-sorted” nodes and add back the “sorted” nodes via the XPathNodeIterator object. So the next step is to now delete the existing “un-sorted” data. To do this, we will create XPathNavigator objects to reference the first and last nodes in this repeating group and then use the DeleteRange method to delete those nodes:
XPathNavigator firstNode = xn.SelectSingleNode("/my:myFields/my:group1/my:group2[1]", this.NamespaceManager);
XPathNavigator lastNode = xn.SelectSingleNode("/my:myFields/my:group1/my:group2[" + xi.Count + "]", this.NamespaceManager);
firstNode.DeleteRange(lastNode);
At this point, we have the sorted data in memory and the un-sorted data has been removed so we are ready to add that sorted data back to the form. For this process, we will use the XPathNodeIterator object we created earlier to iterate over the nodes.
while (xi.MoveNext())
{
//Create string variables to hold the values of each field
//as we iterate the nodes
string strLastName = xi.Current.SelectSingleNode("my:LastName", this.NamespaceManager).Value;
string strFirstName = xi.Current.SelectSingleNode("my:FirstName", this.NamespaceManager).Value;
string strAge = xi.Current.SelectSingleNode("my:Age", this.NamespaceManager).Value;
//Call the AddNewRow method to append a new row
// to the repeating group
AddNewRow(xn.SelectSingleNode("/my:myFields/my:group1", this.NamespaceManager));
//Since we are continually appending new rows, the
//"last" row will always be the one where we need
//to set the values - so here we will create a
//Navigator object for this newly added row - we
//will use this
for setting the field values below
XPathNavigator xnNewRow = xn.SelectSingleNode("/my:myFields/my:group1/my:group2[last()]", this.NamespaceManager);
xnNewRow.SelectSingleNode("my:LastName", this.NamespaceManager).SetValue(strLastName);
xnNewRow.SelectSingleNode("my:FirstName", this.NamespaceManager).SetValue(strFirstName);
//Since the Age field is numeric, it will contain
//the "nil" attribute. We need to remove this
//arrtibute prior to programmatically setting the
//value. To do this, we'll call the DeleteNil
//procedure passing it the node that contains
//(or may contain) the nil attribute
DeleteNil(xnNewRow.SelectSingleNode("my:Age", this.NamespaceManager));
//Now we can set the value of the Age field
xnNewRow.SelectSingleNode("my:Age", this.NamespaceManager).SetValue(strAge);
}
In the while loop above, we used the “AddNewRow” and “DeleteNil” procedures – these are documented below:
public void AddNewRow(XPathNavigator docXN)
{
//Create a Navigator object to reference the node
//we will be adding. To do this, we can use the
//templates' "Manifest.xsf" file to get the
//appropriate node to add. As you can see, this is
//specific to the control's "name", which you can
//get from the Advanced tab on the Properties window
//for the repeating control. Once you have this,
//use the "Save As Source Files" command from the
//File menu in InfoPath and locate the appropriate
//expression in your Manifest.xsf file
XPathNavigator xnNode = this.Template.Manifest.SelectSingleNode("//xsf:xDocumentClass/xsf:views/xsf:view/xsf:editing/xsf:xmlToEdit[@name='group2_1']/xsf:editWith/xsf:fragmentToInsert/xsf:chooseFragment/my:group1", this.NamespaceManager);
//Append the node from the Manifest file to the main DOM
docXN.SelectSingleNode("/my:myFields/my:group1", this.NamespaceManager).AppendChild(xnNode.InnerXml);
}
public void DeleteNil(XPathNavigator node)
{
//Check to see if the nil attribute exists
//and if so, delete it
if (node.MoveToAttribute("nil", http://www.w3.org/2001/XMLSchema-instance))
node.DeleteSelf();
}
And that’s it! You now have the functionality of sorting data in a repeating node. For reference, the complete code for this sample is attached.
Now – about that button label…how did we do that??!
With InfoPath 2007, we have a new feature that allows you to specify a dynamic value for the button label. To do this, simply click the “fx” button next to the label property and you can choose to use an expression of a field/group from your form:
However, for this sample the conditional logic for the button label is quite complex: we need to determine which button was clicked and whether we should show “(Asc)” or “(Desc)” next to the correct label. For this, we used the process demonstrated in this blog post: Conditional Default Values.
So here is the logic that needed to be implemented when each button is clicked; for example, for the Last Name button:
- See if the SortField value (which is set to a button’s ControlID in the SpecifySortOptions procedure) is equal to the clicked button’s ControlID and if the SortOrder value is either an empty string or equal to “Asc”
- If it is, then set the label to: Last Name (Asc)
- See if the SortField value is equal to the clicked button’s ControlID and the SortOrder value is “Desc”
- If it is, then set the lable to: Last Name (Desc)
- If neither of the above are true, then a different button must have been clicked so set the label to: Last Name
This is the logic that needs to be implemented for each button. Here is a sample expression for the LastName field:
concat(substring("Last Name (Asc)", 1, ((my:SortOrder = "" or my:SortOrder = "Asc") and my:SortField = "btnLastName") * string-length("Last Name (Asc)")), substring("Last Name (Desc)", 1, (my:SortOrder = "Desc" andmy:SortField = "btnLastName") * string-length("Last Name (Desc)")), substring("Last Name", 1, not(my:SortField = "btnLastName") * string-length("Last Name")))
Each of the above “substring” expressions are tested in order – so if the SortOrder field does not equal an empty string or does not equal “Asc” and the SortField value does not equal “btnLastName” then we test the next condition. If the SortOrder value does not equal “Desc” and the SortField value does not equal “btnLastName” then we test the last condition. And here we only need to check the value of my:SortField – if this does not equal “btnLastName” then we know a different button was clicked and we only want the label to display “Last Name”.
So there you have it! A way to sort data in your repeating table and a really cool way to let the user know which field they clicked for sorting and in which order the data has been sorted!
** NOTE: It seems we may have a bug with our expression box in that it will accept the entire conditional statement noted above but once you close and re-open the box, the string gets truncated. Once you have this working, you may want to keep that expression saved in a text file.
Scott Heim
Support Engineer