Code

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Thu, 02 Apr 2009 22:34:21 GMT

Hi -- can you give me some more information about what you are trying to do and what is going wrong?

Re: Iterate through repeating table with complex SQL query

bcowley — Wed, 01 Apr 2009 22:57:58 GMT

Hilary, I am having a very similar problem and hope you can help me. I am also very new to C# and XPath!!!

I am trying to copy results from a secondary datasource to a repeating table in my form. The repeating table is setup as follows

/my:myFields

/my:myFields/my:GasPrices

/my:myFields/my:GasPrices/my:GasPrice

/my:myFields/my:GasPrices/my:GasPrice/my:ItemNo

/my:myFields/my:GasPrices/my:GasPrice/my:Description

/my:myFields/my:GasPrices/my:GasPrice/my:UnitPrice

/my:myFields/my:GasPrices/my:GasPrice/my:UnitOfMeasureCode

/my:myFields/my:GasPrices/my:GasPrice/my:PriceIncludesVAT

/my:myFields/my:GasPrices/my:GasPrice/my:MonthEndDate

and my secondary datasource as follows...

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Item_x0020_No_

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Description

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Unit_x0020_Price

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Unit_x0020_of_x0020_Measure_x0020_Code

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Price_x0020_Includes_x0020_VAT

/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1/ns1:Month_x0020_End_x0020_Date

Here is the code behind that I am using...

public void CTRL9_5_Clicked(object sender, ClickedEventArgs e)
{
            // Access the main data source
            XPathNavigator myForm = MainDataSource.CreateNavigator();
            XPathNavigator myRows = myForm.SelectSingleNode("/my:myFields/my:GasPrices/my:GasPrice", NamespaceManager);

            XPathNavigator parent = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:GasPrices", NamespaceManager);

            //Delete the first row in the destination repeating group if it is empty
            XPathNavigator myRow = myForm.SelectSingleNode("/my:myFields/my:GasPrices/my:GasPrice[1]", NamespaceManager);
            if (myRow != null)
                myRow.DeleteSelf();

            //Select the field
            XPathNavigator mySecDS = DataSources["GetGasPriceROI"].CreateNavigator();

            XPathNodeIterator nodes = mySecDS.Select("/dfs:myFields/dfs:dataFields/s0:GetGasPriceROIResponse/s0:GetGasPriceROIResult/ns1:SqlRowSet1/ns1:row1", NamespaceManager);

            foreach (XPathNavigator node in nodes)
            {
                XPathNavigator newNode = mySecDS.Clone();
                newNode.SelectSingleNode("my:ItemNo", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Item_x0020_No_", NamespaceManager).Value);
                newNode.SelectSingleNode("my:Description", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Description", NamespaceManager).Value);
                newNode.SelectSingleNode("my:UnitPrice", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Unit_x0020_Price", NamespaceManager).Value);
                newNode.SelectSingleNode("my:UnitOfMeasureCode", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Unit_x0020_Of_x0020_Measure_x0020_Code", NamespaceManager).Value);
                newNode.SelectSingleNode("my:PriceIncludesVAT", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Price_x0020_Includes_x0020_VAT", NamespaceManager).Value);
                newNode.SelectSingleNode("my:MonthEndDate", NamespaceManager).SetValue(mySecDS.SelectSingleNode("/ns1:Month_x0020_End_x0020_Date", NamespaceManager).Value);

                parent.AppendChild(newNode);
            }

        }

Any help you can give me would be very much appreciated!

Thanks

Brian

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 20:22:04 GMT

Post back with questions as they arise. Understanding what the code is doing is the first step to writing your own!

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 20:00:03 GMT

You're a genius! That worked exactly like you'd expect it to. Now I need to sit down and study your code, and see if I can wrap my head around it enough to put it into VB so that I can reuse the concepts. Thank you so much! You've taught me an invaluable amount of information.

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 19:20:10 GMT

Well, let's try this -- append the cloned node, then, since that will be the new last node in the ApplicationAccess group, we'll grab that and set the value. So this:

newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.Value);
parent.AppendChild(newNode);

To this:

parent.AppendChild(newNode);
parent.SelectSingleNode("d:ApplicationAccess[last()]/@Applications", this.NamespaceManager).SetValue(auxDomNode.Value);

When I tried something like this on my test form, my rules fired correctly....

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 18:28:32 GMT

The field, in comparison to the @Applications field, would be @Officer, @Backup_Officer, @Officer_Email, @Backup_Email. LOL All of those fields are filling automatically when I select from the drop down, just can't for the life of me get them to fire when the code is applied.

I have a custom submit button (because I submit to database, change views, and email the form), and tried to use it to apply the same rule, but to no avail. There are secondary data sources for the officers and the officer emails that the data comes from.

The more I learn about this the more I see that this is probably more complicated than need be, but it is my first go round with a form with this much automation built in.

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 18:21:03 GMT

That is kind of annoying -- this is something I've done in IP 2003 Object Model, and my rules fire when I append first. In the test form I was using for the original code I posted, the behavior is as you report, which would mean a change between IP 2003 and 2007.

Where is your field that is being set located in your data structure? Do you want to just set the value in the foreach loop when you set the other value?

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 17:59:53 GMT

Which is exactly what I thought. The result, however, was unexpected. It gives me a blank first line and does not fire the rule.

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 17:52:18 GMT

Okay, so I see:

newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.Value);
parent.AppendChild(newNode);

But to have your rule fire, I'd want to see:

parent.AppendChild(newNode);
newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.Value);

So that the new node had been added to the main dom prior to having the value set.....

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 17:49:10 GMT

Yes, the rule is set on @Applications node of the data being filled in with this code. The rule is to set the Officer field filtered with @Application=current().

         public void CTRL66_7_Clicked(object sender, ClickedEventArgs e)
        {
            //get the nodelist from the secondary data source -- replace the data source name and xpath with your data source and xpath to the repeating group from your secondary data source
            XPathNodeIterator auxDomNodes = this.DataSources["AppList"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/s0:AppListResponse/s0:AppListResult/sqlresultstream:SqlRowSet/ns1:SqlRowSet1/ns1:row/ns1:Application", this.NamespaceManager);

            //get a main data source node from sampledata.xml --replace the xpath with the xpath to the repeating group from your main data source
            XmlDocument sampleData = new XmlDocument();
            sampleData.Load(this.Template.OpenFileFromPackage("sampledata.xml"));
            XPathNavigator mainDomNode = sampleData.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:Employee/d:ApplicationAccess", this.NamespaceManager);

            //remove any existing nodes in the main data source
            XPathNavigator parent = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:Employee", this.NamespaceManager);
            XPathNodeIterator children = parent.Select("d:ApplicationAccess", this.NamespaceManager);

            foreach (XPathNavigator child in children)
                child.DeleteSelf();

            //create new nodes with the new data and append
            foreach (XPathNavigator auxDomNode in auxDomNodes)
            {
                XPathNavigator newNode = mainDomNode.Clone();
                newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.Value);
                parent.AppendChild(newNode);
            }
        }

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 17:28:09 GMT

Just to verify -- the rule is set on the field you wish to populate via this code? Can you paste your code again so we have the latest?

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 16:36:23 GMT

Dang you guys are good. :) Jimmy, I used your solution for the first line, solved like a charm.

Hillary, I made the change you suggested, and it gives me a blank first line again, but does not fire the rule. Maybe I simply need to apply the rule to something else...although I can't figure out what...since the field it all depends on we are now filling.

Re: Iterate through repeating table with complex SQL query

Jimmy — Wed, 25 Mar 2009 16:28:56 GMT

For the duplicated first line issue, this line is the problem:

XPathNodeIterator children = parent.Select("/d:ApplicationAccess", this.NamespaceManager);

Please try changing it to

XPathNodeIterator children = parent.Select("d:ApplicationAccess", this.NamespaceManager);

(forward slash removed) or

XPathNodeIterator children = parent.Select("*", this.NamespaceManager);

Re: Iterate through repeating table with complex SQL query

Hilary Stoupa — Wed, 25 Mar 2009 16:28:52 GMT

The rule isn't firing because we are filling the values, then appending the node. You can switch the lines in the for each loop so that the line:

parent.AppendChild(newNode);

Comes before the line that starts with:

newNode.SelectSingleNode

This way the newNode has been attached to the main dom BEFORE the value is set.....and your rule should fire.

Re: Iterate through repeating table with complex SQL query

cjg1979 — Wed, 25 Mar 2009 16:23:39 GMT

Yay, both options work. But the first line is simply blank, which I thought the child.DeleteSelf() would take care of..(odd), and the rule that is applied to the field being set to a value isn't firing. If you have some quick ideas, I'd be appreciative, but you've both done more than I could imagine. :) Thanks guys.