2 Replies Latest reply on Jul 13, 2014 6:47 AM by ChrisCantrell

    New to FMP - Question on Calculations between Similar Records

    ChrisCantrell

      Title

      New to FMP - Question on Calculations between Similar Records

      Post

           Hi,

           I'm very new to FMP and have tried to search for an answer and probably overlooking it.

           I have 3 tables.

           Customer ---< Tanks ----< Production History

           I'm using portals to display Production History on Tanks and Tanks on Leases ... that is good.

           What I'm trying to figure out is how to calculate differences between 2 records in the Production History.

           Production History has daily gauges:

           Gauge Date, Gauge Foot, Gauge Inches, Gauge Quarter Inches, Total Inches (calculation field to show total inches)

           What I would like to do is calculate daily production based on the previous record for that specific tank. (and if it goes beyond 1 day, calculate the days between the dates and divide the production by that amount.)

           Example:

           1/4/2014 - 4'0"0, 48  * 24 inches in 2 days and 12 per day.

           1/2/2014 - 2'0"0, 24   ** 12 inches between the two dates.

           1/1/2014 - 1'0"0, 12

           Thanks in advance!

        • 1. Re: New to FMP - Question on Calculations between Similar Records
          philmodjunk

               I will assume that only one history record will be created on a given date for a specific tank and that your relationship between Tanks and Production History is:

               Tanks::__pkTankID = ProductionHistory::_fkTankID

               If so, you can add another Tutorial: What are Table Occurrences? of ProductionHistory, name it "ProductionHistory|Previous" and link it like this:

               ProductionHistory::_fkTankID = ProductionHistory|Previous::_fkTankID AND
               ProductionHistory::Gauge Date > ProductionHistory|Previous::Gauge Date

               Double click the relationship line between these two occurrences and specify a sort order for ProductionHistory|Previous that sorts the records by Gauge Date in descending order. (Gauge Date must be a field of type date, not text.)

               Now you can add a date and a Gauge Reading field that auto-enter these values from the original date and Gauge Reading fields but from ProductionHistory|Previous to copy over the previous readings and date so that you can subtract the dates to get elapsed days and subtract the gauge readings to get the difference in order to divide the difference by the elapsed days.

          • 2. Re: New to FMP - Question on Calculations between Similar Records
            ChrisCantrell

                 Thanks Phil.  Yes, only 1 per day.