The logic of reordering a numeric list

[Jim McKee]

This is an incredible brain-bender for me ... so much so that I need to ask for help with the logic involved in the following:

I have 10 records with a Number field, "sort_order", containing unique values sequentially from 1 to 10. The sort_order field is used to sort the records.

Let's say I change the sort_order field in record 2 to "1" so that record 2 will sort first in the list. Obviously, in this case, record 1 will then have its sort_order set to "2". This works out to be a simple swap, as it would be if I changed any sort_order value to it's next higher or lower position.

But, what if I want to change the sort_order field in record 4 to "7". In this case I don't want to swap the sort_order values, because I want to keep the sort order as close to its original state as possible. So, record 4 becomes "7", and record 7's sort_code can only be changed a maximum of 1 place in the sort order.

I need the logic involved for how assign values to the other records after a record has been assigned a value already belonging to another record.

Is there an equation I can use here?

Mondo TIA for your help.

[Vaughan]

Think outside the square: the numbers don't have to be integers. And the Replace (reserialise) command is your friend.

In your first example, set record 1 to 2.5, then sort and reserialise the numbers. In the second example, set the record from 4 to 7.5 then sort and reserialise.

The gotcha here is that in a multi-user scenario the replace function could miss records due to record locking. However this needn't be a tragedy since the sorted records will still be in the desired order, only the sort field won't be nice and integral.

[Jim McKee]

Vaughan said:

Think outside the square

Vaughn ...

That's exactly what I wasn't able to do after staring at this for a few hours today.

Great solution. Thanks a bunch.

Just a couple of additional thoughts:

1. An exception has to be built-in If the user wants to change a sort_order value to "1" so it will sort first in the list. In this case, the script will automatically set the new sort value to ".5" so it sorts before the existing "1" record. Then reserialize.

2. Give the user the option of placing the reordered record "before" or "after" the existing record with the desired sort_order value. If user selects "before", then subtract .5 from the user-entered sort_order value. If the user selects "after", then add .5

Fortunately, only the administrator has access to this file, so there's no problem with record-locking.