AnsweredAssumed Answered

Max function, but of specific records

Question asked by MichaelBroughton on Oct 13, 2011
Latest reply on Oct 14, 2011 by MichaelBroughton

Title

Max function, but of specific records

Post

I manage a fleet of vehicles. I'm digitizing the maintenance records in FM Pro. I keep a table, "Maintenance Log", with a field called "Routine Maintenance", and a field that says "Mileage", among others. What I'm trying to do is return the mileage of a vehicle the last time it's oil was changed. When I put an oil change into the maintenance log table, I put "Oil Change" under the "Routine Maintenance" field, and then the mileage of the vehicle at the time of the oil change under the "mileage" field.

I need a field (in a different table called "Vehicle Info", which has one record for each vehicle) that tells me when the oil was changed last in each vehicle. I tried making this field refer to this calculation:

If ( Maintenance Log::Routine Maintenance = "Oil Change" ; Max ( Maintenance Log::Mileage))

But what I get is the highest mileage recorded in ANY record for that vehicle, regardless if it was an oil change, or something else. What am I doing wrong? I feel like this should be simple, but I can't figure it out! Ultimately i'll be repeating this for lots of other routine maintenances.

Thanks!

Outcomes