Describes techniques for selecting only one (the last) detail out of many. A similar approach can be adopted to select the first detail.
I have a table of subsystems, and a table of maintenance records for those subsystems. I only want to see the last maintenance record for each subsystem; how do I do this?
Up to a certain point you go through the normal steps you would follow to produce a report of all of the maintenance records for each subsystem:
At this point, if you printed a regular report of the details, you'd get all of the maintenance records for each subsystem. But you want only the last maintenance record (whatever "last" means).
There are two ways to do this; you need to select the appropriate method depending on your needs.
This technique suppresses printing of details entirely, and instead the interesting records are printed on breaks.
If appropriate keys exist to sort the subsystems file by identifier code, and the key used for the maintenance records file includes the subsystem identifier as well as the date (the definition of "last" for this example), then using only the subsystem identifier code to build the key for the maintenance file represents a partial key and your report will be naturally sorted: no separate sort is needed, and the performance should be comparable to printing a detailed report (but with considerably less output).
If you wanted the first maintenance record instead of the last, you could introduce a sort by identifier code (ascending) and date (descending).
In XENTIS version 4.6 and later there are functions which can
look ahead and behind at other records in the stream. For example presuming
the identifier code is defined as a textfield, you can use the
function to get the value of the field as it appears in the next record after
the current one.
LAST_REC = B:ID <> NEXTT( B:ID )
A ! Type of selection logic
LAST_REC ! Selection expression 1
Note that there is no comparison operator in the selection expression: none is needed, because the calculation does the comparison.
This technique may not work correctly if the records are not in a natural key order, or if the field being tested is not in the most nested file.