Reporting One-to-Many Relationships 2

[tiptonb1]

I have to create a report on data from three tables in a one-to-many relationship. I set the parent table information in the page header and created a data grouping on the related field from the parent table. I place the fields from both child tables in the detail section of the report. I don't seem to be getting the related information from the child tables with this setup even though when I'm done reporting, the relationship continues to exist and I can see the relationships in the corresponding browse windows (all this to say it's not a relationship setup problem). I know there is a disconnect but I'm not getting it. Also, are there some books out there that cover VFP in a more detailed way rather than the usual "This is how it works; this is what a database does; etc." An example is searching through the index section on reports and not finding an entry for "using parent/child relationships" in any of the books. Your help is greatly appreciated.

Barry

 

[jimstarr]

Hi Barry,

To get started, look at thread184-74691.

Jim

 

[ramani]

The contributions by Jim and me on the above thread could help you. As mentioned there, remeber to select the table/cursor in the detailed band and then set the relation to the header DBF... while doing the report. However, I would like to add the following for a better understanding of what happens.

1. Dont get confused with 'parent' or 'child' names used. When a DBF1 is selected and its records are related to the DBF2 using its index, DBF1 is the 'parent' and the DBF2 is the 'child' for that occasion. Example... when 'customer' DBF is selected, and all detailed transactions are stored in 'trans' DBF... you will select 'customer' .. set relation to the 'trans' .. make trans as the record source for a grid and ... goes on.. here the parent is the 'customer' and the child is the 'trans' ........... When the report is cast for the same, it may be more convenient to Select the 'trans' and set the relation to the 'customer' on customer id and have the trans as the detailed band and the customer at the header level. Here the parent is 'trans' and the child is 'customer'.......
Hope the parent and child things get clearer based on the context.

2. When a parent and child relationship is set, the record pointers of the child database keep moving according to the relationship in the parent dbf. Now take the situation where the parent is in the detailed band and the child is in the header.... things work fine when you have only one child and and one parent... How does the record pointers will move as the detailed band varies... and there are two parents in the detailed band.... So the problem will be understood, when you realise the situation. The solution is to have one combined dbf for the detailed band... so that you can control the data extraction.

If these points are understood, report creation thru report wizard will never be the problem.

I do not know, if i am posting very long reply and more than required.. I am sorry if your level is not understood by me, but I have assumed lot of beginners could come to the forum and so explained the fundamentals in detail.

Hope this is of help to you
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[tiptonb1]

I understand the concepts in both of your replies but I don't think this will work in my case. What I have is a database that tracks problems. With each problem is an associated occurrences (who reported the problem), references to the problem solution and actions taken to solve the problem. The format of the report is already established and looks something like the following:

Problem Header
Problem Information (Parent Table)

Occurrence Header
Occurrence Data
Occurrence Data
Occurrence Data

Reference Header
Reference Data
Reference Data
Reference Data

Action Header
Action Data
Action Data
Action Data

Problem Footer

As you can see each section needs its own header information and if its tied together in one table it will be impossible to distinguish between the separate sections of data.
I appreciate the help and hope this will clarify the problem. It would help if you could have a detail for each group header. I may need to write a program to send this out to the printer rather than use the ReportWriter. Thanks for taking the time.

Barry Tipton

 

[jimstarr]

Hi Barry,

Without going into too much detail, your idea that "if it's tied together in one table it will be impossible to distinguish between the separate sections of data" isn't really true. You just need to create a "record type" field that contains a code signifying what kind of data is contained in the current record. This, plus a "problem ID" will be all you'll need to do the data grouping.

Jim

 

[tiptonb1]

Thanks for the input Jim but I may have mis-stated the idea that data without the proper headers would be the confusing part. I still have the problem of needing a header before each section of data and each header & section of data printed before the next header & section of data is reported as I displayed in the example. If I place the data in one table or cursor, where would I create the headers for each set of child tables? If this is confusing I apologize but basically what I need is a report in the format of the example and I don't know how to provide the title and column headers for each section. It appears that the more I try to explain the more I'm confusing the issue. I Thank You for your help.

Barry Tipton

 

[chpicker]

Group the data by the "record type" field that Jim suggested and put the headers you need in the Group Header band. The data would need to be in order, of course, since it would create a new group each time the "record type" field changes...simple to do with SQL SELECT commands.

 

[jimstarr]

The headers are generated by the data, e.g. "Occurrences for Problem #719".

Jim

 

[tiptonb1]

Thanks to all! I re-read Ramani and Jim's earlier posts along with the current posts and it all makes sense now. Thanks again to all for your patience, tremendous help and input.