PRB: COUNT TO Can't Count Parent Records in One-to-Many Tables

• Microsoft FoxPro for Windows, version 2.6a

SYMPTOMS

The COUNT TO command does not return a true count of records when tables are set up in a one-to-many relationship (one parent record to many child records) because it can't exclude the child records.

WORKAROUND

There are several possible workarounds for this situation. The first, and perhaps the best, alternative is to use an SQL SELECT command with a WHERE clause on the table that has the records you want to count. Here's an example:

   SELECT * FROM INVOICES WHERE CNO = CUSTOMER.CNO INTO CURSOR TEMP

As an alternative, you can determine how many parent records meet the FOR clause by using the SET SKIP TO command to break the one-to-many relationship before executing the COUNT TO command. After you get the count, re-establish the relationship.

STATUS

This behavior is by design.

MORE INFORMATION

According to Help in FoxPro for Windows, the COUNT TO command:

   ... counts the records within a scope of records for which
   the FOR or WHILE conditions are true. If SET TALK is ON, the
   record count is displayed in this form

   nnnnnn records

   Records marked for deletion are included in the count if SET
   DELETED is OFF.

Steps to Reproduce Behavior

Create and run a program containing this code:

CLOSE ALL SET SAFETY OFF USE SYS(2004)+"\Tutorial\Customer" IN 1 USE SYS(2004)+"\Tutorial\Invoices" IN 2 SELECT 2 INDEX ON cno TAG cno ADDITIVE SET ORDER TO TAG cno OF SYS(2004)+"\tutorial\invoices.cdx" SELECT 1 SET RELATION TO cno INTO invoices ADDITIVE SET SKIP TO invoices DEFINE WINDOW browwin FROM 0,0 TO 20,70 MOVE WINDOW browwin CENTER BROWSE WINDOW browwin NOWAIT LOCATE FOR customer.cno = "A2695" IF (FOUND())

   WAIT WINDOW "Found record ok."

The COUNT TO command reports that there are five records in the parent table that meet the FOR clause, not one, which is really the case. The COUNT TO command includes the child records that are displayed in the one- to-many relationship in the parent table. While the number returned is, in fact, valid for the number of records in the child table, it is not valid for the number of records in the parent table.

NOTE: If you use the COUNT TO command to find out how many child records each parent has, the number returned by the COUNT TO command will be valid only if there are two or more child records. In a one-to-many relationship, FoxPro doesn't show a single child record in the parent table. As a result, you can't use COUNT TO to determine if there is one and only one child record for any given parent.