Find next number in list 2

[SMHSleepy]

Hello all, I have a table with a field [SortOrder] as double. I use it to sort my form lists. Let's say I want to insert a record between [SortOrder] = 5 and [SortOrder] = 6. I currently just make the new [SortOrder] = 5.5 and it works fine but what if I want to insert another record between 5 and 5.5? With my current code, it would try to create another 5.5 and cause an error because I did not allow duplicates.

Assuming I set focus on the record I want to insert a new record immediately after, my idea is to find the next [SortOrder] number after the current one, subtract by the current number, divide by 2, then add that to the current number to get the new number exactly half way in between. This will ensure I never duplicate the [SortOrder] number. What I can't figure out is the first step, finding the next number.

I thought maybe a MIN([SortOrder]) which is greater than current [SortOrder]? Any suggestions? Please include coding, thank you.

 

[TheAceMan1]

How are ya SMHSleepy . . .

Try the following:
[ol][li]Hold the SortOrder of the current record in a variable.[/li]
[li]Run an Update query that increments all values greater than the held value:

[blue]UPDATE TableName SET TableName.SortOrder = [SortOrder]+1
WHERE (((TableName.SortOrder)>Forms!FormName!SortOrder));[/blue]

[/li]
[li]Assign the [blue]held value + 1[/blue] to SordOrder of the inserted record.[/li][/ol]
You can do away with the picky math and just use Long Integer.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Thanks Aceman. Kinda funny though, where were you a few weeks ago when I posted my "Create Sort Order" question? Your answer would have been exactly what I was looking for at the time. However, people suggested I use decimals and the more I thought about it, the better it sounded.

My database has over 25,000 records and growing. If I want to insert a record very early on, your method would have to renumber a lot of records. Wouldn't this take a long time? Also, if something goes wrong during the renumbering process, it could really screw things up.

What do you think I should do?

 

[lionelhill]

I would go with Aceman's version and certainly not the divide-by-2 and insert approach..
If you already have 25000 records, imagine you suddenly want to insert another 60 or so in one place.
A double has "only" 52-bits of stored precision. If you insert 60 records, a double won't be sufficient to store the last 8 or so records with unique numbers.

 

[SMHSleepy]

Thanks for the input, that's great to know. Like I said, Aceman's method was exactly what I was looking for earlier. Come to think of it, almost every insertion I will need to make will be near the end of the table so the "delay" I was worried about wouldn't even be a big issue anyway. Okay, made up my mind, will try it out and let you know how it worked out. Thanks again!

 

[SMHSleepy]

Okay, I get a key violation error. Probably because [SortOrder] does not allow duplicates and when the update query tries to increment the next record by 1, it conflicts with the next record. Is there a way to begin the incrementing process from the last record and work backwards?

 

[TheAceMan1]

SMHSleepy . . .

I don't consider 25k to be alot of records ... 250k, 2.5meg or more ...yes. Since you can only expect your db to grow, what you need is the fastest execution of things you can get. To be sure ... if you can do it in a query, its sure to be the fastest in execution method.

Also bear in mind ... if you needed to make a large hole to insert say 1000 records, the query would become:

[blue]UPDATE TableName SET TableName.SortOrder = [SortOrder]+[purple][b]1000[/b][/purple]
WHERE (((TableName.SortOrder)>Forms!FormName!SortOrder));[/blue]

Hence it only needs to be run once to make any size hole.

Sorry I missed the mentioned thread ... chances are I was tied up in other threads. Also, TheAceMan1 ia not retired yet! ... getting there though.



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Thanks very much Aceman. I hope you don't retire from your valuable contributions to this forum anytime soon. Were you able to solve my key violation problem yet?

 

[TheAceMan1]

SMHSleepy . . .

[blue]SortOrder[/blue] should not be a primarykey ... it should simply be [blue]a field used to sort on[/blue] (thats its purpose) ... however it should be indexed! In this way, assignments to [blue]SortOrder[/blue] can be made without primarykey conflict (the code itself already prevents this).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Excellent! It works like a charm. Thanks again for the help.