6 Replies Latest reply on Jul 9, 2013 9:34 AM by philmodjunk

    Get Field Value from previous date



      Get Field Value from previous date


           I have a simple database to record flow totalizer values for several stations.  My table includes the following fields: StationID, Date, and Value.  Data is entered into the table for several stations, Sta1, Sta2, Sta3.  I would like to add a calculated field to the table called Flow.  For a given station, Flow is the last recorded value minus the current value.  How can I create a calculation or script to find the last recorded (date) value for the current StationID?

           Thanks for your assistance.

        • 1. Re: Get Field Value from previous date

               You have a calculation field flow with the following calculation :   GetNthRecord(previousRecordValue;Get(RecordNumber)-1)-CurrentRecordValue
               Sub previouRecordValue and CurrentRecordValue with your field name.  Also the records need to be sorted in date order.

          • 2. Re: Get Field Value from previous date

                 @S Chamblee, thanks for your response.  However, the problem with this solution, unless I am missing something, is that is does not consider that data is being collected for multiple stations and the "previousRecordValue" must be associated with the current StationID.  Somehow I need to modify your equation to find the "previousRecordValue" where "StationID" = StationID::CurrentRecordValue.  Also, I would prefer to not have to sort the records by date as this will be a database being used by field technicians, unless I somehow run a script on each data entry to automatically sort the records by date. This could likely slow the database sufficient if there are a large number of records though.


                 Thanks, Jeff

            • 3. Re: Get Field Value from previous date

                   If the database is sorted at all times by the date then it shouldn't be a problem with speed, filemaker will not have to rebuilt the complete index just update the index.  If you peform a find on the stationId then the only records that will be included will be that stationId.  It would be very slow to loop through all records looking for a date and stationId.

              • 4. Re: Get Field Value from previous date

                     I would think that you'd want to use a self join relationship that matches by station ID field to make sure that you are referencing previous data from the same station.

                • 5. Re: Get Field Value from previous date

                       Ok, I did the self join relationship and I see how that allows me to produce a nice display of the historic data in a portal, I am not seeing how this allows me to find the last (date) reading and calculate Flow as CurrentRecordValue - PreviousRecordValue.  I am very new to Filemaker but surely this is not as difficult as it is seeming to me.  If I could designate some type of variable field like StationLastReading in the related StationID table (just a listing of stations) and then after a current value is entered and the Flow calculated set the StationLastReading to CurrentValue such that when the next reading is made, the StationLastReading IS the PreviousRecordValue; Flow calculation is made and then StationLastReading is set to CurrentValue.  But, how can you make a calculation a two step process - calculate flow as CurrentValue - StationLastReading AND set StationLastReading = CurrentValue?

                       Thanks again.

                  • 6. Re: Get Field Value from previous date

                         To reference data from the previous record with the same station ID, use this relationship. You'll need an auto entered serial number field in your table so that each new record gets a larger serial number value than the preceding one.


                         StationData::_fkStationID = StationData|SameStation::_fkStationID AND
                         StationData::SerialNumber > StationData|SameStation::SerialNumber

                         Then specify a sort order for StationData|SameStation that sorts by serial number in descending order. Then, from the context of StationData a calculation can refer to fields in StationData|SameStation and it will reference the record for the same station with the largest serial number that is less than the current record's serial number--which will be the record with the value immediately preceding data.

                         Note: if you ever find that you need to enter station data in an order that is different from the order in which it was gathered in the field, you might use a date field in place of the SerialNumber field.