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:
- The NAV key contains the fields used in the filters defined for the FlowField
- The SumIndexFields on the operative key contain the fields provided as parameters for calculation
- The MaintainSIFTIndex property on the key is set to
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;