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.
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.
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.
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"/>
<add key="ListName" value="NSO - Issues - PROD"/>
<add key="SourceField" value="V3Comments"/>
<add key="TargetField" value="CommentsHistory"/>
<add key="SPWSUserName" value="[user]"/>
<add key="SPWSUserPassword" value="[pwd]"/>
<add key="SPWSDomain" value="[domain]"/>
<add key="LogFileName" value=""/>
<add key="SPListItemQueryString" value=""/>
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.
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:
- 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.
- 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.
Version 0.1 was developed by Joao Faneca (firstname.lastname@example.org) on May, 24, 2008.