Changing column type in a list template – upgrade scenario

In this article I would like to describe the scenarios that you can come across when changing  the structure of a list.

Changing column type

Consider following scenario: We have a list that should hold information about a person. the person has following fields: first name , last name and birth date. The part of schema.xml file, that defines the columns could look like this:

<pre><Field Type="Text" DisplayName="Firstname" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="FirstName" Name="FirstName"Version="1" ColName="nvarchar1"/>
<Field Type="Text" DisplayName="LastName" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="LastName" Name="LastName"Version="1" ColName="nvarchar2"/>
<Field Type="Text" DisplayName="BirthDate" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="BirthDate" Name="BirthDate"Version="1" ColName="nvarchar3"/>

So far so good. Now we released this list template and the users happily created lists based on this template and filled thousands list items into these list. But there is an error in the xml: the birth date should not be of type text but DateTime. So we modify the schema.xml accordingly:

<Field Type="Text" DisplayName="Firstname" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="FirstName" Name="FirstName"Version="1" ColName="nvarchar1"/>
<Field Type="Text" DisplayName="LastName" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="LastName" Name="LastName"Version="1" ColName="nvarchar2"/>
<Field Type="DateTime" DisplayName="BirthDate" Required="FALSE" ID="{168a8095-32a8-4b25-a4b6-046b3dc3d466}" StaticName="BirthDate" Name="BirthDate" Version="1"/>

After the activation of the feature the SharePoint will update all columns in the list instances. However it is very probable that the existing list items will not be editable. In my case I become “Input string not in a correct format”. And if you e.g. convert  a column from DateTime to Text you can receive even a CATASTROPHIC_FAILURE. Cool. So this is not the way how to fix it.

Deleting column

If you want to create a robust scenario for updating columns do not ever change the type of the column, instead mark it as hidden and create a new field e.g. BirthDate2 with the correct data type. Then after the installation and activation of the feature you have to iterate through all the existing list instances of the type and transfer the data programmatically from the old to the new field, wherever possible. If you have a temptation to delete the old column and create a new column with another type and guid, be advised that SharePoint will update the list instances accordingly. This means in our case it would delete all data in the Birthdate column in all these lists. This is really dangerous side effect of updating the lists.

Simple advice

Don’t screw up in the first place, because it could be really time consuming to make it right.