4 Replies Latest reply on Feb 26, 2014 12:29 PM by philmodjunk

    Performing calculation on most recent data



      Performing calculation on most recent data


           I'm pretty much a novice at FileMaker and I'm struggling to find out the best way to store and display data.  The data is physical information on a pond system, so things like "Acreage", "Volume", "Shoreline", "Maximum Depth", and "Average Depth".  We have 115 ponds and I currently have a FM data table with one record per pond.  This allows me to have a calculation field in the header that shows the totals of the physical data for the entire system (or a portion of the system if I do a find on a subset).  When we get new information about a given pond, say the pond has gotten wider and more shallow due to erosion, I've just been overwriting the old data with the new (keeping it at a total of 115 records).  What I have been coming to the realize though is that the old data is beneficial in tracking how the ponds change over time.

           So, I'd like to add a "date" to the physical data but how to do that is my dilemma.  If I add it to the Pond Data Table, I'll end up having more than one entry per pond and that will cause my "calculation" Totals in the header to be incorrect.  Is there a way to "perform a find" that gets the latest record for each pond?  Would that be a script in the header field?

           And to complicate things further, the records from the Pond Data Table are used in other related FM tables where calculations are performed on them; e.g. size of the pond size in acres and average depth in feet is used in a "dredging cost estimation" table.

           So......I guess I'm asking for advice on whether to create a new table, (possibly called something like Pond History), where there are multiple, individually-dated entries for the each pond and then just manually update the Pond Data Table with the latest information.....

           Or.....is there a way to put all the data in the Pond Data Table, with there being a default "find function" that finds and displays the most recent data for each pond and calculates the physical totals in the header.  And would the other related tables that link to that data be able to pick up only the most recent data....

           Or....if I created a new Pond History table, could the Pond Data table have a find function in it that "performed a find" for the last record for each pond and displayed it.  And would it be possible to have the related table link to the Pond Data table for their info.

           Thanks for any help, advice or guidance that anyone can provide.

        • 1. Re: Performing calculation on most recent data

               I would add a second table. Keep your current table as one record for each pond. But use a related table to log each new data set for a given Pond. This allows you to have multiple records for each pond and the record with the most recent date will have your current data on that pond.

               You can sort the relationship linking a record in your original table to this new table that sorts by the date field in descending order. Then you can easily reference the most recent related record of Pond data simply by referring to fields in the related table.

               Ponds:::__pkPondID = PondData::_fkPondID

               A portal to PondData placed on a Ponds layout could list all your PondData records for that pond with the most recent record listed first.

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

          • 2. Re: Performing calculation on most recent data


                 Thanks for the quick response.  As I was ruminating over it in the process of writing the post, that was seeming like the way to go.  I'll work on your suggestions but I'm probably going to have some more questions.  Thank you for the input though.

            • 3. Re: Performing calculation on most recent data

                   As an update, I created a second table named Pond List to display only 1 record per pond (total of 117).   I related this table to the original Pond Data table, with the intent of adding all new data to this original table.   I was able to link the records and create the descending sort of the date, displaying only the most recent record in the Pond List table, exactly what I was looking to do.  The question I have now though is how to sum or total all the displayed (most recent) data from all 117 records in the Pond List table.  Currently my field containing the calculation only gives a total for a single selected record, so only 1 record out of the 117.  Is there a way for the calculation to include all 117 most recent records?   

              • 4. Re: Performing calculation on most recent data

                     Define a calculation field in Pond List that returns the value from the related field for which you want the total.

                     Define a summary field that totals this calculation field.