Setting an order for more than one table linked on a report 3

[Steve-vfp9user]

Hello

I have a report with two linked tables with the Linkmaster being a field called MASTLINK

On my report I have two detail bands with the relevant fields from two tables however, the date fields (called DATEFROM) are not in order when you preview or print the report.

When I issue the command:

REPORT FORM MYREPORT NOCONSOLE PREVIEW FOR MASTLINK=mmastlink

all the records show correctly but are not in date order.

I'm not sure how to set the order of two tables at the same time so can someone suggest what I need to do or please point me in the right direction.

Version: VFP9 SP2 with Windows 10

Thank you

Steve

 

[Olaf Doschke]

That's the downside of this kind of linking data "live".

The detail tables are the one the relation ends in, right? So those are indexed on the mastlink field.

You can only set this index to make the relation work. What you can do is let the index be an expression on the mastlink field AND whatever else you want to sort by in second level order. On top of that you'd need to set EXACT OFF to let a SEEK (which indirectly is done by a relation) find a correct related record also by the left part of the index.

Now the problem is, you can't just index on mastlink,date, you have to have an expression like mastlink+date, but you can't always concateneate any two type fields with + operator.

To illustrate this with an example:

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)
INDEX on BINTOC(masterid)+BINTOC(secondlevelorder) TAG xlink
SELECT crsMaster
SET RELATION TO BINTOC(id) INTO crsDetail1
SET SKIP TO crsDetail1

If you browse both crsMaster and crsDetail and you skip through crsMaster you get to the corresponding crsDetail row at the same time via the SET SKIP. The relation looks up BINTOC(crsMaster.ID) in the xlink index of crsDetail, where this corresponds to the left part of the expression [highlight #FCE94F]BINTOC(masterid)[/highlight]+BINTOC(secondlevelorder), sort order of crsDetail still is on the full expression. That now has to be adapted to your situation. You need to convert your date or datetime to a string, so take a look at DTOS(date()) and TTOC(datetime(),1).

Bye, Olaf.

 

[Steve-vfp9user]

Hi Olaf

Thank you for the post.

I am still fairly new to this but my understanding of what you are saying is that the records are inserted into a cursor in the order they should be then the report should print them out correctly? Am I on the right track?

Thank you

Steve

 

[Mike Lewis]

My approach would be not to use linked tables, but to generate a single cursor to drive the report. You would use[tt] SELECT ... FROM ... JOIN ... ON ...[/tt] to generate the cursor, and adding an [tt]ORDER BY[/tt] clause to get it into the right sequence.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

No, I even intentionally put some rows in wrong order to show the compound index expression is doing the sorting on top of allowing to relate master and detail data.

INSERT INTO crsDetail VALUES (3, [highlight #FCE94F]2, 2[/highlight]) &&intentionally insert in wrong order to prove index sorting
INSERT INTO crsDetail VALUES (4, [highlight #FCE94F]2, 1[/highlight])

Correct order of these is inversely, (2,1), then (2,2)

The secret is not just indexing on the masterid, but also secondary fields to sort by. The secret also is not using cursors, I just used them to not cause dbf files on your drive and have disposable test data.
The only secret is having the index on more than just the link field. That works, because VFP can use a partial index for lookups, comparing the seeked value in SET EXACT OFF style.

Mike has a point, but it's not easy to change a report using mutiple tables or cursors in mutiple bands to one table or cursor using a query result.

Bye, Olaf.

 

[Steve-vfp9user]

Thank you Mike and Olaf.

I'll evaluate the posts and see how it goes

Thank you

Steve

 

[TamarGranor]

I just want to add my voice to say that it's almost always better to do a query (SQL SELECT) to organize the data for a report, as opposed to setting relations against tables.

Tamar

 

[Olaf Doschke]

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:

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.

 

[Steve-vfp9user]

Many thanks for the comprehensive posts on this thread.

I managed to sort out the report by creating a DBF with the required fields, after which I was able to append the required records in sequence which then gave me the desired result. The coding was quite long however, I was able to achieve my goal with some inspiration and guidance by those on this thread (thank you)

I haven't included everything but here is the general idea:

tempfile=SYS(3)
USE MYTABLE SHARED  && This is the generic table that accepts all the desired records
COPY STRUCTURE TO tempfile+'.dbf'
USE

USE tempfile+'.dbf' EXCLUSIVE
APPEND BLANK

* Add the header for the report columns

REPLACE HEADDATE	WITH "DATE"
REPLACE HEADACT1	WITH "ACTIVITY"
REPLACE HEADHOUR2	WITH "HOUR"
REPLACE HEADMILE	WITH "MILEAGE"
REPLACE HEADCOST1	WITH "TOTAL COSTS"

* More here....

APPEND FROM MYFIRSTTABLE FOR MASTLINK=mmastlink
APPEND FROM MYSECONDTABLE FOR MASTLINK=mmastlink

* More here....

nmessage=MESSAGEBOX("Preview report first?"+SPACE(10),4+32+0,"System Message")
IF nmessage=6
  REPORT FORM MYREPORT NOCONSOLE PREVIEW FOR MASTLINK=mmastlink
ENDIF

nmessage=MESSAGEBOX("Continue with hard copy report?"+SPACE(10),4+32+256,"System Message")
IF nmessage=6
  lcprinter=GETPRINTER()
  IF NOT EMPTY(lcprinter)
    DO WHILE .T.
      REPORT FORM MYREPORT NOCONSOLE TO PRINTER
      nmessage=MESSAGEBOX("Duplicate hard copy report required?"+ ;
        SPACE(10),4+32+256,"System Message")
      IF nmessage=7
        EXIT
      ENDIF
    ENDDO
  ENDIF
ENDIF

CLOSE DATABASES

* Clear up

IF FILE(tempfile+'.dbf')
  DELETE FILE(tempfile+'.dbf')
ENDIF

I appreciate your time once again

Thank you

Steve

 

[Olaf Doschke]

I can hardly give you feedback on the code, you don't show how you set indexes, all relations base on how you define and use indexes.

One pitfall many people fall into is to always create indexes. Index creation is a single time step, your final code will only SET ORDER.

And besides that, the general idea to preprocess all data into a single record can surely work. I expect you end up with records having half the columns empty. To generate a single repotrt cursor an SQL query would be the simpler approach, than to preprocess the relations as the report engine should traverse them by skipping forward in the main report driving cursor or table.

Bye, Olaf.

 

[Steve-vfp9user]

Olaf

I forgot to mention that the records are already in date order prior to being appended in to the tempfile+'.dbf'

There are no blank fields present as all records contain some data.

Thank you

Steve

 

[Olaf Doschke]

If both appends from two different detail tables fill the same columns, what you do is a Union in SQL query jargon, you could do the query instead and follow Tamars advice, this is then a problem of having related data in two different tables for actually the same purpose. Reports are not meant for such relations for sure.

Bye, Olaf.