How Do I Copy Repeating Data From an External Source into My Form? - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

How Do I Copy Repeating Data From an External Source into My Form?

Upon occasion you may need to copy external data into form fields bound to the form's Main Data Source. In this sample for InfoPath 2007, we will create a form with nested repeating tables and using code copy our external data to the appropriate tables. Our scenario is a selection of vehicles, grouped by type (Sports Cars, Luxury Cars, etc) with an array of dealers for each specific vehicle. All files used in this sample are available here: Copy Repeating External Data Into a Form

Sample Form Schema

The sample form schema for the main data source looks like this:

Main Data Source

Secondary Data Sources 

We need two XML files that we will add as secondary data connections. The first is the Vehicles.xml:

<?xml version="1.0" encoding="utf-8"?>
<vehicles>
  <vehicle>
    <TypeId>1</TypeId>
    <VehicleType>Sports Cars</VehicleType>
    <VehicleId>1</VehicleId>
    <VehicleYear>1968</VehicleYear>
    <VehicleMake>Chevrolet</VehicleMake>
    <VehicleModel>Corvette</VehicleModel>
    <VehicleColor>Red</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>1</TypeId>
    <VehicleType>Sports Cars</VehicleType>
    <VehicleId>2</VehicleId>
    <VehicleYear>2004</VehicleYear>
    <VehicleMake>Dodge</VehicleMake>
    <VehicleModel>Viper</VehicleModel>
    <VehicleColor>Red</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>1</TypeId>
    <VehicleType>Sports Cars</VehicleType>
    <VehicleId>3</VehicleId>
    <VehicleYear>2007</VehicleYear>
    <VehicleMake>Ferrari</VehicleMake>
    <VehicleModel>599 GTB</VehicleModel>
    <VehicleColor>Red</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>1</TypeId>
    <VehicleType>Sports Cars</VehicleType>
    <VehicleId>4</VehicleId>
    <VehicleYear>1978</VehicleYear>
    <VehicleMake>Pontiac</VehicleMake>
    <VehicleModel>Trans Am</VehicleModel>
    <VehicleColor>Black</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>2</TypeId>
    <VehicleType>Classic Cars</VehicleType>
    <VehicleId>5</VehicleId>
    <VehicleYear>1924</VehicleYear>
    <VehicleMake>Ford</VehicleMake>
    <VehicleModel>Model T</VehicleModel>
    <VehicleColor>Black</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>3</TypeId>
    <VehicleType>Trucks</VehicleType>
    <VehicleId>6</VehicleId>
    <VehicleYear>1990</VehicleYear>
    <VehicleMake>Dodge</VehicleMake>
    <VehicleModel>Ram</VehicleModel>
    <VehicleColor>Blue</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>3</TypeId>
    <VehicleType>Trucks</VehicleType>
    <VehicleId>7</VehicleId>
    <VehicleYear>2006</VehicleYear>
    <VehicleMake>Ford</VehicleMake>
    <VehicleModel>F250</VehicleModel>
    <VehicleColor>Pewter</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>3</TypeId>
    <VehicleType>Trucks</VehicleType>
    <VehicleId>8</VehicleId>
    <VehicleYear>2008</VehicleYear>
    <VehicleMake>GMC</VehicleMake>
    <VehicleModel>3500</VehicleModel>
    <VehicleColor>Green</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>4</TypeId>
    <VehicleType>Luxury Cars</VehicleType>
    <VehicleId>9</VehicleId>
    <VehicleYear>2009</VehicleYear>
    <VehicleMake>Rolls-Royce</VehicleMake>
    <VehicleModel>Phantom Coupe</VehicleModel>
    <VehicleColor>Silver</VehicleColor>
  </vehicle>
  <vehicle>
    <TypeId>4</TypeId>
    <VehicleType>Luxury Cars</VehicleType>
    <VehicleId>10</VehicleId>
    <VehicleYear>2009</VehicleYear>
    <VehicleMake>Cadillac</VehicleMake>
    <VehicleModel>CTS</VehicleModel>
    <VehicleColor>Tan</VehicleColor>
  </vehicle>
</vehicles>

The second is Dealers.xml:

<?xml version="1.0" encoding="UTF-8"?>
<dealers>
  <dealer>
    <VehicleId>1</VehicleId>
    <DealerId>dlr_cw</DealerId>
    <DealerName>Cal Worthington</DealerName>
  </dealer>
  <dealer>
    <VehicleId>1</VehicleId>
    <DealerId>dlr_kp</DealerId>
    <DealerName>Kirkland Pontiac</DealerName>
  </dealer>
  <dealer>
    <VehicleId>2</VehicleId>
    <DealerId>dlr_pd</DealerId>
    <DealerName>Penrose Dodge</DealerName>
  </dealer>
  <dealer>
    <VehicleId>2</VehicleId>
    <DealerId>dlr_sf</DealerId>
    <DealerName>Sommerset Ford</DealerName>
  </dealer>
  <dealer>
    <VehicleId>2</VehicleId>
    <DealerId>dlr_wd</DealerId>
    <DealerName>Wilconson Dodge</DealerName>
  </dealer>
  <dealer>
    <VehicleId>4</VehicleId>
    <DealerId>dlr_kp</DealerId>
    <DealerName>Kirkland Pontiac</DealerName>
  </dealer>
  <dealer>
    <VehicleId>5</VehicleId>
    <DealerId>dlr_sf</DealerId>
    <DealerName>Sommerset Ford</DealerName>
  </dealer>
  <dealer>
    <VehicleId>6</VehicleId>
    <DealerId>dlr_pd</DealerId>
    <DealerName>Penrose Dodge</DealerName>
  </dealer>
  <dealer>
    <VehicleId>6</VehicleId>
    <DealerId>dlr_wd</DealerId>
    <DealerName>Winconson Dodge</DealerName>
  </dealer>
  <dealer>
    <VehicleId>7</VehicleId>
    <DealerId>dlr_af</DealerId>
    <DealerName>Allen Ford</DealerName>
  </dealer>
  <dealer>
    <VehicleId>7</VehicleId>
    <DealerId>dlr_pjf</DealerId>
    <DealerName>Penny-James Ford</DealerName>
  </dealer>
  <dealer>
    <VehicleId>7</VehicleId>
    <DealerId>dlr_sf</DealerId>
    <DealerName>Sommerset Ford</DealerName>
  </dealer>
  <dealer>
    <VehicleId>8</VehicleId>
    <DealerId>dlr_kp</DealerId>
    <DealerName>Kirkland Pontiac</DealerName>
  </dealer>
  <dealer>
    <VehicleId>8</VehicleId>
    <DealerId>dlr_spbg</DealerId>
    <DealerName>Seaview Pontiac Buick GMC</DealerName>
  </dealer>
  <dealer>
    <VehicleId>9</VehicleId>
    <DealerId>dlr_bhrr</DealerId>
    <DealerName>Beverly Hills Rolls-Royce</DealerName>
  </dealer>
  <dealer>
    <VehicleId>9</VehicleId>
    <DealerId>dlr_vrr</DealerId>
    <DealerName>Vegas Rolls-Royce</DealerName>
  </dealer>
  <dealer>
    <VehicleId>10</VehicleId>
    <DealerId>dlr_fc</DealerId>
    <DealerName>Florida Cadillac</DealerName>
  </dealer>
  <dealer>
    <VehicleId>10</VehicleId>
    <DealerId>dlr_kp</DealerId>
    <DealerName>Kirkland Pontiac</DealerName>
  </dealer>
  <dealer>
    <VehicleId>10</VehicleId>
    <DealerId>dlr_spbg</DealerId>
    <DealerName>Seaview Pontiac Buick GMC</DealerName>
  </dealer>
  <dealer>
    <VehicleId>10</VehicleId>
    <DealerId>dlr_vsc</DealerId>
    <DealerName>Vegas Strip Cadillac</DealerName>
  </dealer>
</dealers>

As you can see, there is a one to many relationship between the xml files. Each vehicle may have any number of dealers.

Sample Form Construction

Create a new form with a schema like that shown in the Sample Form Schema section. Add the two XML files as resource files:

  1. Select Resource Files from the Tools menu
  2. Select the Add button in the Resource Files window
  3. Browse the Vehicles.xml file and select the OK button
  4. Repeat for the Dealers.xml and select the OK button in the Resource Files window to close

Now create two secondary data connections using the resource files:

  1. Select Data Connections from the Tools menu
  2. Select the Add button in the Data Connections window
  3. Select Create a new connection to:
  4. Select Receive data
  5. Select the Next button
  6. Select XML document for the source of the data
  7. Select the Next button
  8. Select the Resource Files button
  9. Select the Dealers.xml file that you added to the Resource Files in the section above
  10. Select the OK button
  11. Select the Next button
  12. Select the Finish button
  13. Repeat these steps to add a data connection to the Vehicles.xml

In your form, add a drop-down list box bound to the selectedType node in the main data source. The values for the drop-down should be provided by the VehicleType nodes in the Vehicles data connection:

  1. Under List box entries in the properties for the drop-down list box, select Look up values from an external data source
  2. Select Vehicles from the Data source drop-down list box
  3. Select the button next to Entries to open the Select a Field or Group window
  4. Select the VehicleType node
  5. Select the OK button
  6. Select Show only entries with unique display names
  7. Select the OK button

You can preview your form and check that your drop-down is correct:

Add a repeating table connected to Vehicle in the main data source, and a field that will display dealer information in a repeating section in that table.

Add a button with the label "Add Rows". We will use it to run our code for copying our data. The finished form should look something like this:

Form Code

Right-click the Add Rows button, and, under Button Properties, select the Edit Form Code button.

Microsoft Script Editor or VSTA will open, depending on what programming language your form is using (this can be changed under Tools/Options/Programming).

In JScript, the following code for the button OnClick event will copy data from Vehicles.xml and Dealers.xml into the tables on your form:

// Get the selected vehicle type.
 var selectedType = XDocument.DOM.selectSingleNode("/my:myFields/my:selectedType").text;

 // If no form was selected notify the user.
 if(selectedType == "")
 {
  XDocument.UI.Alert("Please select a vehicle type first.");
  return;
 }
 
 // Get the list of vehicles per the selected vehicle type.
 var vehicles =XDocument.GetDOM("Vehicles").selectNodes("/vehicles/vehicle[VehicleType = '" + selectedType + "']");

 // If nothing was found, then return; there will be no items to insert. 
 if(vehicles == null)
  return;
  
 // Get the Vehicle insertion point.
 var vehicleList = XDocument.DOM.selectSingleNode("/my:myFields/my:Vehicles");

 // Add the rows to the main DOM.
 var vehicle;
 while(vehicle = vehicles.nextNode())
 {
  try
  {
   // Get this vehicle's values from the secondary DOM.
   var vehicleId = vehicle.selectSingleNode("VehicleId").text;
   var year = vehicle.selectSingleNode("VehicleYear").text;
   var make = vehicle.selectSingleNode("VehicleMake").text;
   var model = vehicle.selectSingleNode("VehicleModel").text;
   var color = vehicle.selectSingleNode("VehicleColor").text;

   // Build a string representing a main DOM vehicle nodeset using the values from the secondary DOM.
   var vehicleXml = "<my:Vehicle xmlns:my='http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57'>"
       + "<my:Year>" + year + "</my:Year>"
       + "<my:Make>" + make + "</my:Make>"
       + "<my:Model>" + model + "</my:Model>"
       + "<my:Color>" + color + "</my:Color>"
       + "<my:Dealers>";

   // Get the list of Dealers for this vehicle.
   var dealers =  XDocument.GetDOM("Dealers").selectNodes("/dealers/dealer[VehicleId = '" + vehicleId + "']");

   // Add the dealer rows to the XML.
   var dealer;
   while(dealer = dealers.nextNode())
   {
    var dealerId = dealer.selectSingleNode("DealerId").text;
    var dealerName = dealer.selectSingleNode("DealerName").text;
    
    vehicleXml += "<my:Dealer>"
       + "<my:DealerId>" + dealerId + "</my:DealerId>"
       + "<my:DealerName>" + dealerName + "</my:DealerName>"
       + "</my:Dealer>";
   }

   vehicleXml += "</my:Dealers></my:Vehicle>"

   // Load new vehicle string into a temporary XML DOM.
   var domNewRow = XDocument.CreateDOM();
   domNewRow.validateOnParse = false;
   domNewRow.async = false;
   domNewRow.loadXML(vehicleXml);
   domNewRow.setProperty("SelectionNamespaces", 'xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57%22');
   
   // Insert the new vehicle into the main DOM.
   vehicleList.appendChild(domNewRow.selectSingleNode("my:Vehicle"));
  }
  catch(ex)
  {
   XDocument.UI.Alert("Could not insert vehicle row.\n\nError message:\n" + ex.Message);
  }
 }

And, for C#, you can use this code in your button's Clicked event:

// Get the selected vehicle type.
string selectedType = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:selectedType", NamespaceManager).Value;

// If no form was selected notify the user.
if (selectedType.Equals(""))
{
    MessageBox.Show("Please select a vehicle type first.");
    return;
}

// Get the list of vehicles per the selected vehicle type.
XPathNodeIterator vehicles = this.DataSources["Vehicles"].CreateNavigator().Select("/vehicles/vehicle[VehicleType = '" + selectedType + "']", NamespaceManager);

// If nothing was found, then return; there will be no items to insert.
if (vehicles.Count == 0)
{
    return;
}

//Get the Vehicle insertion point.
XPathNavigator vehicleList = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Vehicles", NamespaceManager);

//Add the rows to the main DOM.
foreach (XPathNavigator vehicle in vehicles)
{
    try
    {
        // Get this vehicle's values from the secondary DOM.
        string vehicleId = vehicle.SelectSingleNode("VehicleId", NamespaceManager).Value;
        string year = vehicle.SelectSingleNode("VehicleYear", NamespaceManager).Value;
        string make = vehicle.SelectSingleNode("VehicleMake", NamespaceManager).Value;
        string model = vehicle.SelectSingleNode("VehicleModel", NamespaceManager).Value;
        string color = vehicle.SelectSingleNode("VehicleColor", NamespaceManager).Value;

        // Build a string representing a main DOM vehicle nodeset using the values from the secondary DOM.
        string vehicleXml = "<my:Vehicle xmlns:my='http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57'>"
                        + "<my:Year>" + year + "</my:Year>"
                        + "<my:Make>" + make + "</my:Make>"
                        + "<my:Model>" + model + "</my:Model>"
                        + "<my:Color>" + color + "</my:Color>"
                        + "<my:Dealers>";

        // Get the list of Dealers for this vehicle.
        XPathNodeIterator dealers = this.DataSources["Dealers"].CreateNavigator().Select("/dealers/dealer[VehicleId = '" + vehicleId + "']", NamespaceManager);

        // Add the dealer rows to the XML.
        foreach (XPathNavigator dealer in dealers)
        {
            string dealerId = dealer.SelectSingleNode("DealerId", NamespaceManager).Value;
            string dealerName = dealer.SelectSingleNode("DealerName", NamespaceManager).Value;

            vehicleXml += "<my:Dealer>"
                        + "<my:DealerId>" + dealerId + "</my:DealerId>"
                        + "<my:DealerName>" + dealerName + "</my:DealerName>"
                        + "</my:Dealer>";
        }

         vehicleXml += "</my:Dealers></my:Vehicle>";

        //Load new vehicle string into a temporary XML document
        XmlDocument tempDoc = new XmlDocument();
        tempDoc.LoadXml(vehicleXml);

        // Insert the new vehicle into the main DOM.
        XPathNavigator newVehicle = tempDoc.CreateNavigator().SelectSingleNode("my:Vehicle", NamespaceManager);
        vehicleList.AppendChild(newVehicle);
    }
    catch (Exception ex)
    {
        MessageBox.Show("Could not insert vehicle row.\n\nError message:\n" + ex.Message);
    }
}

Your form is now ready to try. Preview it, select a vehicle type from the drop-down list box, select the Add Rows button, and your repeating tables should populate with the correct data from your secondary data sources.

Published Jul 04 2008, 07:57 AM by Hilary Stoupa
Filed under: , , , ,

Comments

 

How To Submit and Refresh the Active Form Window Programmatically? | keyongtech said:

Pingback from  How To Submit and Refresh the Active Form Window Programmatically? | keyongtech

February 19, 2009 8:07 PM
 

RhysAZ said:

Thanks for the tutorial Hilary.

While I was testing out your example form, I noticed that your are able to select the same vehicle type & insert the vehicle's data into the repeating table as many times as you want, by pushing Add Rows.

I would like only for my form user to be able to select the  "vehicle type" once, prefill the repeating table rows with the vehicle's data by using the Add Rows button, and then the user will not be able to select that particular "vehicle type" again.

Can this be done? And if so, how?

May 18, 2009 6:57 PM
 

riaz_secl said:

Thak you so much for this very helpfull article.

Now i have a question to you...here i see you doesn't use the existing first blank row.

have any process to use this first blank row..???

if have any......please reply me.  

July 6, 2009 10:15 PM
 

DerekB said:

Hi,

This is exactly what I need, with one small problem.  All of the code in my forms is vbScript, and secondly I'm stuck in Infopath 2003.  Can this still be done?

September 11, 2009 11:15 AM
 

Hilary Stoupa said:

As to RhysAZ's question, you'd need to add a filter to the list so it didn't display anything that had previously been selected. Previous-sibling can help with that.

To riaz secl -- just don't include the blank row by default -- you'll find this under the default values.

And to Derek -- the jscript sample is for IP 2003. You'll just have to translate from jscript to vb.

September 27, 2009 6:31 AM
 

DerekB said:

Hi...I'm afraid I cannot seem to find anybody that can convert the jScript to VB, is there anybody around here that might be able to do so?

April 1, 2010 1:16 PM
 

Hilary Stoupa said:

What about changing your form to use JScript instead of VB? Or do you alread have VB Script in your form?

May 26, 2010 4:49 PM
 

mahakbhalla said:

URGENTLY HELP SOUGHT!!!

I have an infopath form and I want to display top 3 values from secondary data source in a repeating table. I will select a name from drop down and it should display only top 3 rows and not all from database on infopath repeating table out of many in database. How can it be done.

July 5, 2010 10:14 PM
 

Hilary Stoupa said:

Hey, mahakbhalla -- I know you also emailed me on this, as well as posted a question on the forum on this. Here's a link to the question: www.infopathdev.com/.../56596.aspx

July 7, 2010 5:17 PM
 

Beircheart said:

This tutorial was of great help to me. Thank you Hilary!

See here for more detail: www.infopathdev.com/.../57561.aspx

August 6, 2010 6:57 AM
 

Gennady Vanin (Геннадий Ванин, Новосибирск) said:

For the stupid like me remember to replace

 schemas.microsoft.com/.../2008-06-03T00:09:57

in

string vehicleXml = "<my:Vehicle  xmlns:my='schemas.microsoft.com/.../2008-06-03T00:09:57'>"

                                   + ....

by the value from your Infopath form (from Details of properties of myFields in Fields pane)

September 3, 2012 12:14 PM
 

Kourtney said:

Hilary

I hope you can help me, I am new to programming.

I have a similar situation with an InfoPath 07 form that is pulling from the SharePoint 07 form library (Second Data Source) that it submits to.

On a change request, one can select a specific docId and prepopulate the new form with data from an existing form.  

Of course, the repeating section does not prepopulate.

I merged the data and then published it back to sharepoint.  I could tell it is trying to read something, but I pretty much ruined everything good that I had going on.

So, not only do I submit my form data to SharePoint, but I also submit the form data as XML to SQL Server which is where I generate my docId.  I'm not sure how to reference my XML in the database from the code. Nor do I know how to extend the repeating table, using code (c#).

The above code looks helpful, but I have no idea what its trying to tell me and I know my situation is a bit different than what is explained.

Could you please guide me in the right direction and/or maybe point out specific areas that would be helpful to me?

So far I have played around with the following code:

       public void ExistingFormList_Changed(object sender, XmlEventArgs e)

       {

           // Write your code here to change the main data source.

           //XPathNavigator myCarList = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:grpMain/my:grpCarRental/my:grpCarRentalRepeating/my:grpCarRentalItinerary", NamespaceManager);

           //myCarList.AppendChild();

           //myCarList.AppendChild();

           XPathNavigator xPathNavi = MainDataSource.CreateNavigator();

           XPathNavigator details = xPathNavi.SelectSingleNode("/my:myFields/my:grpMain/my:grpCarRental/my:grpCarRentalRepeating", NamespaceManager);

           XPathNavigator repTable = details.SelectSingleNode("/my:myFields/my:grpMain/my:grpCarRental/my:grpCarRentalRepeating", NamespaceManager);

           XPathNavigator node;

           int counter = 0;

           for (int i = 0; i < 1; i++)

           {

                   node = repTable.Clone();

                   node.SelectSingleNode("my:txtCarFrom", NamespaceManager).SetValue("my:txtCarFrom" + i.ToString());

                   node.SelectSingleNode("my:txtCarTo", NamespaceManager).SetValue("my:txtCarTo" + i.ToString());

                   node.SelectSingleNode("my:picCarPickupDate", NamespaceManager).SetValue("my:picCarPickupDate" + i.ToString());

                   node.SelectSingleNode("my:picCarPickupTime", NamespaceManager).SetValue("my:picCarPickupTime" + i.ToString());

                   node.SelectSingleNode("my:picCarReturnDate", NamespaceManager).SetValue("my:picCarReturnDate" + i.ToString());

                   node.SelectSingleNode("my:picCarReturnTime", NamespaceManager).SetValue("my:picCarReturnTime" + i.ToString());

                   node.SelectSingleNode("my:optOneWayRental", NamespaceManager).SetValue("my:optOneWayRental" + i.ToString());

                   node.SelectSingleNode("my:chkCarInterview", NamespaceManager).SetValue("my:chkCarInterview" + i.ToString());

                   node.SelectSingleNode("my:chkCarBusComp", NamespaceManager).SetValue("my:chkCarBusComp" + i.ToString());

                   details.AppendChild(node);

                   counter += 1;

           }

       }

   }

}

****NullReferenceException was unhandled by user code****

Let me know if you need better explaination

Thank you so much for all your help

November 6, 2012 3:06 PM
 

Hilary Stoupa said:

Hey, Kourtney - so you are getting the null ref exception when you follow the full tutorial? You aren't trying to use this code in a form with a different schema, right?

January 10, 2013 3:03 PM
 

Jorge Luna said:

Hi again, i have the drop down list but with this format:

4;#Mirage

9;#Decameron

4;#Sheraton

what i do, for delete or elimate this symbols (4;# - 9;# - ....) ?

thanks..

July 12, 2013 8:10 AM
 

gec13 said:

Hi Hilary

Very nice post, thanks.

I would be grateful, if you could guide me on the following please (this may already have been asked before).

I am new to InfoPath and designed forms which Submit to one row in SQL table.

My question is, Is there any way that I can store information to multiple rows in SQL table?

If yes than which components should I use?

Many thanks in advance.

March 19, 2014 2:35 AM

About Hilary Stoupa

I wandered into development after working as a business process analyst for a global manufacturing company. I create InfoPath solutions for our clients as well as work as a developer on company tools that extend InfoPath. I've also been instrumental in creating the InfoPath Master Class training provided by Qdabra.

Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.