Parent-Child relationships...literally

[EllenG]

I have an animal database. Each animal can have a dam and sire. I have calc fields in the main animal record that uses the dam-sire relationship from previous records so that I can create virtually unlimited ancestry for each animal.

It goes like this:

Animal record (unique Animal ID) contains 2 numeric fields:

Dam ID

Sire ID

2 relationships:

Dam (matches to related Animal ID for Dam's record)

Sire (matches to related Animal ID for Sire's record)

Just with that, I now have calc fields that build on each one down, as follows (d=dam, s=sire)

d_d = Dam::Dam

d_s = Dam::Sire

s_d = Sire::Dam

s_s = Sire::Sire

d_d_d = Dam:_d

d_s_d = Dam::s_d

d_d_s = Dam:_s

d_s_s = Dam::s_s

etc...

I take it to 4 generations in each animal (ie. d_d_d_d) for a total of 30 fields. So for any given animal, I can easily create the ancestry tree to 4 generations and can even drill down deeper by cascading from prior records.

What I need is a "backward" relationship; to find the progeny for a given animal (to 4 generations). Unlike the ancestry, however, which has only 1 dam and sire for any animal; that animal can have an unlimited number of progeny and progeny relationships.

So let's say Betty is the Dam for Joey (she will be in the Dam ID field for Joey. For Joey's offspring, Betty will be d_s (dam of sire).

Betty can be any of the following to any number of other animals (using the existing relationship codes):

d (Dam/Mother)

d_d (Dam to Dam / Maternal Grandmother)

d_s (Dam to Sire / Paternal grandmother)

d-d_d (Dam to Dam to Dam / Maternal great grandmother)

d_s_d

d_s_s

d_d_s

d_d_d_d

d_d_d_s

d_d_s_d

d_d_s_s

d_s_d_d

d_s_d_s

d_s_s_s

d_s_s_d

Same for Sire (all above beginning with s_)

I know I can run a script to pull out all the animals into a Progeny table and build relationships from that; but then I have to be sure both tables are maintained whenever an animal is added or a relationship added/changed.

Is there an "easy" way to build a portal for progeny of any selected animal; including the relationship (son/daughter; grandson...) with the existing animal table??

BTW: I have a progeny relationship now, but just to direct descendent (son/daughter only.)

See attached sample database.

As always, any help is appreciated.

Ancestry.fp7.zip

Edited July 19, 2009 by Guest
spelling error

[fabriceN]

In this type of relationship, I would recommand a 'genealogy' multiple key.

In your case, 2 fields (1 for the sire side genealogy, 1 for the dam's), and a calculated field listing the 2.

The two first fields should be auto enter calculations, appending current record ID to the parent genealogy list.

This way, you can go backward and forward, no matter how many generations you have.

The difficult part is if you need to change a parentID on one of the records. All descendants parentID must be modified too, in the generation order.

[comment]

I suggest you look at these:

http://fmforums.com/forum/showtopic.php?tid/135595/

http://fmforums.com/forum/showtopic.php?tid/184473/

http://fmforums.com/forum/showtopic.php?tid/190208/

http://fmforums.com/forum/showtopic.php?tid/198869/

[EllenG]

Thanks for the links. Although none did what I needed, the List Hierarchy example put me on the right track. The result turned out to be really easy (although it has taken me all day to figure it out!!) I am able to now get progeny by generation, exactly what I wanted. All I needed was 1 additional calc field and a relationship for each generation.

Thanks for the help. You guys are great.

[comment]

a relationship for each generation.

Take a look at the following post in the same thread.