Edit

FieldRef.CalcSum() Method

  • Article

Version: Available or changed with runtime version 1.0.

Calculates the total of all values of a SumIndexField in a table.

Syntax

[Ok := ]  FieldRef.CalcSum()

Parameters

FieldRef
 Type: FieldRef
An instance of the FieldRef data type.

Return Value

[Optional] Ok
 Type: Boolean
true if the operation was successful; otherwise false. If you omit this optional return value and the operation does not execute successfully, a runtime error will occur.

Remarks

This method is like the CalcSums Method (Record) method. This method operates only on records that meet the conditions of any filters associated with the record.

If possible, the CalcSum method uses SumIndexField Technology (SIFT). SIFT is used only if the following conditions are true:

  • The Dynamics 365 key contains the fields that are used in the filters that are defined for the FlowField.

  • The SumIndexFields on the Dynamics 365 key contain the field to which the FieldRef parameter refers.

  • The MaintainSIFTIndex Property is set to true.

Note

By default this property is set to true for all keys.

For Dynamics 365 Business Central, CalcSum execution is decoupled from Dynamics 365 SIFT index definitions. This means that if any of the conditions for using SIFT indexes are not true, then Dynamics 365 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 Dynamics 365, 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 CalcSum method. This provided a degree of protection in earlier versions against accidentally requesting a sorting for which no index existed. In Dynamics 365 Business Central, 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.

Example

This example sets a RecordRef variable to refer to table 21, the Cust. Ledger Entry table. Next, it creates a reference to field 18, the Sales (LCY) field, in the Cust. Ledger Entry table and assigns the field reference to a FieldRef variable. The Sales (LCY) field is a decimal field and is one of the SumIndexFields on a Dynamics 365 key in the Cust. Ledger Entry table. The code displays the original Value of the FieldRef variable, then calls the CalcSum method and displays the calculated value of the field.

var
    MyFieldRef: FieldRef;
    MyRecRef: RecordRef;
begin
    MyRecRef.Open(21);  
    MyFieldRef := MyRecRef.Field(18);  
    Message('Before CalcSum, Sales (LCY) is %1.', MyFieldRef.Value);  
    MyFieldRef.CalcSum;  
    Message('After CalcSum, Sales (LCY) is %1.', MyFieldRef.Value);  
end;

On a computer that has the regional format set to English (United States), the first message window displays the following:

Before CalcSum, Sales (LCY) is 0.

The second message window displays the following:

After CalcSum, Sales (LCY) is 55,162.67.

See Also

FieldRef Data Type
Get Started with AL
Developing Extensions
CalcSum Method (Record)
AL Database Methods and Performance on SQL Server