SQL Database Integration

Re: Pre-populating repeating section from database.

Hilary Stoupa — Wed, 02 Nov 2016 16:36:55 GMT

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());

Re: Pre-populating repeating section from database.

kee81 — Tue, 01 Nov 2016 23:54:59 GMT

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.

Re: Pre-populating repeating section from database.

Hilary Stoupa — Tue, 01 Nov 2016 22:17:17 GMT

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?

Re: Pre-populating repeating section from database.

kee81 — Tue, 01 Nov 2016 17:15:29 GMT

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();
        }

Re: Pre-populating repeating section from database.

Hilary Stoupa — Mon, 31 Oct 2016 14:25:55 GMT

Post your code if you need a hand. :)

Re: Pre-populating repeating section from database.

kee81 — Mon, 31 Oct 2016 12:37:28 GMT

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! ;)

Re: Pre-populating repeating section from database.

Hilary Stoupa — Fri, 28 Oct 2016 22:22:13 GMT

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....

Pre-populating repeating section from database.

kee81 — Tue, 25 Oct 2016 14:52:38 GMT

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!