Pre-populating repeating section from database. - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Pre-populating repeating section from database.

Last post 11-02-2016 09:36 AM by Hilary Stoupa. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 10-25-2016 07:52 AM

    • kee81
    • Not Ranked
    • Joined on 10-25-2016
    • Posts 9

    Pre-populating repeating section from database.

    Having some trouble figuring out how to pre-populate my repeating section in InfoPath. Here's my scenario. I need to provide some production personnel with a form that lets them submit production data to the database. I have a form that has a repeating section that lets them submit whatever number of rows they need to. Each row represents the production numbers for a given part on a given shift. Often times the full production run of these parts might take several days, sometimes weeks to complete. At the end of a shift, a supervisor will look over the numbers carefully and submit them to the database via the form. Then, when the next shift's supervisor opens the form, I want the last shift's record rows to auto-populate in the form upon opening, so that they don't have to fill in 20 rows of part numbers for parts that still have many shifts to go before the order is complete. I have the query worked out to get these "last shift's numbers" records from the SQL Server, and I know InfoPath can query the server, but I can't figure out how to get InfoPath to populate that repeating section based on it. I have a main connection set up that allows me to submit the form data. I created a secondary "retrieve data" connection to query the data, but I can't figure out if the query is truly working or not, or how to get that queried data to my form. Thanks for any help or direction!
  • 10-28-2016 03:22 PM In reply to

    Re: Pre-populating repeating section from database.

     So - is the goal to submit new data to the DB that has some of the same data? Or are you trying to update the last shift's records?  Generally - copying data from one repeating table to another in InfoPath is going to require code (or qRules) - I don't think you'd be able to use the default data trick with a DB bound form....

    Hilary Stoupa

  • 10-31-2016 05:37 AM In reply to

    • kee81
    • Not Ranked
    • Joined on 10-25-2016
    • Posts 9

    Re: Pre-populating repeating section from database.

    The goal is to submit new data to the database based on the previous shift's data. Not an update to the old record. I may not have worded that very clearly. I think you're right about the code. So far I have code to loop through the secondary data source records. Having trouble getting it to copy to the main data connection. I'm sure I'll get it soon enough! ;)
  • 10-31-2016 07:25 AM In reply to

    Re: Pre-populating repeating section from database.

    Post your code if you need a hand. :)
    Hilary Stoupa

  • 11-01-2016 10:15 AM In reply to

    • kee81
    • Not Ranked
    • Joined on 10-25-2016
    • Posts 9

    Re: Pre-populating repeating section from database.

    Help! :) So here's my code, and an attached picture of my data sources. I was essentially trying to follow what was going on here: http://www.bizsupportonline.net/infopath2007/4-way-programmatically-add-row-repeating-table.htm Again, the goal is to copy the data from the secondary source to the main data source.
    private void AddItem(string po_number, string qty_on_po, string line, string description, string start_box,
                string end_box, string parts_per_box, string run_time, string setup_time, string comments)
            {
                XmlDocument doc = new XmlDocument();
                XmlNode group = doc.CreateElement("q:production_items", NamespaceManager.LookupNamespace("my"));
    
                XmlNode field = doc.CreateElement("PO_NUMBER", NamespaceManager.LookupNamespace("my"));
                XmlNode node = group.AppendChild(field);
                node.InnerText = po_number;
                
                field = doc.CreateElement("QTY_ON_PO", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = qty_on_po;
    
                field = doc.CreateElement("LINE", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = line;
    
                field = doc.CreateElement("DESCRIPTION", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = description;
    
                field = doc.CreateElement("BOX_START", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = start_box;
    
                field = doc.CreateElement("BOX_END", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = end_box;
    
                field = doc.CreateElement("PARTS_PER_BOX", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = parts_per_box;
                
                field = doc.CreateElement("RUN_TIME", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = run_time;
    
                field = doc.CreateElement("SETUP_TIME", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = setup_time;
    
                field = doc.CreateElement("COMMENTS", NamespaceManager.LookupNamespace("my"));
                node = group.AppendChild(field);
                node.InnerText = comments;
                
                doc.AppendChild(group);
                //MessageBox.Show(doc.OuterXml.ToString());
    
                // crashes on append
                MainDataSource.CreateNavigator().SelectSingleNode("d:production_items", 
                    NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());
    
            }
    
            private void DeleteFirstEmptyItem()
            {
                XPathNavigator domNav = MainDataSource.CreateNavigator();
    
                XPathNavigator itemNav = domNav.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:production_record/d:production_items[1]", NamespaceManager);
                if (itemNav != null)
                    itemNav.DeleteSelf();
            } 
    
            public void CTRL224_7_Clicked(object sender, ClickedEventArgs e)
            {
                XPathNavigator secDSNav = DataSources["last_shift_query"].CreateNavigator();
                
                // Retrieve the rows of the secondary data source
                XPathNodeIterator rows = secDSNav.Select("/dfs:myFields/dfs:dataFields/d:previous_shift_production", NamespaceManager);
                
                
                // Loop through the rows of the secondary data source and fill the repeating table
                while (rows.MoveNext())
                {
                    string po_number = rows.Current.SelectSingleNode("@PO_NUMBER", NamespaceManager).Value;
                    string qty_on_po = rows.Current.SelectSingleNode("@QTY_ON_PO", NamespaceManager).Value;
                    string line = rows.Current.SelectSingleNode("@LINE", NamespaceManager).Value;
                    string description = rows.Current.SelectSingleNode("@DESCRIPTION", NamespaceManager).Value;
                    string start_box = rows.Current.SelectSingleNode("@BOX_START", NamespaceManager).Value;
                    string end_box = rows.Current.SelectSingleNode("@BOX_END", NamespaceManager).Value;
                    string parts_per_box = rows.Current.SelectSingleNode("@PARTS_PER_BOX", NamespaceManager).Value;
                    string run_time = rows.Current.SelectSingleNode("@RUN_TIME", NamespaceManager).Value;
                    if (run_time == null) {
                        run_time = "";
                    }
                    string setup_time = rows.Current.SelectSingleNode("@SETUP_TIME", NamespaceManager).Value;
                    if (setup_time == null) {
                        setup_time = "";
                    }
                    string comments = rows.Current.SelectSingleNode("@COMMENTS", NamespaceManager).Value;
                    if (comments == null) {
                        comments = "";
                    }
    
                    // Add the item to the repeating table
                    AddItem(po_number, qty_on_po, line, description, start_box, end_box, parts_per_box, run_time, setup_time, comments);
                }
    
                // Remove the first empty item from the repeating table
                DeleteFirstEmptyItem();
            }

  • 11-01-2016 03:17 PM In reply to

    Re: Pre-populating repeating section from database.

    Hmmm. A couple of things I notice in your "AddItem" method - you are starting with q:production_items - don't you want "d" instead of "q"? Then, at the end, you are using append child, after selecting d:production_items, but looking at your data source, don't you want to be appending this new child to d:production_record?
    Hilary Stoupa

  • 11-01-2016 04:54 PM In reply to

    • kee81
    • Not Ranked
    • Joined on 10-25-2016
    • Posts 9

    Re: Pre-populating repeating section from database.

    Oops, I originally had it as d:production_items. I was trying stuff out and forgot to change it back. I changed the bottom to try to append to d:production_record, but that's not working either. The final append line is throwing up a null reference exception. I'm doing something wrong in that statement and I'm not sure what.
  • 11-02-2016 09:36 AM In reply to

    Re: Pre-populating repeating section from database.

     Sorry, I should have seen that. You need to use the full path to the production_record group here - right click the field in the fields taskpane and select copy XPath.

    MainDataSource.CreateNavigator().SelectSingleNode("FULLPATHTOPRODUCTION_RECORD", NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());

    Hilary Stoupa

Page 1 of 1 (8 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.