Custom Sorting Methods for End User?

[kd2017]

I will frequently require itemized lists for output to reports. The user will need to be able to arbitrarily customize the sort order of these items. That is the items won't necessarily be sorted by date/time or alphabetically or any predefined logical order. The user needs to be able to say this item is at the top of the list, this item goes before that one, this other one goes before that one, etc. Arbitrary sort order.



I figure at the time of reports that there will be line numbering but that will be handled dynamically by the report. BUT the reports could just be bullet point lists and not numbered lists.

So my question is what is the most efficient method to save custom arbitrary sort orders in the database?

At first thought I would simply have a SortOrder field in the table of an integer datatype. They would simply be sequential integers the report queries could sort by, and when the user moved an item around in the sort order I'd have a procedure to move the rest of the items in the list appropriately to make room. The user would have access to directly edit this integer. But I'm wondering if it would be wiser to use floating points. Each time an item is added to the list the SortOrder = previous item's SortOrder + an arbitrary large number. When an item is moved up and down the list instead of changing the SortOrder field of every item in the list that is affected all I'd have to do is make the SortOrder # of the item I'm moving greater than the item i'm moving it behind, and less than the item im moving it in front of.

For example:
Step 1) I add the first item A to the table and vba assigns arbitrary sort order # 1.0
Step 2) I add another item B with sort order # 1000.0
Step 3) I add another Item C with sort order # 2000.0

If I want to move C between A and B I simply set C's sort order # to (1.0 + 1000.0)/2=500.5. Of course the math would be handled behind the scenes and the user would never see the actual sort order field value. And as for UI i'm guessing something like a listbox where they select the item and have up and down command buttons to move the item up and down the list...

Any thoughts/issues? Examples? I'm guessing this has been done before, I don't want to reinvent the wheel over here.

[June7]

Is this a multi-user db? Cannot use the original data table to assign sort order to each record. Would be conflicts between simultaneous users.

Really should be saving user choices to a table. If you want to maintain history of these choices the records would be permanent, otherwise use a 'temp' table where records reside long enough to complete a process and then purged.

[kd2017]

Is this a multi-user db?

Not at the moment but we're open to the possibility in the future. I'll say there shouldn't be multiple users editing/entering data at the same time.

Cannot use the original data table to assign sort order to each record. Would be conflicts between simultaneous users.

Can you elaborate on how/why things would conflict? I'll admit my experience with multi user access apps is none at the moment, about the only thing I know is I need to split the db and use a different FE for each user.

I don't know if this makes a difference but the way I'm figuring it at the moment is when a user clicks a button "Move Up" or "Move Down" the button will call a Sub that will Dim a new recordset, make necessary changes to the sort column, save and close the recordset. In other words it's not hanging open for any significant length of time.

Again, I don't really have much of an understanding of the nuances of multi user environment...

Really should be saving user choices to a table. If you want to maintain history of these choices the records would be permanent, otherwise use a 'temp' table where records reside long enough to complete a process and then purged.

I do need to maintain a sort order and was planing on saving it to the table.

[Micron]

Cannot use the original data table to assign sort order to each record.

You can if the sort isn't user based. I may have missed where the OP says it is, but I read it more than once and don't see where that was declared. It could be that if you make an initial sort and I add data, I should be able to insert my records in between yours and the desired sort would still be maintained. If you see where I made a mistake in my sort, then you should be able to correct it. If that isn't the situation, then I agree.

I have used sort order fields based on increments of 100 but didn't apply any calculation to determine the next ordinal number. Sounds like a good idea to find the mid point between two records. If you're talking about a LOT of records eventually and you happen to run out of positions, you could use a multi-field sort where the 1st is alpha. Just don't use integer data type for your sort field.

[June7]

You propose field in the data table where user would assign a value for sort order. If there are multiple simultaneous users trying to change data in the same records, which should have precedence?

[kd2017]

You propose field in the data table where user would assign a value for sort order. If there are multiple simultaneous users trying to change data in the same records, which should have precedence?

Which user should have precedence? I guess I don't understand why this scenario is any different than a user entering any other data in the record. ?

[kd2017]

You can if the sort isn't user based. I may have missed where the OP says it is, but I read it more than once and don't see where that was declared. It could be that if you make an initial sort and I add data, I should be able to insert my records in between yours and the desired sort would still be maintained. If you see where I made a mistake in my sort, then you should be able to correct it. If that isn't the situation, then I agree.

I have used sort order fields based on increments of 100 but didn't apply any calculation to determine the next ordinal number. Sounds like a good idea to find the mid point between two records. If you're talking about a LOT of records eventually and you happen to run out of positions, you could use a multi-field sort where the 1st is alpha. Just don't use integer data type for your sort field.

The sort order is not supposed to be unique to different users if that's what you all mean.

[orange]

How about giving us a real life example of the types of sort you think you will encounter?

People are guessing ----- let's work with a sample.

[kd2017]

Let's say I'm creating a quote to send to a customer. I have a report that will generate the quote on company letterhead. The quote will have multiple lists of qualifications. Each qualification is a "list item", a quote can have dozens or more of qualifications.

I list of qualifications might look like the following

...
Included in this quote:

1. Floor Framing
2. Wall Framing
3. Roof Framing
4. Windows
5. HVAC
6. Plumbing

...

The person preparing the quote needs to be able to arbitrarily move line items around in this list.

[Edit]

And let's say that list was generated from a qualifications table that looks like this:

Qualifications
QualID as AutoNumber
QualDescription as Short Text
QualSortOrder as Number
QuoteID as Number (foreign key)

[June7]

Well,

The user will need to be able to arbitrarily customize the sort order of these items.

means to me the sort will be dynamically defined by each user. Which seems to be contradictory to

The sort order is not supposed to be unique to different users if that's what you all mean.

[kd2017]

Well, means to me the sort will be dynamically defined by each user. Which seems to be contradictory to

A user will define the order of the list but the order is not dependent on which user is looking at it. The order is what it is for everyone. If I move an item from the middle of the list to the top of the list that's where it goes, for everyone! I'm not sure we're on the same page?

[June7]

So I presume you have Quotes and QuoteDetails tables where user enters record(s) and defines sort order for that subset of records. I expect more than one way to skin this cat. Pick one and go for it.

[orange]

I remember writing some code to move an Item up or down in a listbox. Don't know if it applies.

[kd2017]

So I presume you have Quotes and QuoteDetails tables where user enters record(s) and defines sort order for that subset of records. I expect more than one way to skin this cat. Pick one and go for it.

Lol! Thanks I guess In all sincerity I do appreciate you taking your time to look at this.



So my question is what is the most efficient method to save custom arbitrary sort orders in the database?
Any thoughts/issues? Examples? I'm guessing this has been done before, I don't want to reinvent the wheel over here.

[kd2017]

I remember writing some code to move an Item up or down in a listbox. Don't know if it applies.

Yep, that's what I'm after! Assuming the order in the list is saved to the backend.