Problem: Need a formula to find previous lowest odometer reading for a list of records of randomly entered odometer readings so I can calculate MPG. Example. Previous record readings were 41789,43582, 47135, 46902. New reading is 44234. I need the calculation to find 43582, the previous lowest reading. Each reading is a new record.

Hi KYC,

What you are describing can be done with either a self-join relationship based on range calcs, or by parsing the field index. Here's an example of the index parsing method that you might like to try.

First create a time field called 'ModTime' and define it to auto-enter the record modification time. Then create a value list called 'Readings' and define it to draw its values from the 'OdometerReading' field.

Last of all, create an unstored calculation field with a formula along the lines of:

Case(ModTime,

RightWords(

Middle(Substitute(ValueListItems(Status(CurrentFileName), "Readings"), ",", ""), 1,

Position(Substitute(ValueListItems(Status(CurrentFileName), "Readings"), ",", ""), Substitute(OdometerReading, ",", ""), 1, 1) - 1)

, 1)

)

The calculation result will not appear correctly until you exit the record (because it is reliant on the field index which is not updated until the record is committed) but it will then give you the next lowest OdometerReading value you are looking for.

Thanks for the formula, you're a life saver.