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?
Discussion posts and replies are publicly visible
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
I did mean x divided by y, thank you.The issue I have is that i need to be able to handle all of these cases in the same grid, and preferably still using the grid pagination, in which case I think that I cannot easily just translate the data in case 1, since then I wouldn't be able to move to the next page and keep the sorting. Thank you for your answer though!
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.
Thank you for the suggestion!