Recommendation for custom sorting of dropdown list dataset?

[rpavlicek]

I've been wanting to implement a way to custom sort for different users of our location sites which are in dropdown list to navigate from. The reason, is everyday they go to each one in order how they go to each location. Right now, they are alphabetical (which is great for everyone else).

I can think of several ways to accomplish this, but seems rough or too hardcoded. However, I do expect some hardcoding and maintenance.

1. For instance, I can add a new sql column of integer in the main table, put in 1-xx and when I run the query, I can do an "order by" on that new integer column. However, I need it a little more dynamic for 2+ users.

2. Another way is to create another table relationship off our main once specifically for this which would separate it from the main table so it's not so cluttered. This new table would also have a column of user, so it can associate by that user for their sort order, but I see a lot of redundant location values, but maybe this is expected and best?

3. This could be done in a sql case statement (in the order by clause), aliasing a column to sort, but I don't think I can make this dynamic easily, even worse for maintenance compared to the above.

4. Then I could always sort within the binding by using python scripting, but need this to work smoothly in both Vision and Perspective.

I just need a recommendation on which practice might be best.

[diat150]

I would create a separate table and save the locations in the order that each user wants. when you pull up the drop down list first check if there is an entry for that user in the "user ordered" table, if so load from that table, if not load in alphabetical order from the original table. You can probably have it automatically load into a client dataset tag when the user logs in so that there is no wait when the window is opened and bind the dropwdown to the client dataset tag in vision. not sure how that would work with perspective but I am sure there is a similar flow.

[rpavlicek]

Which format would be best, I'm leaning towards the tall (first) below?

Tall format, don't have to worry about adding columns, more dynamic.

Location	User	Order
Alpha	Tom	4
Beta	Tom	3
Charlie	Tom	1
Delta	Tom	2
Alpha	Amy	3
Beta	Amy	1
Charlie	Amy	2
Delta	Amy	4

The format below, wider, saves space, but columns are not dynamic.

Location	UserOrder1	UserOrder2
Alpha	4	3
Beta	3	1
Charlie	1	2
Delta	2	4

[jlandwerlen]

@rpavlicek

Tall format, don't have to worry about adding columns, more dynamic.

How would you handle adding rows if that requirement came up?

[rpavlicek]

I would setup the three columns as a unique index and with logic see if it exists or not.

Now that I think of it, I may try the wide format setup with a finite amount of user columns. I could add for instance 20 or more of these.

We have 300 locations, so 300 rows with 25 columns may be better than 6000 rows with several columns.

[jlandwerlen]

Not every user needs a reference. If there isn't a user reference use the default.

[diat150]

I would go with tall. I see no reason to go with a wide format although I am no database expert. Only thing I may add is a "group" id. while you may have only one group now in the future you may need the ability to sort on multiple groups where the same user has multiple groups assigned to them. you could always change later but if you add a group id column now you dont have to worry about that in the future.

6000 rows is nothing for a database table. I think you will have a big complicated mess if you try and do this with a wide table. IMO of course. If you are worried about saving space which isnt a concern imo you could serialize the dataset and store everything in 1 row

How to serialize a dataset?

Well, I had just figured out how to do it in pure Python by converting the dataset to a list of lists, using the python pickle module to serialize it, base64 encoding it and putting it in the database… but thanks to pturmel I was able to get the Java version working in just a couple minutes. I also referred to this blog post: joeygibson.com/2003/05/22/jytho … oo-useful/ . So here’s how I got it to work. Database (PSQL) has some character varying fields for identifying the save, and a bytea fiel…

[WillMT10]

I would also recommend the tall format. Much cleaner and easier to maintain. If you remove a user from a parent user table, you can have a Foreign Key that automatically deletes the rows from this table, keeps the User values in sync in case you ever change a username, etc.

As @diat150 mentioned, 6,000 rows isn't even a shadow of a blip on the radar for databases.

For the User column, will that be a username/user id? They look like first names but I'm assuming you made up those examples. I just want to make sure you are using something that will always be unique to the user for this column.

Nice thing with this approach is you could pretty easily build a feature in the HMI that allows the user to reorganize the locations however they prefer and save them to this table. That could remove a lot of your maintenance headache concerns.