ORDER BY column1 DESC, column2 with setfilter

[JD Kromkowski]

In personal (or programatically) I’d like to do a two field order using setfilter. Is this possible? There are aspects of setfilter in Personal that are buggy (previously noted and I know it was first cut), but one of the things I’ve noticed is that if you setfilter, then you can’t change indexes. And it seems pretty clear that if you change index to column2 first and then run filter you lose the index change. I have been unable to even ascertain what index the result even is! Can you advise? (If you try to use quick report you’ll crash it when you try to run the report.) I think I’ve been able to do this with in program by using set filter to reflect a filter on column1 and then changing index to get column two sorted right. But it seems like maybe for future setfilter might be a bit more robust. I do understand it was first cut. JDK

[Michael]

On 29/04/2013 19:37, kromkowski wrote:
> In personal (or programatically) I'd like to do a two field order using
> setfilter.
>
> Is this possible? There are aspects of setfilter in Personal that are
> buggy (previously noted and I know it was first cut), but one of the
> things I've noticed is that if you setfilter, then you can't change
> indexes. And it seems pretty clear that if you change index to column2
> first and then run filter you lose the index change. I have been unable
> to even ascertain what index the result even is! Can you advise?
>
> (If you try to use quick report you'll crash it when you try to run the
> report.)
>
> I think I've been able to do this with in program by using set filter to
> reflect a filter on column1 and then changing index to get column two
> sorted right. But it seems like maybe for future setfilter might be a
> bit more robust. I do understand it was first cut.

The current version of setfilter() does not support indexes or orders. The reason behind it is that to support an order, I
couldn't use the query engine the way I currently do. I would have to read every matching record first, then sort them. What I
have done is to produce a filter that only reads new records as it requires them, and it caches the ones already read so it can
revisit them (queries only go forwards as a rule). I realize this is different to the way the Superbase one worked, but it was
also horribly inefficient, because people would use an index that is completely unrelated to the filter and then it would look at
every single record until it found a match. The current one uses the query optimizer, but at the price that it does not currently
support sort orders.

We are open to suggestions on a different approach, or even for adding an optional order capability to this, though it means
reading and holding every record into a cache. The alternative might be to store the internal record ID in a list in the correct
sort order, and use it to reselect. The biggest difference between this and the way Superbase does it, is that new records
matching the filter won't be found in the list once it is created. The upside is that the snapshot of records is stable, even if
you make changes to it that would normally drop the record from the filter.

Ciao, Neil

[Author]

Posts