3 Replies Latest reply on Oct 1, 2014 2:07 PM by philmodjunk

    Help with calculations



      Help with calculations



      Being new to FM, I am trying to design a report for the transport industry.

      I have a vehicle come in for inspection = Date In
      Vehicle leaves inspection = Date out.
      Date out - Date in = Days in workshop

      2. I need a calculation for the number of days between Date Out and the next Date In


      Date In - last Date Out this needs to give me the period in days between inspections

      It would also be good to show the number of days 42 until next inspection date

      3. When the vehicle comes in I also need to record the odometer.
      I need to calculate Odometer In - Last Odometer In to give total miles travelled

      Thank you in anticipation

        • 1. Re: Help with calculations

          So what you describe in 1. are three fields in the same record of the same table? Do you also have a field that uniquely identifies each vehicle?

          If so, a sorted self join relationship can be devised that enables a calculation to access data for the record in the same table that logged the same vehicle's previous inspection.

          • 2. Re: Help with calculations


            Hello Phil

            Please see fields being used below.

            The rest of your description has gone over my head I'm afraid, sorry

            • 3. Re: Help with calculations

              Apologies, my answer wasn't really intended to tell you all you needed to know to do this, just to indicate that it is possible provided you have a field that uniquely identifies each vehicle that you inspect.

              But I recommend that you define a table of vehicles with an auto-entered serial number field where you store the VIN number for each vehicle plus any other data (year, model, make?) specific to that vehicle that you might need. You could then set up this relationship:

              Vehicles::__pkVehicleID = PMI Converted::_fkVehicleID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              A portal on the vehicles layout, among many other advantages, could list al inspection records from PMI Converted for a given vehicle. (And I'd rename your table to something more descriptive than "PMI Converted". wink)

              But that's an option for you to consider, the following method can use either Vehicle Reg or my suggested _fkVehicleID field to produce what you want.

              So each place in the following example where you see _fkVehicleID, you can use Vehicle Reg if you prefer.

              Open Manage | Database | Relationships.

              Select PMI Converted by clicking it and then create a duplicate Tutorial: What are Table Occurrences? by clicking the duplicate button (two plus signs).

              Drag from _fkVehicleID in one table occurrence box to _fkVehicleID in the other to link the two in a relationship matching records by VehicleID. Since these two occurrences both refer to the same table of records, this is called a "self join".

              Double Click the relationships line to open the Edit Relationship dialog.

              Click to select Date IN on both sides of the relationship, select the > operator from the drop down and click the Add button to produce:
              PMI Converted::_fkVehicleID = PMI Converted 2::_fkVehicleID AND
              PMI Converted::Date In > PMI Converted 2::Date In

              Click the Sort button on the PMI Converted 2 side of the relationship and specify that the related records be sorted by Date In in descending order. This makes the most recent record with the same vehicle ID and a Date In less than the current record's the "first related record".

              Click OK to close the Edit Relationship Dialog.

              Click the FIelds tab and add a calcluation field to PMI Converted:

              Date In - PMI Converted 2::Date Out

              This will compute the number of days since the previous inspection. A similar calculation can give you the number of odometer miles since last inspection.

              Caulkins Consulting, Home of Adventures In FileMaking