Max function, but of specific records
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.