I personally also do what Mike and Tamar suggest.
I don't know if you inherited the report or did it from scratch. If you don't get how top make use of a compound index the way I suggest, it may be easier to redesign the report. Without knowing how many detal and additional bands your report has, it's hard to judge, what's easier. You talked about two detail tables, while my example only covers one.
Maybe I illustrate the difference of a solution to the usual relation, so you see what change is making the index used for the relation als sort the detail data:
Code:
CREATE CURSOR crsMaster (id I)
INSERT INTO crsMaster VALUES (1)
INSERT INTO crsMaster VALUES (2)
CREATE CURSOR crsDetail (id I, masterid I, secondlevelorder I)
INSERT INTO crsDetail VALUES (1, 1, 1)
INSERT INTO crsDetail VALUES (2, 1, 2)
INSERT INTO crsDetail VALUES (3, 2, 2) &&intentionally insert in wrong order to prove index sorting
INSERT INTO crsDetail VALUES (4, 2, 1)
* the normal way to index for a relation
SELECT crsDetail
INDEX on [highlight #FCE94F]masterid[/highlight] TAG xlink
SET ORDER TO TAG xlink && will be the order after index creation anyway, but will be the SET ORDER you need in code using an already once indexed table
SELECT crsMaster
SET RELATION TO [highlight #FCE94F]id[/highlight] INTO crsDetail
SET SKIP TO crsDetail
* report data sort order is like this
? "wrong sorting of related data"
SELECT crsMaster
SCAN
? crsMaster.id, crsDetail.Secondlevelorder
ENDSCAN
* unrelate data
SET RELATION TO
* now special indexing for both relation and sorting:
SELECT crsDetail
DELETE TAG xlink
INDEX ON [highlight #FCE94F]BINTOC(masterid)+BINTOC(secondlevelorder)[/highlight] TAG xlink
SET ORDER TO TAG xlink && will be the order after index creation anyway, but will be the SET ORDER you need in code using an already once indexed table
SELECT crsMaster
SET RELATION TO [highlight #FCE94F]BINTOC(id)[/highlight] INTO crsDetail
SET SKIP TO crsDetail
* report data sort order is like this
? "correct sorting of related data"
SELECT crsMaster
SCAN
? crsMaster.id, crsDetail.Secondlevelorder
ENDSCAN
The result row order will be this way:
So you see the difference is on the index expression of the xlink tag. You can't use one index tag to relate data and another to sort, so the index used for the relation also has to sort data. It will depend on your data types what index expression you need.
I can't index on id+secondlevelorder, because int1+int2 are not always in the same order as concatenated numbers. eg 2,1 should be after 1,3, but 2+1=3 is lower than 1+3=4. In the end the goal is to concatenate the values. In this case STR(masterid)+STR(secondlevelorder) would also work. If your columns would be char columns, you can simply use col1+col2, but since you sort by a date you'd need BINTOC(foreignkey)+DTOS(datecol) most probably.
So this is the part needing some thinking about what and how to index. I totally agree with Mike and Tamar a query gives you a better and comfortable way to sort data with the ORDER BY clause, but it may not be easy to change a report using multiple workareas/tables/cursors into a report only using one. If you just did your report from scratch, it might be easier to use that advice and start from scratch.
And all you need from this code is learn how to index. You will not use this code, but add a new index for this type of relation to your detail table. Then you make the relation from master to detail with this new index. That's the only change in your software and report usage: A new index you use for the relation. It would be easier if VFP would offer INDEX ON (masterid, secondlevelorder) TAG xlink, so you have to think about an expression concatenating the columns you want to sort by, the field you link by first. What helps to concatenate is transform any non char value to string, BINTOC() does so for ints, DTOS() does so for dates, TTOC(datetime,1) does so for datetimes. Watch out about varchars, they also need to be PADR() to a static length for the index. And the overall expresion length must stay below 240 bytes.
Bye, Olaf.
