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.