PRB: Report Duplicates Parent Records in One-to-Many

• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6a, 2.6a
• Microsoft FoxPro for Windows, versions 2.5x, 2.6, 2.6a
• Microsoft FoxPro for Macintosh, versions 2.5x and 2.6a

SYMPTOMS

If a parent-child one-to-many relationship is set between two databases and a report is created that prints only parent records, the parent records will be duplicated in the detail line as many times as there are related child records. For example, if a parent record has three associated child records, the parent record will be repeated three times on the report.

RESOLUTION

Set SKIP to OFF to terminate the one-to-many relationship. If this is not feasible, choose Data Grouping from the Page Layout menu and set up a grouping on the related field. Then move all parent record fields to the group header or footer band.

STATUS

This behavior is by design.

MORE INFORMATION

The following is an example using the CUSTOMER and INVOICES databases in the TUTORIAL subdirectory.

1. Set up the one-to-many relationship.

2. Create a report with a data grouping on the CNO field in CUSTOMER. Place the desired fields in the group header and/or group footer band.

3. In the Detail band, place information from INVOICES. In the Summary band, add JUNK().

4. Save the report.

5. In the Command window, issue the following command to create JUNK.PRG:

         MODIFY COMMAND junk
   

6. Type the following commands in JUNK.PRG:

         SET SKIP TO
         SUM ytdpurch TO test
         RETURN test
   

7. In the Command window, issue the following command:

         SET PROCEDURE TO junk
   

8. Issue a SET FILTER command if needed.

9. In the Command window, issue the following command:

         REPORT FORMAT <filename> PREVIEW