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
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.