Monday, September 13, 2010

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

Psst! I have a new nested solution posted here.

Background
In attempting to create a solution within form code to satisfy the scenerio/requirements below, I found several different posts/links that described certain functions. Each link only provided a piece of the puzzle. This post puts all the pieces together.


Scenario/Requirements
Clicking a button or changing a selection in an InfoPath form needs to query a web service (or any secondary data source) based on that selection and populate a repeating table with the data. If the selection changes, the repeating table entries need to be cleared and re-populated with the new set of data.


Assumptions
The querying of the secondary data source (such as a web service) based on selections is already in place (this can be easily done with rules). We just need the proper form code within the control change-event to handle the repeating table population.



Solution Piece-by-Piece

Namespace Variable
Within the changed event form code (using Visual Studio for Office Applications) the first thing we need is the standard namespace variable so we can use that throughout:

string myNamespace = NamespaceManager.LookupNamespace("my");

Access Web Service-Based Secondary Data Source
There is a MainDataSource object at the form level but how can you access secondary data sources? If the datasource is named "GetData" here is the code to access and setup the XPath objects for looping:

DataSource ds = DataSources["GetData"];
XPathNavigator domNav = ds.CreateNavigator();
XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/tns:GetDataResponse/tns:GetData/NewDataSet/DynamicData", NamespaceManager);

Looping Through the Secondary Data Source
You can loop through the XPathNodeIterator collection using a while-loop. The source fields data can be retrieved by using the Current pointer within the XPathNodeIterator:

while (rows.MoveNext())
{
string accountID = rows.Current.SelectSingleNode("AccountID",NamespaceManager).Value.ToString();
string accountNumber = rows.Current.SelectSingleNode("AccountNumber",NamespaceManager).Value.ToString();
string amount = rows.Current.SelectSingleNode("Amount",
NamespaceManager).Value.ToString
}

Populating the Repeating Table
The repeating table is actually part of the main data source so we can access that and use the XMLWriter to write the field values from the web service to the table. The code below will live within the loop from above:

using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:MainDataSource/my:group1", NamespaceManager).AppendChild())
{


writer.WriteStartElement("group2", myNamespace);
writer.WriteElementString("Amount",myNamespace ,amount);
writer.WriteElementString("AccountID", myNamespace, accountID);

writer.WriteElementString("AccountNumber", myNamespace,
accountNumber);

writer.WriteEndElement();
writer.Close();
}

The above assumes the repeating table created a Group1\Group2 data source entry. You must look at the main datasource to determine the group names.

ALSO! VERY IMPORTANT! The order in which you write the values to the table should be the order that they appear in the main data source. So if you expand the group1 and group2 and see field1, field2, field3, that is the order you must have in the code above. Otherwise you will receive a non-datatype schema validation error.


Clearing Previous Entries
Before anything happens, we need to check if there are already rows in the repeating table and remove them. This must be done by removing the rows in a descending fashion because the count actually represents the highest index.

First we check to see if there are any rows in the repeating table and if so we loop through and delete them. Notice how the actual row is accessed using the SelectSingleNode (SelectSingleNode("/my:MainDataSource/my:group1/my:group2[row#]):

XPathNavigator rTable = MainDataSource.CreateNavigator();
XPathNodeIterator tableRows = rTable.Select("/my:MainDataSource/my:group1/my:group2", NamespaceManager);
if (tableRows.Count > 0)
{


for (int i = tableRows.Count;i > 0; i--)
{
XPathNavigator reTable =
MainDataSource.CreateNavigator();

XPathNavigator reTableRows =
reTable.SelectSingleNode("/my:MainDataSource/my:group1/my:group2[" + i + "]", NamespaceManager);

reTableRows.DeleteSelf();
}
}




Solution - Complete Code
//Get namespace
string myNamespace = NamespaceManager.LookupNamespace("my");
//Clear any previous entries
XPathNavigator rTable = MainDataSource.CreateNavigator(); XPathNodeIterator tableRows = rTable.Select("/my:MainDataSource/my:group1/my:group2", NamespaceManager);

if (tableRows.Count > 0)
{
for (int i = tableRows.Count;i > 0; i--)
{
XPathNavigator reTable =
MainDataSource.CreateNavigator();
XPathNavigator reTableRows =
reTable.SelectSingleNode("/my:MainDataSource/my:group1/my:group2[" + i + "]",
NamespaceManager);

reTableRows.DeleteSelf();
}
}
//Connect to secondary data source
DataSource ds = DataSources["GetData"];
XPathNavigator domNav = ds.CreateNavigator(); XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/tns:GetDataResponse/tns:GetData/NewDataSet/DynamicData", NamespaceManager);

//Loop through secondary data source and populate the repeating table

while (rows.MoveNext())
{

string accountID =
rows.Current.SelectSingleNode("AccountID", NamespaceManager).Value.ToString();

string accountNumber =
rows.Current.SelectSingleNode("AccountNumber",NamespaceManager).Value.ToString();

string amount =
rows.Current.SelectSingleNode("Amount", NamespaceManager).Value.ToString();

using (XmlWriter writer =
MainDataSource.CreateNavigator().SelectSingleNode("/my:MainDataSource/my:group1",
NamespaceManager).AppendChild())


{
writer.WriteStartElement("group2", myNamespace);
writer.WriteElementString("Amount",myNamespace ,amount);
writer.WriteElementString("AccountID",
myNamespace, accountID);

writer.WriteElementString("AccountNumber", myNamespace,
accountNumber);

writer.WriteEndElement();
writer.Close(); }

}


WHAT ABOUT A NESTED REPEATING TABLE? THE ANSWER IS HERE!

You can download a sample form and project code with the purchase of my bestselling InfoPath book:



The Nested Solution will be part of my new InfoPath with SharePoint 2013 book download.
 

88 comments:

  1. thanks for the details. What would change if the main data source was another repeating table on the form?

    ReplyDelete
  2. Instead of getting the secondary data source, use the main data source:

    //Connect to secondary data source (which is part of main)

    XPathNavigator domNav = MainDataSource.CreateNavigator();
    XPathNodeIterator rows = domNav.Select("/my:MainDataSource/my:SourceGroup", NamespaceManager);

    Where SourceGroup is the repeating table group folder.

    ReplyDelete
  3. If I use
    for (int i = tableRows.Count;i > 0; i--) I get a NullReferenceException error. Nonetheless, I am a lot closer than before.

    ReplyDelete
  4. Make sure you are selecting the correct path in:

    XPathNodeIterator tableRows = rTable.Select("/my:MainDataSource/my:group1/my:group2", NamespaceManager);

    And that you check for the count
    if (tableRows.Count > 0)

    I just noticed that I may have a bracket out of place in the code. Let me update that.

    ReplyDelete
  5. Hello,

    I am trying to populate a repeting table in the main datasource with data from a secondary datasource (XML file). I have copied your code and think I understand it. But my problem is that my repeating table has a repeting table inside it. So I got errors when I'm trying to write elementstring. Do you know how I can write my code to fix this?

    ReplyDelete
  6. You need to have a nested loop to accomplish this. The outer loop would write out any fields at the first level. The inner loop would create the sublevel items.

    So if you have something like this:

    group1
    group2
    field1
    field2
    group3
    field3
    field4

    You would first write out the group2 items:

    using (XmlWriter writer =
    MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group1",
    NamespaceManager).AppendChild())
    {
    writer.WriteStartElement("group2", myNamespace);
    writer.WriteElementString("field1", myNamespace, <>);
    writer.WriteElementString("field2", myNamespace, <>);
    writer.WriteEndElement();
    writer.Close();
    }


    Then you would need a delete loop to remove any items in the group3 level.

    Then use the "Loop through secondary data source and populate the repeating table" in my post to loop through the subitems and populate the group3 fields (field3 and field4).

    I assisted another reader on this and I am hoping I will create a new blog on the exact steps in this situation.

    ReplyDelete
    Replies
    1. The indents did not show. In the listing group3 is your repeating group under group2.

      Delete
  7. Hi Steven,
    Great Article, I have got repeating table in a repeating section. How would I add rows to both (section and table) by using above method? Any help would be much appreciated.

    I have read your above comment but I'm really new to programming in InfoPath. Please help.

    ReplyDelete
    Replies
    1. It would be similiar. It all depends on your structure. Looks like I'll have to come up with an example sooner than later. Maybe this weekend.

      Delete
  8. Thanks Steve..Will wait for your example. In the meantime, I'll try to do it.
    Thanks again
    Kash

    ReplyDelete
    Replies
    1. Sorry it took so long:

      http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html

      Delete
  9. I am trying to nestead reapting table .. 1st table has dropdown & based on selection populate & filter child table..
    but when select dropdown it's going to add child data into 1st row child table of parent only instead of new row of parernt on each selection

    Parent --- 1) Item 1 (Dropdown) - Add child when select
    Child Item 1
    Child Item 2
    2) Item 2
    Child Item 1
    Child Item 2
    3) Item 3
    Child Item 1
    Child Item 2

    ReplyDelete
    Replies
    1. http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html

      Delete
  10. That may be a little more complicated. Once I have time to breath, I will demonstrate various nested scenarios. I'll attempt your embedded dependant parent scenario as well.

    ReplyDelete
    Replies
    1. http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html

      Delete
  11. Hi. How would I go about doing this using an Access database as my source, and filtering the database to only populate with certain fields based on the value of a main field?

    When I try to filter the database in the "connect to secondary source" section, I get "xsltcontent is needed for this query", and then when I get to the loop section, I get a null reference error.

    Thanks for any help you can give. :)

    ReplyDelete
  12. I actually figured out my previous question. Now, I have a new question! :D

    I am only populating one field per row with this (staff name) based on the selection of a "unit" (I have done this successfully, woo!!), and then the rest of the fields in each row need to be filled out manually (start and end time, overtime hours, etc.). However, what happens is that every field other than the name field is grayed out and cannot be changed. Is there a simple solution to this?

    ReplyDelete
  13. You still need to add the other fields into your StartElement loop. Set them to null or empty string. The reason they are grayed out is because they don't exist for those entries (rows).

    ReplyDelete
  14. Thank you! Last question: what if the field I want to populate is a non-string field? I can't seem to make them take non-string values...

    ReplyDelete
    Replies
    1. Since everything is XML behind the scenes, everything is a string. Declare your variable as the appropriate data type and then convert to string when assigning. For dates you may need to set to a dummy date (eg. 1/1/1990) but then once that row is added, set that field value to an empty string (I have sample code if needed).

      Delete
    2. Okay, I've gotten everything populated the way it needs to be EXCEPT for two time fields that need to be pre-populated. I want one to say 7:30 AM and another to say 4:00 PM, but when I use those times to the time fields as strings, I get a validation error that says the fields must contain a valid time... Thank you so much for your help - I've never used C# or InfoPath before.

      Delete
    3. Yeah it looks like Time can be tricky. You need to enter values using the 24-hour notation without the AM or PM in the format of HH:MM:SS. So for your 7:30 AM - enter 07:30:00. For 4:00 PM - enter 16:00:00.

      Delete
    4. You are THE man! You saved me so much time, and now I have a beautiful form.

      Delete
    5. Awesome! Feel free to help support my user group if you can.

      http://tinyurl.com/PhillySNUGDonate

      Delete
  15. I am getting this error on writer.close
    "schema validation found non-data type errors"

    ReplyDelete
    Replies
    1. Read this part of the post:

      ALSO! VERY IMPORTANT! The order in which you write the values to the table should be the order that they appear in the main data source. So if you expand the group1 and group2 and see field1, field2, field3, that is the order you must have in the code above. Otherwise you will receive a non-datatype schema validation error.

      Delete
  16. Hey Steve, I have a annoying problem while working with InfoPath form, I have a parent table (tbl_Parent {Parent_ID, name, ...}) and child table(tbl_Child{Child_ID,Parent_ID, name,.. }) in the database, Parent_ID is the primary key of parent table, Child_ID is the primary key for child table, they have one to many relationship. Then I designed a main infopath form with data fields associated with parent table and a repeating table in the form with data fields associated with child table. In the Infopath form, the main connection has two tables associated by Parent_ID.
    I can submit data into two tables correctly, but when I retrieve, make some changes on existing rows and add a new row in the repeating table and resubmit, one of the existing rows will be re-inserted again, instead of new row. Sounds like you cannot make changes and add new row at the same time, is that right?

    By the way, I am using InfoPath 2010, but in order to enable the "submit" button and submit data to database directly, I changed form type from "Web browser form" to "InfoPath 2007 filler form".

    Do you guys happen to know the solution for this? Thanks a lot!

    ReplyDelete
    Replies
    1. Most of my experience is on the web-enabled end of things. However, it sounds like your submit is only doing INSERTs and any rows submitted are considered new rows. You would need to submit to a stored procedure that would check to see if the row already exists, and if so, update the values, otherwise insert a new row.

      I can research the exact implementation...

      Delete
  17. Finally documented a nested scenario! http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html

    ReplyDelete
    Replies
    1. I could really use the nested example to Review. the link is not working

      Delete
    2. It worked for me on my phone at least. Try this:

      http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html?m=1

      Delete
  18. Steve,

    I'm getting the Schema validation found non-data type errors. Error still.

    I have the fields in the correct order. I have A Space, Keys and Values fields in the /GroupTotals/groupRepeat table

    I load up the key and vaule from a list I have loaded already.

    here is my code

    For Each de In dL0157630
    key = de.Key
    value = de.Value

    'put the data to the screen
    'Get namespace
    Dim myNamespace As String = NamespaceManager.LookupNamespace("my")

    'Set up the table
    Dim rTable As XPathNavigator = MainDataSource.CreateNavigator()
    Dim tableRows As XPathNodeIterator = rTable.[Select]("/my:myFields/my:GroupTotals/groupRepeat", NamespaceManager)

    'Delete the table info
    If tableRows.Count > 0 Then
    For i As Integer = tableRows.Count To 1 Step -1
    Dim reTable As XPathNavigator = MainDataSource.CreateNavigator()
    Dim reTableRows As XPathNavigator = reTable.SelectSingleNode("/my:myFields/my:GroupTotals/groupRepeat[" + i + "]", NamespaceManager)
    reTableRows.DeleteSelf()
    Next
    End If


    Using writer As XmlWriter = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:GroupTotals", NamespaceManager).AppendChild()

    writer.WriteStartElement("groupRepeat", myNamespace)
    writer.WriteElementString("Space", myNamespace, String.Empty)
    writer.WriteElementString("Keys", myNamespace, key.ToString())
    writer.WriteElementString("Values", myNamespace, value.ToString())
    writer.WriteEndElement()
    writer.Close()
    End Using







    Next

    ReplyDelete
    Replies
    1. I ran into issues before when trying to add an empty string (or a null) to a field. It treats it as if it is not being populated and thus produces the schema validation error.

      I would create a variable and set that to a space (" ") and then use that variable in the WriteElementString.

      Delete
    2. Added this code

      Dim sSpace As String
      sSpace = " "
      writer.WriteElementString("Space", myNamespace, sSpace)

      Same error still.

      Delete
    3. Ok. Send me the form and code behind - steve@stevethemanmann.com. I'll take a look.

      Delete
    4. I just sent you the code.

      Delete
    5. any luck here guys? I'm running into the same thing.

      thanks,
      Josh

      Delete
    6. In this case the repeating group was set at the wrong level (folder). Could be the same deal for but without seeing anything I can't tell...

      Delete
  19. I'm in need of copying a Secondary Data Connection Repeating Table to my main conncection repeating table row by row, and i was hoping to find a "no code" solution for this, but this is the best that i have found on the topic. Anyways, this is probably an extremely silly question, especially since coding isn't really in my skillset, but i was just wondering where i would actually copy and paste this code if i were to test it out? Would this be behind a library form template (code editor), or is this somthing embedded within SP Developer, or perhaps even none of the above? I'd really like to start getting into SP development over time, but just trying to find my way.

    ReplyDelete
    Replies
    1. Yes it would be behind a library form template using the Code Editor. You'll need to modify the code based on the fields that you are using.

      Delete
    2. Hi Steve, I really appreciate your reply. The issue I'm running into is this: I currently have a repeating section within a list template that tracks event dates. The nice thing about the list is that when a user submits the form containing the repeating table (as the main connection), it will populate within list as individual rows per the repeating table. This give us a quick overview of the dates, and makes reporting quite easy. However, I've now gotten to a place where we need to write code so that we can copy information from a secondary data source into new rows within the repeating table. Obviously, it seems like we can't write code to a list template. So we have the code written to copy on a library form template, but when we submit the repeating table, it shows as one distinct row instead of showing each line of the repeating table within the library. Any suggestions? Thank you for your time!

      Delete
    3. I'm thinking that maybe an ASP.Net User Control or SharePoint Visual Web Part (which contains a user control) would be a better option. The future of InfoPath is unknown at this point and what you need to have done is more on the complex side. In InfoPath you could connect to the SharePoint list web services and create multiple entries behind the scenes. The user control option would give you more flexibility and not lock you into the InfoPath interface. So in other words, if you going to go through all the coding trouble to get this to work in InfoPath, you might as well just do it using ASP.Net. It will make any future migration easier too.

      -=Steve

      Delete
  20. Great point. I'll look into doing it this way instead! Very much appreciated.

    ReplyDelete
  21. Steve,

    I need to populate a repeating section inside repeating section.
    I am using SQL Stored Proc to get the data from the database.
    Here is my Info path datasource
    -Myfields
    - group1
    -group 2
    - Agenda
    - group 3
    - group 4
    - Description
    - ECD
    There can be multiple Agenda and each agenda can have multiple Description and ECD.

    Here is my code

    public void getOldBusiness()
    {
    SqlConnection myConnection = new SqlConnection(m_ConnectionString);
    XPathNavigator myNav = MainDataSource.CreateNavigator();

    myConnection.Open();
    SqlCommand business_cmd = new SqlCommand("Get_Business", myConnection);
    SqlDataAdapter myDataAdapter = new SqlDataAdapter();
    myDataAdapter.SelectCommand = business_cmd;
    business_cmd.CommandType = CommandType.StoredProcedure;
    business_cmd.Parameters.AddWithValue("@Meeting_ID", _URLUniqueID);
    business_cmd.Parameters.AddWithValue("@Meeting_Flag_Old_New", "O");
    DataTable myDataTable = new DataTable();
    myDataAdapter.Fill(myDataTable);
    // Get a reference to the node the repeating table is bound to
    XPathNavigator xNav;
    xNav = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group1/my:group2", this.NamespaceManager);
    foreach (DataRow row in myDataTable.Rows)
    {
    XPathNavigator xRow = xNav.Clone();
    string s_Agenda_Name = row.ItemArray[1].ToString();

    int i_Meeting_Business_ID = Convert.ToInt32(row.ItemArray[0].ToString());
    xRow.SelectSingleNode("/my:myFields/my:group1/my:group2/my:Agenda", this.NamespaceManager).SetValue(s_Agenda_Name);
    xNav.InsertBefore(xRow);

    getOldBusinessDisucssion(_URLUniqueID, s_Agenda_Name);

    }
    xNav.DeleteSelf();
    }

    public void getOldBusinessDisucssion(string meeting_ID, string agenda_Name, )
    {
    SqlConnection myConnection = new SqlConnection(m_ConnectionString);
    myConnection.Open();
    SqlCommand business_cmd = new SqlCommand("Get_Meeting_Business_Item_Discussion", myConnection);
    SqlDataAdapter myDataAdapter = new SqlDataAdapter();
    myDataAdapter.SelectCommand = business_cmd;
    business_cmd.CommandType = CommandType.StoredProcedure;
    business_cmd.Parameters.AddWithValue("@Meeting_ID", meeting_ID);
    business_cmd.Parameters.AddWithValue("@Meeting_Business_ID", meeting_Busiess_ID);
    DataTable myDataTable = new DataTable();
    myDataAdapter.Fill(myDataTable);

    // Get a reference to the node the repeating table is bound to
    XPathNavigator repTable = xNav.CreateNavigator().SelectSingleNode("/my:myFields/my:group1/my:group2/my:group3/my:group4", NamespaceManager);
    XPathNavigator node;
    foreach (DataRow row in myDataTable.Rows)
    {

    string s_Meeting_Discussion = row.ItemArray[0].ToString();
    node = repTable.Clone();
    node.SelectSingleNode("/my:myFields/my:group1/my:group2/my:group3/my:group4/my:Description", NamespaceManager).SetValue(s_Meeting_Discussion);
    xNav.InsertBefore(node);

    }
    }



    ReplyDelete
    Replies
    1. You need to follow my "nested" solution here:
      http://stevemannspath.blogspot.com/2012/11/infopath-200720102013-dynamically.html

      That may provide more insight.

      Delete
    2. Steve,

      Is it possible to write to one repeating table (inside an InfoPath form) to a repeating table that is the primary read/submit table for an access database? I am using this code to write from one repeating table to anoth, but I am stumped on how to get the information into the read/write table so I can update the access main table:

      Public Sub CTRL174_23_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

      Dim Nav As XPathNavigator = Me.DataSources("PartsList").CreateNavigator()
      Dim rows As XPathNodeIterator = Nav.Select("/dfs:myFields/dfs:dataFields/d:PartsList", NamespaceManager)

      While rows.MoveNext()

      Dim PartNumb As String = rows.Current.SelectSingleNode("@PartNumb", NamespaceManager).Value
      Dim PartDesc As String = rows.Current.SelectSingleNode("@PartDesc", NamespaceManager).Value
      Dim Color As String = rows.Current.SelectSingleNode("@Color", NamespaceManager).Value
      Dim OracleNmbr As String = rows.Current.SelectSingleNode("@OracleNmbr", NamespaceManager).Value
      Dim RevitNme As String = rows.Current.SelectSingleNode("@RevitNme", NamespaceManager).Value
      Dim Catg As String = rows.Current.SelectSingleNode("@Catg", NamespaceManager).Value
      Dim CSPK As String = rows.Current.SelectSingleNode("@CSPK", NamespaceManager).Value
      Dim Selecta As String = rows.Current.SelectSingleNode("@Select", NamespaceManager).Value

      If Selecta = "True" Then
      Try

      Dim node As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode( _
      "/dfs:myFields/my:Contain/my:PartsListUpdate", NamespaceManager)

      DeleteNil(node)

      Dim myNamespace As String = NamespaceManager.LookupNamespace("my")
      Using writer As XmlWriter = MainDataSource.CreateNavigator().SelectSingleNode( _
      "/dfs:myFields/my:Contain", NamespaceManager).AppendChild()
      writer.WriteStartElement("PartsListUpdate", myNamespace)
      writer.WriteElementString("PartNumb", myNamespace, PartNumb)
      writer.WriteElementString("PartDesc", myNamespace, PartDesc)
      writer.WriteElementString("Color", myNamespace, Color)
      writer.WriteElementString("OracleNmbr", myNamespace, OracleNmbr)
      writer.WriteElementString("RevitNme", myNamespace, RevitNme)
      writer.WriteElementString("Catg", myNamespace, Catg)
      writer.WriteElementString("CSPK", myNamespace, CSPK)
      writer.WriteEndElement()
      writer.Close()
      End Using
      Catch ex As Exception
      MessageBox.Show(ex.Message)
      End Try
      End If
      End While
      End Sub

      Delete
    3. I have not had experience with Access data sources within InfoPath. You can't get to it using the MainDataSource object?

      Delete
  22. Hello Steve, I'm building a form using infopath 2010. The primary repeating table should be populated with the secondary table. The secondary data source is a sharepoint list. This list is updated 2x a day. I'm very naive when it comes to C+ but doing my best to follow it. so much so that I created a list and database to match the criteria here to get the feel for it. I'm getting stuck where it connects to the secondary source.

    //Connect to secondary data source
    DataSource ds = DataSources["GetData"];
    XPathNavigator domNav = ds.CreateNavigator(); XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/tns:GetDataResponse/tns:GetData/NewDataSet/DynamicData", NamespaceManager);

    I get a Namespace prefix 'tns' is not defined. error
    I'm assuming because this was set up for web service based connection? in that case what would I use instead of tns:?
    I really appreciate your time.

    ReplyDelete
    Replies
    1. In the data source task pane (where you can see the fields on your form) use the drop-down to select your GetData secondary source. Expand the response folders until you get to the last folder. Right-click the last folder and select Copy XPath. Paste it into your code. That will give you the correct data path.

      Delete
    2. Hi, in my case, I cannot see any response folder. How will the reponse folder show?

      Delete
    3. You need to select the secondary data source in the data source task pane. There should be two sub-folders. The bottom one is usually the response folder.

      Delete
  23. Mr Mann, Your are the Man! So after copying the XPath, I was getting some Schema Validation errors, Well I knew that i followed the Sequence correctly. What I wasn't aware of is CAPITALIZATION makes a difference. Field1 would not write to field1. Got that sorted out and everything is working perfectly. I would like to thank you for your great breakdown and explanation of complete code. MANY google searches did not come close to all the info I have gotten here. thanks again.

    ReplyDelete
  24. Hmm One last question, I recreated the form using my existing main and secondary tables. When I execute the code, it deletes table, and populates the table with the correct amount of rows, but all rows have the same info as row 1.? I'm going thru the code now, but nothing is getting my attention. Do you know where I made a mistake?

    ReplyDelete
    Replies
    1. You have the variable settings (similiar to below) within the loop, correct?

      string accountID = rows.Current.SelectSingleNode("AccountID", NamespaceManager).Value.ToString();

      Delete
    2. //Get namespace
      string myNamespace = NamespaceManager.LookupNamespace("my");
      //Clear any previous entries
      XPathNavigator rTable = MainDataSource.CreateNavigator(); XPathNodeIterator tableRows = rTable.Select("/my:myFields/my:RepeatingTable/my:TMMTXItem", NamespaceManager);

      if (tableRows.Count > 0)
      {for (int i = tableRows.Count; i > 0; i--)
      {
      XPathNavigator reTable =
      MainDataSource.CreateNavigator();
      XPathNavigator reTableRows =
      reTable.SelectSingleNode("/my:myFields/my:RepeatingTable/my:TMMTXItem [" + i + "]",
      NamespaceManager);
      reTableRows.DeleteSelf();
      }
      }
      //Connect to secondary data source
      DataSource ds = DataSources["TMMTXPasteList"];
      XPathNavigator domNav = ds.CreateNavigator(); XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager);

      //Loop through secondary data source and populate the repeating table

      while (rows.MoveNext())
      {

      string field1 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title", NamespaceManager).Value.ToString();
      string field2 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:RELEASE_x0020_NBR", NamespaceManager).Value.ToString();
      string field3 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:SOLD_TO_x0020_COMPANY", NamespaceManager).Value.ToString();
      string field4 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:SHIP_TO_x0020_COMPANY", NamespaceManager).Value.ToString();
      string field5 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:PART_NO", NamespaceManager).Value.ToString();
      string field6 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:MST_x0020_MATERIAL_x0020_ID", NamespaceManager).Value.ToString();
      string field7 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:MILL_x0020_COIL_x0020_ID", NamespaceManager).Value.ToString();
      string field8 = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ID", NamespaceManager).Value.ToString();

      using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:RepeatingTable",
      NamespaceManager).AppendChild())
      {
      writer.WriteStartElement("TMMTXItem", myNamespace);
      writer.WriteElementString("PickSlipNbr", myNamespace, field1);
      writer.WriteElementString("ReleaseNbr", myNamespace, field2);
      writer.WriteElementString("SoldToCompany", myNamespace, field3);
      writer.WriteElementString("ShipToCompany", myNamespace, field4);
      writer.WriteElementString("PartNo", myNamespace, field5);
      writer.WriteElementString("MSTMaterialId", myNamespace, field6);
      writer.WriteElementString("MillCoilID", myNamespace, field7);
      writer.WriteElementString("ItemNo", myNamespace, field8);
      writer.WriteEndElement();
      writer.Close();}
      }

      Delete
    3. In your select single node statements, just use "d:<>" not the full path again. That is only selecting the first row each time.

      Delete
    4. Steve, I think I might be experiencing a similar issue. The code works but all the entries on the table are the first result of the data set. The funny thing is that using break points in code it sets the variables correctly up to the writer.Close line, so I'm not sure exactly what is going on on this. Thanks so much for your assistance, this is the only example of this unique situation I have found.

      Delete
    5. Send me your code and I'll have a look.

      Delete
    6. {
      //Get namespace
      string myNamespace = NamespaceManager.LookupNamespace("my");
      //Clear any previous entries
      XPathNavigator rTable = MainDataSource.CreateNavigator(); XPathNodeIterator tableRows = rTable.Select("/my:Root/my:Details/my:RepTable", NamespaceManager);

      if (tableRows.Count > 0)
      {
      for (int i = tableRows.Count; i > 0; i--)
      {
      XPathNavigator reTable =
      MainDataSource.CreateNavigator();
      XPathNavigator reTableRows =
      reTable.SelectSingleNode("/my:Root/my:Details/my:RepTable[" + i + "]",
      NamespaceManager);
      reTableRows.DeleteSelf();
      }
      }

      //Connect to secondary data source
      DataSource ds = DataSources["1F Dashboard Call Times"];
      XPathNavigator domNav = ds.CreateNavigator();
      XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager);

      //Loop through secondary data source and populate the repeating table

      while (rows.MoveNext())
      {

      string repName = rows.Current.SelectSingleNode("d:Title", NamespaceManager).Value.ToString();
      using (XmlWriter writer =MainDataSource.CreateNavigator().SelectSingleNode("/my:Root/my:Details",NamespaceManager).AppendChild())

      {
      writer.WriteStartElement("RepTable", myNamespace);
      writer.WriteElementString("RepName",
      myNamespace, repName);

      writer.WriteEndElement();
      writer.Close();
      }
      }
      }

      I know this will only create a 1 column table, but I'd like to get it working and then I will create blank fields for the other columns.

      Thanks,

      Delete
    7. I can't find anything wrong. Maybe it doesn't like just one column.

      Delete
    8. I found what was wrong, nothing to do with the code. I had a rule on the repeating table itself to set the RepName field to d:Title from when I was trying a codeless solution. Color me embarrassed. I removed that rule and it worked perfectly. Thanks Steve!

      Delete
  25. That's what it was, Appreciate your help.

    ReplyDelete
  26. Steve, I've got everything working well, Thanks to your help. I wanted to add something now.
    I have a secondary List adding all info to the Main Repeating List. I've added an additional field in the main list that I would like populated with an option that is selected before importing the list.
    example:
    user selects option 2 of 3, clicks import, repeating table is populated with secondary source and the option from the main.
    How do I call the value from the main and populate it to the repeating Table?

    Also I started creating another form, I was wondering, Is it possible to populate not only the main repeating table but also a secondary repeating table with the 2nd data source?

    Reason because, I would like to have a running list without clearing the data. So the primary list would have Just the new data, the secondary table will have the previous data plus the newly imported appended.

    ReplyDelete
    Replies
    1. You can place a rule on the option (when this field changes) or on the button (when clicked). The rule would Query the secondary datasource using the option selected as a filter on the data.

      You can use similiar code and loops to populate another repeating table - just subsitute the name of the first group with the second group.

      Delete
  27. I also experienced the schema validation error. i checked my main data source and the writelementstring is in correct order. btw, i have a people picker in my repeating table
    heres the code

    writer.WriteStartElement("RepeatingTable", myNamespace);
    writer.WriteElementString("ActionItemsPP", myNamespace, userVal.LookupValue);
    writer.WriteElementString("Task", myNamespace, task);
    writer.WriteElementString("Status", myNamespace, status);
    writer.WriteElementString("displayName", myNamespace, Convert.ToString(userVal.LookupValue));
    writer.WriteEndElement();
    writer.Close();

    ReplyDelete
    Replies
    1. You need to generate the people picker structure. You can't just enter displayName. You'll need to create the people picker group, repeating group (pc:Person), and then the DisplayName, AccountId, and AccountType fields.

      Delete
  28. Hi Mr Mann,

    I've been tasked with creating a classroom course attandence record. An InfoPath form pulls data from a SharePoint list. A repeating table shows the expected students to attend the class when a student signs in, it sets a flag to signed in. After class the course tutor submits the form to a SharePoint libray for further use.

    I now know that you cannot use a secondardy repeating table to display/submit the data as I cannot add additional fields or resubmit the secondary data, so I need to copy the data to the primary repeating table.

    I'm new to post and without training, therefore I just don't understand where the code meant to be stored.

    Sorry if I sound a little thick. I hope you can Help...

    Stephen Deacon

    ReplyDelete
    Replies
    1. On the Developer top ribbon there should be a Code Editor button. You will probably need the tools installed.

      Delete
    2. Thank you Mr Mann,

      I found it and added your code, the funny thing is I found your book on your shelf and tried to go through it bit by bit, I've written the code, but there is coding errors. I did create a word document to show you the code etc, I'm unable to find anyway of uploading for you, so I will do my best in text:

      CODE:
      //Get namespace
      string myNamespace = NamespaceManager.LookupNamespace("my");
      //Clear any previous entries
      XPathNavigator rTable = MainDataSource.CreateNavigator(); XPathNodeIterator tableRows = rTable.Select("/my:myFields/my:RepeatingTable/my:group4/my:group5", NamespaceManager);

      if (tableRows.Count > 0)
      {for (int i = tableRows.Count;i > 0; i--)
      {
      XPathNavigator reTable = MainDataSource.CreateNavigator();
      XPathNavigator reTableRows = reTable.SelectSingleNode("/my:myFields/my:RepeatingTable/my:group4/my:group5[" + i + "]",NamespaceManager);
      reTableRows.DeleteSelf();
      }
      }

      //Connect to secondary data source
      DataSource ds = DataSources["FDoctors"];
      XPathNavigator domNav = ds.CreateNavigator(); XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager);

      //Loop through secondary data source and populate the repeating table

      while (rows.MoveNext())
      {

      string Student = rows.Current.SelectSingleNode("D:StaffMember/pc:Person/pc:DisplayName", NamespaceManager).Value.ToString();

      using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:RepeatingTable/my:group4",NamespaceManager).AppendChild())

      {
      writer.WriteStartElement("group5", myNamespace);
      writer.WriteElementString("StudentName",myNamespace ,Student);
      writer.WriteEndElement();
      writer.Close(); }
      }

      End Namespace


      PRIMARY STRUCTURE:
      myFields
      +RepeatingTable
      StudentName

      There are other fields but not being used for copying data over from the secondary repeating table.

      SECONDARY STRUCTURE:
      FDcotors
      +dataFields
      +StaffMember
      +pc:Person
      DisplayName

      I really hope you can help,

      Thanks in advance,

      Stephen Deacon

      Delete
    3. Send me your form and code - steve at stevethemanmann.com. I'll take a look and figure out what the problem is.

      Delete
  29. I got it working just fine as posted. Then I started tweaking to my needs and got an error message, a "challenge". My datasource is actually an Xml I build from the XmlNode returned from the sharepoint webservice (GetListItems webmethod). Since the list I am retrieving is big, I invoked an asyncronous webmethod (GetListItemsAsync). My problem is when the Callback method is invoked, then is when I try to access the my infopath form's MainDataSource to make the call in the line "XPathNavigator rTable = MainDataSource.CreateNavigator();". There is where i get the following message:
    "Operation is not valid due to the current state of the object."

    ReplyDelete
  30. This looks really great, but not precisely what I'm looking for. I've been google'ing for days and this is the closest.

    I have a very large List in terms of columns. In fact I reached the max. So I need to segregate some of the data into other List Libraries.
    If I have 200 rows in the primary table, I will have 200 rows in the 'secondary' tables as well with a matching bit of info.

    1. I want to create a new row on the secondary table when a new record is created on the main.
    Does this add 1 new line?
    while (rows.MoveNext())
    {
    }
    2. When editing the main, I want to update only that 1 matching entry in the secondary table. How do I do a validation for that?

    ReplyDelete
    Replies
    1. The loops through the rows. The code within the loop creates a new line:

      writer.WriteStartElement("group2", myNamespace);
      writer.WriteElementString("Amount",myNamespace ,amount);
      writer.WriteElementString("AccountID", myNamespace, accountID);
      writer.WriteElementString("AccountNumber", myNamespace,
      accountNumber);
      writer.WriteEndElement();
      writer.Close();

      To get the matching entry you would need to retrieve it from the DOM. Not sure off hand how to retrieve just a specific row. Since you have so many you probably don't want to loop through to find the right one.

      Delete
  31. Hi Steve,
    I came across your blog via my 'insert new row in repeating table using code' search so excuse me if I'm asking unrelated questions. Anyway, I am trying to create a mockup system for training purposes. The new system has 2 views (or pages), one is where staff enters note, and the other is where staff views all entered notes (current and past). In my mockup system, I'm trying to create a button in the 'ENTER NOTES' page that will insert a new blank row and populate the fields of my repeating table in the "VIEW NOTES" page. I searched everywhere and tried their solutions but nothing works.

    VIEW NOTES (1 repeating table with 2 columns)
    - RT_Date
    - RT_Notes


    ENTER NOTES (2 textboxes)
    - EN_Date
    - EN_Notes

    and a button labeled "Save"

    So basically, each time the save button is clicked, the value from EN_Date and EN_Notes are transferred to RT_Date and RT_Notes. The repeating table is sorted in decending order as well (by date).

    Any input is greatly appreciated. I already spend hours searching the internet for a complete solution rather than snippet of codes I cant put together.



    ReplyDelete
    Replies
    1. I have:

      myFields
      DataEntryDate
      DateEntryNotes
      group1
      group2
      RT_Date
      RT_Notes

      Here is my click event code:
      public void CTRL3_6_Clicked(object sender, ClickedEventArgs e)
      {
      //Get Namespace
      string myNamespace = NamespaceManager.LookupNamespace("my");

      // Select the Data Entry fields.
      XPathNavigator myForm = this.CreateNavigator();
      XPathNavigator dateField = myForm.SelectSingleNode("/my:myFields/my:DataEntryDate", NamespaceManager);
      XPathNavigator notesField = myForm.SelectSingleNode("/my:myFields/my:DataEntryNotes", NamespaceManager);

      //Write values to repeating table
      using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group1", NamespaceManager).AppendChild())
      {
      writer.WriteStartElement("group2", myNamespace);
      writer.WriteElementString("RT_Date", myNamespace, dateField.Value);
      writer.WriteElementString("RT_Notes", myNamespace, notesField.Value);
      writer.WriteEndElement();
      writer.Close();
      }

      //Clear data entry fields
      dateField.SetValue("");
      notesField.SetValue("");
      }

      Delete
    2. The indentations did not come through. Let me know if you need more clarification.

      Delete
  32. Very helpful article ! I was always curious about all these complex algorithms that are being used in these ssl encryptions.

    ReplyDelete
  33. Hi Steve and all,

    I have used the code below but the the first row is repeated 'n' times as the row count (n):
    Please, can you help me ?


    public void RechercherRegr_Clicked(object sender, ClickedEventArgs e)
    {

    // Tapez votre code ici.

    string myNamespace = NamespaceManager.LookupNamespace("my");
    DataSource ds = DataSources["getUnpaidFromRegroupCode"];
    XPathNavigator domNav = ds.CreateNavigator();
    XPathNavigator codeRegrep = ds.CreateNavigator();
    string codeRegrepField = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:mesChamps/my:recherche/my:Section_Regr/my:No_Regr", NamespaceManager).Value.ToString();
    codeRegrep.SelectSingleNode("/dfs:myFields/dfs:queryFields/ns1:getUnpaidFromRegroupCode/ns1:regroupCode", NamespaceManager).SetValue(codeRegrepField);
    ds.QueryConnection.Execute();
    XPathNodeIterator rows = domNav.Select("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos", NamespaceManager);

    while (rows.MoveNext())
    {
    string code_regr = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos/ns1:codeRegroupement", NamespaceManager).Value.ToString();
    string numero_Contr = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos/ns1:numeroContrat", NamespaceManager).Value.ToString();
    string numero_fact = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos/ns1:numeroFacture", NamespaceManager).Value.ToString();
    string nom_Cli = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos/ns1:nomClient", NamespaceManager).Value.ToString();
    string montant_Impaye = rows.Current.SelectSingleNode("/dfs:myFields/dfs:dataFields/ns1:getUnpaidFromRegroupCodeResponse/ns1:getUnpaidFromRegroupCodeResult/ns1:BillInfos/ns1:montantImpaye", NamespaceManager).Value.ToString();
    //string mountant_Paye = rows.Current.SelectSingleNode("montantImpaye", NamespaceManager).Value.ToString();
    using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:mesChamps", NamespaceManager).AppendChild())
    {
    writer.WriteStartElement("TableImpayes", myNamespace);
    writer.WriteElementString("Code_Regr", myNamespace, code_regr);
    writer.WriteElementString("No_Contr", myNamespace, numero_Contr);
    writer.WriteElementString("No_Fact", myNamespace, numero_fact);
    writer.WriteElementString("Nom_Cli", myNamespace, nom_Cli);
    writer.WriteElementString("Montant_Impaye", myNamespace, montant_Impaye);
    // writer.WriteElementString("Montant_Paye", myNamespace, montant_Impaye);
    // writer.WriteElementString("choix", myNamespace, "1");
    writer.WriteEndElement();
    //writer.Close();
    }
    AddItem(code_regr, numero_Contr, numero_fact, nom_Cli, montant_Impaye);

    }


    }


    JLB



    ReplyDelete
    Replies
    1. In your rows loop, do not select the whole path again:

      string code_regr = rows.Current.SelectSingleNode("ns1:codeRegroupement", NamespaceManager).Value.ToString();
      string numero_Contr = rows.Current.SelectSingleNode("ns1:numeroContrat", NamespaceManager).Value.ToString();
      ...

      Delete
  34. It's work fine.

    Thank a lot, Steve.

    JLB

    ReplyDelete
  35. Hi Steve,

    It's worked fine.

    Thank you very much.

    JLB

    ReplyDelete
  36. Hi Steve, Is there a way to populate a Multiple repeating table from another Multiple repeating Table without code?
    I have a Sharepoint List form, I have a Multiple repeating Table in my Form. I create a View to EDIT items.
    But when I try to get the values from my MainDataSource, a different ID I use a rule when this field change and I recieve My ID, names, date but, when I try to have my selected options from my Multiple selection list to the MultipleSelectionList in the form I just get the 1st one or Get all values in a same row and it appears as a new option.
    Or wht do you suggest me to do.

    ReplyDelete
  37. Hi!
    I have a Sharepointlist form.
    I need to edit my items but i cant use quick edit because it doesnt have filters.
    I need to populate a MultipleSelectionList from my Main data, when I select a different ID I use a rule to get all my information in different fields, I can do this.
    But when I try to get my values that were in the MultipleSelection I just get the 1st one or I get all values in a single row, creating a new option in my Multiple selection list.
    like. I populate my Multiple selection list from another sharepoint list and there is no problem, just when i want to receive the values, to appear SELECTED in the Multiple selection list.
    MultipleSEl
    x Option 1
    Option 2
    Option 3
    or get this

    MultipleSel
    Option 1
    Option 2
    Option 3
    x Option 1 Option 3

    ReplyDelete
    Replies
    1. I would have to look at this with you. I haven't been doing InfoPath in awhile.

      Delete
    2. I would have to look at this with you. I haven't been doing InfoPath in awhile.

      Delete