



Products	Support	Download	Contact

Selecting the Last Detail

return to contents or index

Abstract

Describes techniques for selecting only one (the last) detail out of many. A similar approach can be adopted to select the first detail.

Q&A:

Question:

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?

Answer:

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:

Select the subsystems file as the first file.
Select the maintenance records file as the second file.
Use something appropriate (such as a subsystem identifier code) to build the key for the second file.
Set Multiple records to "Y".

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.

Technique #1: Printing On Subtotal

This technique suppresses printing of details entirely, and instead the interesting records are printed on breaks.

Instead of using a print frequency of "D" (detail), use a print frequency of "S" (subtotal).
Break on the subsytems file subsystem identifier code.
In the Output section, set Suppress detail lines to "Y".

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).

Technique #2: Using the `NEXTT( )` Function

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 NEXTT( ) function to get the value of the field as it appears in the next record after the current one.

Define a calculation such as:

LAST_REC = B:ID <> NEXTT( B:ID )

Select on it:

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.