Individual, Mothers, Father and Offspring... relationship issue

[Sholly]

Hi All,

I know that questions about genealogy are common, and I have searched the forums here for problems similar to mine. I have also read mr_vodka's page on recursive relationships. However, I am still having problems in that I cannot get my head around what I need to do. Let me explain:

I study chimpanzee mating behavior. I have a table called "Copulations" (sex, basically). This table contains the following fields:

Date (of copulation)

Time (of copulation)

FemaleID

MaleID

Parity - has the female ever had a baby before the day of the copulation

Pregnant - was the female pregnant on the day of the copulation

I have another table titled "Chimps". This table has all basic the information about each chimp, male or female. The fields include:

ID - chimp ID, same as male/female id above

Date of Birth

Date of Death

Mother - not always known

Father - not always known

I want to do 2 things:

1. create a portal in the "individual" layout that lists all offspring

2. have the Parity and Pregnant fields in the "copulation" table calculated by referencing the birthdates of an individual's offspring. In my mind, since filemaker knows the birthdates of all the individuals and their parentage it should be able to reference an individual's offsprings' birth dates.

In looking at all the topics on the forums, I think because I have entered the mother and father into the individual file (instead of offspring 1, offspring 2, offspring 3) I am getting confused. Also, chimps are promiscuous and there is really no way to add a "Family" table.

I hope I have explained things well.

Thank you,

Sholly

[comment]

The first part should be quite easy, once you change the Mother/Father fields in the Chimps table into MotherID and FatherID, and use them to define self-join relationships back to their parents and forward to their offspring - see a very similar example here:

http://fmforums.com/forum/showpost.php?post/198077/

The Parity field should be a field with auto-entered calculated result (since it needs to "freeze" the status of the female at the time of copulation). The calculation would be a count of the female's offspring, expressed as a Boolean.

The Pregnant field seems a bit more tricky, since it depends on something that is yet not known at the time the record is created (if I understand correctly). You'd probably want to run a script every time a new chimp record is created, to find any copulation records of the mother within the last ?? days, and update them.

Alternatively, either or both fields could be unstored calculations - but then you would need to add filtered relationships to show (1) female's offspring whose DateOfBirth is smaller than the Date in Copulations; and (2) female's offspring whose DateOfBirth is between the Date in Copulations and ?? days later.

Edited May 9, 2009 by Guest

[Sholly]

Hi "comment". Thanks a lot. You have helped me every time I've come here. Anyway, I've got the first part of the problem worked out now. Offspring are showing in a portal no problem. But I'm still a bit stumped on the second part of the problem.

I'm not sure which of your methods would be best for parity. If I calculate the number of offspring (and basically say <1 = NP, aka no babies) then I would somehow need to tell it to only count the offspring present before the date of the copulation. So... I think your alternative options may be best suited, though perhaps they need to be used together.

There is something that is totally going over my head here though. How do I create a relationship based on the DOB of offspring specifically, when offspring don't have their own "offspringID"? I think I am missing something very simple here.

I am attaching what I have so far so that you can see what I am doing. I'm embarrased that my field names are so messy, but I'm just doing this a bit rough and intend to clean it up (famous last words). You will also see that this data is already entered (and much of the info, like pregnant?, is already known), but the project is ongoing so more will be added in the future.

Chimps.zip

Edited May 9, 2009 by Guest

[comment]

I have added the two calcs and the necessary relationships to your file. However, I am not getting the same results as you indicate, so you will need to check which one of us has it wrong.

Chimps.fp7.zip

[Sholly]

Wow, thanks! I'll get to work putting these into my file (and trying to use the logic to do all the other things I need to do). It truly is a totally different way of thinking.

Your parity calculations are correct, but I haven't entered the dead babies yet. So all offsring are not in. Also, you either know a lot about chimps or took the time to look up the gestation period. Either way, I'm impressed.

[comment]

Off topic, a few notes about your file:

1. You have a serial ID defined in the Chimps table, but you're using a short name (which you call ID) field for the relationships. This is not the best practice (if you ever change the "ID", your relationships will break).

2. Some of your names are prefixed with "Inf'". Again, good practice calls for 'one fact per field'. If the prefix indicates a status, there should be a dedicated field for this.

3. Any time you find yourself creating multiple fields such as Rank1, Rank2, Rank3, etc., that's an indication you need another table.

[Sholly]

I think you're right and that I do need a new table for rank, because I just don't see how I can apply a chimp's rank to a record in another table (ex: what was male HO's rank when he copulated with BIN on 4/10/06?). I will look into the best way to do that. I have to hit my Filemaker Bible. I will have a similar issue with age class (infant, juvenile, adult) when I add that too. So probably best to figure it out now.

As for the Inf' IDs, I agree that it is not ideal. These are the infants of certain females. We don't give them real names right away because they die so often (and you can run out of names really fast), so it's really just a place holder that I will change later.

And that brings me to your next point. You are right that by linking through ID the relationships will break if I change their ID (as I will do one day when those infants survive and are assigned names). Those are really just the abbreviations I use when I am taking or reporting my data. SO, do you suggest that I give each chimp a "serial #" as well and link them through that? How would that work when entering data? You'd need to remember each chimp's ID # and enter that? Hmm....

Putting these things on my to-do list.

[comment]

I am not sure what a chimp's rank means (you're not training a chimp army, are you?), but if it changes from time to time, and you need to keep to track of the history, then you definitely should put that in a related table.

Regarding age, I believe you could look that up into a Copulation record from an unstored calculation field in the chimp record - or calculate it using the difference between Chimp::DOB and the local CopulationDate field.

You'd need to remember each chimp's ID # and enter that?

No, you could select the ID from a value list or from a portal or from a list view - in each of these you can show the name next to the ID (if your names are unique, you can even hide the ID and let it work "behind the scenes").

[Sholly]

I remember reading how to set up value lists like you're saying, so that they show a name and and ID#. So I think I will be able to do that no problem. I just need to go back and add the chimp ID# into all my current records.

Rank = dominance rank. Chimps have a linear hierarchy, so there is a #1, #2, #3, #4, etc... It changes quite often (I record new ranks yearly) as some males get stronger and some get weaker. It's mostly decided based on a chimp's ability to beat up other chimps. Anyway, I'm wondering what kind of table would be suited for that. Maybe this:

Table:

Dominance (and or age class)

Fields:

chimp ID#

Chimp

year

rank

age - based on years

age class - based on developmental stage

Does that seem right? It will require a lot more data entry on my end, which is not a problem. But I always start thinking I'm not doing something right when I end up having to enter (or re-enter) a lot more information.

Thanks again!

[comment]

I just need to go back and add the chimp ID# into all my current records.

Remember that if you have any related child records, you need to populate their foreign key field as well (you can do this using the existing relationship, before switching it to use the new ID's).

based on a chimp's ability to beat up other chimps.

Ah, a corporate structure. I think the fields you need are:

• ChimpID

• Rank

• FromDate

• cAge (a calculation using Chimps::DOB and FromDate)

You don't need the Chimp field, since it's available from the parent record in Chimps.

Similarly, the AgeClass can come from a related table where each class is a record {ClassName; FromAge}.