CALCSUMS Function (Record)
Calculates the total of a column in a table. You specify which fields to calculate by using parameters.
[Ok :=] Record.CALCSUMS (Field1, [Field2],...)
Type: Record The record that contains the SumIndexFields that you want to calculate.
- Field1, Field2, …
Type: Decimal The fields that you want to calculate. Each field must be a decimal field, defined as a SumIndexField, in the current key. The field class of the field must be Normal. For more information about field class, see FieldClass Property.
Property Value/Return Value
If you omit this optional return value and if one of the fields is not a SumIndexField, a run-time error occurs. If you include a return value, you must handle any errors.
Use this function to total columns in a table. This function operates only on records that meet the conditions of any filters associated with the record.
If possible, the CALCSUMS function uses SumIndexField Technology (SIFT). SIFT is used only if the following conditions are true:
The Microsoft Dynamics NAV key contains the fields that are used in the filters that are defined for the FlowField.
The SumIndexFields on the Microsoft Dynamics NAV key contain the fields that are provided as parameters for calculation.
The MaintainSIFTIndex Property is set to Yes.
By default this property is set to Yes for all keys.
For Microsoft Dynamics NAV 2013 R2, CALCSUMS execution is decoupled from Microsoft Dynamics NAV SIFT index definitions. This means that if any of the conditions for using SIFT indexes are not true, then Microsoft Dynamics NAV traverses all records in the base table to perform the calculation instead of using SIFT. This can reduce the number of required SIFT indexes, which can improve performance. In earlier versions of Microsoft Dynamics NAV, if the conditions for using SIFT indexes were not true and the MaintainSIFTIndex property was enabled, then you received an error when you called the CALCSUMS function. This provided a degree of protection in earlier versions against accidentally requesting a sorting for which no index existed. In Microsoft Dynamics NAV 2013 R2, an index is not required to support a certain sorting, but sorting without an index could lead to bad performance if a search returns a large result set, which would then have to be sorted before the first row is returned.
This example shows how to use the CALCSUMS function. It requires that you create a Record variable named WhseEntry for the Warehouse Entry table.
WhseEntry.SETFILTER("Item No.", 'LS-*'); WhseEntry.SETRANGE("Bin Code", 'W-11-0001'); WhseEntry.SETRANGE("Location Code", 'WHITE'); WhseEntry.CALCSUMS("Qty. (Base)");
The first three lines set filters on fields in the Warehouse Entry record so the total that is calculated in the last line of code uses records only in the specified range.