InfoPath Dev

Hi I have a repeating table which is bound to a data connection and getting data from SQL. When the form is run offline the data is not available. Makes perfect sense to me. So I found the options on setting it to be cached, both in Form Options and on the data connection. This works well for small tables. The problem is that I have some tables that contain hundreds of thousands of rows, storing that entire data object with the form won't work. The users will always be online when first generating the data that will be in the repeating table. What I need is a way to store that data initially and if they are connected to the network let it update, otherwise they get what they had as of the last time they were connected. Can someone please point me to an example of how to do this? Is it something that has to be done in the code?

Well if they need access to tables with hundreds of thousands of rows when they are offline then they would need to take it with them.  No matter when it updates with the backend.  Does the form submit to SQL as well?

They could run SQL Express on their own machines and then sync the data when online is about the only way I can think of.  You could probably control the data connections and have an online set and an off-line set but they will get errors when they are off-line.

I should have mentioned that they will only be working with a subset of the data. Based on some fields they enter we have pass a custom SQL command to the dataadapter. For example there is a table that holds all of the people in the company, the name, phone, etc. They will fill in a specific hierarchy and get back only the people who are under that particular hierarchy. When online the repeating table displays, what I need is for that same subset of data to be saved when they save it offline. If I have a regular textbox on the same form and enter some text into it, it is saved in the offline version. I guess what I need is a way in the code to copy the data from the repeating table to a code generated table that would then be saved with the form?

Ok Jake.

If the data from SQL is not being saved into your form, you are just showing them the secondary data then yes it would not be available offline.  You would need to move the data into the form itself to be preserved.  I know there are some examples here of copying repeating data from a secondary dc to a group in your main DOM.

I don't know if I'm just searching on the wrong stuff or not knowing exactly what I'm looking for but I cannot find any examples. I understand what you mean about copying the data from the secondary data connection to the main DOM. I did find this http://enterprise-solutions.swits.net/infopath2007/loop-through-items-repeating-table.htm which shows how to loop through the items in a repeating table. I think this is getting me close to what I need. I know enough .net code to start from this sample, the problem is that I'm getting an error in visual studio on the "MainDataSource" and "NamespaceManager" referenced in the example. I'm thinking that I'm missing an imports statement or missing a reference. I've tried adding several and searching for it in other examples but no luck. Any one have an idea?

 Thanks

Hi Jake,

What's the error message? If you'd like to have one of our InfoPath MVPs help you quickly implement the code to solve your problem, just purchase a support credit from our qdabra.com support site: http://www.qdabra.com/support.aspx

Cheers!

it was telling me that MainDataSource and NamespaceManager were not declared. I think I found the reason for that though. I had the programming language set to Visual Basic (Infopath 2003 compatible). I switched to just Visual Basic and it worked. Problem is that we have to make it work with 2003. So far all of the visual basic examples I have found call those methods. If there is an example that someone can point me to that uses JScript I can code in that as well.

Thanks

We have done this in jscript before, but we'd need to spend some time to find and publish an example. I will ping Agni and see if he can find something. More soon,

Any help will be appreciated, I've spent a while yesterday and today trying to find an example but haven't turned up anything that has worked yet.

Thank you

This is no longer needed as I was able to figure it out using Visual Basic code. Here is the code in case anyone else ends up needing it. This will take data returned in a secondary data source and fill a repeating table with the data which is then saved in the main DOM, and available offline.

For this example I have a secondary data connection called "tbl_SPOC_Hierarchy_Responsibilities", it has two fields in it, "PERSON_NUMBER" and "HIER_RESP".
group5 is the name of the repeating table, with two fields, field1 and field2

            'Loop thru data returned to secondary data source and add to local table in Main DOM
            Dim oNodeList As IXMLDOMNodeList
            Dim oNode As IXMLDOMNode
            Dim item As IXMLDOMNode
            Dim oDOM As IXMLDOMDocument2 = CType(thisXDocument.GetDOM(DataConnectionName), IXMLDOMDocument2)
            Dim Value1 As String = ""
            Dim Value2 As String = ""
            oDOM.setProperty("SelectionNamespaces", "xmlns:d=""http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"" " & _
                                                    "xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" " & _
                                                    "xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-08-16T13:30:52"" ")
            oNodeList = oDOM.selectNodes("dfs:myFields/dfs:dataFields/d:tbl_SPOC_Hierarchy_Responsibilities") 'secondary data source name
            If (oNodeList.length > 0) Then
                For Each oNode In oNodeList
                    For Each item In oNode.attributes
                        If item.nodeName = "PERSON_NUMBER" Then
                            Value1 = item.text
                        End If
                        If item.nodeName = "HIER_RESP" Then
                            Value2 = item.text
                        End If
                    Next
                    Dim myclone As IXMLDOMNode
                    Dim mynode As IXMLDOMNode = thisXDocument.DOM.selectSingleNode("/my:myFields/my:group4/my:group5")
                    myclone = mynode.cloneNode(True)
                    thisXDocument.View.ExecuteAction("xCollection::insert", "group5_16")
                    myclone.selectSingleNode("/my:field1").text = Value1
                    myclone.selectSingleNode("/my:field2").text = Value2
                    Dim parent As IXMLDOMNode = thisXDocument.DOM.selectSingleNode("/my:myFields/my:group4")
                    parent.insertBefore(myclone, mynode)
                    thisXDocument.View.ExecuteAction("xCollection::remove", "group5_16")
                Next
            End If

Jake.

Thanks so much for this contribution! Others will really appreciate it.