CODEWITHSUNDEEP
sharepointsharepoint-2010
Jonnus
Jonnus

Reputation: 3038

SharePoint Calculated Field resets on item edit

I am using a SharePoint list that was migrated from an old Windows 2003 server to an existing 2008 server. This list is used to track issues raised by users against another application - a list of jobs to do if you will.

In the old list there was an Id field. When the list was recreated in the new area the assigned ID number was different, for example

Old ID    New ID
--------- ---------
5         204
6         2
7         3
8         159
9         4

This assignment does appear to be fairly random.

To negate this a calculated column was created, so IDs raised before the migration use the Old ID number, and new calls raised after the migration use a calculated value. The calculation is 

=IF(ISBLANK([ID (Old)]),ID+8,IF([ID (Old)]<=348,[ID (Old)],ID+8))

This compares the ID (Old) to blank, and if so, it is a new call and should have the default ID number, plus 8 (I think there was a total of 8 calls made during development that were then removed).

Otherwise if the Old ID number is less than 348 (the call number when we migrated), use the Old ID number.

If neither condition is met, simply set the ID to the new ID number plus 8.

This works fine when creating new calls.

The problem arises when I edit a call. For example if I set the ETA to a new value and save the call, the Calculated ID is always reset to 8.

I can fix this by going in to List Settings -> Opening the calculated column -> and clicking OK. This reapplies the calculation and everything is numbered correctly again.

For information - due to the environment this is deployed in I am not able to use SharePoint Designer at all.

The question is, how can I stop the renumbering when I edit an item?

Upvotes: 0

Views: 2248

Answers (1)

Thriggle
Thriggle

Reputation: 7059

The ID field behaves a bit oddly with calculated columns. A calculated column formula will find the ID of an item and resolve it properly when items are created or when the formula is modified, but as you discovered, when an item is modified, the formula can't find the ID value. (My guess is that this has to do with the ID field not being included in the list of column values being sent to the database on update, perhaps because the ID field should never change.)

One way around this is to use a workflow (or a custom event handler, if you have too much time on your hands) to copy the ID field to another column, such as a number or single line of text column, whenever an item is created. You can then use that new column in your formula instead of using the ID column.

To prevent people from updating your dummy ID column, you can hide it from the forms. To do this, first enable management of content types for the list, then edit the Item content type, find the new dummy ID column, and change it to "Hidden" (instead of "Required" or "Optional").

Upvotes: 2

Related Questions