Thursday, November 8, 2012

InfoPath 2007/2010/2013: Dynamically Populate a Nested Repeating Table from a Secondary Data Source

This post builds on top of my original population of repeating table post. I had a reader ask about a nested solution in which the repeating group not only contained fields, but yet another repeating group.

The scenario of the form fields looks like the following:


In my examples (on my blog and in my InfoPath How-To book), I used Regions and Offices as data sources (they are SharePoint lists but web services or other data sources work the same way - just different XPath). Offices has a lookup column for the Region.

For the nested scenario, I decided to create a third datasource named Clients. The Clients have a relationship to Offices. Therefore, in my Clients SharePoint list, I have a lookup column to Offices. The overall relationships are:
  • Regions can have one-to-many Offices (An Office can only have one Region)
  • Offices can have one-to-many Clients (A Client can only belong to one Office)

My Clients data source contains the Client Name (Title), Address, and Phone along with the Office field which is the Office ID:


So now, when I select a Region from the drop-down in my form, I want to get the Offices for that Region along with all of the Clients that belong to that Office. Since it's a nested structure it calls for a nested loop within the Populating code-behind (see my previous post for context):

(click on image to zoom in)


The nested loop code involves these steps:
  • Creating the data source object for the Clients list
  • Setting the query value to the current Office ID
  • Executing the query against the data source
  • Looping through the data source results and populating the nested repeating table structure
The results speak for themselves:


 I will have more details on these steps as well as downloadable code in my upcoming InfoPath with SharePoint 2013 How-To book! Stay tuned!