Report Form: Setting the order of Child records 2

[keepingbusy]

Hello guys

I have created a report with two tables (e.g. MYMAINTABLE, MYCHILDTABLE)

Both table have a common link of MASTLINK which is a numeric field

The report itself prints fine with no problem but I am stumped to find out how you can order the Child records on the report so they show in a particular order in the detail band.

The Child table has several fields MASTLINK (N), FREETEXT (M), PARANO (N) etc

There are two issues here:

1: IF you print or preview the report as normal, the child records are shown in reverse to the way they were actually entered

2: When we are entering details into the table (MYCHILDTABLE), each FREETEXT field is assign a PARANO so this gives a sequential number. Sometimes if an entry is missed in the FREETEXT field we may have to add and change the PARANO to a number before so for example, FREETEXT PARANO 1, FREETEXT PARANO 2, FREETEXT PARANO 4, FREETEXT PARANO 3.

We have an INDEX ON the PARANO field and if you SET ORDER TO PARANO and BROWSE the table that shows them in the desired order.

What I would like to do is:

REPORT FORM MYREPORT PREVIEW FOR MASTLINK=15 (and have the child records on that report ordered by PARANO)

I am sorry for the comprehensive explanation above but I have learned with this forum, the more you give, the better response you get!

Some guidance would be very much appreciated.

Many thanks


Lee

 

[keepingbusy]

After I posted this, I tried something with a piece of code. Whilst maybe not the easiest way, it actually achieves what I was trying to do. I would welcome any suggestions or alternatives outside of this if possible please.

* Create a temporary file and copy the structure from the CHILD table

tempfile=SYS(3)
USE MYCHILDTABLE SHARED
COPY STRUCTURE TO tempfile+'.dbf'

* Insert the required records into the above temporary table

SELECT * FROM MYCHILDTABLE WHERE MASTLINK=15 ORDER BY PARANO DESCENDING INTO TABLE tempfile+'.dbf'

* Now delete the records from the CHILD table

USE MYCHILDTABLE SHARED
DELETE FOR MASTLINK=15

* Now put them back in the order that is required for the report

APPEND FROM tempfile+'.dbf'

* Clean up

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

This works so is it "If it 'aint broke don't fix it?" or what do you think guys?

Lee

 

[TamarGranor]

The best solution to your problem is to run a query that puts the data you want from both tables into a cursor in the order you want it in the report, and then run the report against that.

Since it's a cursor that you'll let disappear as soon as you've finished the report, the fact that it's denormalized doesn't matter.

Your query will look something like:

SELECT <fill in the list of fields you need> ;
  FROM MYMAINTABLE ;
    JOIN MYCHILDTABLE ;
      ON MyMainTable.MASTLINK = MyChildTable.MASTLINK ;
  WHERE MASTLINK = 15 ;
  ORDER BY Parano ;
  INTO CURSOR csrReportData

Of course, in an application, you probably wouldn't hard code the WHERE clause like that. You'd use a variable to indicate which MastLink value you're looking for: WHERE MastLink = m.DesiredLink

Tamar

 

[keepingbusy]

Tamar

Thank you for the comprehensive and clear reply for which I'm grateful.

You mentioned,

"You'd use a variable to indicate which MastLink value you're looking for: WHERE MastLink = m.DesiredLink"

Yes, we have a variable (m.mastlink) in place of the 15

Kind regards to you


Lee

 

[Olaf Doschke]

The common way to link a parent/master to a child/slave table is to set a relation, a report does so, to be precise, the child/slave table order is set to a foreign key index, so the primary key of the parent/master table can be looked up.

So if you set MYCHILDTABLE to the MASTLINK order, do you get the Parano in the desired order? If not, this is in short what you can do:

Define an index sorting both MASTLINK and PARANO with a single expression, then use that index to both sort the child table and relate the master with just the first part of the index expression containing the first field. What expression to use, depends on the value range and precision of your N fields. The BINTOC() function is a candidate for concatenating two numeric fields to one index expression you can use that way. STR() is another candidate of course.


The lengthy explanation with more guidance on what exactly to use in this case:

You define a new index in the child table, that includes both MASTLINK and PARANO fields. Both fields being type N this is one of the not so easy situations, as already said, If they contain integer values, you should change to int. You can then combine the fields via an expression BINTOC(MASTLINK)+BINTOC(PARANO). The BintoC() function also offers conversion of N fields with their various size and precision, but you need to dig into the parameterisation then. You can of course also use STR(MASTLINK,len,decimals)+STR(PARANO,len,decimals)

Then you could set order to that combined index and still use it to link from the single MASTERLINK field to slave table via using the relational expression STR(MASTLINK,len,decimals) only. This is making use of partial string matching. Even though the index has STR(PARANO,...) in it, too, comparing or SEEKING or looking up just the left part is also possible, and that's what a master/child relation is about, it looks up the MASTLINK value in the child table index used to order by.

Bye, Olaf.

Such an index expression is the equivalent in Foxpro for a composite index on multiple fields. Also equivalent to ORDER BY MASTLINK, PARANO. As you see you can't simply define such an index, you have to index an expression concatenating the two fields in a way the total expression will sort as wanted.

 

[keepingbusy]

Hi Olaf

Wow!!

Really comprehensive response also, one of which I will certainly look into.

Many thanks.

Lee