Another Way of Sorting Repeating Data

re: Another Way of Sorting Repeating Data

wizmotr — Thu, 03 Jan 2013 18:30:23 GMT

I'm new to SharePoint 2010, having never used SharePoint 2007.

Thanks so much for this code and your explanation; it was very understandable and I was able to successfully debug my code. I would however suggest modifying the comment at the start of the AddNewRow procedure to state:

//Once you have this, use the "Publish" button from the File menu in

//InfoPath and choose "Export Source Files". Then locate the

//appropriate expression in your Manifest.xsf file

The comment in the source code seems to be a holdover from SharePoint 2007 and it took me a while to figure out how to extract source files in 2010.

re: Another Way of Sorting Repeating Data

infopath1 — Mon, 02 Mar 2009 18:29:25 GMT

Hi spawforths,

What this error indicates is the "vxnNewRow" variable is not declared at the correct location. Now, if you are using the sample code I provided, then the variable name should be "xnNewRow" - did you change the variable names? If so, then it sounds like it is not declared at the correct location.

The other thing it could be is when you create that variable, it is done on this line:

XPathNavigator xnNewRow = xn.SelectSingleNode("/my:myFields/dataFields/d:Project_Hours[last()]", this.NamespaceManager);

Maybe in your case, the XPATH expression does not return any rows? If not, when you step through the code and hover your mouse over this variable (after moving past this line) if the "vxnNewRow" variable is "null" then there again is a problem with the expression.

Scott

re: Another Way of Sorting Repeating Data

spawforths — Mon, 02 Mar 2009 13:07:55 GMT

Hi Scott,

Thanks for that,

I started to change the x-path references, it seems to fail on

vxnNewRow.SelectSingleNode("@Project", this.NamespaceManager).SetValue(strProject);

But it gives me the error: The name ' vxnNewRow' does not exist in the current context.

any idea?

re: Another Way of Sorting Repeating Data

infopath1 — Fri, 27 Feb 2009 01:32:03 GMT

Hi spawforths,

It may simply be your XPATH expressions are not correct. For instance, this looks wrong:

XPathExpression xe = xn.Compile("/my:myFields/dataFields/d:Project_Hours");

If you have built your XSN from a data source (i.e. SQL), this would typically be like this:

XPathExpression xe = xn.Compile("/my:myFields/dfs:dataFields/d:Project_Hours");

To be sure of the XPATH expressions you can select the node you want in the Data Source Task Pane, right-click and choose Copy XPath.

I would first verify all your expressions and if it still fails, then you will need to debug (step through) your code to find the exact line that is failing and correct that problem.

Scott

re: Another Way of Sorting Repeating Data

spawforths — Thu, 26 Feb 2009 18:09:33 GMT

The error I receive is

nullReferenceException was unhandled by user code

re: Another Way of Sorting Repeating Data

spawforths — Thu, 26 Feb 2009 17:40:46 GMT

Thanks for that, I managed to get to work.

When I try and get it to work by altering the code for another form I struggle.

The form I am trying use is a repeatable table that submits to an SQL database.

My 6 fields are

Project

Project_FD

Date_Worked

Work_Code

Work_Description

Here is my altered code, although it fails on

if (xnSortField.Value == ControlID)

public void btnPro_Clicked(object sender, ClickedEventArgs e)

{

SpecifySortOptions("Project", XmlDataType.Text, e.ControlId);

}

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);

}

public void SortTheData(string strSortField, string strSortOrder, XmlDataType dataType)

{

//Create a Navigator object for the main DOM

XPathNavigator xn = this.MainDataSource.CreateNavigator();

XmlSortOrder sortOrder = XmlSortOrder.Ascending;

if (strSortOrder == "Desc")

sortOrder = XmlSortOrder.Descending;

XPathExpression xe = xn.Compile("/my:myFields/dataFields/d:Project_Hours");

xe.AddSort("*[local-name()='" + strSortField + "']", sortOrder, XmlCaseOrder.None, "", dataType);

xe.SetContext(this.NamespaceManager);

XPathNodeIterator xi = xn.Select(xe);

XPathNavigator firstNode = xn.SelectSingleNode("/my:myFields/dataFields/d:Project_Hours[1]", this.NamespaceManager);

XPathNavigator lastNode = xn.SelectSingleNode("/my:myFields/dataFields/d:Project_Hours[" + xi.Count + "]", this.NamespaceManager);

firstNode.DeleteRange(lastNode);

while (xi.MoveNext())

{

//Create string variables to hold the values of each field

//as we iterate the nodes

string strProject = xi.Current.SelectSingleNode("my:Project", this.NamespaceManager).Value;

string strProject_FD = xi.Current.SelectSingleNode("my:Project_FD", this.NamespaceManager).Value;

string strDate_Worked = xi.Current.SelectSingleNode("my:Date_Worked", this.NamespaceManager).Value;

string strWork_Code = xi.Current.SelectSingleNode("my:Work_Code", this.NamespaceManager).Value;

string strWork_Description = xi.Current.SelectSingleNode("my:Work_Description", this.NamespaceManager).Value;

string strBillable_Hours = xi.Current.SelectSingleNode("my:Billable_Hours", this.NamespaceManager).Value;

//Call the AddNewRow method to append a new row

// to the repeating group

AddNewRow(xn.SelectSingleNode("/my:myFields/dataFields", 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/dataFields/d:Project_Hours[last()]", this.NamespaceManager);

xnNewRow.SelectSingleNode("my:Project", this.NamespaceManager).SetValue(strProject);

xnNewRow.SelectSingleNode("my:Project_FD", this.NamespaceManager).SetValue(strProject_FD);

xnNewRow.SelectSingleNode("my:Work_Description", this.NamespaceManager).SetValue(strWork_Description);

//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:Date_Worked", this.NamespaceManager));

DeleteNil(xnNewRow.SelectSingleNode("my:Work_Code", this.NamespaceManager));

DeleteNil(xnNewRow.SelectSingleNode("my:Billable_Hours", this.NamespaceManager));

//Now we can set the value of the Age field

xnNewRow.SelectSingleNode("my:Date_Worked", this.NamespaceManager).SetValue(strDate_Worked);

xnNewRow.SelectSingleNode("my:Work_Code", this.NamespaceManager).SetValue(strWork_Code);

xnNewRow.SelectSingleNode("my:Billable_Hours", this.NamespaceManager).SetValue(strBillable_Hours);

}

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='Project_Hours_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/dataFields", 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();

}

re: Another Way of Sorting Repeating Data

infopath1 — Wed, 25 Feb 2009 16:12:29 GMT

Hi spawforths,

I apologize as I am not sure what happened to the attachment with the completed code; however, here are all the procedures as I documented them in this post. If you followed the naming conventions in this sample you should be able to copy and paste the code in each procedure. NOTE: For ease, I named by buttons as follows:

btnLastName

btnFirstName

btnAge

public void btnLastName_Clicked(object sender, ClickedEventArgs e)

{

SpecifySortOptions("LastName", XmlDataType.Text, e.ControlId);

}

public void btnFirstName_Clicked(object sender, ClickedEventArgs e)

{

SpecifySortOptions("FirstName", XmlDataType.Text, e.ControlId);

}

public void btnAge_Clicked(object sender, ClickedEventArgs e)

{

SpecifySortOptions("Age", XmlDataType.Text, e.ControlId);

}

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);

}

public void SortTheData(string strSortField, string strSortOrder, XmlDataType dataType)

{

//Create a Navigator object for the main DOM

XPathNavigator xn = this.MainDataSource.CreateNavigator();

XmlSortOrder sortOrder = XmlSortOrder.Ascending;

if (strSortOrder == "Desc")

sortOrder = XmlSortOrder.Descending;

XPathExpression xe = xn.Compile("/my:myFields/my:group1/my:group2");

xe.AddSort("*[local-name()='" + strSortField + "']", sortOrder, XmlCaseOrder.None, "", dataType);

xe.SetContext(this.NamespaceManager);

XPathNodeIterator xi = xn.Select(xe);

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);

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);

}

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();

}

Scott

re: Another Way of Sorting Repeating Data

spawforths — Wed, 25 Feb 2009 14:05:58 GMT

I have not been able to get this to work with Infopath 2007 it just comes back and says that the form cannot be opened.

Is there downloadable example of the form?

Sorting repeating table

All About SharePoint - S.S. Ahmed - MVP Microsoft SharePoint — Wed, 04 Feb 2009 04:04:29 GMT

I have already given a programmatic solution of sorting a repeating table in InfoPath but here is another

re: Another Way of Sorting Repeating Data

infopath1 — Wed, 05 Mar 2008 01:11:51 GMT

Hi BobC,

Glad you were able to get it to work.

Scott