InfoPath - Update Repeating Elements/Nodes in a Secondary Data Source - SharePoint Development & Administration + InfoPath

Saturday, July 25, 2009

InfoPath - Update Repeating Elements/Nodes in a Secondary Data Source

How to update repeating elements/nodes in a secondary data source by attribute:

You may have a data source in an InfoPath form which contains repeating elements, each with the same name. When this is the case elements are usually distinguished by an attribute, commonly "name". The value for the name attribute can then be used to identify the specific element in the data source. Example below:

<?xml version="1.0" encoding="UTF-8"?>

<Batch OnError="Continue">

<Method ID="1" Cmd="Update">

<Field Name="ID"></Field>

<Field Name="field1"></Field>

<Field Name="field2"></Field>

</Method>

</Batch>


Each "Field" element above corresponds to a field/column in the SharePoint list, specified when connecting to the Lists web service.
For details and instructions for setting up InfoPath data connections to SharePoint lists using CAML, see InfoPath - Update Existing SharePoint List Item Programmatically Using CAML.
The following demonstrates how to loop through each node/element in an InfoPath data source to find and update a specific element by testing against an attribute value.
The first parameter accepted passed to the function below is the name of the Field element in the CAML template to be updated, the second is the XPATH to the field which was changed by the user. The CAML data source is iterated until the required element is found by checking if the "name" attribute matches the required field in the CAML template.

JScript Example (see below for VBScript Example):

[edit - 19-09-2009] - Declare and set xmlDataSource and UpdateXML variables when the form loads by moving the lines to above the function - see below: 

//Select all nodes from the CAML XML Data Source - Declare outside of
//updateXMLDataSouce function, otherwise it will be set every time
//the function is called, which is once for each repeating element

var xmlDataSource = XDocument.GetDOM("CAML_XMLt");
var UpdateXML = xmlDataSource.selectNodes("/Batch/Method/Field");

function updateXMLDataSource(XMLFieldName, sourceField)
{

var count;

//Loop through each element in the data source to find the
//required field in the CAML template

for (count = 0; count « UpdateXML.length; count++)
{
//Test if the name attribute of the current
//element matches the required field

if (UpdateXML[count].getAttribute("Name").localeCompare(XMLFieldName) == 0)
{
//Element Found: Update the value of current
//element to the value of souceField parameter (XPATH)
var DSNode = XDocument.DOM.selectSingleNode(sourceField);
UpdateXML[count].text = DSNode.text
}
}
}
Set up On After Change event handlers on each required InfoPath form control or field in the main data source. When a user updates a value on the form, the corresponding event handler is called, which can the call the updateXMLDataSource function (above), passing the required field name in the CAML template and the XPATH for the field which was changed. Example Below (replace CAML_FIELD_NAME with the required field name from the CAML data source):
function msoxd_my_fieldNameHere::OnAfterChange(eventObj) //Generated by InfoPath
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is
// writable. Write code here to respond to the changes.

//Call the updateXMLDataSource function, passing the
//required CAML field name and the XPAT to the field which
//contains the required/updated value.
updateXMLDataSource("CAML_FIELD_NAME", "/my:myFields/my:fieldNameHere");
}

Load data into CAML data source from another data source (SharePoint List) when form loads

To help distribute processing time required to retrieve, process and submit data to data connections from and InfoPath form, the initial data can be loaded into the CAML data source when the form loads. As a result, only individual elements in the data source need to be updated when data in a form's control is changed. Once the form is submitted, no further processing is required so the CAML template can be sent via the web service data connection immediately.
For instructions and details about setting up web service data connections from InfoPath forms to SharePoint lists using CAML, see InfoPath - Update Existing SharePoint List Item Programmatically Using CAML.

[edit - 19-09-2009] - Added VBScript example below.

VBScript (VBS) Example:
The following is a VBScript example of how to loop through a repeating element in the data source (primary or secondary) of an InfoPath Form.
'Load the XML Document of the repeating data source Dim xmlDataSource Dim UpdateXML Set xmlDataSource = XDocument.GetDOM("CAML") Set UpdateXML = xmlDataSource.selectNodes("/Batch/Method/Field") 'This loops through the repepeating element in the data source and 'sets the value of the repeating field if the name attribute matches 'the first param: XMLFieldName. 'the sourceField param accepts a string containing the XPath to the 'field which has the value which is to be set as 'the value of the required repeating element. Sub updateXMLDataSource(XMLFieldName, sourceField) Dim count Dim DSNode 'Loop through each element in the data source to find the 'required field in the CAML template For count = 0 To UpdateXML.length -1 'Test if the name attribute of the current 'element matches the required field If UpdateXML(count).getAttribute("Name") = XMLFieldName Then 'Element Found: Update the value of current 'element to the value of souceField parameter (XPATH) Set DSNode = XDocument.DOM.selectSingleNode(sourceField) UpdateXML(count).text = DSNode.text End If Next End Sub
The updateXMLDataSource() function can the be called using the event handler of a button of the form of the OnAfterChange event  handler of the field which contains the value to be set to the repeating element when found by the function.
'You can then call the Sub using the event handler of a button on the form, 'or the OnAfterChange Event of a field in the main data source (below). Sub CTRL5_5_OnClick(eventObj) ' Write your code here Call updateXMLDataSource("field2", "/my:myFields/my:fieldText") End Sub '======= ' The following function handler is created by Microsoft Office InfoPath. ' Do not modify the name of the function, or the name and number of arguments. ' This function is associated with the following field or group (XPath): /my:myFields/my:fieldText ' Note: Information in this comment is not updated after the function handler is created. '======= Sub msoxd_my_fieldText_OnAfterChange(eventObj) ' Write code here to restore the global state. If eventObj.IsUndoRedo Then ' An undo or redo operation has occurred and the DOM is read-only. Exit Sub End If ' A field change has occurred and the DOM is writable.  ' Write code here to respond to the changes. Call updateXMLDataSource("field2", "/my:myFields/my:fieldText") End Sub
Share this article:
Stumble This Delicious
Delicious
submit to reddit
Facebook
MySpace
MySpace

10 comments:

Leon said...

Can you give us a VBScript example?

Thanks!

Daniel Soutter said...

Leon,

I have created a VBScript example of the function (See 19-09-09 edits for this post above)

Thanks

Daniel

Leon said...

HI Daniel. I’m working with your example, and the VBS makes it much easier to understand. Thanks for taking the time to do that!

I’ve done VBS and VBA for a long time, but the InfoPath and SharePoint object models are still pretty new to me. I’m trying to adapt the code to do the following.

We have an InfoPath form with a rich text field. The rich text field can’t be saved into the SharePoint forms library because it does not support rich text. So, we are writing the rich text to a secondary SharePoint list and joining the data back together in a dataview in SPD. I have that part working OK. I’m storing the InfoPath forms unique name (a field named “FileName”) which is stored in a in the secondary list’s “Title” field for use when joining the list items in the dataview.

I need to loop through all of the list items in that secondary list to see if the rich text field for that InfoPath form is already in the list. It would then be an update rather than a new list item.

“CAMLNew” is my xml datasource mapped to the secondary SharePoint list.

Here’s my code:

Set xmlDataSource = XDocument.GetDOM("CAMLNew")
Set UpdateXML = xmlDataSource.selectNodes("/Batch/Method/Field")

For count = 0 To UpdateXML.length -1
MyField = UpdateXML(count).getAttribute("Name")
Msgbox MyField
MsgBox UpdateXML(count).text
Next

When I run this, it get “Title”, “ArticleTitle”, and “ArticleBody” which are the attribute names. The second MsgBox comes up empty all 3 times. How can get the fields’ values? Then, how do I iterate through all of the list’s rows and compare the Title field in the list with my InfoPath form’s name? Is this just looking at the CAML values and not at the actual list?

Feel free to ignore this raving lunatic! I think I’m confused because I’m not using a repeating table. Any insight will be greatly appreciated. Thanks again.

Daniel said...

Hi Leon,

Yes, the code is reading the CAML template values, which will only be used to update the SharePoint list item when it has been found. The code is written to loop through a repeating element (such as the "Field" element in a CAML template) ustil it finds the required element by matching an attribute value. You will need to use this code, but also need something different.

I will try to explain how to do this by breaking up the process into separate components. You are going to need a number of data connections to achieve this, which in some ways work together, but will all perform diferent tasks:

- Retreive the list items from SharePoint
("Receive" Data Connection -> "SharePoint Library or list" )

- The CAMLNew template - To be used to create a new SharePoint list item if one doesn't exist.
(Receive Data Connection -> XML Document)

- A Secondary Submit - to submit the CAMLNew template (CAMLNew) to SharePoint
(Submit Data Connection -> To a Web Service)

- A CAMLUpdate template - To be populated with the data being updated in the SharePoint list item if it already exists.
(Receive Data Connection -> XML Document)

- A Secondary Submit - to submit the CAMLUpdate template (CAMLNew) to SharePoint
(Submit Data Connection -> To a Web Service)

The CAMLNew and CAMLUdate templates are basicaly the same, but the Cmd attribute of the method element will be "New" to create a new item, and "Update" to update an existing item. When creating a new item, the ID field can be ignored as SHarePoint automatically assigns the next available ID. When updating an existing item using CAML, the "ID" field is used to determine which item is to be affected. It should be fine to use the "FileName" field in the code to find the required SharePoint item, as this is separate to the CAML template.

See my article http://blog-sharepoint.blogspot.com/2009/07/summary-how-to-programmatically-update.html for details on configuring an InfoPath Form to update an existing SharePoint listg item using a CAML XML template and the SharePoint Lists Web Service.

Daniel said...

The first is a data connection to the SharePoint list to Receive the items from the list with the rich text field.
Lets say you call this data connection "DataFromSharePoint". You will need to select the "ID", "Title", and the rich text columns/fields when configuring the data connection.

Once you have configured the data connection, you should then use code to loop through the items in the secondary data source "DataFromSharePoint" to find the item with the "Title" which matches the "FileName" value. You will need to modify the code slightly to do this (see below).

The difference is that you are finding the list item by matching the value of a single field, instead of an attribute from a repeating field. The following are the lines which will differe in the code from the original function provided.

Find the correct field in by matching the attribute value:
If UpdateXML(count).getAttribute("Name") = RequiredValue Then

Find the correct item/node by matching the value of a known field (Title):
If Items(count).selectSingleNode(@Title) = FileNameValue Then


If the item is found, you then populate the CAMLUpdate template using the original function from this post with the "ID" of the SharePoint list item, and the rich text field with it's required value. You need to use the "ID" field in the CAML template so that the correct item in the SharePoint list is updated.

If the item is not found, populate the CAMLNew template with data required to create a new list item using another instance of the same function, which loops through the CAMLNew template instead.

Create a separate field in the main data source which can be used to determine which CAML template to use when updating/adding data to SharePoint. This could be called "ItemExists" (boolean), and can be set to true if the item is found, and false if not.

You will need two separate submit data connections, with each of the CAML templates as the data for the "Updates" paramater of the UpdateListItems method. When it comes time to update or create the item in SharePoint (with the rich text field), you can use rules with conditions to test if the "ItemExists" field is True or False. If true, the rule should execute the submit data connection which uses the CAMLUpdate template. A second rule will submit using the data connection which uses the CAMLNew template if "ItemExists" is false.

Hope I haven't confused you, please let me know how you go with this. Also, I haven't tested the code below as it is only partially complete.

Daniel

Daniel said...

Dim xmlDataSource
Dim Items
Dim Found
Set SharePointDataSource = XDocument.GetDOM("DataFromSharePoint")

'Note: You will need to set the namespace for the data source for the code to work.
' You can get the namespace from the properties of the repeating group in the data source.
' The example below is for a data connection to a Task list in SharePoint.

xmlDataSource.setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" ');

'copy the XPath of the repeating group in the DataFromSharePoint data source
Set Items = SharePointDataSource.selectNodes("/dfs:myFields/dfs:dataFields/dfs:Tasks")

FileNameValue = XDocument.DOM.selectSingleNode(XPath to FileName field)

Found = false

For count = 0 To Items.length -1
'Test if the name attribute of the current
'element matches the required field

If Items(count).selectSingleNode(@Title) = FileNameValue Then
'Item found

'Set the "ItemExists" field to tru for use when submitting the form - details below
XDocument.DOM.selectSingleNode(XPath to "ItemExists" field)

'Populate the the CAMLUpdate template with the required values here using the original function.
'You will need to modify the original function as well to accept the actual Value as the second
'paramater instead of the XPath to the field containing the required value.

updateXMLDataSource("ID", NewValue )

End If

Next

'Populate the CAMLNew template if the item was not found
If Found = False

'Populate the CAMLNew template with the required details here.

End If

Leon said...

Awesome. I may just get there yet! I'm going step by step with the code to get it working in my 2003 form. I had to modify the syntax on the selectNamespaces arguments, and I'm thinking it should be:

SharePointDataSource.setProperty

rather than

xmlDataSource.setProperty

Is that correct?

Here's what I have so far. I want to ensure that I am interating the secondary list ("MSR_FLDArticles") correctly. Unfortunately, the for loop gives me the first title twice. I'm not sure why. There are 2 items in the secondary list.

Sub cmdTest_OnClick(eventObj)
Dim xmlDataSource
Dim Items
Dim Found
Set SharePointDataSource = XDocument.GetDOM("MSR_FLDArticles")

SharePointDataSource.setProperty "SelectionNamespaces", "xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"""
Set Items = SharePointDataSource.selectNodes("/dfs:myFields/dfs:dataFields/dfs:MSR_FLDArticles")

MsgBox Items.Length 'returns correct number -- 2

For count = 0 to Items.Length - 1
MsgBox Items(count).selectSingleNode("//@ArticleTitle").Text 'returns the first title twice, but not second
Next

End Sub

I really, really appreciate your help!

Daniel said...

Hi Leon,

I must have missed updating the the variable name for the SharePointDataSource.SetProperty(...) , you are correct to change this from xmlDataSource.SetProperty(...).

First, make sure you are selecting the correct repeating group in the SharePoint data source with the SharePointDataSource.selectNodes(...) line. i think this is ok, as you mentioned that the MsgBox Items.Length line returns the correct number of results from the SharePoint data source

If you try displaying the title for each item explicitly (not using the for loop), do you get the same result?.

Try the following before the for loop in the code:

MsgBox Items(0).selectSingleNode("//@ArticleTitle").Text
MsgBox Items(1).selectSingleNode("//@ArticleTitle").Text

Also try the following without the "//" before the @ArticleTitle field name, which is more likely to be the issue:

For count = 0 to Items.Length - 1
MsgBox Items(count).selectSingleNode("@ArticleTitle").Text
Next


Let me know how this goes.

Thanks

Daniel

Scott said...

Will the orignal posted method both update and Add Items? or only update

Team said...

I keep getting an issue with the below code:

Invalid character
File:script.js
Line:51
for (count = 0; count « UpdateXML.length; count++)

Does any one else see this issue?

Post a Comment