InfoPath - Update Existing SharePoint List Item Programmatically Using CAML - SharePoint Development & Administration + InfoPath

Saturday, July 25, 2009

InfoPath - Update Existing SharePoint List Item Programmatically Using CAML

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:
  5. Update Repeating Field elements in the CAML Template Programmatically How to Submit using a Secondary Data Connection in InfoPath


Technical Requirements





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.

The following information about the SharePoint site and list which will be updated when the form is submitted is required:
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:

The internal field names of the columns in the SharePoint list which are being updated
are used in a CAML query to identify a column rather than the its
display name. The internal name for a custom columns/field called "Task Number", will be "Task_x0020_Number". A space is replaced by "_x0020_".
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.


For example, a field named "Internal Task File Number", would be identified as "Internal_x0020_Task_x0020_File_x", as only the first 32 characters are used to identify the field. If another two fields are then added to the same list named "Internal Task File Number 001" and "Internal Task File Number 002", they would be identified as "Internal_x0020_Task_x0020_File_x0" and "Internal_x0020_Task_x0020_File_x1". Note the incrementing number at the end of each field identifier, which ensures that each field is unique.


For assistance with obtaining field identifiers as well as generating CAML queries, U2U CAML Query Builder is available for free from http://www.u2u.be/res/Tools/CamlQueryBuilder.aspx. This is an excellent tool to use when creating CAML templates for submitting data to a SharePoint list. See below for an instructions and an example of one of it's many uses.


Things to know about this CAML template plus some general info
  • 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:
  2. <?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>
  3. 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.
  4. 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.


U2U CAML Query Builder is a free tool which helps generate
CAML queries for lists and libraries in a SharePoint site
collection. I also use the tool to determine the correct
identifier for each field in a particular list.


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
SharePoint list web service along with the SharePoint list GUID to
update the item in the list with the same ID as the Field element in
the CAML template.
  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 as
instructed 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:



All fields in the CAML template above use the same "Field" element distinguished by a "name" attribute. This makes it difficult when trying to update fields in the CAML template without
having to display the data source as a repeating table/section with controls on the form for direct input.
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
connections, it can be useful to submit each data connection
programmatically, as it gives you more control over the entire
process.
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.



  6. 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
Share this article:
Stumble This Delicious
Delicious
submit to reddit
Facebook
MySpace
MySpace

1 comment:

Marty said...

Hi Daniel,

when I hit submit, i get the following error:
Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.
There is no Web named "/sites/MySite".


I had to use the list.asmx located not at the site level, but the root level since the site level wsdl wasn't exposed....

This site is a form library

any ideas?

Thanks

Post a Comment