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

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.

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:

• Create a SharePoint list
• How to add a data connection to a SharePoint list or library in InfoPath
• Change the programming language of a form template
• 4 Ways to programmatically add a row to a repeating table in InfoPath
• Programmatically delete the first row of a repeating table

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:
   
   
   Figure 1. The the form template in Design mode.
   
   with a Main data source as shown in the following figure:
   
   
   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).
   
   
   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:

• How to add a data connection to a SharePoint list or library in InfoPath
• How to submit the rows of a repeating table in InfoPath to a SharePoint list
• Programmatically move items between two lists in InfoPath using C# code

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 of S.Y.M. Wong-A-Ton. Usage of 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, S.Y.M. Wong-A-Ton.