What is the best/most efficient way to achieve this:

I have a table called "Animals". There are 3 pertinent ID fields:

Animal_ID (unique table ID)

Sire_ID

Dam_ID

The dam and sire IDs are unique Animal_IDs related to the "child" Animal_ID. A "Child" Animal_ID may be a dam or sire in other "child" record, but not all "child" IDs will be parents.

I need to create a portal that only includes Ancestry or "parent" (dam and sire) animals. It would be a subset of the full animal list.

I've tried several ways to achieve this, but can't seem to get either a calc field or list function to work.

Thanks.

When you say you want to see these parent (limited to two) or ancestor (potentially very many) records in a portal, that implies that you have a record on which you are displaying the portal.

What record are you browsing when the portal is displayed? Is it the animal whose parents or ancestors you want to see?

If that is the case, the relationship for the parent records is straightforward.

You can create a calculated field on the animal's record that is composed of the sire_id and dam_id on separate lines. Use this as the key to relate to the animal_id on the Parent instance of the animal table.

Listing the ancestors is more complicated.

One method is to create a series of relationships connecting and table occurrences for each animal in the genealogy as far back as you want to go. With these relationships in place (connecting the sire's sire and sire's dam onto the sire table occurrence, and so on), you now can create a calculation on the animal's record that has one ancestor id per line where you are picking ids from these various ancestor table occurrences. Use this calculation as a key to the animal_id in a new table for all ancestors.

If you want to generate a key to list all ancestors no matter how far back, you may want to create a text field for the list of ancestor ids and populate it with a script that methodically wanders back along the genealogy visiting every ancestor and keeps track of all of the ids it has visited.

Edited July 31, 200916 yr by Guest

If you want to show a list of only records that are parents, you need a way to identify those. Looking a records, you will ask, "Is this record a parent", or, "Is this record ID used in either the Sire or Dam IDs in another record?"

So, create two relationships: one for the Sire and one for the Dam. Then, if there is a related record in either relationship, return a 1. Then you can search for all the "1"s, or you can display those records in a portal via a relationship that matches to the value "1".

Make sense? Please reply if you would like more details. Good luck!