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

ID: Q124386

The information in this article applies to:

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

Once the information is in the cursor TEMP, you can derive the total number of records that met the WHERE clause condition by checking the _TALLY variable. _TALLY holds the number of records returned by the SQL SELECT statement.

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.

The COUNT TO command supports a FOR clause to limit the scope of records counted. If the command includes a scope clause, it will only operate on the table in the active work area. However, if that table is engaged in a one-to-many relationship (one parent record to many child records), the COUNT TO command can't count just the parent records because the child records are part of the active work area scope.

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

ENDIF COUNT TO myvar FOR customer.cno = "A2695" LOCATE FOR customer.cno = "A2695" WAIT WINDOW "COUNT shows " + ALLTRIM(STR(myvar))+" records for A2695"

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.

Additional reference words: FoxWin 2.60a COUNT TO SUM KBCategory: kbprg kbprb KBSubcategory: FxprgGeneral

Last Reviewed: June 27, 1995