Programmatically copy rows from a SharePoint list to a repeating node in the Main data source in InfoPath

Applies to: InfoPath 2007

Learn how to use C# or Visual Basic code to copy the rows from a SharePoint list to a repeating node in the Main data source of an InfoPath form.

ADVERTISEMENTS

Problem

You have added a Data Connection in InfoPath to a SharePoint Contacts list (a secondary data source) and would like to copy all of the names and email addresses from the contacts in the Contacts list to a repeating node in the Main data source of the InfoPath form.

Solution

Write C# or Visual Basic code to programmatically loop through all of the items of the SharePoint list (secondary data source) and add rows to the repeating node in the Main data source of the InfoPath form.

Before You Begin

You should know how to do the following:

Discussion

You can achieve this functionality as follows:

  1. In SharePoint, create a new Contacts list or use an existing one.
  2. Create a new blank InfoPath form template and add a Repeating Table and a Button control to it as shown in the following figure:

    The the form template in Design mode
    Figure 1. The the form template in Design mode.

    with a Main data source as shown in the following figure:

    The Main data source of the form template
    Figure 2. The Main data source of the form template.

  3. On the Tools menu, click Data Connections.
  4. On the Data Connections dialog box, click Add and follow the instructions to add a Receive data connection to your Contacts SharePoint list. Ensure you select the Last_Name, First_Name, and E-mail_Address fields from the Contacts list when adding the data connection. Here we will name the data connection MyContacts.
  5. Double-click the button to open its Properties dialog box.
  6. On the Button Properties dialog box, click Edit Form Code to add a Clicked event handler for the button.
  7. Add the following C# code to the Clicked event of the button:

    XPathNavigator secDSNav = DataSources["MyContacts"].CreateNavigator();

    // Retrieve the rows of the secondary data source
    XPathNodeIterator rows = secDSNav.Select(
    "/dfs:myFields/dfs:dataFields/dfs:MyContacts",
    NamespaceManager);

    // Loop through the rows of the secondary data source and fill the repeating table
    while (rows.MoveNext())
    {
    string lastname = rows.Current.SelectSingleNode(
    "@Last_Name", NamespaceManager).Value;
    string firstname = rows.Current.SelectSingleNode(
    "@First_Name", NamespaceManager).Value;
    string email = rows.Current.SelectSingleNode(
    "@E-mail_Address", NamespaceManager).Value;

    // Add the item to the repeating table
    AddItem(lastname, firstname, email);
    }

    // Remove the first empty item from the repeating table
    DeleteFirstEmptyItem();

    Or add the following Visual Basic code to the Clicked event of the button:

    Dim secDSNav As XPathNavigator = DataSources("MyContacts").CreateNavigator()

    ' Retrieve the rows of the secondary data source
    Dim rows As XPathNodeIterator = secDSNav.Select( _
    "/dfs:myFields/dfs:dataFields/dfs:MyContacts", _
    NamespaceManager)

    ' Loop through the rows of the secondary data source and fill the repeating table
    While rows.MoveNext()
    Dim lastname As String = rows.Current.SelectSingleNode( _
    "@Last_Name", NamespaceManager).Value
    Dim firstname As String = rows.Current.SelectSingleNode( _
    "@First_Name", NamespaceManager).Value
    Dim email As String = rows.Current.SelectSingleNode( _
    "@E-mail_Address", NamespaceManager).Value

    ' Add the item to the repeating table
    AddItem(lastname, firstname, email)
    End While

    ' Remove the first empty item from the repeating table
    DeleteFirstEmptyItem()

    Note: You can use the Copy XPath functionality in the Data Source pane (also see Programmatically retrieve the value of an InfoPath form field using .NET code) to find out what the XPath expression of the MyContacts node in the secondary data source (Contacts SharePoint list) should be. You can do the same for the @Last_Name, @First_Name, and @E-mail_Address attributes (see Figure 3).

    The Copy XPath functionality accessed from the Data Source pane
    Figure 3. The Copy XPath functionality accessed from the Data Source pane.

  8. Add the following private function in C# to add a row to the repeating table:

    private void AddItem(string lastname, string firstname, string email)
    {
    XmlDocument doc = new XmlDocument();
    XmlNode group = doc.CreateElement("group2",
    NamespaceManager.LookupNamespace("my"));

    XmlNode field = doc.CreateElement("field1",
    NamespaceManager.LookupNamespace("my"));
    XmlNode node = group.AppendChild(field);
    node.InnerText = lastname;

    field = doc.CreateElement("field2",
    NamespaceManager.LookupNamespace("my"));
    node = group.AppendChild(field);
    node.InnerText = firstname;

    field = doc.CreateElement("field3",
    NamespaceManager.LookupNamespace("my"));
    node = group.AppendChild(field);
    node.InnerText = email;

    doc.AppendChild(group);

    MainDataSource.CreateNavigator().SelectSingleNode(
    "/my:myFields/my:group1",
    NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());
    }

    Or the following Private Sub in Visual Basic to add a row to the repeating table:

    Private Sub AddItem( _
    ByVal lastname As String, ByVal firstname As String, ByVal email As String)

    Dim doc As XmlDocument = new XmlDocument()
    Dim group As XmlNode = doc.CreateElement("group2", _
    NamespaceManager.LookupNamespace("my"))

    Dim field As XmlNode = doc.CreateElement("field1", _
    NamespaceManager.LookupNamespace("my"))
    Dim node As XmlNode = group.AppendChild(field)
    node.InnerText = lastname

    field = doc.CreateElement("field2", _
    NamespaceManager.LookupNamespace("my"))
    node = group.AppendChild(field)
    node.InnerText = firstname

    field = doc.CreateElement("field3", _
    NamespaceManager.LookupNamespace("my"))
    node = group.AppendChild(field)
    node.InnerText = email

    doc.AppendChild(group)

    MainDataSource.CreateNavigator().SelectSingleNode( _
    "/my:myFields/my:group1", _
    NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator())

    End Sub
  9. Add the following private function in C# to delete the first item from the repeating table:

    private void DeleteFirstEmptyItem()
    {
    XPathNavigator domNav = MainDataSource.CreateNavigator();
    XPathNavigator itemNav = domNav.SelectSingleNode(
    "/my:myFields/my:group1/my:group2[1]",
    NamespaceManager);

    if (itemNav != null)
    itemNav.DeleteSelf();
    }

    Or add the following Private Sub in Visual Basic to delete the first item from the repeating table:

    Private Sub DeleteFirstEmptyItem()

    Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()
    Dim itemNav As XPathNavigator = domNav.SelectSingleNode( _
    "/my:myFields/my:group1/my:group2[1]", _
    NamespaceManager)

    If itemNav IsNot Nothing Then
    itemNav.DeleteSelf()
    End If
    End Sub
  10. Save your work, build the code, and test the form.

Now when you click the button, all of the rows from the SharePoint Contacts list will be copied over to the repeating node of the repeating table in the Main data source of the form.

 


Related InfoPath Articles:

 

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services