I cannot follow your description. Is "flag" the same thing as "location"? If you have a FlagID in the Animals table, then you have a one flag has many animals type of relationship - and conversely each animal has only one flag and no history.
It sounds like you should have three tables, say Animals, Locations and Sightings - where Sightings would be a join table between the other two (IOW,. a many-to-many relationship between Animals and Locations). Then you could look at the last sighting for each animal, compare it to the one before, and calculate the distance between the two associated locations.
Thanks for responding Michael.
Flags were used to mark locations. Locations were measured at a later date. Each animal could have multilpe flags associated with it, and each flag may have multiple animals associated with it.
I'm really new at all of this, so a bit of clarification would be helpful.
I understand what you're saying about the three tables, but how do I get Filemaker to look up the last sighting, use the in that record to look up the associated flag/distance, and then subtract the most current distance from the looked-up distance?
I can think of several ways. Could you give us a rough idea of the numbers you expect to have: how many animals, how many locations and how many sightings?
BTW, what data do you have for a location that will enable you to calculate the distance? Is it latitude and longitude?
I expect 4000 sighting records, 1200 locations, and 660 animals.
My location data is a single number (meters moved). So the distance moved
would be straight subtraction of a single field.
Well, the simplest solution (I think) would be to lookup the Location::Meters datum into a field in Sightings. Alternatively, this could also be an unstored calculation field doing nothing except pull the number out of the related location record.
Then you could calculate the difference in the Animals table as =
Let ( [ n = Count ( Sightings::AnimalID ) ] ; GetNthRecord ( Sightings::Meters ; n ) - GetNthRecord ( Sightings::Meters ; n - 1 ) )
This is assuming that sightings are entered in chronological order and/or that the relationship is not sorted otherwise.
Great! My only concern is that the database already exists. Would the
GetNthRecord work for this case?
If the Sightings::Meters field is a calculation, it will work with no further ado; if it's a lookup, you'll need to perform a relookup on the exisitng records in order to populate it.
One last question. For the initial lookup of distance in Location::Meters, the Location and Sightings tables are related by multiple criteria (both must be met for the match. I'm having problems getting that code to work. Right now I have the calculation:
If ( Flag_Sighting = Location::Flag and Site= Locations::Site;GetField ( Location::Distance );"")
but I'm just getting the false test. Location and Sightings are related by both Site and Flag. The same Flag ID was used multiple times over the course of the experiment, but Flag and Site combination is unique.
Location and Sightings are related by both Site and Flag.
In such case, the formula for the calculation field in Sightings needs to be only =
BTW, what kind of animal moves in one dimension only?
Right you are. There was an underlying issue with me relationships.
Thank you for you all of your help!
Great question. No animal that I can think of moves in one dimension. I'm releasing amphibians in 2 m x 50 m pens and tracking them daily. Since their left to right movement is very confined by the pen, and I have no way of knowning if they went straight to their current location or zig-zagged all over the place, I'm just measuring their movement along the y-axis (0 - 50 m).