Sorting Repeating and Tabular Data
InfoPath is a great way to easily gather and present XML data to the masses. But what about efficiently presenting a massive amount data? Tons of data (e.g., a list of hundreds product names) usually find their ways into lists that we’ll find on SharePoint, in a database, or just sitting in an XML file. Controls like Repeating Sections and Tables are useful mechanisms for displaying lists of data. But their usefulness is limited to how efficiently the user of your form can find and work with such large lists of data. An easy solution to effectively present large lists of data to your users is through sorting that data.
No Sorting Options in InfoPath
Try scouring the Repeating Table or Repeating Section controls’ properties dialogs for anything related to “sort”. Sorry to say, you’ll come up empty handed. That’s why we thought you’d find this blog entry handy. Our goal is to show you how to sort any repeating data and to do so in many different ways: sort by string, sort by number, sort ascending or descending, and even sort by different fields (or columns) in the repeating data. What we’ll show you, in both the code and the design of the view, is designed to be browser-compatible. This means you can publish this form template to a SharePoint server running Forms Services 2007 and fill out the form in a Web browser. Let’s get started.
Figure 1: Filling out the SortingPeople sample form template
The sample form template is attached; download the ZIP to your desktop, expand it, then right-click the XSN and select "Open in Design Mode". This sample requires InfoPath 2007, and will work in the browser when published to InfoPath Forms Services.
The View for the SortingPeople Form Template
To show you how to sort data in your form, we’ll use a concocted example to sort a list of people. As you can see in Figure 1, there are various options to sort the data. Clicking the “Sort” Button triggers the sort otherwise the data is not sorted automatically (more on this later). You can also sort by clicking any of the “Sort by this column” Buttons in the header and footer of any column. There are three columns in the Repeating Table representing our repeating data: Last Name, First Name, and Age. (The string data type is used for the first two columns and an integer data type for the last column.) In Figure 1, we’ve filled out the form with names of famous architects. (Their ages are fictitious.) While we’re not working with enough data to really justify a sort, this example is simply for demonstrative purposes. As you might expect, we can sort the Repeating Table data by any column and by any direction (ascending or descending). At any time we can change the existing data or even add or remove rows. But we would need to invoke the sorting functionality to put things into place again.
The Data Source for the SortingPeople Form Template
To begin designing this form template we started with controls and the data source. We inserted a Repeating Table control and renamed its data source groups and fields to map with our people example. The main data source is shown in Figure 2. To accommodate the various sorting options that we expose via controls at the top of the view (e.g., the Sort By Drop-Down List Box), we’ve added attribute fields under the SortingPeople document element. These attribute fields remember, for example, whether we’re sorting by the Last Name or the Age column.
Figure 2: Data source for the SortingPeople sample form template
Considerations When Sorting Data
Now we’ll look at how we implemented sorting functionality behind this form template. This is accomplished through C# code behind the form. While we could have opted for an easier XSL-based implementation to sorting data, it would not have been compatible with Forms Services. So we’re left to actually sorting the data. There are advantages as well as disadvantages to our approach. Sorting the data is preferred when you want to persist the sorting in the saved or submitted form. While the initial cost is much higher to sort the data in the data source instead of the view, there is much less processing that occurs on subsequent visits to that data because it will already be sorted. If your form template has multiple views, switching to and from a view that performs an XSL sort is very expensive. On the contrary, a sorted data source adds no additional processing requirements on view switches. A final reason why you may want to sort the data in the data source instead of the view: submitting sorted data to a database or Web service may be optimal (or even a requirement) for that backend system.
Sorting Data with C# Form Code
To best understand how we designed the code behind form template to support sorting, we’ll be talking about its C# form code and how it interacts with the main data source. Let’s start by looking at some of the supporting form code that will make it easier for us to implement the sort feature. The properties are used within the sort itself to read the options at the top of the view about how to sort the data. The methods are very useful helpers that we use throughout our sample code.
/// <summary>
/// Returns the "Sort By" Drop-Down value.
/// The value returned by this property MUST match with an item within the Repeating Table.
/// </summary>
private string SortBySelection
{
get { return GetValue("@my:SortBy").Replace(" ", string.Empty); }
}
/// <summary>
/// Does the user want the SortBy by number (true) or string (false)?
/// </summary>
private bool SortAsNumber
{
get
{
return 0 == GetValue("@my:SortAs").CompareTo("number");
}
}
/// <summary>
/// Does the user want an ascending (asc) or descending (des) sort?
/// </summary>
private bool SortAscending
{
get
{
return 0 == GetValue("@my:Order").CompareTo("asc");
}
}
/// <summary>
/// Helper to wrap an int within brackets.
/// </summary>
/// <param name="intToWrap"></param>
/// <returns></returns>
private string WrapAsIndexer(int intToWrap)
{ return WrapAsIndexer(intToWrap.ToString()); }
/// <summary>
/// Helper to wrap a string within brackets.
/// </summary>
/// <param name="strToWrap"></param>
/// <returns></returns>
private string WrapAsIndexer(string strToWrap)
{ return "[" + strToWrap + "]"; }
/// <summary>
/// Helper to get an XPathNavigator's value.
/// </summary>
/// <param name="xpath"></param>
/// <returns></returns>
private string GetValue(string xpath)
{
return Root.SelectSingleNode(xpath, NamespaceManager).Value;
}
/// <summary>
/// Helper to set an XPathNavigator's value.
/// </summary>
/// <param name="xpath"></param>
/// <param name="value"></param>
private void SetValue(string xpath, string value)
{
Root.SelectSingleNode(xpath, NamespaceManager).SetValue(value);
}
/// <summary>
/// Helper to get the document element of the main data source.
/// </summary>
private XPathNavigator Root
{
get { return CreateNavigator().SelectSingleNode("/my:SortingPeople", NamespaceManager); }
}
Next, let’s take a look at the code behind all of the Buttons in our form template. We created these event handlers through each Button’s properties dialog. Their implementations are quite trivial. You can see that adding additional sorting columns to the Repeating Table is a simple task. If you wanted to add a column that doesn’t need to be sorted, there’s nothing to do beyond adding the column in the Table!
public void SortButton_Clicked(object sender, ClickedEventArgs e)
{
SortList();
}
public void LastNameSort_Clicked(object sender, ClickedEventArgs e)
{
SetValue("@my:SortBy", "Last Name");
SortList();
}
public void FirstNameSort_Clicked(object sender, ClickedEventArgs e)
{
SetValue("@my:SortBy", "First Name");
SortList();
}
public void AgeSort_Clicked(object sender, ClickedEventArgs e)
{
SetValue("@my:SortBy", "Age");
SortList();
}
Now the million dollar question: what’s behind the SortList method? Let’s look and then we’ll explain how it works.
/// <summary>
/// Bubble sorts the list of people.
/// </summary>
private void SortList()
{
string sortBy = SortBySelection;
string itemsToSort = "my:People/my:Person";
System.Globalization.CultureInfo currentThreadCulture =
System.Threading.Thread.CurrentThread.CurrentCulture;
int numPeople = Root.Select(itemsToSort, NamespaceManager).Count;
// basic bubble sort implementation
for (int i = 1; i < numPeople; i++) // xpath is 1-based
{
for (int j = i + 1; j <= numPeople; j++) // keep j ahead of i; we can index [numPeople]
{
// swap (i,j) if necessary
string iValue = GetValue(itemsToSort + WrapAsIndexer(i) + "/my:" + sortBy);
string jValue = GetValue(itemsToSort + WrapAsIndexer(j) + "/my:" + sortBy);
// Do we sort by number or string?
if (SortAsNumber)
{
int iNum, jNum;
if (!Int32.TryParse(iValue, out iNum) || !Int32.TryParse(jValue, out jNum))
{
// Let InfoPath take care of the invalid datatype with its own validation, we'll keep sorting the rest
continue;
}
if ((SortAscending && iNum > jNum) || (!SortAscending && iNum < jNum))
{
Swap(itemsToSort + WrapAsIndexer(i), itemsToSort + WrapAsIndexer(j));
}
}
else // SortAsString
{
if ((SortAscending && String.Compare(
iValue, jValue, true /*ignoreCase*/, currentThreadCulture) > 0)
|| (!SortAscending && String.Compare(
iValue, jValue, true /*ignoreCase*/, currentThreadCulture) < 0))
{
Swap(itemsToSort + WrapAsIndexer(i), itemsToSort + WrapAsIndexer(j));
}
}
} // end inner-for
} // end outer-for
}
Analyzing the C# Form Code
Let’s break down what we’re doing in this SortList method. First we get the column to use for sorting, the XPath to the repeating group that we want to sort, and the culture of the thread so we respect the current locale when sorting. Next we get the number of people that we’ll be sorting. We need this number because we’ll use it for our bubble sort implementation.
The two nested for-loops implement the bubble sort algorithm. We chose bubble sort because of its simplicity and for demonstrative purposes. (We’d recommend you use the most efficient sorting algorithm based on your requirements.) The variables i and j iterate through the people. We use the iValue and jValue variables to select the data pointed at by i and j to determine if a swap is necessary as part of the sort loop.
Next, we have an if-else statement that checks if the sort is by string or by number. A sort by number will attempt to parse out 32-bit integer values from the iValue and jValue fields. If the parse fails for any reason, we skip this specific comparison and continue trying to sort the rest of the data. Once we have integers, we do a simple comparison and swap if needed. If we’re sorting by string instead of numerical value, we use the static .NET library String.Compare method to make a culture sensitive comparison. A swap is performed if it’s necessary. (Note that we could have combined some code in the SortList method to make it more compact. We left the structure of the code unoptimized for maximum readability.)
The last bit of code we have not yet revealed is the Swap method. This method, as its name suggests, simply swaps the positions of two XPathNavigator objects as identified by their XPaths. (An XPathNavigator is a pointer into an XML tree of data. You can read more about the XPathNavigtor class on MSDN.)
/// <summary>
/// Swaps two XPathNavigators at xpath1 and xpath2.
/// </summary>
/// <param name="xpath1">First XPath.</param>
/// <param name="xpath2">Second XPath.</param>
private void Swap(string xpath1, string xpath2)
{
XPathNavigator item1 = Root.SelectSingleNode(xpath1, NamespaceManager);
XPathNavigator item2 = Root.SelectSingleNode(xpath2, NamespaceManager);
// Make a copy of item1
XPathNavigator item1Clone = item1.Clone();
// Move item2 to item1
item1.ReplaceSelf(item2);
// Make the original item2 be item1 that we cloned earlier
item2.ReplaceSelf(item1Clone);
}
Sorting Automatically
One of the things you might ask is why we decided against sorting the data automatically. The most important reason is user experience and then followed by form responsiveness. Think about what would happen if the Repeating Table rows sorted themselves whenever data changed that required a sort. Say you were entering data into a new row. You start by typing a person’s last name and then either hit tab or click into the First Name field. If the form is sorting by the Last Name field, the row may have jumped to another location relative to the other rows in the Repeating Table! You would expect that you could fill in all of the data for a new row before it sorts itself. There are also other weird cases that automatic sorting would spoil. For example, you would not see a new row added within the middle of the Repeating Table. Why? As soon as you added it, it immediately jumped to the top (or bottom) of the Repeating Table as sorted data. Let’s consider for a moment why we didn’t automatically sort because of form responsiveness; for now let’s assume that we somehow worked out all of the kinks with the user model. Obviously the form will be less responsive in InfoPath, especially on a slow computer, with so much sorting. But the real problem is realized when filling out such an intense form template in a Web browser. Every time code runs while a form is filled out in a browser, the form data is posted back to the server for processing. Postbacks themselves can take several seconds or even minutes depending on many variables including the network connection speed as well as the client and server machines’ performance capabilities. As you can see, an automatically sorting form isn’t necessarily a better form.
Hagen Green
Software Design Engineer in Test
Comments
Anonymous
December 15, 2006
If you have ever attempted to sort repeating node data (i.e. a Repeating Table or Repeating Section)Anonymous
January 23, 2011
This form cannot be opened because it requires the domain permission level and it currently has restricted permission. To fix the problem, open the form from the location it was published to. Publish location: file:////C:Documents%20and%20SettingsalexweinDesktopblogsortTableSortSample.xssAnonymous
November 16, 2011
I get the same error trying to open the formAnonymous
September 16, 2012
Hi Hagen, Can you provide the same example in VB.net language please? I'm not very familiar with this languege but even less with C#. Thanks FlipAnonymous
February 10, 2015
Thank you for this example. I wonder if you could answer this: How are you pulling in the names? From an XML? How did you set it up. I downloaded the file, but I don't see a DataConnection.I don't see you reference any xml in the code. Thanks