2 Replies Latest reply on Oct 14, 2011 12:00 PM by MichaelBroughton

    Max function, but of specific records

    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!

        • 1. Re: Max function, but of specific records
          philmodjunk

          The IF function has no control over how the Max function evaluates. It only controls whethter or not it evaluates at all. If the first related record in Maintenance Log is not an oil change entery, you will get a blank field as the first part of the expression can only look at the first related record and in this case, the If function will prevent the max function from evaluating.

          The Max function can only return the maximum value for all related records, it doesn't have any options to make this selective.

          You have three options that I can think of:

          Use a relationship that matches only to Oil change records for a given vehicle. Then Max will work for you.

          Use a summary field "maximum of" Mileage defined in the Maintentance Log table, but place it inside a one row portal with a portal filter set to exclude all records that are not oil change records. (You have to have FileMaker version 11 for this option.)

          Set up a report layout based on the Maintenance Log instead of the Vehicle table. Then you can use Finds and Sorts to list such entries not only for an oil change but for any other maintenance activities where you want to see the mileage.

          • 2. Re: Max function, but of specific records
            MichaelBroughton

            The summary field worked. Thank you!