InfoPath Dev

Hi all,

i'm pretty sure the answer is "no" but I'm trying to come up with a solution in which I can take a repeating table's rows and insert/update a SharePoint list in one query. I'm currently using CAML approach but for some reason the script successfully gets so far through the repeating table and then stops/times out... Not to mention, the user is left sitting there as the script grabs the first row, updates Sharepoint, grabs the 2nd row, updates SharePoint, 3rd row, updates SharePoint, etc until it cycles through ~50 rows and this can be pretty time consuming while the InfoPath file establishes the connection over and over again.

Thoughts? Is there any better way that I could accomplish this?

I'm not sure how much the code is going to make sense, but hopefully those reading will be able to get the general idea of what I'm trying to accomplish....

Public Sub PostPQRRElementsByGroup(ByVal sGroup As String)
Try
'* Define variables
Dim sElementNumber As String = ""
Dim iElementID As Integer = 0
Dim iFileID As Integer = 0
Dim iRequirementID As Integer = 0
Dim iRecordID As Integer = 0
Dim iProgramID As Integer = 0
Dim sUniqueID As String = ""
Dim sFilename As String = ""
Dim sComments As String = ""
Dim sStatus As String = ""
Dim sGroupXMLpath As String = "/my:myFields/my:" + sGroup
Dim xnElements As XPathNavigator = Me.DataSources("PQRR Element Repository").CreateNavigator()
Dim xnCAML As XPathNavigator = Me.DataSources("Add List Item Template").CreateNavigator()
Dim xnMain As XPathNavigator = Me.MainDataSource.CreateNavigator()
Dim dcElements As DataConnection = Me.DataConnections("PQRR Element Repository")
Dim dfElements As XPathNodeIterator = xnElements.[Select]("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW", NamespaceManager) '* I used df for data fields
Dim dqElements As XPathNodeIterator = xnElements.[Select]("/dfs:myFields/dfs:queryFields/q:SharePointListItem_RW", NamespaceManager) '* I used dq for data query fields
Dim xiPQRRElements As XPathNodeIterator = xnMain.[Select](sGroupXMLpath, NamespaceManager)
Dim wsSubmit As WebServiceConnection = DirectCast(Me.DataConnections("Web Service Submit"), WebServiceConnection)
Dim iRecordCount As Integer = 1

iProgramID = xnMain.SelectSingleNode("/my:myFields/my:HiddenFields/my:ProgramID", NamespaceManager).ValueAsInt

'* Check if there's any elements
If xiPQRRElements.Count > 0 Then
'* Loop through all of the elements
While xiPQRRElements.MoveNext()
'* Set the values in the Add List Item Template XML file using the values in the new row.
sElementNumber = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "ElementNumber"), NamespaceManager).ToString
sFilename = xiPQRRElements.Current.SelectSingleNode("../my:HiddenFields/my:FileName", NamespaceManager).ToString
sComments = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "Comments"), NamespaceManager).ToString
sStatus = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "Status"), NamespaceManager).ToString
iElementID = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "ElementID"), NamespaceManager).ValueAsInt
sUniqueID = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "UniqueID"), NamespaceManager).ToString
iFileID = xiPQRRElements.Current.SelectSingleNode("../my:HiddenFields/my:FileID", NamespaceManager).ValueAsInt
iRequirementID = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "RequirementID"), NamespaceManager).ValueAsInt
iRecordID = xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "ID"), NamespaceManager).ValueAsInt

xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Title']", NamespaceManager).SetValue(sElementNumber)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Filename']", NamespaceManager).SetValue(sFilename)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Comments']", NamespaceManager).SetValue(sComments)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Status']", NamespaceManager).SetValue(sStatus)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='ElementLookup']", NamespaceManager).SetValue(iElementID)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='UniqueID0']", NamespaceManager).SetValue(sUniqueID)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Associated_x0020_PQRR']", NamespaceManager).SetValue(iFileID)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='Requirement_x0020_ID']", NamespaceManager).SetValue(iRequirementID)
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='ProgramCode']", NamespaceManager).SetValue(iProgramID)

'* Check if the PQRR Element has been posted already. If true, then set to update and set the ID field to iRecordID
If iRecordID > 0 Then
xnCAML.SelectSingleNode("/Batch/Method/@Cmd", NamespaceManager).SetValue("Update")
xnCAML.SelectSingleNode("/Batch/Method/Field[@Name='ID']", NamespaceManager).SetValue(iRecordID)
Else
'* iRecordID is not > 0 so this is a new record
xnCAML.SelectSingleNode("/Batch/Method/@Cmd", NamespaceManager).SetValue("New")
End If

'* Submit the batch XML file
wsSubmit.Execute()

'* Refresh the Elements data connection
dcElements.Execute()

'* Now that it's submitted, let's get the record ID for the submitted Element
dfElements = xnElements.[Select]("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:UniqueID0 = """ + CStr(sUniqueID) + """]", NamespaceManager)

'* Check if there's a match (there should be) and if so, update the form row's Record ID with the ID from SharePoint
If dfElements.Count > 0 Then
dfElements.MoveNext()
iRecordID = dfElements.Current.SelectSingleNode("d:ID", NamespaceManager).ValueAsInt
xiPQRRElements.Current.SelectSingleNode(("my:" + sGroup + "ID"), NamespaceManager).SetValue(iRecordID)
End If
End While
End If

Catch ex As Exception
ErrorLogging("PostPQRRElements", ex)
End Try
End Sub

Hi, meissen - oh if you are using code for this you do NOT need to submit a row at a time! Loop through your data source with the info you wish to submit, and create and append new Method nodes to your Batch. Give it a try, holler if you get stuck & need help w/ the code.

Hi Hilary -- that's excellent news to hear! Could you help point me in the right direction to get started? Would I instead use NodeIterator to create new nodes that way?

You need to start with a NodeIterator for your source data - that's repeating right? What's your repeating group?

Then use a foreach to go through each node, using relative XPath to the fields - so if your NodeIterator is for my:myFields/my:group1/my:group2, to get data from each node in your foreach, you use my:field1, my:field2 etc.

Create and append new method nodes to the batch inside the loop.

Here's a good blog post:
http://www.bizsupportonline.net/infopath2007/how-to-submit-items-rows-repeating-table-infopath-sharepoint-list.htm 

Thanks Hilary - that blog post got me in the right direction. One last question - do you know if you can mix New and Update method nodes, or do they all have to be one or the other? I think where I'm at is I'll have one sub for AddMethodNode and then one for UpdateMethodNode respectively for "New" or "Update" method cmd. Theoretically, could I append a "New" to my XML and then the next node be "Update"?

You can mix them! So yes, you can have some "new" and some "updated", just have to set the attribute to the correct value when you create the new node.