Create custom sort order 3

[glgcag1991]

I haven't found anything in the FAQ's or in this forum about this subject but I've got to believe there are a lot of people needing this feature, so here goes:

I have a table of team members that are assigned to a client. My users want to be able to sort the team members in the client's record view in a custom order, not based on alpha, or on the order they were entered. The sort order may need to be changed every so often, so I can't lock it in.

What I'm going to give the user is a chronological sort in a list box based on the primary key number ascending (essentially, the order they entered the support members) and then give them up and down arrows next to the list box so they can highlight a team member and move them up or down in the team member list.

My thought is that I should have a field for the Client Team Member table that is a sort field and the sort value is assigned as they are entered. Once the users start moving the team member up or down in the list, the sort field values are changed to reflect the new order. Is this what is generally done in order to achieve a custom sort order, and if not, what's the best technique?

Thanks for the help!

 

[ousoonerjoe]

Another Option is to create a Sort Table:

CREATE TABLE SortTable
SortID INT IDENTITY(1,1) NOT NULL
SortField VARCHAR(20)
SortOrder SMALLINT
SqlUserID INT

Add this table as a JOIN to the query. Then you can simply use your ORDER BY SortTable.SortOrder and it will allow each user to have their own personalized sort order.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

Hmmmm.... Interesting.

My answer to your question is... 'It depends' (don't you just love those answers?)

If the custom sort order is consistent, then I recommend adding a column to the existing table.

If I'm not mistaken, the advice offered by ousoonerjoe extends this functionality a bit. You see, if you need custom ordering depending on user, or area of functionality within the app, then creating a separate table makes sense. With the separate table, you can have each item in the list multiple times, each with a different sort order.

For example, suppose you have a list like this...

Apple, Banana, Orange.

Now, suppose you want the list sorted by flavor preference. Furthermore, suppose I like Bananas better then apples, and I like Oranges better than apples. But... suppose ousoonerjoe prefers apples, oranges, and then bananas.

If you have a seperate table, it might look something like this...
[tt]
ItemId, UserName, SortOrder
Banana George 1
Orange George 2
Apple George 3
Apple ousoonerjoe 1
Orange ousoonerjoe 2
Banana ousoonerjoe 3
[/tt]

By using a separate table, you can have multiple sort orders. If you add a column to the existing table, you can have only 1.

So.... what's me advice? If you only need to maintain 1 sortorder column, then add it to the existing table. If you need multiple sort orders, create a new table for this. If you're not sure, and think that some day, maybe, you may need to support multiple sort orders, then create a new table now (even if you only plan on using it for 1 sort order).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glgcag1991]

ousoonerjoe,

Perfect, just what I'm looking for!

gmmastros,

Thank you for the tutorial, you expanded brilliantly on ousoonerjoe's reply!

I will use the sort table, as this does give me the flexibility to do what I need and have future customization in mind just in case.

Lastly, connecting the dots between a user hitting the "up" or "down" arrow next to a list box seems like the following should happen:

If the user wants the team member id 425 that has a value of 5 for their sort order to be moved up to 4 I would: (assuming the list box is sorted ascending by sort order)

1. Get team member that has sort field of 5 - 1 and set it to it's current sort value + 1 (it's being moved down in the list)
2. set team member 425 sort field to 5 - 1 (it's being moved up the list)
3. Requery the list box and highlight team member id 425.

Vice versa for selecting the "down" arrow.

It seems like this is the easiest way to do it, though it may be tedious for the user if they have a lot of moving to do. Furthermore, with every click, I'm running the update, which I don't mind, but it just seems so inelegant- is this how you guys would do it?

 

[JoeAtWork]

Furthermore, with every click, I'm running the update...

You don't need to update the database every time the user clicks the up or down arrow. Do all that in memory until the user is done, then save the changes. Of course, you need to populate the listbox yourself instead of using straight binding (unless you are using .NET, in which case you could create your own data source objects).

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[glgcag1991]

JoeAtWork,

Thanks for the suggestion- I'm primarily programming for a company of 30 users and recently began the migration process from an Access db to SQL Server 2005 using a web front-end, so running 30 users on an intranet website using "click and update" wouldn't be too much of a stress on SQL Server 2005, but I am curious as to how you might program it without sending the updates after every click. (I'm just getting the SQL Server stuff in order right now, I haven't even addressed the client web programming- it's going to be a steep learning curve . . .)

In your .NET comment, do you mean sending the data to the browser and rearranging as needed using the data object in RAM on the client and sending the whole batch of updates back once the user is done re-sorting? Sort of like an array?

 

[Crowley16]

what he means is to have one listbox on your form, when opened, populate the listbox with your figures. Let the users play about a bit, and then after hitting the save button, loop through your list box and modify the custom order of each record...

at least, that's what I think Joe means...

--------------------
Procrastinate Now!

 

[ousoonerjoe]

Yes... Populate the Listbox with the most resent saved sort order. Let the user move the items around how ever they deem fit in the listbox. Once they have finished, they click a <Save Sort Order> button and simply UPDATE the list once. This will allow for Cancelling of changes and minimizes the network traffic as well as the database activity. The listbox by its very nature will store the changes in memory for you.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[glgcag1991]

Thanks guys, yes what you are saying makes sense. Of course, having to hit a "save sort order" button is too much for most of my users- trust me, I work in entertainment and I'm going to get a bunch of "I re-sorted it but the changes didn't take!" calls. (I had a user say, "I searched for the name but it's not in the database." When I pointed out she misspelled it, she was mystified why the database couldn't just guess at what she was searching for, keeping in mind no Soundex or Double Metaphone code would have figured out her atrocious spelling . . .) I'll play with both but I may just have to do the update-after-click to avoid the user problem.

Lastly, have any of you implemented an AJAX tool that allows the user to just drag and drop items to re-sort a list and if so what did you use? (The .NET AJAX tools come to mind but I'm not familiar with them yet . . .)