Self join set-up question

[Hurlz 0]

Hi, I have attached a mock-up database and schematic to explain this. Basically, I have a list of animals with lots of vet history. One aspect we want to track is the breeding history for each animal. Each female can be a Sam (mother), each male a Sire (father) and all animals could be Offspring.  I would like to look at the record for each Dam and Sire and see their offspring, as well as looking at each Offspring and seeing their parents. This could, of course, end up multi-generational where an individual animal is both a parent (Dam or Sire) and an Offspring.

Each animal has a unique ID. I have tried creating a Dam ID for each Dam, a Sire ID for each Sire and an Offspring ID for each offspring and relating them in joining tables - one for Sires and one for Dams.

So far, I can see the parents of each Offspring, but not the Offspring that belong to each Dam and/or Sire.  I'm stumped as to how to further set up relationships and portals for this.

 

Animal breeding records.fmp12 Breeding.pptx

[comment 1,817]

See if attached sketch can work for you. Note that it needs more work - for example, the choice of parents should be limited by sex.

--
See also this thread about sire, dam and herd: 
https://fmforums.com/topic/41352-how-do-i-create-new-record-if-certain-fields-are-filled-in/?do=findComment&comment=192957&_rid=72594

 

 

 

Lineage.fmp12

Edited March 16 by comment

[Hurlz 0]

Oh my goodness. You are freakin' amazing. That is perfect. Thank you so much