StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Setting Order By in Browse Dialog Search Results

Setting Order By in Browse Dialog Search Results

Govinda Berrio — Mon, 13 Apr 2015 15:30:49 GMT

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

RE: Setting Order By in Browse Dialog Search Results

Edhy Rijo — Mon, 13 Apr 2015 15:30:49 GMT

Hi Govinda
Awesome and simple!!!

Thanks for sharing this idea!!!

RE: Setting Order By in Browse Dialog Search Results

Govinda Berrio — Mon, 13 Apr 2015 15:09:04 GMT

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.

[codesnippet]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");

}[/codesnippet]

RE: Setting Order By in Browse Dialog Search Results

Edhy Rijo — Sun, 12 Apr 2015 16:50:29 GMT

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.

RE: Setting Order By in Browse Dialog Search Results

Govinda Berrio — Sun, 12 Apr 2015 14:03:29 GMT

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

RE: Setting Order By in Browse Dialog Search Results

Edhy Rijo — Fri, 10 Apr 2015 19:54:41 GMT

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!!!