General

Re: Populate main table from secondary table

hayer89 — Fri, 10 Jan 2014 16:49:28 GMT

Thanks Hilary, I forgot to put on the previous comment that I am unable to add in an "id" field on to the Secondary Database Connection ...

Also thanks for the help and you time so far Hilary and donStephen

Re: Populate main table from secondary table

Hilary Stoupa — Fri, 10 Jan 2014 15:57:43 GMT

Actually, since those fields are not in the repeating group, you should be able to add them to your database bound form - right click the topmost folder in the data source task pane and you can add fields that won't be submitted to your DB.

Re: Populate main table from secondary table

hayer89 — Fri, 10 Jan 2014 14:44:21 GMT

The problem with that approach is that I am trying to submit into a database table, meaning I am unable to add the "count" and "lastitem" fields .. which are needed :(

Unless there's a way to submit user created fields into the database table?

Re: Populate main table from secondary table

donstephen — Fri, 10 Jan 2014 13:00:47 GMT

InfoPath's built in functionality has its limitations, and automatically inserting multiple rows is one of them. Please check the form I am attaching for the copy table approach without using qRules. I hope this helps.

Re: Populate main table from secondary table

hayer89 — Fri, 10 Jan 2014 11:00:22 GMT

Thanks donstephen, but I do not have qRules, is there any way to do it without?

Re: Populate main table from secondary table

donstephen — Fri, 03 Jan 2014 01:05:16 GMT

It seems that you’ll need to manually add the qRules DLL instead of injecting. Please refer to this guide for the steps. Also, there were some rules which were omitted at target the repeating group (see image below).

Hope this helps.

Re: Populate main table from secondary table

hayer89 — Tue, 31 Dec 2013 14:36:54 GMT

I'm trying to use the second approach that uses the qRules' Insert command, but I am having difficulty getting it to work. Maybe its because I am using a database as the main connection instead of an XML connection? I've attached my InfoPath form with the QdabraRules inside the form.

Would really appreciate it if you could have a look :)

Re: Populate main table from secondary table

donstephen — Tue, 31 Dec 2013 10:08:01 GMT

Whoops. Didn't notice that. I've updated the previous attachment to 2007.

Re: Populate main table from secondary table

hayer89 — Tue, 31 Dec 2013 10:03:02 GMT

Thanks for the reply donstephen, could you please resend that form in InfoPath 2007 :)

Re: Populate main table from secondary table

donstephen — Tue, 31 Dec 2013 09:55:16 GMT

Please check the two approaches we have for copying repeating tables on the attached form. The first approach uses a trigger technique, while the second one uses qRules' Insert command.

Re: Populate main table from secondary table

hayer89 — Tue, 31 Dec 2013 09:07:11 GMT

Thank you for your help donstephen, but I was looking for the secondary data source fields to be submitted into the main data connection into separate records, not one large concatenated entry, please see the attached example of what I am trying to do.

Basically trying to copy the fields in a secondary data source like for like and submitting them into my main data connection as separate records. So if I have 20 colours names in my secondary data source, I want those 20 colour names to be submitted into my main data connection as 20 records. I feel that the VB code I have been working on is almost complete but it is throwing up an error. Could you please take a look :)

Thanks you once again, your help throughout has been much appreciated

Re: Populate main table from secondary table

donstephen — Mon, 30 Dec 2013 14:32:53 GMT

Thank you for the additional information. I am attaching an updated form. Please check if this approach works for you.

Re: Populate main table from secondary table

hayer89 — Mon, 30 Dec 2013 13:14:58 GMT

Thanks donstephen, the approach is what I am looking for, however I need to populate the same field multiple times from the Secondary data source.

Re: Populate main table from secondary table

donstephen — Mon, 30 Dec 2013 13:06:37 GMT

Apologies for that. Here's the form converted to InfoPath 2007. Please check if this approach is close to what you're looking for. If not, kindly attach a copy or a sample of your form so I can get a better idea of what you need.

Re: Populate main table from secondary table

hayer89 — Mon, 30 Dec 2013 12:53:21 GMT

Thanks for your reply donstephen, just to answer your question my button is located within the repeating table, I am however unable to open your sample form as I only have InfoPath 2007 SP3 to work with.

The code is:

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

Dim secDSNav As XPathNavigator = DataSources("SecondaryTable").CreateNavigator()

'Retrieve the rows of the secondary data source'

Dim rows As XPathNodeIterator = secDSNav.Select( _

"/dfs:myFields/dfs:dataFields/d:SecondaryTable", _

NamespaceManager)

'Loop through the rows of the secondary data source and fill the repeating table '

While rows.MoveNext()

Dim ID As String = rows.Current.SelectSingleNode( _

"@SecondID", NamespaceManager).Value

' Add the item to the repeating table '

AddItem(ID)

End While

' Remove the first empty item from the repeating table '

DeleteFirstEmptyItem()

End Sub

Private Sub AddItem( _

ByVal ID As String)

Dim doc As XmlDocument = New XmlDocument()

Dim group As XmlNode = doc.CreateElement("MainTable", _

NamespaceManager.LookupNamespace("d"))

Dim field As XmlNode = doc.CreateElement("MainID", _

NamespaceManager.LookupNamespace("@"))

Dim node As XmlNode = group.AppendChild(field)

node.InnerText = ID

doc.AppendChild(group)

MainDataSource.CreateNavigator().SelectSingleNode( _

"/dfs:myFields/dfs:dataFields", _

NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator())

End Sub

Private Sub DeleteFirstEmptyItem()

Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()

Dim itemNav As XPathNavigator = domNav.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/@MainID[1]", _

NamespaceManager)

If itemNav IsNot Nothing Then

itemNav.DeleteSelf()

End If

End Sub

However my code works when I try to populate custom made Fields and Groups, here is my code:

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

Dim secDSNav As XPathNavigator = DataSources("SecondaryTable").CreateNavigator()

' Retrieve the rows of the secondary data source

Dim rows As XPathNodeIterator = secDSNav.Select( _

"/dfs:myFields/dfs:dataFields/d:SecondaryTable", _

NamespaceManager)

' Loop through the rows of the secondary data source and fill the repeating table

While rows.MoveNext()

Dim ID As String = rows.Current.SelectSingleNode( _

"@SecondID", NamespaceManager).Value

' Add the item to the repeating table

AddItem(ID)

End While

' Remove the first empty item from the repeating table

DeleteFirstEmptyItem()

End Sub

Private Sub AddItem( _

ByVal ID As String)

Dim doc As XmlDocument = New XmlDocument()

Dim group As XmlNode = doc.CreateElement("group2", _

NamespaceManager.LookupNamespace("my"))

Dim field As XmlNode = doc.CreateElement("MainID", _

NamespaceManager.LookupNamespace("my"))

Dim node As XmlNode = group.AppendChild(field)

node.InnerText = ID

doc.AppendChild(group)

MainDataSource.CreateNavigator().SelectSingleNode( _

"/dfs:myFields/my:group1", _

NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator())

End Sub

Private Sub DeleteFirstEmptyItem()

Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()

Dim itemNav As XPathNavigator = domNav.SelectSingleNode( _

"/dfs:myFields/my:group1/my:group2/my:MainID[1]", _

NamespaceManager)

If itemNav IsNot Nothing Then

itemNav.DeleteSelf()

End If

End Sub

End Class

But what I really need is to populate my Main Data Connection so that I can Submit all of the ID's.