3 Replies Latest reply on Jun 25, 2012 9:15 AM by philmodjunk

    Calc based on multiple records in related file

    glendalf

      Title

      Calc based on multiple records in related file

      Post

      Hi Guys,

       

      I've been out of Filemaker for a while but am having to step back into it so I'm a little rusty to say the least!

      Anyway, I have 2 tables

      • Vehicles
      • Mileage
      The vehicles table simpley holds records of individual vehicles.
      The mileage table holds the mileage reading and the date that it was taken and of course a relationship back to a vehicle.
      I need to work out the average mileage per annum of a vehicle based upon the miles travelled between the dates in the Mileage table. the sum is easy enough, I just can't seem to translate this into filemaker and get the calc to work across the different tables.
      Any help is appreciated, let me know if you need any more information.
      P.S I would probably end up limiting the calc to the last two mileage readings as they will be the most up to date.
      Best regards
      G

        • 1. Re: Calc based on multiple records in related file
          philmodjunk

          Does the mileage field record miles traveled since last reading or just the current odometer value?

          I believe you are just recording odometer values.

          You have this relationship:

          vehicles::vehicleID = Mileage::vehicleID

          Let's add another relationship:

          Mileage::VehicleID = MileageStartYear::VehicleID AND
          Mileage::cYear = MileageStartYear::cYear

          In Manage | Database | relationships, make a new table occurrence of Mileage by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box MileageStartYear as I have done.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Now this calculation field, cMilesYTD, can be defined as:

          OdometerReading - MileageStartYear::OdometerReading

          define cYear as Year (yourdatefield)

          • 2. Re: Calc based on multiple records in related file
            glendalf

            Thatnks for your reply. I've been out of it so long I forgot about selfjoin relationships :S

             

            The above worked just fine although I have removed the additional cYear references in the new relationship as that wouldn't work with the data we have as some customers visit us just once per year.

             

            I have now set up a calc which works out the annual mileage based on the first record in the relationship(effectively mile 0) and whatever the current record is

            Each mileage record now has an updated calculated annual mileage amount which alters from record(odometer reading) to record(odometer reading) which is fine.

            The only difficulty I have now is getting the last calculated annual mileage into the main vehicle record which I think would be ideally stored with the vehicle record as many scripts / calc will be looking at this information in the future.

            What am I missing for this part of the problem...?

            Many thanks again..

            • 3. Re: Calc based on multiple records in related file
              philmodjunk

              You mentioned a need for yearly mileage, which was the purpose for including the cYear field so that you could calculate a total mileage for each year.

              Removing that part of the relationship means that the mileage you get from the mileage table will not be annual mileage. I'm guessing that you are using a different calculation to divide by the number of years elapsed since the year recorded with the first mileage entry to compute an average annual mileage.

              Generally, it's best not to store such a value separately like this. If you do, you have to take great care to keep it correclty up to date--and this is why such denormalized data is best avoided.

              But a script can use set field to update such a number field on command using your calculation for average annual mileage. Unless I observe a significant slow down, I'd just use the calculation directly.