In Chapter 3, Data Types and Fields, we discussed SumIndexFields and FlowFields in the context of table, field, and key definition. To recap briefly, SumIndexFields are defined in the screen where table keys are defined. They allow very rapid calculation of values in filtered data. In most ERP and accounting software systems, the calculation of group totals, periodic totals, and such, require reading of all the data to be totaled.
SIFT allows a NAV system to respond almost instantly with totals in any area where the SumIndexField was defined and is maintained. In fact, use of SIFT totals combined with NAV's retention of detailed data supports totally flexible ad hoc queries in similar form to: "What were our sales for red widgets between the dates of November 15th and December 24th?" And the answer is returned almost instantly! SumIndexFields
are the basis of FlowFields which have a Method of Sum
or Average
; such a FlowField must refer to a data element that is defined as a SumIndexField
.
When we access a record that has a SumIndexField defined, there is no visible evidence of the data sum that the SumIndexField represents. When we access a record that contains FlowFields, the FlowFields are empty virtual data elements until they are calculated. When a FlowField is displayed on a page or report, it is automatically calculated by NAV; the developer doesn't need to do so. But in any other scenario, the developer is responsible for calculating FlowFields (using the CALCFIELDS
function).
FlowFields are one of the key areas where NAV systems are subject to significant processing bottlenecks. Even with the improved NAV 2015 design, it is still critical that the Table Keys used for SumIndexField definition are designed with efficient processing in mind. Sometimes, as part of a performance-tuning effort, it's necessary to revise existing keys or add new keys to improve FlowField performance.
In addition to being careful about the SIFT-key structure design, it is also important not to define any SumIndexFields that are not necessary. Each additional SumIndexField adds additional processing requirements and thus adds to the processing load of the system.
The syntax for CALCFIELDS
is as follows:
[BooleanField := ] Record.CALCFIELDS ( FlowField1 [, FlowField2] ,…)
Executing the CALCFIELDS
function will cause all the specified FlowFields
to be calculated. Specification of the BooleanField
allows us to handle any run-time error that may occur. Any runtime errors for CALCFIELDS
usually result from a coding error or a change in a table key structure.
The FlowField
calculation takes into account the filters (including FlowFilters) that are currently applied to the Record (we need to be careful not to overlook this). After the CALCFIELDS
execution, the included FlowFields
can be used similarly to any other data fields. CALCFIELDS
must be executed for each cycle through the subject table.
Whenever the contents of a BLOB
field are to be used, CALCFIELDS
is used to load the contents of the BLOB
field from the database into memory.
When the following conditions are true, CALCFIELDS
uses dynamically maintained SIFT data:
Yes
(this is the default setting)If all these conditions are not true and a CALCFIELDS is invoked, we will not get a run-time error as in the previous NAV version, but SQL Server will calculate the requested total(s) the hard way, by reading all the necessary records. This could be very slow and inefficient, and should not be used for frequently processed routines or large data sets. On the other hand, if the table does not contain a lot of data or if the SIFT data will not be used very often, it may be better to have the MaintainSIFTIndex property set to No
.
The syntax for
SETAUTOCALCFIELDS
is as follows:
[BooleanField := ] Record.SETAUTOCALCFIELDS ( FlowField1 [, FlowField2] [, FlowField3]…)
When SETAUTOCALCFIELDS
is inserted in to the code in front of the record retrieval, the specified FlowFields
are automatically calculated as the record is read. This is more efficient than performing a CALCFIELDS
on the FlowFields after the record has been read.
If we want to end the automatic FlowField calculation on a record, call the function without any parameters:
[BooleanField := ] Record.SETAUTOCALCFIELDS()
Automatic FlowField calculation equivalent to SETAUTOCALCFIELDS
is automatically set on for the system record variables Rec
and xRec
.
The CALCSUMS
function is conceptually similar to CALCFIELDS
for the calculation of Sums
only. But CALCFIELDS
operates on FlowFields and CALCSUMS
operates directly on the record where the SumIndexFields are defined for the keys. This difference means that we must specify the proper key plus any filters to apply when using CALCSUMS
(the applicable key and filters to apply are already defined in the properties for the FlowFields).
The syntax for CALCSUMS
is as follows:
[ BooleanField := ] Record.CALCSUMS ( SumIndexField1 [,SumIndexField2] ,…)
Prior to such a statement, to maximize the probability of good performance, we should specify a key that has SumIndexFields
defined. Before executing the CALCSUMS
function, we also need to specify any filters that we want to apply to the Record
from which the sums are to be calculated. The SumIndexField
calculations take into account the filters that are currently applied to the Record.
Executing the CALCSUMS
function will cause the specified SumIndexField
totals to be calculated. Specification of the BooleanField
allows us to handle any runtime errors that may occur. Runtime errors for CALCSUMS
usually result from a coding error or a change in a table key structure. If possible, CALCSUMS
uses the defined SIFT. Otherwise, SQL Server creates a temporary SIFT on the fly.
Before the execution of CALCSUMS
, SumIndexFields
contain only the data from the individual record that was read. After the CALCSUMS
execution, the included SumIndexFields
contain the totals that were calculated by the CALCSUMS
function (these totals are only in memory, not in the database). These totals can then be used the same as data in any field, but if we want to access the individual record's original data for that field, we must either save a copy of the record before executing the CALCSUMS
or we must reread the record. The CALCSUMS
must be executed for each read cycle through the subject table.
In the Sales Header record, there are FlowFields defined for Amount
and "Amount
Including
VAT"
. These FlowFields are all based on Sums
of entries in the Sales
Line
table. The CalcFormula for Amount
is Sum("Sales
Line".Amount
WHERE
(Document
Type=FIELD(Document
Type),Document
No.=FIELD(No.)))
. Remember, Amount
must be a SumIndexField
assigned to a Sales Line
key that contains the fields on which we will filter (in this case by Document Type
and Document No.
). To calculate a TotalOrderAmount
value while referencing the Sales Header table, the code can be as simple as:
"Sales Header".CALCFIELDS (Amount); TotalOrderAmount := "Sales Header".Amount;
To calculate the same value from code directly referencing the Sales Line table, the required code would be similar to the following (assuming a Sales Header record has already been read):
"Sales Line".SETRANGE("Document Type","Sales Header"."Document Type"); "Sales Line".SETRANGE("Document No.","Sales Header"."No."); "Sales Line".CALCSUMS(Amount); TotalOrderAmount := "Sales Line".Amount;