InfoPath Dev

Some background:  We are creating a form for our HR department that can be filled upon hiring.  This form will contain all the user information in a single table, and a related table will hold all user information for the applications that they will need.  What I would like is for HR to be able to click a "fill" button to fill the repeating table for the applications they need.  I only need it to populate the "Application" field as I have rules that will fill the related fields.  I have searched for books on this subject, forum posts, etc and have found very limited information.

This is going to be a many-to-many relationship within SQL since I will have many applications and multiple job positions that can be selected.  I'd like the "fill" button to add a row to the Application table for each application necessary to that job position.  I have figured out the relationship in sql, and the query to perform is something like this:

Select Application From Applications
Where ID IN
 (Select Position_Applications.ApplicationID from Position_Applications
 Where PositionID IN (Select Position.ID from Position where Position = 'Proof'))

The SQL database consists of 5 tables: Employee Info, Application Access needed, Applications, Positions, AppID=PositionID (to join the two pieces of data to show which apps are applicable to each job position)

I know this will take code, but being a n00b at coding, I was hoping for some help.  I know it will take a looping XPathNodeIterator of some sort, but google has only gotten me so far.  What I can't figure out is how to write the code to iterate the table by repeating the complex sql query.

Would this be a stored SQL procedure that is called using variables that are set based on the Position selection?  Should the SQL query be inside the code?  As you can see, I'm much better with SQL than Visual Basic...but I'm trying to learn the visual basic coding. 

Any help is greatly appreciated

 Ok, so no one has posted any help on this yet, and I have had it on the back burner due to some other projects.  I have made some progress, however.  What i have done is created a stored procedure to produce the sql query with the infromation that I need.  The only thing left would be to figure out the XPathNodeIterator in order to fill the repeating table with the data.  I have been able to successfully query the web service and get the list of applications, but I have not been able to fill my table with them yet, only a table bound to the web control.

Any help is greatly appreciated.

Hi:

So you need to copy data from one table to another? We have a free tool for that -- qRules has a copy table command and you can download it here. It won't co-exist with other code, though, so if you are already using code in your form, you'll need to do something like this (C# and not the prettiest code in the world, but it should give you an idea.... note, no error handling):

            //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["Test"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/d:Test1", 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("/my:myFields/my:topGroup/my:repeatingGroup", this.NamespaceManager);

 

            //remove any existing nodes in the main data source

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

            XPathNodeIterator children = parent.Select("my:repeatingGroup", 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("my:rgField1", this.NamespaceManager).SetValue( auxDomNode.SelectSingleNode("@Text", this.NamespaceManager).Value);

                newNode.SelectSingleNode("my:rgField2", this.NamespaceManager).SetValue( auxDomNode.SelectSingleNode("@Boolean", this.NamespaceManager).Value);

 

                parent.AppendChild(newNode);

   

            }

 I'll have a look at this in the next day or two.  Honestly, I'm better with VB than C#, but I can pick my way through.  :)  I'm not really copying from one table to another, but rather querying a secondary data source (web service created in SQL) and using the query results as a single field in a repeating table.  Does that make sense?

So you are querying in code and then need to access the results of that query in code? Can you share your code thus far?

 Sorry, I'm not as of now. 

 I have looked at what you provided, and made a few changes and put it into my form.  Like I said, I'm not very familiar with C#, but I gave it my best shot.  LOL  Here is what I came up with:

            //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("my: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("my:Applications", this.NamespaceManager).SetValue(auxDomNode.SelectSingleNode("@Text", this.NamespaceManager).Value);

                parent.AppendChild(newNode);

            }

(Of course, now I can't even figure out how to make code look right...lol)

So anyway, there is what I have according to my schema.  I get an error message "NullReferenceException" on the "newnode.SelectSingleNode..." line.  The code does fill in the first line, but fails to add a new child node to the repeating table.

I didn't specify that you'd need to change the xpath on that line, but you do... sorry for that! See where it says 'auxDomNode.SelectSingleNode("@Text") -- you'll want to change that to point to the actual field in your app list data source that holds the value you want to use in newNode....My test database has silly columns like 'Text' and 'Boolean', but I bet your database has normal column names!

Well, if I had been awake, I should have caught it.  Unfortunately, it didn't make any difference.  I still get the NullReferenceException error.  I've changed it to simply "Application", as this is the node name in the secondary datasource.  It's almost as if the error occurs before it gets to the node name. 

 I just had a thought.  This data source accepts a parameter of "Position", a value which is set on the change event of a drop down list, long before this button click.  It is always set previously (as I've been very careful to select the position prior to clicking.  Should there be any code that deals with the input of that parameter?  What you have in C# I think I've stumbled upon in VB code also, or at the very least the exact same error message.  :)

 Thanks again for all your help!

 So in order to overcome the possibility that the data source wasn't being queried again, I added an action to the drop down list of positions.  Now it does two things:  set the query field value, and query the data source.  If my thinking is correct, this should populate the source with the data for the table, and when the code is executed should fill the table.  But I get the same NullReferenceException error in the For Each loop.

I don't think "my:Applications" is a child of the newNode node, is it? Your xpath for the parent variable is "dfs:myFields/dfs:dataFields/d:Employee" which makes me believe this is a databound form. I'd expect '@Applications' for newNode.SelectSingleNode("my:Applications", this.NamespaceManager).

 Well you're absolutely correct.  Again, like my first post said, I'm learning XPath and such now.  I simply copied the XPath from my data sources and pasted them in, and we do get a better, yet not correct, result.  The result now is that the repeating table does fill, but every entry is the first in the query list.  (i.e. rather than being BVS Training Systems, Email, etc...it is BVS Training Systems, BVS Training Systems, etc). 

 Not being familiar with C#, but using some VB.NET in my life, I'm wondering if there is a "move next" command that should happen on the auxDomNode?  I could be really off base here.  

Sounds like you're using an absolute XPath expression in one of your SelectSingleNode statements, or something along those lines.  Could you post the latest version of your code so we can have a look?

You aren't off base... but that is what we are doing with this line: foreach (XPathNavigator auxDomNode in auxDomNodes). We have a node list that we are calling auxDomNodes. In the for each statement, we are saying "for each of the nodes in that node list, do the following". So when we refer to auxDomNode in the for each statement, we should be getting the next node each trip through. Which makes me wonder -- would you double check your code, and make sure this line, in your current, edited code with fixed xpaths:

newNode.SelectSingleNode("my:Applications", this.NamespaceManager).SetValue(auxDomNode.SelectSingleNode("@Text", this.NamespaceManager).Value);

Refers to the correct variable of auxDomNode (no 's' at the end)? The fact that you are only getting the first row from your secondary data source makes me think that maybe you are referring to the node list, and not the individual node....

         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.SelectSingleNode("/dfs:myFields/dfs:dataFields/s0:AppListResponse/s0:AppListResult/sqlresultstream:SqlRowSet/ns1:SqlRowSet1/ns1:row/ns1:Application", this.NamespaceManager).Value);
                parent.AppendChild(newNode);

            }
        }

Anything you can provide is appreciated.  I'm learning a lot here, but not quickly enough to keep up.  :)

Jimmy was right -- it is the xpath you are using. The variable auxDomNode is already at ns1:Application. So instead of:

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

newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.SelectSingleNode("/dfs:myFields/dfs:dataFields/s0:AppListResponse/s0:AppListResult/sqlresultstream:SqlRowSet/ns1:SqlRowSet1/ns1:row/ns1:Application", this.NamespaceManager).Value);

Try:

XPathNodeIterator auxDomNodes = this.DataSources["AppList"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/s0:AppListResponse/s0:AppListResult/sqlresultstream:SqlRowSet/ns1:SqlRowSet1/ns1:row", this.NamespaceManager); 

newNode.SelectSingleNode("@Applications", this.NamespaceManager).SetValue(auxDomNode.SelectSingleNode("ns1:Application", this.NamespaceManager).Value);

Note that you have two lines to change -- the one where you declare auxDomNodes variable, and the one where you get the value of the Application element.