Monday, March 29, 2010

Creating a Multiple selection List Box in browser enabled InfoPath form

InfoPath 2007 browser enabled forms (a part of InfoPath Form services) does not support Multi-select List Box control. Read some other InfoPath Form services limitations here: http://office.microsoft.com/en-us/infopath/HA102040851033.aspx

I had a tough time explaining and convincing clients about this and they got another excuse of blaming it all on Microsoft, so I finally decided to solve this problem on my own.

The overall solution looks like this:
a. Create a  custom Data structure in InfoPath 2007 browser enabled form.
b. Bind a Repeating Table with our custom Data Structure and tweak the look and feel to make it look more like a Multi-select List box.
c. Use Form Load event for programmatically loading Multi-Select List options.
d. On Submit button click, Save the multi-selected List box items as a semi-colon separated string in another InfoPath text field.

I will explain the above solution Step-by-Step with snapshots to make it more easier to implement.

a. Create a Data Structure in InfoPath 2007: See the snapshot below:

i) In this case, my field name for storing the values selected in the multi-select List items is WorkcenterAssignment. You can give any name you want to the Group and the field. Values selected by the user will be stored in this field.

ii) For MultiSelectOptions, follow the same structure as mentioned below:
-MultiSelectOptions(Repeating Group) - Will be applied as a repeating table in Step b.
    - selectedOption (True/False Boolean field) - This will be converted into Checkbox on the UI
    - optionDescription (Text string field) - This will be used for storing the multi-select List Item options

b. Bind a Repeating Table with the Data Structure
i) Right click on the above schema -> Choose Repeating table as follows:

ii) It will look like this on the InfoPath form:
iii) Format it to look more like a multi-select ListBox on your form:
Note: You can also wrap it inside a Table with a fixed width and fixed height, so that Listbox items can act like scroll bar for options)

c. Programmatically loading Multiple items in the List Box from SharePoint List/Library:
Now that our schema and control are all set, we need to load values into multiselect Listbox control.
The key event here is the InfoPath form load event. 

The strategy here is to bind the repeating table created above programmatically using Visual Studio for Applications. I prefer to write the code in C# .NET.

Here is my Form Load event, I call the generic method LoadMultiSelectListBox() method, copy the code below:

 public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {
            try
            {
                this.Errors.DeleteAll();

                    XPathNavigator root = MainDataSource.CreateNavigator();
                    XPathNodeIterator listItems = root.Select("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions", NamespaceManager);

                    if (listItems.Count == 1)
                    {
                        //Load Multi-Select List Box
                        LoadMultiSelectListBox();
                    } 
            }
            catch (Exception exp)
            {
                ThrowException(exp);
            }
        }



Here is the code for Loading Multi-Select items into the repeating table programmatically. I first need to get all my List item options from my SharePoint List/Library, I have created a XML Receive Data connection in InfoPath, you can alternatively get all the options from SharePoint List/Library using the SharePoint object model, copy code below:

        private void LoadMultiSelectListBox()
        {
            try
            {
                this.Errors.DeleteAll();

                //Query Get Approvers From SharePoint List data connection
                FileQueryConnection queryConnection = (FileQueryConnection)this.DataConnections["WorkCenterXML"];
                queryConnection.Execute();

                //Get all nodes from the Work Center XML
                XPathNavigator connectionNav = DataSources["WorkCenterXML"].CreateNavigator().SelectSingleNode("//rs:data", this.NamespaceManager);
                XPathNodeIterator allItems = connectionNav.Select("//z:row", this.NamespaceManager);

                //Iterate via all records
                foreach (XPathNavigator item in allItems)
                {
                    AddMultiSelectListItems(item);
                }

                //Delete the first row of InfoPath Repeating table programmatically
                if (GetCurrentXPathNav("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions[1]") != null)
                {
                    GetCurrentXPathNav("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions[1]").DeleteSelf();
                }
            }
            catch (Exception exp)
            {
                ThrowException(exp);
            }
        }

This method adds values into our custom repeating table data structure:

        private void AddMultiSelectListItems(XPathNavigator item)
        {
            try
            {
                this.Errors.DeleteAll();

                //Get Reference to Multi-Select List Options
                XPathNavigator Item = GetCurrentXPathNav("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions");

                //Create a new Item node for Multi-Select List items
                XPathNavigator newItemNode = null;

                if (Item != null)
                {
                    //Clones the new item w.r.t repeating table structure
                    newItemNode = Item.Clone();
                }

                //Get Reference to Option description
                XPathNavigator optionDescription = newItemNode.SelectSingleNode("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions/my:optionDescription", this.NamespaceManager);

                //Set Option description values
                optionDescription.SetValue(item.GetAttribute("ows_Workcenter", String.Empty));

                //Add a new Item in the Multi-Select List options
                Item.InsertAfter(newItemNode);

                optionDescription = null;
                newItemNode = null;
            }
            catch (Exception exp)
            {
                ThrowException(exp);
            }
        }

Updated as on 5th April, 2010: GetCurrentXPathNav is a generic method used to get the handler on the particular node/field in InfoPath, copy the code below:

///
/// Get XPath Navigator gets the Navigator object for the passed in XPath expression.
/// This method should be used as a helper function at all times
///
/// XPathNavigator
public XPathNavigator GetCurrentXPathNav(String XPath)
{
    XPathNavigator DataSource = MainDataSource.CreateNavigator();
    XPathNavigator XPathNav = DataSource.SelectSingleNode(XPath, NamespaceManager);
    return XPathNav;
}

Preview your InfoPath form, your control should load all the options dynamically and look like this:

d. Saving the values selected from the Multi-select List box item on saving the form:
Assume user selects multiple items, we will have to save the values selected in our Multi-select Listbox control using a separate field in the InfoPath form, in this example, we created the WorkcenterAssignment text field in Step a.

Again we will use the programming approach to iterate through the repeating table nodes, identify the list items selected and store them as semi-colon (;) separated values in the WorkcenterAssignment InfoPath field.

WorkcenterAssignment can be further promoted as a SharePoint column using Property Promotion feature in the InfoPath form. To know more on publishing and deployment of InfoPath forms, see my detailed post on Publishing and Deploying browser based InfoPath forms


Here is the code to Save the selected Multi-select Listbox items:

private void SaveMultiSelectListItems()
        {
            try
            {
                this.Errors.DeleteAll();

                XPathNavigator root = MainDataSource.CreateNavigator();
                XPathNodeIterator listItems = root.Select("/my:Request/my:WorkCenterAssignments/my:MultiSelectOptions", NamespaceManager);

                String optionsSelected = String.Empty;
                StringBuilder selectedListItems = new StringBuilder(String.Empty);

                int counter = 0;

                while (listItems.MoveNext())
                {
                    optionsSelected = listItems.Current.SelectSingleNode("my:selectedOption", NamespaceManager).Value;

                    //Check whether the Check box against the option was selected or not
                    if (Boolean.Parse(optionsSelected) == true)
                    {
                        if (counter == 0)
                        {
                            selectedListItems.Append(listItems.Current.SelectSingleNode("my:optionDescription", NamespaceManager).Value);
                        }
                        else
                        {
                            selectedListItems.Append("; " + listItems.Current.SelectSingleNode("my:optionDescription", NamespaceManager).Value);
                        }

                        //increment the counter
                        counter++;
                    }

                    //Clear variable
                    optionsSelected = String.Empty;
                }

                //Set all the multi-selected List Box values
                GetCurrentXPathNav("/my:Request/my:WorkCenterAssignments/my:WorkcenterAssignment").SetValue(selectedListItems.ToString());
            }
            catch (Exception exp)
            {
                ThrowException(exp);
            }
        }


I think the above code is self-explanatory. This is how we simulate a Multi-select List control using the repeating table model and dynamic options loading in a browser enabled InfoPath form.

Feel free to share your comments.


References: InfoPath Team blog

30 comments:

  1. Hi Mehul,

    Great post!

    The code above is self-explanatory, I'm just a bit confused on the GetCurrentXPathNav. This seems to be a method, or is it a part of the System.Xml.XPath Namespace? Can you please elaborate on that some more.

    Thanks!

    ReplyDelete
  2. Hi,

    The GetCurrentXPathNav is a generic method that I have used to get the node handle. Here is the full code for it:

    ///
    /// Get XPath Navigator gets the Navigator object for the passed in XPath expression.
    /// This method should be used as a helper function at all times
    ///
    /// XPathNavigator
    public XPathNavigator GetCurrentXPathNav(String XPath)
    {
    XPathNavigator DataSource = MainDataSource.CreateNavigator();
    XPathNavigator XPathNav = DataSource.SelectSingleNode(XPath, NamespaceManager);
    return XPathNav;
    }

    I hope this helps.

    ReplyDelete
  3. thanks for shareing this information,
    could you please explain how to iterate items from sharepoint list as a secondry data source

    ReplyDelete
  4. LoadMultiSelectListBox() is the method to iterate the secondary data source, I have used an XML secondary data source receive data connection.
    Whereas, you might even use CAML for iterating the list items programmatically if it is a SharePoint List or you can use an InfoPath receive data connection and iterate using XPathNodeIterator and XPathNavigator objects.

    ReplyDelete
  5. Mehul,

    Thanks for posting. I used the same to show List Items as checkbox list.

    Thanks a ton.
    Pradeep

    ReplyDelete
  6. Thanks for the info. Is there a way to load the options from an xml resource file?

    ReplyDelete
  7. Hi Mehul,
    Reading XML works absolutely fine. I tried reading a SharePoint List , I am facing an issue while setting the value for optionDescription. //Set Option description values
    optionDescriptionReadSP.SetValue(item.GetAttribute("ows_Workcenter", String.Empty));Here instead of ows_Workcenter , what do I need to put for reading a SharePoint List??. Appreciate your help .

    ReplyDelete
  8. Hi Mehul

    Your solution is good when the list contains a small number of items. I am trying to populate a list with over 1000+ items. Your note in section (b)

    "Note: You can also wrap it inside a Table with a fixed width and fixed height, so that Listbox items can act like scroll bar for options)"

    is not working. There is no way to set a fixed height for a layout table in infopath. Also, when I try to use a section with a fixed height, it is ignored.

    Can you suggest an alternative?

    Thanks

    ReplyDelete
  9. Hi Mehul


    Your solution is good when the list contains a small number of items. I am trying to populate a list with over 1000+ items. Your note in section (b)

    "Note: You can also wrap it inside a Table with a fixed width and fixed height, so that Listbox items can act like scroll bar for options)"

    is not working. There is no way to set a fixed height for a layout table in infopath. Also, when I try to use a section with a fixed height, it is ignored.

    Can you suggest an alternative?

    Thanks
    Vinay Tiwari

    ReplyDelete
  10. Mehul

    A very interesting article from an experienced practionner - I gather this issue goes away with SPD 2010.

    Anyway, I am finding the use of name spaces very confusing and difficult to test as my forms must be managed centrally.

    I have created the following data structure

    MyFields
    -->RiskControls (Group)
    ------>UserSelectedControls (String)
    ------>MultiSelectedOptons (Repeating Group)
    ---------->SelectedOption (Boolean: Yes/No)
    ---------->OptionDescription (String)

    ------------------------------------------

    So If we go back to FormEvents_Loading. Could you please let me know if the line below correct and also why is my MultiSelectOptions appearing in the request since a InfoPath group cannot be a promoted field.

    XPathNavigator root = MainDataSource.CreateNavigator();

    XPathNodeIterator listItems = root.Select("/my:Request/my:myFields:RiskControls/my:myFields:MultiSelectOptions", NamespaceManager);


    Note, in other examples "/my:" is replaced by "/dfs:" ... which confuses me further ( though I can guess what dfs stands for!)

    cheers

    Daniel

    ReplyDelete
  11. Mehul Bhuva,

    Thanks a lot for such a helpful code. It did really helped me a lot.

    Thanks again.

    ReplyDelete
  12. Hello Mehul,

    I am using SP2010 and using InfoPath forms with a SharePoint list. I have added the Multiple-Selection List Box control in my form. Can you please suggest how to get the values in a string separated with (;)? Thanks!

    Best Regards,
    SK

    ReplyDelete
  13. InfoPath 2010 has a Multi-Select List Box control built-in to it.

    Drag and drop the Multi-Select List box within the form, if you are using a Receive Data connection to get Multi-select values via a SharePoint List/Library or SQL database, SOAP/Rest Webservice or XML... In this case ensure that you keep the MultiSelect control ID and Value properties as Name, the Multi-Select Group property gets automatically promoted as comma separated values.

    I hope it helps.

    ReplyDelete
  14. Very useful, but the one part I can't get to work is the SaveMultiSelectListItems, none of the selected options are writing to my equivalent of the WorkcenterAssignment field. What event should the SaveMultiSelectListItems code be added to?

    ReplyDelete
  15. Any chance you could share some sample XML?

    ReplyDelete
  16. This is Great!

    Thank you very much Mehul.

    ReplyDelete
  17. I tried this but this disabled the validation for the form. Did this happen for anyone else?

    ReplyDelete
  18. It does not disable the validation on the form. You can still use XPathNavigator to validate specific Elements in the form or use InfoPath OOTB validations

    ReplyDelete
  19. This might not be related to the above post?
    But I would like to print the infopath 2010 form(.xml) which is stored in sharepoint library?
    Any Idea about it?
    Please help me

    ReplyDelete
  20. Thank you for your post - it saved me a lot of time. I modified the code slightly to use itemdata.xml to remember the selections in case users return to the task edit form.

    ReplyDelete
  21. I know this is an old post, but it's been very helpful. The only problem I'm having is that my repeating table is populated in reverse sort order. That is, my source SharePoint list is sorted alphabetically, but this process creates a repeating table sorted descending. How would I get the nodes added in the same order as the original data source?

    ReplyDelete
  22. I am attempting something like this using SP2010/BCS and VisualStudio2010. I haven't found any out-of-the-box SP solution.

    Rather than a selection list I want to use an autocomplete field against a BCS source. The user then "Add"'s the value to something like your WorkcenterAssignment.

    This seems doable?

    ReplyDelete
  23. Hello, I am using infopath 2010 and need use many SQL query to fill my List:
    but all my items are "Selected"("checked" by default), How to uncheck all nodes plz? i don't find the attribute or something else.

    public void AddItem(String itemId, String itemName)
    {
    XPathNavigator DOM = MainDataSource.CreateNavigator();
    XPathNavigator group = DOM.SelectSingleNode("//my:../my:groupe", NamespaceManager);
    XPathNavigator field = DOM.SelectSingleNode("//my:.../my:groupe/my:MultiSelectionList", NamespaceManager);

    XPathNavigator newNode = field.Clone();

    newNode.SetValue(itemName);

    group.AppendChild(newNode);
    }

    ReplyDelete
  24. I am using IP2010 to create an IP2007 browser enabled form that will receive & submit to as SQL2008 database. I have been attempting to utilize your code, but I continue to receive the following message:
    "The name 'ThrowException' does not exist in the current context." Please let me know if anyone has experienced this issue & how they may have resolved it.

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. Hi Mehul,

    This is greatbut you can create the same logic without code - very simple!

    1. After creating the data structure as per your instructions, you need to create multiple instances of the repeating section using the default values section. To do this, just go into Tools > Default Values. Create as many instances as there are list items.
    2. Then set each instance of the optionDescription field to one of the list items
    3. Finally, set the default value of the main field (the non-repeating field, 'WorkcenterAssignment' in this example) with the following formula.

    eval(eval(optionDescription[selectedOption = string(true())], 'concat(., ";")'), "..")

    Done! Any questions, send to danny.levan@collectionhouse.com.au

    **Deleted original posts because they were poorly worded**

    ReplyDelete
  28. Hi, I have been facing this problem for a long time and I hope someone could help me 1. I have a multiple selection list box whose values come from a sql table with a secondary data source Eg let us say there is a list of courses taken by a student like Mathematics, Science etc 2. Now I have a save button with which I want to save the selection made for each student 3. I have a output table (Student name and subject) and a procedure in sql to save the list of choices I made for each student 4. I was not sure how to save the choices selected for each student directly, so I convert them as a string in another text box in infopath and use this to save the choices made for a student to the table 5. I believe since I store it as a string from the text box instead of storing directly from the multiple select list box I am not able to retrieve the values back when I want to see again what were the subjects a student was enrolled earlier Could some one help me to find how I can directly store multiple select list box value into a sql table and how I can retreive them again from the table to see what choices were made early. Below is my current code XPathNavigator dom = MainDataSource.CreateNavigator(); string StudentName = dom.SelectSingleNode("/my:myFields/my:CompanySnapshot/my:StudentName", NamespaceManager).Value; string Subject = dom.SelectSingleNode("/my:myFields/my:TaxBusinessProcess/my:Subjecttextbox", NamespaceManager).Value; // this comes from the text box which converts selected choices into a string this needs to change AdoQueryConnection connection = (AdoQueryConnection)DataConnections["Subject Choice"]; // Save the original statement that is stored in the Command property string originalCommand = connection.Command; // Execute the stored procedure connection.Command = "EXEC SubjectProc'" + StudentName + "','" + Subject + "'"; connection.Execute(); // Restore the statement of the Command property to its original value connection.Command = originalCommand;

    ReplyDelete
  29. This is the most complete and insightful set of advices.
    It’ll definitely help me start contributing with the projects I use and was trying to figure it out how to help them.

    ReplyDelete
  30. Hey Mehul,

    Is it possible to extend this further to check the value that has been saved, and make sure it is selected when the form is loaded again?

    I am trying to populate a drop-down list with valid choices in a repeating section that is associated with a secondary data source. I want the selection to be saved somewhere and when the form is loaded again, the drop-down list is populated and the previously selected choice is selected.

    Possible?

    Thanks,

    Nathan

    ReplyDelete