I have a table with a list of items, which I would like the end user to be able to reorder into an order of their preference which isn't associated with any existing field.

I was thinking of adding a new field to achieve this and then indexing and sorting by this field but assuming the field contains integers of 1, 2, 3, 4 and they then wanted the list to read 4, 1, 2, 3  i'm looking for advise on the best way to achieve this.

Many thanks

Anthony


If the ordering is intended to be persistent both across users and across usage sessions then you have to re-write the indexed column every time. If its not meant to be persistent then look at a non-data bound control. If you don't need a sensitive result set then a cross reference table may work.

Over the years I've used several different approaches including using a float rather than an integer - it means you can insert between without renumbering the entire database.

Can you give a bit more information about your use case?

Roy Lambert

Anthony wrote:

> I have a table with a list of items, which I would like the end user to be able to reorder into an order of their preference which isn't associated with any existing field.
>
> I was thinking of adding a new field to achieve this and then indexing and sorting by this field but assuming the field contains integers of 1, 2, 3, 4 and they then wanted the list to read 4, 1, 2, 3  i'm looking for advise on the best way to achieve this.

In my experience, it always takes a few goes to get it right! But the key here will be to display the rows with the data holding both the row's ID and the sort order. So you show the list in the sort order, allow them to change position accordingly, and then at the end you go down the list and set the order column in the database by the row's ID. That's sort of the easy part. The hard part is the visual sorting, but don't be clever, just keep it simple and assign the order value from the top.

--

Matthew Jones

Thanks for your replies.

I can see that a single solution may not be suitable for all scenarios as pointed out by Roy and also the floating point is a great idea so I can insert an entry between two previous items without renumbering everything, which is think was the main point I was trying to achieve.

Also I appreciate Matthew's comments that it is going to take a few goes to get it right and also the manual entry of the index is going to be much better for experimenting and testing than the hopefully end result of good visual and user experience.

I was hoping to create the look and feel found in so many popular to do lists apps where items can be reordered by dray and drop and reordering would persist in the database.

Anthony



>I was hoping to create the look and feel found in so many popular to do lists apps where items can be reordered by dray and drop and reordering would persist in the database.

I have done that a couple of times for my own dbgrid descendent - its not easy, and I'm not sure I could do it for a standard dbgrid. The approach really only works for small data volumes. If the volumes are low I'd suggest looking for a component that already does what you're after and manage the data linkage yourself eg VirtualTreeView (http://www.soft-gems.net/index.php/controls/virtual-treeview)


Roy

Anthony,

<< I have a table with a list of items, which I would like the end user to be able to reorder into an order of their preference which isn't associated with any existing field.

I was thinking of adding a new field to achieve this and then indexing and sorting by this field but assuming the field contains integers of 1, 2, 3, 4 and they then wanted the list to read 4, 1, 2, 3  i'm looking for advise on the best way to achieve this. >>

Is this a multi-user system, or just single-user ?

Tim Young
Elevate Software
www.elevatesoft.com