Real-time data gathering – the SIFT technology

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:

  1. Open the Dynamics NAV development environment.
  2. Locate the table 21 Cust. Ledger Entry.
  3. Click on Design to open the table designer for the table 21 Cust. Ledger Entry.
  4. Locate the field 14 Remaining Amount.
  5. Click on View | Properties or press Shift + F4 to open the Properties window for the field 14 Remaining Amount.
  6. The Properties window is opened.
    Real-time data gathering – the SIFT technology

    There are two key properties called FieldClass and CalcFormula that define that the SIFT technology will be used.

    • FieldClass: By setting its value to FlowField, we are telling the system that this field will not be stored in the database; it will be calculated every single time it is needed
    • CalcFormula: This is the formula that will be used to calculate the value of the field

    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:

    • The value in the field 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

    Note

    Using a date filter in the calculation will allow us to know not just the actual remaining amount for Cust. Ledger Entry but we will also know the remaining amount for that particular time.

  7. Close the Properties window for the field 14 Remaining Amount.
  8. Close the table designer for the table 21 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:

Real-time data gathering – the SIFT technology

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.

Note

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:

  1. Open the Dynamics NAV development environment.
  2. Locate the table 379 Detailed Cust. Ledger Entry.
  3. Click on Design to open the table designer for the table 379 Detailed Cust. Ledg. Entry.
  4. Click on View | Keys to open the Keys window.
    Real-time data gathering – the SIFT technology

    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.

  5. Close the Keys window.
  6. Close the table designer.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset