Show/Hide Toolbars


NoteThis command is defined in a header file and will be preprocessed by the X# preprocessor to a function call. If you disable the standard header (-nostddefs) files then this command will not be available. If you tell the compiler to use a different standard header file (-stddef ) then this command may also be not available


Summarize records in the current work area by key value to a database file.


TOTAL ON <uKeyValue> FIELDS <idFieldList> TO <xcTargetFile> [<Scope>] [WHILE <lCondition>] [FOR <lCondition>]


ON <uKeyValue>The key value used to summarize the records.  To make the summarizing operation accurate, the current database file should be indexed or sorted on this expression.


FIELDS <idFieldList>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 in the source file matching the key value.


TO <xcTargetFile>The name of the target database file to write the totaled records, including an optional drive, directory, and extension.  See SetDefault() and SetPath() for file searching and creation rules.  The default extension for database files is determined by the RDD.
If <xcTargetFile> does not exist, it is created.  If it exists, this command attempts to open the file in exclusive mode and, if successful, the file is overwritten without warning or error.  If access is denied because, for example, another process is using the file, NetErr() is set to TRUE.  


<Scope>The portion of the current database file to process.  The default is all visible records. Scope is one or more clauses of:
[NEXT <NEXT>]        Optionally specifies the number of records to process starting
               with the first record of the source file.
[RECORD <rec>]        An optional record ID If specified, the processing begins
               with this data record in the source file.
[<rest:REST>]        The option REST specifies whether records are sequentially
               searched only from the current up to the last record.
               If a condition is specified, the option ALL is the default value.
[ALL]                The option ALL specifies that all records from the source file are imported.
               This is the default setting.


WHILE <lCondition>A condition that each visible record within the scope must meet, starting with the current record.  As soon as the while condition fails, the process terminates.  If no <Scope> is specified, having a while condition changes the default scope to the rest of the visible records in the file.


FOR <lCondition>A condition that each visible record within the scope must meet in order to be processed.  If a record does not meet the specified condition, it is ignored and the next visible record is processed.  If no <Scope> or WHILE clause is specified, having a for condition changes the default scope to all visible records.



TOTAL works by first copying the structure of the current database file to specified target file, except for memo fields.  It then sequentially scans the current database file within the specified scope of records.


As each record with a unique key value is encountered, that record is copied to the target database file.  The values of numeric fields specified in the FIELDS list from successive records with the same key value are added to fields with the same names in the target file.  Summarization proceeds until a record with a new key value is encountered at which point the process is repeated.


Important! To successfully total numeric fields, the numeric fields in the current database file structure must be large enough to hold the largest total possible for that numeric field.  A runtime error will be raised if there is a numeric field overflow.


TOTAL is functionally equivalent to DBTotal().  


Deleted records: If SetDeleted() is FALSE, deleted records in the source file are processed.  Records in the target file inherit the deleted status of the first matching record in the source file.


Visibility: If SetDeleted() is TRUE, however, deleted records are not visible and are, therefore, not processed.  Similarly, filtered records (with DbSetFilter() or a conditional controlling order) are not processed.


In this example, a database file is totaled on the key expression of the controlling order using a macro expression.  When the macro expression is encountered, the expression is evaluated and the resulting string is substituted for the TOTAL <uKeyValue> argument:


USE sales INDEX branch NEW
TOTAL ON &(IndexKey(0)) FIELDS Amount TO Summary



See Also

AVERAGE, DBTotal(), INDEX, SetDefault(), SetPath(), SORT, SUM, UPDATE