Function DbTotal() Foundation

Totals data from numeric fields of a work area into a second file.

DbTotal( <cFilename>, ;
         <bIndexKey>, ;
        [<aFieldnames>], ;
        [<bForCondition>], ;
        [<bWhileCondition>], ;
        [<nCount>], ;
        [<xRecordID>], ;
        [<lRest>] ) --> NIL
<cFilename> is a character string containing the name of the file into which the totaled results are written.
<bIndexKey> is a code block containing the index key used to sort the records in the work area. Summation continues until the return value of this code block contains a different value. A new record in <cFilename> is then created to store the values before the summation restarts (from zero) for the next record in the work area.
<aFieldNames> := { [<cField,...>] }
<aFieldNames> specifies an array containing the names of the numeric fields in the file <cFilename> to total. Each array element must contain a character string specifying a field name. If <aFieldnames> is not included, no totals are calculated. Instead for each key expression a record is copied into the target file.
<bForCondition> is an optional code block containing a condition as a logical expression. Only records where <bForCondition>returns the value .T. (true) are totaled.
<bWhileCondition> is an optional code block containing a condition as a logical expression. As long as this code block returns the value .T. (true), records are totaled. The operation is terminated as soon as <bWhileCondition> returns the value .F. (false).
<nCount> optionally specifies the number of records totaled beginning with the current record.
<xRecordID> is an optional record ID (for DBF files it is the record number). If <xRecordID> is specified, only this record is copied into the file <cFilename>.
The optional logical value <lRest> specifies whether all data records in a work area are totaled (<lRest>==.F.), or only the data records from the current record to the last record (<lRest>==.T.). The default value is .F. (false), meaning all data records are totaled.

The return value of DbTotal() is always NIL.


The function DBTotal() totals all fields of a work area which are specified in the array <aFieldnames> and writes the results into fields with the same names in the file <cFilename>. This target file cannot be open. It is created with the same structure as the source file in the work area. If the target file already exists, it is overwritten without warning.

The file in the work area must be sorted or indexed by the index key <bIndexKey>. Each time the value of the index key changes, a new record is copied into the target file and the summation is done into the new record. The lengths of the numeric fields must be long enough to store the result. Otherwise a runtime error occurs caused by numeric overflow.

If no field is specified in the array <aFieldnames>, a record is copied into the target file each time the value of the index key changes. This effectively deletes multiple values of the index key in the target file.

The scope for the summation can be limited by conditions or by specifying the number of records. If SET DELETED is turned ON, data records marked for deletion are not included. If a filter is active, records not matching the filter condition are also not included. When SET DELETED is turned OFF, a deletion mark is carried over to the target file only when the first record matching the index key is marked as deleted. For subsequent records having the same index key the deletion is ignored.


// The example calculates monthly sales for the year 1994 
// using the data in an invoice file. A sales summary file 
// is created. 


   INDEX ON SubStr(DtoS(InvDate),1,6) TO Temp 

   DbTotal( "Sales94", ; 
            {|| SubStr(DtoS(InvDate),1,6) }, ; 
            { "Payment" }, ; 
            {|| Year(InvDate)==1994 } ) 

   USE Sales94 

   DO WHILE ! Eof() 
      ? "Sales in the month", CMonth( InvDate )+":", Payment 


If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.