Creating a Report for a One-to-Many Relationship

ID: Q113178

The information in this article applies to:

Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b
Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b
Microsoft FoxPro for Macintosh, version 2.5b

SUMMARY

Using FoxPro's Report Writer, you can produce a report that displays records from databases engaged in a one-to-many relationship. In a one-to- many relationship, the parent record prints once and all corresponding (related) child records print out.

MORE INFORMATION

The following code opens the INVOICES and DETAIL database files, sets the order in the child database based on the INO field, and establishes a one- to-many relationship. In this example, INVOICES.DBF is the parent database and DETAIL.DBF is the child database.

   SELECT 1
   USE c:\<foxpro directory>\tutorial\invoices.dbf
   SELECT 2
   USE c:\<foxpro directory>\tutorial\detail.dbf
   SET ORDER TO TAG ino OF c:\<foxpro directory>\tutorial\detail.cdx
   SELECT 1
   SET RELATION TO ino INTO detail ADDITIVE
   SET SKIP TO detail

NOTE: If you have a parent-child-grandchild relationship, you will need to SELECT the parent and SET SKIP TO both the child and the grandchild. For example:

   SELECT parent
   SET SKIP TO child,grandchild

Using the data grouping band is an easy and intuitive method of producing a one-to-many report. The data grouping must be based on the field that relates the two databases. In the example above, the invoice number (INO) field links the INVOICE and DETAIL databases.

After you have established the previously described one-to-many relationship, do the following to create a one-to-many report:

1. In the Command window, type:

      CREATE REPORT one_many

2. Create a data grouping band based on the INVOICES.INO field.

3. Using the sizing bars, increase the size of the group band, if

   necessary.

4. Place the INO and CNO fields from the INVOICES database in the data

   grouping band.

5. Place each field from the DETAIL database in the Detail band of

   the report.

6. From the Report menu, choose Page Preview.

The one-to-many report will be displayed on the page preview screen.

The data grouping band executes each time the data grouping changes. For example, when the INO field changes its value, the fields within the data group header print. This functionality causes the parent record data to print one time.

The Detail band of the report contains the fields from the child (DETAIL.DBF) database. This band executes each time a child record is available.

Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b RWriter KBCategory: kbprint kbcode KBSubcategory: FxtoolRwriter

Last Reviewed: June 27, 1995