Summarize records by key value to a database (.dbf) file


      TOTAL ON <expKey> [FIELDS <idField list>]
            TO <xcDatabase>
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]


ON <expKey> defines the group of records that produce a new record in the target database file. To make the summarizing operation accurate, the source database file should be INDEXed or SORTed on this expression.

FIELDS <idField list> specifies the list of numeric fields to TOTAL. If the FIELDS clause is not specified, no numeric fields are totaled. Instead each numeric field in the target file contains the value for the first record matching the key expression.

TO <xcDatabase> is the name of the target file that will contain the copy of the summarized records. Specify this argument as a literal file name or as a character expression enclosed in parentheses. Unless otherwise specified, TOTAL assumes a .dbf extension.

<scope> is the portion of the current database file to TOTAL. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to TOTAL within the given scope.


TOTAL is a database command that sequentially processes the current database file, summarizing records by the specified key value and copying them to a new database file. TOTAL works by first copying the structure of the current database file to <xcDatabase>, except for memo fields. It then sequentially scans the current database file within the specified scope of records. As each record with a unique <expKey> value is encountered, that record is copied to the new database file. The values of numeric fields specified in <idField list> from successive records with the same <expKey> value are added to fields with the same names in <xcDatabase>. Summarization proceeds until a record with a new key value is encountered. The process is then repeated for this record.

Since TOTAL processes the source database file sequentially, it must be INDEXed or SORTed in <expKey> order for the summarization to be correct.

To successfully TOTAL numeric fields, the source numeric fields must be large enough to hold the largest total possible for that numeric field. If not, a runtime error is generated.


. Deleted source records: If DELETED is OFF, deleted records in the source file are TOTALed. Records in the target <xcDatabase> inherit the deleted status of the first matching record in the source file, just as nontotaled fields inherit their values. If DELETED is ON, however, none of the deleted source records are TOTALed.


      .  In this example, a database file is TOTALed ON the key
         expression of the controlling index using a macro expression.  When
         the macro expression is encountered, the expression is evaluated and
         the resulting character string is substituted for the TOTAL <expKey>

      USE Sales INDEX Branch NEW
      TOTAL ON &(INDEXKEY(0)) FIELDS Amount TO Summary




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s