Setting Order By in Browse Dialog Search Results

StrataFrame Forum

Back

Setting Order By in Browse Dialog Search Results

Author	Message
Govinda Berrio	Govinda Berrio posted 9 Years Ago Topic Details Share Topic
Group: StrataFrame Users Posts: 94, Visits: 481	Hello all, I have a browse dialog that is querying on a table with 10s of thousands of records. When the user searches, with criteria selected, there are a lot of results. But, I have the Browse Dialog set to only get TOP 1000, to improve responsiveness. My intention is to have the search results return the latest records first, sorted by the added_date field. So query I would need the Browse Dialog to perform is something like this: SELECT TOP 1000 * ... ORDER BY added_date DESC When I set the sort order in the SearchFields editor, it sorts the records that it gets back from a regular query with no Order By clause, so the user is not getting the latest records. They're getting the TOP 1000 records in the order that they are in the table. When I set the BrowseDialog.OrderBy property, I can put the field there, but I can't specify "DESC"; I get the SQL error, "Invalid column name 'added_date DESC'." Could someone please help me display the latest records in the Browse Dialog. Thank You, Govinda Tags BrowseDialog
	Reply Like 1
Edhy Rijo	E Edhy Rijo posted 9 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Govinda, Sorry but I have not used the BD in a long time. I normally create my own search fields and use a MS-SQL stored procedure to grab the data in the way I want. The good thing is that the BD exposes some events that will allow you to manipulate the QueryInfo object and I am pretty sure you can trap any of these events and insert the required ORDER BY clause. Take a look at the SF help file in the following spot: Application Framework->UI Layer->Controls->Browse Dialog->Events Also take a look at this link http://forum.strataframe.net/Search.aspx?Keywords=BeforeSearchExecuted from the forums which has related information about the BD. Please let us know how you fix this issue. P.S. I took a quick look in the SF help field at the QueryInformation Members and there is a property named OrderByFields, use the debugger to break in the BeforeSearchExecuted event and look at the OrderByFields property to examine its value and probably all you need to do is replace it with your own values. Good luck!!! Edhy Rijo Tags BrowserDialog QueryInformation.OrderByFields Edited 9 Years Ago by Edhy Rijo
	Reply Like 1
Govinda Berrio	Govinda Berrio posted 9 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 94, Visits: 481	Hi Edhy, Thanks for the reply. I'm looking in the framework code that builds the sql from the QueryInfo object, and it does not provide facilities for specifying a sort direction. OrderByFields is simply a StringCollection and the framework code assumes each item in the list is a field name. While I was in the code, I noticed how the ReplaceTokens logic works and I think I'm going to use the OrderByFields and Tokens collections in conjunction to do what I need. I'll let you know if I can get it working. Govinda
	Reply Like 1
Edhy Rijo	E Edhy Rijo posted 9 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Govinda, You are welcome. I spend sometime with the BD and was able to duplicate your easy right away and yes you are right about the OrderByFields property. I know you can use the BD with a Stored Procedure and a view, but probably if your don't have too many search fields, you can simply build your own search feature. Here is what I do today.... I use DevExpress controls, specially their grid, it is very powerful, flexible and super fast. In most cases I will always show the grid with many records so end user select the one they need. I create a View to be used with the grid and then a Stored Procedure (SP) to grab the data for the view. Once your SP is working as expected then create your search fields controls and then call your SP to load the grid. I know this is not a generic method as with the BD, but I have full control of the search, specially optimizing it and showing the data the way I want with the DevExpress grid. See the attached image to see how it looks and if you need more details, just let me know. Edhy Rijo Attachments CustomSearchForm.png (174 views, 159.00 KB)
	Reply Like 1
Govinda Berrio	Govinda Berrio posted 9 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 94, Visits: 481	That is a good strategy, Edhy, but I can't spend the time converting it over. I did go ahead with the idea that I had and it worked. See the code below. private void JobTicketBD_BeforeSearchExecuted(object sender, BrowseDialogBeforeSearchExecutedEventArgs e) { e.QueryInfo.OrderByFields.Add("add_date_descending"); e.QueryInfo.Tokens.Add("add_date_descending", "add_date DESC"); }
	Reply Like 1
Edhy Rijo	E Edhy Rijo posted 9 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	Hi Govinda Awesome and simple!!! Thanks for sharing this idea!!! Edhy Rijo
	Reply Like 1