Automatic population of fields using multiple search criteria?
Hi I'm using FM Pro 11, and am trying a unique setup for managing a pedigree tree for an animal colony. I've got one table for animal information, which includes the following:
- Animal ID
- Current Cage ID
- Source Cage ID (the Cage ID where its parents are housed)
- Mother ID
- Father ID
I have a second table for cage information, which contains the following:
- Cage ID
- Cage Type (value of either "Source" or "Non-Source")
- Female Parent ID (used if cage is a "Source")
- Male Parent ID (used if cage is a "Source")
I am able to set up the animal information table so when I enter its Source Cage ID, then the Mother ID and Father IDs (if present) are properly looked up from the "Cage Information" table. What I'm trying to do in addition are several things:
In the Animal Info table, have "Source Cage ID" be a drop down list or menu, but only have this show cages that have "Cage Type" set to "Source." Currently by using value lists, I can only get FM Pro to show all Cage IDs in this list, and not a subset of them.
- For Cage Types "Source," have FileMaker both ensure only one Male and one Female animal are in the cage, and then auto-populate the "Female Parent ID" and "Male Parent ID" fields with the respective animal IDs for the male and female in this cage. My guess is this can be done by triggering a validation script (or other means?) that will take the current cage ID and search the Animal Info table for entries that have this value. After ensuring only two animals of opposite gender are found, the script should then pull the ID for the female to place in the "Female Parent ID" field, and then do the same for the male parent.
The overall goal here is with these two cage and animal info tables, to populate animal info and cage info records with ID numbers, and then have FM automatically make associations for which animals are the parents of another animal.
My limitation is not knowing how best to implement this (using lookups and table relationships, and scripts, and then what commands to use in the scripts to do so). If anyone has insights on how to best do this, I would be most grateful.