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

• Download ConsoleAppCopyHistoryListField - 23.42 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 that type: 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 export 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 SharePoint 2007 lists webservice (_vti_bin/lists.asmx), I want to get the items from the list, using the methog 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'll 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.
Note1: 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 result (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, knowing which user made the change.
Note2: 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 XP workstation.

Configuration

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

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 wil 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 SharePoint Lists WebService, there is no way (at least, that I know of) other than call the GetVersionCollection twice for each record: one call to retrieve the history for the desired field, another call to retrieve the user that made the change. Nevertheless, the UpdateListItems method does support updating several records in a single call. Doing so will decrease the number of calls to the WebService, 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 troubles calling the update method with a large number of records.
Another issue afecting 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 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 the 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 that 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 finding 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, 24th, 2008.