Setting Order By in Browse Dialog Search Results

Author Message
 Posted 4/10/2015 7:33:14 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 5/8/2020 7:04:55 AM
Posts: 97, Visits: 499
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:
Post #32928
Add to Twitter Add to Facebook
 Posted 4/10/2015 1:54:41 PM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 2/15/2021 10:48:58 AM
Posts: 2,436, Visits: 24,173
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
(Using VB.Net 2019/SQL Server 2017)


Tags:

Edited: 4/10/2015 2:06:34 PM by Edhy Rijo
Post #32929
Add to Twitter Add to Facebook
 Posted 4/12/2015 8:03:29 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 5/8/2020 7:04:55 AM
Posts: 97, Visits: 499
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
Post #32930
Add to Twitter Add to Facebook
 Posted 4/12/2015 10:50:29 AM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 2/15/2021 10:48:58 AM
Posts: 2,436, Visits: 24,173
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
(Using VB.Net 2019/SQL Server 2017)


 CustomSearchForm.png (9 views, 159.48 KB)
Post #32931
Add to Twitter Add to Facebook
 Posted 4/13/2015 9:09:04 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 5/8/2020 7:04:55 AM
Posts: 97, Visits: 499
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");
}

Post #32932
Add to Twitter Add to Facebook
 Posted 4/13/2015 9:30:49 AM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 2/15/2021 10:48:58 AM
Posts: 2,436, Visits: 24,173
Hi Govinda
Awesome and simple!!!

Thanks for sharing this idea!!!


Edhy Rijo
(Using VB.Net 2019/SQL Server 2017)
Post #32933
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.