|Powered by QM on a Rpi server|
KnowledgeBase 00086: Using Calculated Values in Alternate Key Indices
This article was originally published as a Tip of the Week.
It is possible to construct an alternate key index based on any data item defined in the dictionary, however, use of calculated values (C-type, I-type or A/S-type with a correlative) may not work as the designer intended.
The calculation defined by the dictionary item will be evaluated whenever a record is added, updated or deleted. Only these actions will cause updates to the index. If a calculation uses some external data, changes to that data will not cause the index to be updated.
Common examples of this are expressions that depend on some time variant item or expressions that use TRANS() or T-conversions to fetch data from elsewhere. Building an index on a person's age calculated in whole years will not update the index at their birthday. Building an index on data retrieved by TRANS() will not update the index if the record referenced by the TRANS() changes though it is acceptable to do this if the retrieved data is never updated after the indexed record has been written.
Note that in both cases, simply rewriting the indexed record will not correct the problem. The new index entry will be added but there is no way to identify the previous index entry that needs to be deleted. The only solution is to rebuild the index.
For indices to work correctly without manual intervention, the rule is the calculation must always return the same result when evaluated for the same input data from the indexed record.
Expression Evaluation in the Query Processor
A query that uses the indexed field in a selection condition that can be resolved using the index will not re-evaluate the expression.
A query that uses the indexed field in a sort clause, display clause or in a selection condition that cannot use the index will re-evaluate the expression.
Expression Evaluation in Index Updates
Adding a new record will evaluate the expression to determine the new index entry.
Deleting a record will evaluate the expression to determine the index entry that must be removed.
Updating an existing record will evaluate the expression twice; once to delete the old index entry and once to add the new index entry. If the result of the two evaluations is the same, no actual index updates are performed.
Sometimes it is useful for the index expression to be able to determine why it is being executed. The @ITYPE.MODE variable will contain 1 for evaluation when removing an index entry, 2 for evaluation when adding an index entry, and 0 at all other times (e.g. evaluation as a display clause item).