A SumIndexField is a fundamental feature which is the basis of FlowFields. A SumIndexField is associated with a key; each key can have a maximum of 20 SumIndexFields.
During database design, a field of the decimal type can be associated with a key as a SumIndexField. This tells the DBMS to create and maintain a structure which contains the accumulated sum of values in a column. When a new current key is selected, any SumIndexField associated with it becomes accessible.
Example
The following illustration shows a table where the Amount field (column) is defined as a SumIndexField in the Account No + Date key. This enables the DBMS to automatically maintain the accumulated sum of the column. Every time a change is made to a field in the column, the accumulated values are updated.
To the right of the table is shown an area in the database where the accumulated sums for the Amount column are kept. In the previous illustration, the third field in the column, with the accumulated sum, contains the value 600 because the first three Amount values are 100, 200, and 300, respectively—a total of 600. The fourth virtual field contains 1000, the total of the first four values in the Amount column, and so on. If the table contained a second SumIndexField, its values would be accumulated in the same way.
Advantages of SumIndexFields
With SumIndexFields sums (of columns) can be quickly calculated and the result displayed in FlowFields. If you want the sum of all the values in the Amount fields, in a conventional system, the DBMS is forced to access every record and add each value in the Amount field, This is a very time-consuming operation in a database with thousands of records. You would need to create a FlowField, define the calculation formula of this FlowField to sum the Amount field, so the DBMS only needs retrieve the value from the SumIndexField.
Operations with SumIndexFields are as fast when FlowFilter fields are applied. The second table in the previous illustration shows a group of records selected by a FlowFilter field in the Account No. field. Two records fulfil the conditions of the calculation filter.
Only two accesses are needed to sum the Amount for these records: one access to get the accumulated sum associated with the last record before the specified range, and one access to get the accumulated sum associated with the last record in the specified range.
The value 300 is subtracted from the value 1000 to produce the correct sum (700). No matter how many records there are in the selected range, the system will always need to perform only two accesses in order to compute the sum.
SIFT has been built into the index structure used on Classic Database Server and the more SumIndexFields that are added the larger the index becomes. However, the time used to maintain the accumulated sum for SumIndexFields is negligible due to a special index structure used in the DBMS.