SharePoint Development & Administration + InfoPath

Can you give us a VBScript example?

Thanks!

Leon,

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

Thanks

Daniel

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.

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.

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

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

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!

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

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

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?