Sum Index Field Technology(SIFT) is a built-in technology that exists in Dynamics NAV and is used for totaling.
In other ERP systems, totals, subtotals, and balances are calculated and stored somewhere. This calculation has to be redone over and over so that numbers are up-to-date.
In Dynamics NAV, as a developer you don't have to worry about that as it is done by the SIFT technology. Creating a new subtotals field is as easy as indicating in the field properties that the field is a Flowfield
and specifying the formula that is going to be used to calculate the subtotal. After this you will not have to worry about keeping it up-to-date.
As a user, you know that balances for your G/L accounts, customers, vendors, or bank accounts are always up-to-date, just like many other calculations done using the SIFT technology, such as the inventory of an item or all the customer statistics that are shown on the right-hand side of the screen when looking for or creating sales orders.
Do you remember when we explained a few sections ago, in the Creating ledger entries section of this chapter, that some ledger entries had detailed ledger entries and that some information shown at the ledger entry level was actually a calculation done over its detailed ledger entries? Well, this is actually managed by the SIFT technology.
In that example, we talked about the remaining amount of Cust. Ledger Entry
field that was a sum of the field Remaining Amount
of its Detailed Cust. Ledger Entry
field. Let's see how this is defined:
There are two key properties called FieldClass
and CalcFormula
that define that the SIFT technology will be used.
In this example, the value of the field Remaining Amount
will be the result of totaling the field Amount
from the table Detailed Cust. Ledg. Entry
, by applying the following conditions:
Cust. Ledger Entry No.
in the table Detailed Cust. Ledg. Entry
has to match the value in the field Entry No.
in the table Cust. Ledger Entry
Posting Date
in the table Detailed Cust. Ledg. Entry
has to match the date filter specified in the field Date Filter
in the table Cust. Ledger Entry
In prior versions of Dynamics NAV, defining a field that was going to be calculated and the formula that had to be used was not enough for the SIFT technology to act. If nothing else was done and FlowField
was used somewhere in the application, the user encountered a runtime error like the one shown in the following screenshot:
The first part of the error message stated that the FlowField
value could not be calculated.
The second part of the error message actually told the user what had to be done to be able to calculate the value of FlowField
. A key had to be defined (in the table for which its records were filtered and the sum was calculated) and the field that would be the base of the calculation had to be associated to the key as SumIndexField
. The key had to contain all the fields that were part of the condition that was set to calculate the value of the field.
This was a runtime error. Only the conditions applied at the moment when the error occurred were taken into account. In the example, the conditions were set for the fields Cust. Ledger Entry No.
and Date Filter
. The error message only mentioned the field Cust. Ledger Entry No.
because when it occurred, we had no date filter set, so the condition over the Posting Date
field in the table Detailed Cust. Ledg. Entry
was not applied.
The following steps show how all of this was done in Dynamics NAV:
As you can see in the preceding screenshot, there are a bunch of keys defined for the table 379 Detailed Cust. Ledg. Entry and some of them have one or more fields associated with them called SumIndexFields.
To calculate Remaining Amount
in the table Cust. Ledger Entry
, a key had to exist that included the fields Cust. Ledger Entry No.
and Posting Date
(the fields for which conditions could be stated), and this key had to have the field Amount
(the field that was being summed in the calculation) as SumIndexField
.
The third key you can see in the screenshot complies with all the conditions.
Having SumIndexField
associated with a key actually tells Dynamics NAV that we want to maintain the totals and subtotals of the field specified for all the values of the key.
These totals and subtotals are maintained in Microsoft SQL as a view.
SumIndexField
calculations and their conversion to Microsoft SQL views still exists in Dynamics NAV 2013, but they are not a mandatory requirement anymore for the SIFT technology to act.