search and sort

Classic Servoy
1

I do a search via scripting (on datefields) and the outcome is correct.

But, when I sort the records afterwards, in the same script, all records show up again…

2

This works in Servoy 2.0 rc6: (on example_data/orders)

controller.find()
orderdate = '>01-05-1998|dd-MM-yyyy'
controller.search()
controller.sort('orders_to_order_details.quantity asc');

Can you specify what is not working?

3

Jan, below is an extraction of the complete code but essentially this is it. Without sorting all is fine. After sorting I have an issue… So maybe it was not only the date but also the newRecord() :?

controller.find();

startdatetime = utils.dateFormat(d1, "dd-MM-yy");
classification = '!private';
username = application.getUserName();

controller.newRecord();

startdatetime = utils.dateFormat(d1, "dd-MM-yy") + "..." + utils.dateFormat(d2, "dd-MM-yy") + '|dd-MM-yy';

controller.search();

controller.sort("completed asc, startdatetime desc");
4

how many rows are returned by your search?

var count = controller.search();
5

to comment a bit more on youre code (will not fix youre problem but..)

don’t do this:

controller.search(); 
controller.sort("completed asc, startdatetime desc");

but

controller.sort("completed asc, startdatetime desc");
controller.search();

youre code will trigger a sql query twice..
(first it will seach and then search again with the sort query)

6

Well Johan and Jan,

First I changed my code towards the suggestion of Johan. To be honest Johan, it did solve the problem but created a new one.

By sorting first and searching afterwards the foundset is what it should be. So this is solved so it seems…

But now the sorting bit. As you can see I have a dataprovider ‘completed’ . Completed is a value 1 or 0.
I have two issues with sorting on this dataprovider:

  • It does not sort in scripting (I isolated this sort from the other part)
  • It does not sort by clicking in a table header (completed is set in this table with a checkbox)…
7

Is completed filled in? (ie. not null)

8

Problem solved… Thanks guys…

Wrap up:

  • sort() before search()
  • sorting on null values won’t work?
9

IT2BE:

  • sorting on null values won’t work?

The database determens where Null values are placed (before ‘a’ or after ‘z’) if the entire column in a table is null nothing will happen.

10

OK, the issue is and was solved but…

If I understand correct, sorting with only one row value set to 1 (and the rest null) should sort the column both ascending and descending. Or am I wrong…