2 Replies Latest reply on Jan 22, 2014 7:19 PM by skip7396

    Calculated Field Help



      Calculated Field Help


           Hello all - this is my first post here and I am pretty new to filemaker.  I am in the process of re-creating a database for our family's small oil and gas company (going from MS Access to Filemaker 13 Pro Adv.).  We record oil production daily as the oil enters storage tanks.  We physically gauge the height (in feet and inches) of the oil in the tank each day and record those "gauges" in our reporting software....which was a part of the MS Access database I am replacing.  I have created a table in my filemaker database called "production".  Inside this production table we can select the tank we are gauging, enter the date and time of the gauge, and enter the gauge in feet and inches - in separate fields....feet in one field (gauge feet) and inches in another field (gauge inches).  I have created a calculated field that will convert the gauge into total tank volume in barrels -        =((Gauge Feet *12)+Gauge Inches)*multiple.

           I need to create an additional field (I think!!) that will calculate the tank volume difference between that days gauge and the prior days gauge....which will be the total production of oil for that particular day.  Below is the structure for my table:

           1. prodID - Number - Auto-enter serial number

           2. tankID - Number - (All storage tanks are set up in a "Storage Tanks" table....this is the related field to that table)

           3. Tank Number - Text - Lookup

           4. Date

           5. Time

           6. Gauge Feet - Number

           7. Gauge Inches - Number

           8. Calculated Volume - Calculated field - =((Gauge Feet * 12)+Gauge Inches)*Storage Tanks::Multiple

           9. Calculated Daily Production - Calculated Field - This is the field where I am asking for some help

           I haven't been able to piece together how to access a prior days gauge (Date - 1) for a specific "Tank Number" to incorporate it into a calculated field.....which is essentially (Today's Calculated Volume - Yesterday's Calculated Volume).

           I really appreciate any guidance and help - and hopefully I will be able to return the favor some day.

           --Skip Griffith 

        • 1. Re: Calculated Field Help

               lets say the Table name is 'Production'

               Create self relationship as follows

          1.           Two occurrences 'Production' and 'ProductionPrior'
          3.           set relationship with two fields
                      Production::ProdId > ProductionPrior::ProdID
                      Production::TankId = ProductionPrior::TankID
          5.           Sort ProductionPrior records by ProdId descending
          7.           Now define the calculation of field Production::Calculated Daily production as
                      Calculated Volume - ProductionPrior::CalculatedVolume


          • 2. Re: Calculated Field Help

                 Thank you so much!  With your excellent instructions I was able to get it to do exactly what I was needing it to do!  Now, I am about to go do some research to figure out how this actually works (sorry....still learning the basics).  Again, thank you so much for taking the time to answer my question, and to do it so thoroughly.