Custom ranking field on planning item should update and re-sort if 2 records have the same value.

[RitaS]

I have a requirement to create a manual ranking field on the SPW planning item table because the drag and drop ranking field has proved to be inefficient and error prone when we have hundreds of items on the list.  I've created the entry field and can populate the custom field on the SPW planning screen.  However, I would like to have the values in the field re-incremented if a duplicate ranking is entered.  For example, if I change a rank value from 20 to 5 and there is already another record with a rank of 5, I need the other 5 value to update to the next number up, and on up the list until it hits a consecutive next number (i.e., 21).  Has anyone done something similar or have suggestions on where to start?  

[OlaN]

Hi,

This sounds like doable at first glance, have a business rule run and check for unique numbers whenever a number is changed on a record. And if any duplicates are found, then set new numbers on them in a recursive way so that all numbers are still unique. Easy as long as only one transaction happens at a time.

Then comes the challenge.

What if two records are changed more or less at the same time ?

If there are 20 records, and they have numberings from 1-20, and one is changed from 17 to 3, and almost at the same time another one is changed from 14 to 8.

What happens then, is that all records in the interval 8-14 is supposed to be changed twice due to two operations happening which should change the number. This could lead to race conditions depending on the order of execution.

And this will become increasing harder to handle if the number of concurrent transactions happen at the same time.

Not sure if this will be possible to solve.