Index on 2 expressions, one ascending, one descending

[CEMrob]

VFP9, Win7.
I have a table containing details of visits to various sites around the country. The field REF (C,9) represents the site, and VISITDATE (D) is, unsurprisingly, the date of a visit. I want to browse the visits in a grid ordered by REF alphabetically, starting with the 'A's, but in date order within REF, with the most recent visit date at the top, thus:
A123 20/06/11
A123 15/01/11
B321 22/06/11
C456 10/06/11
C456 19/02/11
C456 12/12/10

I can index on REF + DTOS(VISITDATE) and by default that gives me Alpha order on REF but oldest visit date first, so thats no good. I could SQL SELECT * ... ORDER ON VISITDATE DESC into another table, then index that on REF, but I'd rather just stick to the original table, since the users need to be able to edit the data having selected a record via the grid.

I keep thinking that there is something really obvious and simple that I'm not seeing, and if so, a big sorry to everyone, but my brain has turned to mush on this. Suggestions please.

 

[Mike Lewis]

Try this:

INDEX ON Ref + DTOS(DATE(2999,12,31) - VisitDate) TAG RefDate

Not tested, but I think it should work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[CEMrob]

Thanks Mike for your (ever) speedy reply - don't you have a home to go to?.
I get an invalid function count type error.
On reflection, DATE(2999,12,31)-visitdate gives a number of days, which DTOS can't handle. Nevertheless, you've woken my brain up and sparked new ideas, which after all is really what the forum is about.

Rob

 

[CEMrob]

Oops, meant to say, STR instead of DTOS seems to work. Thanks once again, the "subtract from some future date" approach is the key.
Rob

 

[Mike Lewis]

Rob,

You're right, of course, about the subtraction giving the number of days. As you've realised, it's the "subtract from a high value" approach which is the key.

As for your other question, I do have a home, but unfortunately I have a computer there as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Another idea is to use chrtran() on data you want to reverse orderwise, in this case after dtos you only need to inverse the digits:

INDEX ON Ref+chrtran(dtos(visitdate),'0123456789','9876543210')

As a sidenote: A similar chrtran() expression including alphabetical chars also helps sorting like with a certain collation sequence, without actually using a collation sequence. Indexing with collation sequences has disadvantages, one of them being their double size. In this case, Mike's idea to subtract from a future reference date is as good and not having that collation disadvantage at all. That's why I just made this as a sidenote towards the chrtran() approach´and more general is just meant as another approach, not as the better solution.

It helped me to create such an index with partial ascending and descending orders with both fields being char data, that's what makes it a solution worth keeping in mind.

Bye, Olaf.