Using the GetListItems, GetVersionCollection, and UpdateListItems methods from the SharePoint 2007 Lists WebService

• Download source code - 23.4 KB

Introduction

This code was developed after a client asked to export a SharePoint 2007 issue tracking list to Microsoft Excel. It happens that when a SharePoint 2007 field has the feature "Append Changes to Existing Text", the field history is shown on the browser, but it is not shown on Microsoft Excel after the export. The issue tracking list from SharePoint 2007 has two fields of types: Description and Comments. This code was developed to solve the issue, by copying the desired field history into another field, containing the history in a single record, allowing it to be exported to Excel in a straightforward way. The objective is, for each record in the list, get the full history for a given field, and copy the full history into a single field existing in the same SharePoint list.

Solution

Using the SharePoint 2007 Lists Web Service (_vti_bin/lists.asmx), I want to get the items from the list using the method GetListItems. In order to get the history for a given field in each record, I'll use the method GetVersionCollection. This method only allows getting the history for a single field: it would be nice to have a method for retrieving the history for several fields. I think there is no way for doing that :( After getting the history, I call the method UpdateListItems to update each record. Updates will only be made if the history has changed since the last update on the history field.

Note 1: I want to have on the history field the users that made the changes. The GetVersionCollection method called on the source field doesn't return the users who made the changes. In order to get the users, I'll call the method GetVersionCollection for the "Editor" field. Iterating through each node at the same time in the two results (the GetVersionCollection result from the source field and the Editor field), I'll have a match from the history on the source field and the editor field, thereby knowing which user made the change.

Note 2: I'll call the UpdateListItems once for each record. Actually, I could include several updates in a single call to the UpdateListItems method.

Requirements

The solution was built using VS2005 running on a Windows XP SP2. The SharePoint 2007 Server was running on a Windows 2003 server, in the same domain as an XP workstation.

Configuration

The program configuration is made through an appSettings section on the application config file. No arguments from the command line are supported.

The supported parameters are:

<add key="PrintMessages2Console" value="true"/>
<!-- Print messages to console -->

<add key="UrlListSite" 
  value="http://sharepoint.viatecla.pt/nso/issues_business/_vti_bin/lists.asmx"/>
<!-- The url for SharePoint list WebService. Be sure to have the correct 
     sharePoint site in it: the one where the list exists --> 

<add key="ListName" value="NSO - Issues - PROD"/>
<!-- The list name to look for --> 

<add key="SourceField" value="V3Comments"/>
<!-- The field used to retrieved the history from -->

<add key="TargetField" value="CommentsHistory"/>
<!-- The field used to store the history retrieved the source field --> 

<add key="SPWSUserName" value="[user]"/>
<!-- Username required to access SharePoint site. 
     Current credentials will be used if left blank --> 

<add key="SPWSUserPassword" value="[pwd]"/>
<!-- Password required to access SharePoint site. 
     Current credentials will be used if left blank --> 

<add key="SPWSDomain" value="[domain]"/>
<!-- Domain required to access SharePoint site. 
     Current credentials will be used if left blank --> 

<add key="LogFileName" value=""/>
<!-- Logfilename and path. Program file name, 
     replace by the extension ".log" will be used if left blank --> 

<add key="SPListItemQueryString" value=""/>
<!-- SPListItemQueryString can be set to retrieve just some records from 
  the SharePoint List. If not set, all records will be retrieved.
  An example of a query string: <Query><Where><Eq><FieldRef Name=\"ID\" />
  <Value Type=\"Counter\">2213</Value></Eq></Where></Query> -->

Known issues

Performance was not a concern in this approach. By using the SharePoint Lists WebService, there is no way (at least, that I know of) other than to call GetVersionCollection twice for each record: one call to retrieve the history for the desired field, another call to retrieve the user that made the changes. Nevertheless, the UpdateListItems method does support updating several records in a single call. Doing so will decrease the number of calls to the Web Service, but take care: that approach will force you to control the number of records to update in each call. In the past, I've found some trouble calling the update method with a large number of records. Another issue affecting performance: the issue tracking list from SharePoint 2007 has two fields with the "Append Changes to Existing Text" feature: Description and Comments. This code only updates one field. If you want to have the history for both fields, you have to run this code twice. One last note: if performance is an issue, you shouldn't really use the WebService approach.

Possible improvements

You might need to update several fields in the same list. Currently, the code only supports one SourceField and one TargetField. The code could easily be changed to support a collection of fields to update, in a pair of Source-Target fields. This code has several performance problems. Apparently, there is no way to avoid calling GetVersionCollection twice for each record, but it's possible to avoid calling the UpdateListItems once for each record to update. That could increase performance a little bit, but the main problem is calling GetVersionCollection twice for every record (remember that the update will only be called for the records that had the history changed). If performance is an issue, you shouldn't really use the WebService approach. I think there are two other directions to look for:

1. You could seek for the SharePoint object model, hoping that the object model offers more than what is exposed through the WebService. Of course, the code must run on the SharePoint machine.
2. You could look into the SQL Server SharePoint tables, trying to find where the information is kept inside the data model. Running a SQL profiler while requesting the history information on a SharePoint site using a browser will help find the information you need. Until this moment, I had no need to hack the SharePoint SQL tables.

A request for feedback

This code was developed to solve the problem for having history information after an export from a SharePoint list to a Microsoft Excel workbook. Although the code can be a way of learning SharePoint 2007 WebServices, I would like to know if someone out there is aware of a better solution.

History

Version 0.1 was developed by Joao Faneca (jfaneca@hotmail.com) on May, 24, 2008.