Creating a Report for a One-to-Many Relationship

• 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

   SELECT parent
   SET SKIP TO child,grandchild

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 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.