Sorting Columns in a Grid When Order isn't Clear

Question

Suppose I query a database table for some data and display it in a table where I can set the sorting of each column. Here are three scenarios where the data either doesn't have an obvious order, or what we display is not in the database: 
 
 Case 1: Suppose we store usernames and not full names, but we only display full names in the table. Is there an easy way of being able to sort the table based on the full name without having to also save the full name in the database? Case 2: Supposed we store two numerical values x and y in the database, and we display x/y. How can I sort by the value of x/y? 
 Case 3: Suppose that we have some text values each associated with an id, but that the id (for whatever reasons) cannot be used to order the text by significance. Is there a way to sort the text in some predetermined but custom order, without having to save information in the database about what this custom order is?

Mike Schmitt · Accepted Answer

Possible? yes. Easy? not really. It requires a workaround, along the lines of querying the whole table into one local variable, iterating over that array to create a second "translated" local varaible, then creating a sorted data subset from that. Obviously this requires it to be a fairly limited data set (no more than a few hundred items at the largest, preferably). My preferred method, however, is that if the column is sorted on username, then display the full name along with the username (rich text grids FTW) in a way that will make sense to the user. 
 When you say "x/y", do you mean literally the string of "x", & the "/" sign, then "y", or do you mean you'd be displaying "x divided by y"? In that case, you might be able to accomplish the heavy lifting necessary using a view. 
 My suggested approach for this is to also add an "Order ID" column, which is not the primary key ID (and not related to it), but just an ID strictly used for ordering of items in the table.

vimalkumars · Answer

Hi dannyh0001 
 If the entire dataset[Total Count] is lesser. You can pull all the data from the database and create a dictionary data with the necessary information. Once the dictionary dataset is manipulated you can use the todatasubset function with the paging info to get the details in the specific order you want. Note: This can have an impact on the performance if the dataset returned from the query is large. 
 In addition to the above approach, Case 2 Can also be achieved by creating a view with the necessary manipulation

Mike Schmitt · Answer

Gotcha - then I second my previous suggestion that you do this by creating a View. Additionally I've found it's almost always a good idea to keep a "Users" table that mimics the user account information (you need to manually sync this, but doing so isn't very hard) -- and having this would allow you to do various different possibilities in your view, such as having columns for first name, last name, full name, etc, and you could potentially sort on any of those per your need.

Sorting Columns in a Grid When Order isn't Clear

Top Replies