none
Sorting a browser based form drop-down list alphabetically.

    Question

  • Hi,
     
    When I open a form that connects to a library to populate a dropdown list in Infopath the dropdown sorts by the default sort column. This is exactly what I want it to do.

    When  I open the form in a browser the items sort in the order they were added to the library. This is not what I want it to do.

    I edited my view1.xsl for the form and added a xsl:sort :-

    <xsl:for-each select="xdXDocument:GetDOM(&quot;Asset Register&quot;)/dfs:myFields/dfs:dataFields/dfs:Asset_Register">
        <xsl:sort select="."/>
        <option>
        <xsl:attribute name="value">
            <xsl:value-of select="@Asset_Name"/>
        </xsl:attribute>
        <xsl:if test="$val=@Asset_Name">
            <xsl:attribute name="selected">selected</xsl:attribute>
        </xsl:if>     
        <xsl:value-of select="@Asset_Name"/>
        </option>
    </xsl:for-each>

    The line I added is in bold. The form saves fine and I can open in Infopath with no errors. However when I try to publish the form via 

    Central Administration > Application Management > Manage Form Templates > Upload Form Template

    I get the following error "Unexpected element {http://www.w3.org/1999/XSL/Transform}sort encountered."

    Can anyone help?

    Cheers Dave



    Friday, November 28, 2008 12:31 PM

Answers

  • Hi All,

    I spent the whole of this morning working out how to actualy do this and my solution was to sort my datasource in the FormEvents_Loading event method.

    Add a FormEvents_Loading event handler and method by selecting Tools > Programming > Loading Event

    Create a method SortDataSource which we will call from FormEvents_Loading like so :-

     public void FormEvents_Loading(object sender, LoadingEventArgs e)  
            {  
                SortDataSource();  
            } 


    Here is the method that sorts the data.

     
     
            public void SortDataSource()  
            {  
     
                  // Creates an XPath Navigator copy of the datsource the drop down is bound to. Replace  
                  // Asset Register with the name of your datasource.  
                  XPathNavigator datasource = this.DataSources["Asset Register"].CreateNavigator();  
     
                  // Creates an XML document so we can create a namespace manager  
                  System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();  
                  xmlDoc.LoadXml(datasource.InnerXml);  
     
                  // Namespace manager so the the dfs namespace is recognised. Note this is because my   
                  // data source comes from a sharepoint list you may need to change the namespace values.  
                  XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);  
                  nsmgr.AddNamespace("dfs""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution");  
     
                  // Create tje XPathExpression that can be used to sort the XML  
                  XPathExpression expr;  
                  // Replace dfs:myFields/dfs:dataFields/dfs:Asset_Register with the XPath to your  
                  // datasource can be found by selcting properties of your dropdown  
                  // copy the value from the Entries field on the data tab remove the first /  
                  expr = datasource.Compile("dfs:myFields/dfs:dataFields/dfs:Asset_Register");  
                  expr.SetContext(nsmgr);  
     
                  // change @Asset_Title to the value you want to sort by can be found by  
                  // selcting properties of your dropdown copy the value from the Display name field  
                  expr.AddSort("@Asset_Title", XmlSortOrder.Ascending, XmlCaseOrder.None, "", XmlDataType.Text);  
     
                  // Executes the sort  
                  XPathNodeIterator iterator = datasource.Select(expr);  
                  
                  // Group 1 used to add the sorted data to the datasource change value realtive to above  
                  XPathNavigator group1 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields", nsmgr);  
                  // Group 2 used to delete the sorted data change value realtive to above  
                  XPathNavigator group2 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields/dfs:Asset_Register", nsmgr);  
     
                  // Count the number of items so we can delete them all.  
                  XPathNodeIterator iter = datasource.Select("//dfs:myFields/dfs:dataFields/dfs:Asset_Register", nsmgr);  
                  int group2NodesCount = iter.Count;  
     
                  // Find the last node to delete and delete.  
                  XPathNavigator field1 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields/dfs:Asset_Register[" + group2NodesCount.ToString() + "]", nsmgr);  
                  group2.DeleteRange(field1);  
     
                  // Add the sorted data via group 1.  
                  foreach (XPathNavigator item in iterator)  
                  {  
                      group1.AppendChild(item.OuterXml);  
                  }  
                          
            } 


    This has been driving me, and going by other posts on the internet alot of other people, mad!! I haven't refactored my code yet this was my first attempt, I will be creating a reusuable methods that I can pass values to for differnent datasources and probably renaming some of my objects. But feel free to give it a try hope it helps someone!

    • Marked as answer by DaveC1982 Wednesday, February 11, 2009 3:12 PM
    Wednesday, February 11, 2009 3:09 PM
  • Hi all,

    If I want a drop down list in InfoPath (data coming from a SharePoint list) to be in a particular order I query the SharePoint list via this method

    http://blogs.msdn.com/infopath/archive/2007/01/15/populating-form-data-from-sharepoint-list-views.aspx

    You can create a data connection to a particular view in SharePoint - make sure that view is sorted how you want it to be and the item limit is set to 9999.

    You do not need code for this method and it works in browser enabled forms.
    • Marked as answer by DaveC1982 Tuesday, February 17, 2009 10:35 AM
    Monday, February 16, 2009 10:01 PM
  • Here is my answer :-

    "Moreover, view-based sorting is not supported in browser-enabled form templates because it requires manual hand editing of the view XSL file."

    Ref http://www.informit.com/content/images/9780321410597/samplechapter/roberts_ch15.pdf

    • Marked as answer by DaveC1982 Friday, November 28, 2008 1:17 PM
    Friday, November 28, 2008 1:17 PM

All replies

  • Here is my answer :-

    "Moreover, view-based sorting is not supported in browser-enabled form templates because it requires manual hand editing of the view XSL file."

    Ref http://www.informit.com/content/images/9780321410597/samplechapter/roberts_ch15.pdf

    • Marked as answer by DaveC1982 Friday, November 28, 2008 1:17 PM
    Friday, November 28, 2008 1:17 PM
  • Hi All,

    I spent the whole of this morning working out how to actualy do this and my solution was to sort my datasource in the FormEvents_Loading event method.

    Add a FormEvents_Loading event handler and method by selecting Tools > Programming > Loading Event

    Create a method SortDataSource which we will call from FormEvents_Loading like so :-

     public void FormEvents_Loading(object sender, LoadingEventArgs e)  
            {  
                SortDataSource();  
            } 


    Here is the method that sorts the data.

     
     
            public void SortDataSource()  
            {  
     
                  // Creates an XPath Navigator copy of the datsource the drop down is bound to. Replace  
                  // Asset Register with the name of your datasource.  
                  XPathNavigator datasource = this.DataSources["Asset Register"].CreateNavigator();  
     
                  // Creates an XML document so we can create a namespace manager  
                  System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();  
                  xmlDoc.LoadXml(datasource.InnerXml);  
     
                  // Namespace manager so the the dfs namespace is recognised. Note this is because my   
                  // data source comes from a sharepoint list you may need to change the namespace values.  
                  XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);  
                  nsmgr.AddNamespace("dfs""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution");  
     
                  // Create tje XPathExpression that can be used to sort the XML  
                  XPathExpression expr;  
                  // Replace dfs:myFields/dfs:dataFields/dfs:Asset_Register with the XPath to your  
                  // datasource can be found by selcting properties of your dropdown  
                  // copy the value from the Entries field on the data tab remove the first /  
                  expr = datasource.Compile("dfs:myFields/dfs:dataFields/dfs:Asset_Register");  
                  expr.SetContext(nsmgr);  
     
                  // change @Asset_Title to the value you want to sort by can be found by  
                  // selcting properties of your dropdown copy the value from the Display name field  
                  expr.AddSort("@Asset_Title", XmlSortOrder.Ascending, XmlCaseOrder.None, "", XmlDataType.Text);  
     
                  // Executes the sort  
                  XPathNodeIterator iterator = datasource.Select(expr);  
                  
                  // Group 1 used to add the sorted data to the datasource change value realtive to above  
                  XPathNavigator group1 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields", nsmgr);  
                  // Group 2 used to delete the sorted data change value realtive to above  
                  XPathNavigator group2 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields/dfs:Asset_Register", nsmgr);  
     
                  // Count the number of items so we can delete them all.  
                  XPathNodeIterator iter = datasource.Select("//dfs:myFields/dfs:dataFields/dfs:Asset_Register", nsmgr);  
                  int group2NodesCount = iter.Count;  
     
                  // Find the last node to delete and delete.  
                  XPathNavigator field1 = datasource.SelectSingleNode("//dfs:myFields/dfs:dataFields/dfs:Asset_Register[" + group2NodesCount.ToString() + "]", nsmgr);  
                  group2.DeleteRange(field1);  
     
                  // Add the sorted data via group 1.  
                  foreach (XPathNavigator item in iterator)  
                  {  
                      group1.AppendChild(item.OuterXml);  
                  }  
                          
            } 


    This has been driving me, and going by other posts on the internet alot of other people, mad!! I haven't refactored my code yet this was my first attempt, I will be creating a reusuable methods that I can pass values to for differnent datasources and probably renaming some of my objects. But feel free to give it a try hope it helps someone!

    • Marked as answer by DaveC1982 Wednesday, February 11, 2009 3:12 PM
    Wednesday, February 11, 2009 3:09 PM
  • Hi all,

    If I want a drop down list in InfoPath (data coming from a SharePoint list) to be in a particular order I query the SharePoint list via this method

    http://blogs.msdn.com/infopath/archive/2007/01/15/populating-form-data-from-sharepoint-list-views.aspx

    You can create a data connection to a particular view in SharePoint - make sure that view is sorted how you want it to be and the item limit is set to 9999.

    You do not need code for this method and it works in browser enabled forms.
    • Marked as answer by DaveC1982 Tuesday, February 17, 2009 10:35 AM
    Monday, February 16, 2009 10:01 PM
  • Hi,

    There seems to be a bug to watch out for with the owssvr.dll method if you use the All Documents view and sort it by the column you want it seems to still sort by the order the items were added to the list. Has anyone else noticed this? I had to create a new view to get it to work.

    I had given up on the owssvr.dll method in the past for exactly this reason!

    Also the programatic approach may be of benifit if you wish to change the sort order of the data source at runtime within your form. However I would say that the methos suggested by Lanijade is probably less CPU intensive at form runtime which is espeicaly benifitial when dealing with large lists.
     
    One other problem I find with the owssvr.dll method is the unfriendly column names!

    It is going to be a nightmare developing a from against a development environment and then moving it to production as the GUIDs for data sources may change can anyone sugest a good way of managing this?

    Cheers Dave
    Tuesday, February 17, 2009 10:36 AM
  • Hi Dave,

    What I do to get round this on my dev system (MS VPC2007 with AD & SQL 2005) is:

    1) Create web app with same DNS name as 'real' one

    2) Backup live site using stsadm

    3) Restore on dev system using stsadm

    Lo and behold, all the GUIDs are the same, all the URLs are the same, and everything works fine.  Have been doing this a lot recently for 7 form solutions at a client.

    HTH,

    Rich.
    Wednesday, April 08, 2009 1:20 PM
  • Dave,

    Please excuse my ignorance; I'm new to InfoPath. After selecting Tools > Programming > Loading Event, InfoPath opens a VB window. How do I get it to open a C# window instead? Your code looks very useful, but I don't know enough to convert between VB and C#. Thank you.

    Rob 
    Tuesday, August 18, 2009 4:00 PM
  • You shouldn't have to convert the code.  It works in the VB window.
    Tuesday, December 08, 2009 4:07 PM
  • Go to Tools > Form Options > Programming > and set C# as your default language.  Then, when edit form code, it will default to C# instead of VB.  However, you should not need to use code to accomplish this task.  There is a non-code method given above unless you need code for other reasons.

    This should probably be a separate thread as its own question, since others are unlikely to know this is buried deep within a separate question.
    SharePoint Architect || My Blog
    Tuesday, December 08, 2009 8:25 PM
  • Thank you for the wonderful post. Your post saved me 2 days of work.

     

    Wednesday, April 21, 2010 2:59 PM