Parent with 2 Child Tables in a report

[bonger06]

Hey guys! I'm having problem with the report I'm doing. I have 1 parent table and 2 child tables (child1 and child2). They are all cursor tables. On my report, I want the parent fields to appear on the page header, child1 fields on the detail and child2 on the summary band. I already established the parent and child1 relationship and the details are showing fine but not on the child2. It only shows only the first record it finds on the table. I want all the records to appear on the summary band. I am having 'cyclic relation' error if I established a new relationship between the parent and child2. Please help.

 

[Mike Lewis]

Which version of VFP are you using?

In version 9.0, you need to create two detail bands. In earlier versions, you can't do that. You have to create a child cursor that contains records from both types of children, and use some sort of record selection field to decide which ones to print.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bonger06]

I'm using VFP 7. I did try creating a cursor that contains selected fields from both child tables but I encountered the same thing. Also, there is a blank line after a printed line.

 

[JRB-Bldr]

Assuming that you have multiple records in your Child2 table for a single record in Child1, you may need to do some more pre-processing prior to sending data to your Report Form.

If you have already done so by "creating a cursor that contains selected fields from both child tables", then perhaps your 'extra' pre-processing is already done.

You should end up with a new cursor (e.g. RptCursor) which contains ALL information from Child2 for EACH Child1 record.

Then you need to set up a 'backward' relation to the Parent table so that the Report Form will see ALL Child1 record data + ALL Child2 data within each RptCursor record.

SELECT Parent
SET ORDER TO KeyFld

SELECT RptCursor  && New Child1+Child2 Combined Cursor
SET RELATION TO KeyFld INTO Parent
GO TOP
REPORT FORM MyReport NOCONSOLE TO PRINTER

Then for each RptCursor record 'seen' by the Report Form it will have available ALL Child2 fields, all Child1 fields and, through the Relation, all Parent fields.

Good Luck,
JRB-Bldr

 

[TamarGranor]

If you have two unrelated children of the same parent (such as Customer with Invoices and Payments), the best solution is to create a cursor with all the data in the order you want it printed. The UNION clause of SELECT lets you do that.

Here's an example using the TasTrade data. This creates a "customer profile" with one record for each employee a customer has placed an order with and one for each product the customer has ordered:

SELECT Customer.Customer_Id, Company_Name, ;
       PADR(TRIM(Last_Name) + ", " + First_Name, 40) AS cName, ;
       SPACE(50) AS cEngName, ;
       SUM(Quantity * Unit_Price) AS nTotal, ;
       "E" AS cType ;
    FROM Customer ;
      JOIN Orders ;
        JOIN Order_Line_Items ;
          ON Orders.Order_ID = Order_Line_Items.Order_ID ;
        ON Customer.Customer_ID = Orders.Customer_ID ;
      JOIN Employee ;
        ON Orders.Employee_ID = Employee.Employee_ID ;
    GROUP BY 1, 2, 3, 4, 6 ;
UNION ALL ;        
SELECT Customer.Customer_ID, Customer.Company_Name, ;
       Products.Product_Name AS cName, ;
       Products.English_Name AS cEngName, ;
       SUM(Quantity * Order_Line_Items.Unit_Price) AS nTotal, ;
       "P" AS cType ;
    FROM Customer ;
      JOIN Orders ;
        JOIN Order_Line_Items ;
          JOIN Products ;
            ON Order_Line_Items.Product_ID = Products.Product_ID ;
          ON Orders.Order_ID = Order_Line_Items.Order_ID ;
        ON Customer.Customer_ID = Orders.Customer_ID ;
    GROUP BY 1, 2, 3, 4, 6 ;
  ORDER BY 2, 6, 3 ;
  INTO CURSOR CustomerProfile

Once you have a cursor like this, you can add a group to the report based on the cType field, and use the Print When conditions (and IIF() if you need it) to make the report look like it has multiple detail bands.

You'll find an example of this example, along with a report based on the cursor, on the downloads page of my website:

http://www.tomorrowssolutionsllc.com/session_materials.htm

It's in the Building Queries in Visual FoxPro session.

Tamar

 

[bonger06]

Regarding you're suggestion jrbbldr, I tried combining child1 and child2 into a cursor. Basically, these two are unrelated. They are supposed to be linked with the header.
So it is not one of child1 to many of child2. It should be one
of header to many of either child1 and child2. On my report, I did see the records displayed but only if they are in one detail line. I need to print child2 records on the summary band. Looking at Tamar's suggestion, this seems to be the answer. I'll give it a try and I'll let you know guys if works.