SharePoint Development & Administration + InfoPath

Summary

How to programmatically update an existing SharePoint list item from an InfoPath form using a CAML query and the lists web service of the SharePoint Site.

Introduction:
This article demonstrates how to set up secondary data connections in an InfoPath form to connect to the Lists web service to update an existing item in a SharePoint list. An XML document containing a Collaborative Application Markup Language (CAML) query is sent containing data which will map to specific fields in the specified SharePoint list. The examples reference from the article currently use JScript.  The CAML template in this solution is updated programmatically. This can be achieved using a JScript function referenced below when different events occur while completing a form (eg. updating the value of a control). 

There are many methods that can be used to achieve a similar result to this outcome of this article, some of which would also be an improvement to the techniques used here.  If you have a suggestion for improvement, please leave a comment.  Some of the examples can also be written in additional languages if this becomes a common request, and my time allows me to do so. If you require assistance with, or an alternative to one of the examples referenced below, please post a comment to the page containing the example.


Summary

1. Create Initial InfoPath Form
2. Set up the CAML query
3. Set up InfoPath Data Connections
4. Set up the Form Template and Required Code
   
   Additional Information:
   
Update Repeating Field elements in the CAML Template Programmatically How to Submit using a Secondary Data Connection in InfoPath

Technical Requirements

• SharePoint (MOSS 2007)
• The article was written for Microsoft InfoPath 2007, but can
  also be achieved using InfoPath 2003.
• Optional:
  U2U CAML Query Builder (free):
  
  http://www.u2u.be/res/Tools/CamlQueryBuilder.aspx

Process

1. Obtain Required Information from
SharePoint & Create Initial Form

For the purpose of this article, you will need a SharePoint list which can be created from any of the standard templates, or a custom list containing your own fields.

• Full address/URL of the SharePoint site containing the list
  you are updating.
  
• The Unique ID (GUID) of the SharePoint list being updated.
  See Finding the ID (GUID) for a SharePoint List.

Once you have the address and GUID, design a new blank form in InfoPath. Add a text field named "FormID" to the form's Main data source, then set the SharePoint list GUID as the field's default value to the form's GUID.

2. Create the XML file containing the CAML query

Important Notes:

Column identifiers (including the "_x0020_" characters which replace the spaces in between words in column names) can be a maximum of 32 characters. If the names of two or more columns are greater than 32 characters long and are identical if ignoring any character after the first 32, a number is appended to the field identifier to make each field unique.

• The command for the method is "Update", indicating that an
  item from a list is to be updated.
• The Batch element can have more than one Method element, but
  each must have a unique ID attribute value. This can be
  used to match a response or error to the method which instigated
  it.
• The field element the the name attribute "ID" is used to find the item
  in the SharePoint list to be
  updated.

Process:

1. Save the following as a .xml file using a text editor:
<?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>
2. Rename the name attributes for the Field elements currently
   identified as "field1" and "field2" to the column/field identifiers
   of the list you are submitting to.
3. Once complete, save the CAML template to file (XML) which will be added to the
   form as a secondary data connection in the next step.
   See instructions below for using U2U to obtain the required field
   identifiers if required.

Using U2U to obtain SharePoint column/field identifiers
for use in CAML queries:

1. Enter the full URL of the SharePoint site containing the
   list you are submitting the InfoPath form data to. (http://servername/TeamSites/Finance)

1. If you are not already logged in using an account with the
   required access, enter the credentials of an account with access
   to the required SharePoint site and lists. Leave the Connect via
   SharePoint Web Services option selected and press Connect.
2. Select the SharePoint list or library from the list of
   options.
3. Select the fields which you will be submitting data to, then
   check the "Order By" box for each. U2U will then
   progressively build the CAML query in the editor below.

1. The name values for each FieldRef element are the required
   identifiers for each of the corresponding fields in the
   SharePoint list.

3. Add the CAML template as a Secondary Data Connection

The XML file created above is a CAML query which when sent to a

1. From the Tools menu, Select Data Connections
2. Select Add, from the Data Connections Dialog
3. Select Create a new connection to Receive Data
4. Select XML document when asked where to receive your data
5. Add the XML file containing the CAML query created in step 2
6. Optional: Select "Access the data from the specified
   location" to enable changes to the CAML template to be made
   easily. Check "Store a copy of the the data for offline
   use" to ensure the CAML template is available when the xml file
   is not.
7. Type a name for the Data Connection, leave "Automatically
   retrieve data when form is opened" checked and select finish.

4. Add the Data Connection to Update the SharePoint List Item Using the CAML Template

A Submit Data Connection needs to be created, that connects to the SharePoint Lists web service. Once the CAML template is populated with data the XML from the CAML template is passed with the SharePoint list GUID to the web service and is processed asinstructed in the CAML query.

1. From the Tools menu, Select Data Connections
2. Select Add, from the Data Connections Dialog
3. Select Create a new connection to Submit Data
   
4. Select "To a Web Service" when asked how you want to submit
   your data.
   
5. Enter the address to the lists.asmx web service on the
   SharePoint site. You need to use the Lists web service for
   the specific SharePoint site/sub-site (http://servername/TeamSites/Finance/_vti_bin/lists.asmx).
6. Select "UpdateListItems" from the list of options.
   
7. For the ListName parameter, choose the FormID field in the
   main data source created in Step 1.
8. For the Updates parameter, select the "Batch" node of the
   CAML data source
9. Select "XML subtree, including selected element" from the
   dropdown menu and press Next
   
10. Give the data connection a name and press Finish.

At this point, you have added the secondary data connection to store the CAML data, and a connection to submit the data to SharePoint.

How to Update Repeating Field elements in the CAML Template Programmatically:

One solution is to update the required element/node in the CAML template programmatically when a user changes a field's value on the form. This can be done by looping through each element/node in the data source until the name attribute of the current element matches the required field. Event handlers (On After Change events) can be set for fields on the form or data source to pass the CAML data source field name and the XPATH to the field containing the required value to a function, with updates the required element based on the name attribute.

How to Submit using a Secondary Data Connection
in InfoPath

To submit he CAML template to the web service, you can either set up the form to use the data connection created above using the standard submit functionality (Tools -> Submit Options...), or submit programmatically using the form's On Submit event, which is fired when a user submits the form.

Set up the Form to Submit to the Web Service Using the Submit Options Dialog:

1. From the Tools menu, select Submit Options
2. Tick "Allow users to submit this form",
3. Leave "Send form to a single destination" selected if you
   are only submitting using a single data connection.
   
   Note: If you are submitting using multiple data
   connections, select "Perform custom action using rules", then
   create a separate rule to submit each of the required data
   connections. If using rules, please ignore the steps
   below.
4. Select "Web Service" from the list if options
5. Select the web service data connection created in Step 4
   when asked to choose a data connection for submit
6. Press Ok

Set up the Form to Submit to the Web Service Programmatically:

When submitting data from an InfoPath from using multiple data
The code used in this example is JScript.

1. From the Tools menu, select Submit Options
2. Tick "Allow users to submit this form"
3. Select "Perform actions using code"
4. Press the "Edit Code..." button to open the Script Editor
5. Paste the following code into the function created by
   InfoPath which is called when the form is submitted (XDocument::OnSubmitRequest()
   if using JScript for the form code). Replace "WebServiceDataConnection"
   with the name of the web service data connection.
   
   
   
   
   
XDocument.DataAdapters["WebServiceDataConnection"].Submit();

InfoPath - CAML Date Time format in when submitting to a SharePoint list via a Web Service
Date/Time formats are important when using CAML queries to update SharePoint list items, as only ISO 8601 format is supported. If a date value is not the correct format, the date/time element, and any other elements/updates in the CAML template will not be applied to the SHarePoint list item. See CAML Date/Time formats for details.

Related Articles

• DanielS (July 25, 2009): InfoPath: Update Repeating Elements 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..."
• DanielS (July 7, 2009): InfoPath Secondary Data Connection - retrieve data from a SharePoint List or Library
  "The following demonstrates how to use InfoPath data connections to connect to and retrieve SharePoint list contents (item metadata) for use in the form. This can be useful when InfoPath forms are used as a workflow component or interface, or if the form..."
• DanielS (June 28, 2009): Submit InfoPath form to SharePoint List or Library using Content Type
  "The following article demonstrates how to integrate an InfoPath form with a SharePoint form library, using a centralised template stored in a SharePoint document library and linked to a site Content Type..."
• Matt Faus (February 2nd 2006): Add SharePoint List Items with InfoPath
  "One very useful feature of Microsoft SharePoint is its ability to create and maintain lists that users can leverage to store, view, and share items with other users of the SharePoint site..."
• Karine Bosch (December 15 2007): Updating a SharePoint list using the CAML Query Builder dlls
  "Another question I got today was how you could use the CAML Query Builder dls to add a new item to a SharePoint list..."
• Karine Bosch (August 27, 2008): Writing CAML Queries For Retrieving List Items from a SharePoint List
  "The Collaborative Application Markup Language (better known as CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS site..."