10 Replies Latest reply on Jul 27, 2012 6:00 AM by benchwrm11

Many to one relate and calculate issue

I've built a database for an animal tracking study. I have one table containing the Animal ID and a Flag ID marking the location of each animal I relocate. In another table I have Flag ID and the location of the flag. I'd like to get Filemaker to look up the last Flag ID of each animal, and calculate the distance moved (simple subtraction).

Currently, the Flag table and Recapture table are related by Flag ID.

Is that something easily accomplished?

• 1. Re: Many to one relate and calculate issue

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.

• 2. Re: Many to one relate and calculate issue

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?

• 3. Re: Many to one relate and calculate issue

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?

• 4. Re: Many to one relate and calculate issue

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.

• 5. Re: Many to one relate and calculate issue

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.

• 6. Re: Many to one relate and calculate issue

Great! My only concern is that the database already exists. Would the

GetNthRecord work for this case?

• 7. Re: Many to one relate and calculate issue

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.

• 8. Re: Many to one relate and calculate issue

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.

• 9. Re: Many to one relate and calculate issue

benchwrm11 wrote:

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 =

```Location::Distance
```

BTW, what kind of animal moves in one dimension only?

• 10. Re: Many to one relate and calculate issue

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