I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks and keeping records of the various crosses this entails. Both of the databases have a main table plus subtables for various things, and both interface with a pre-existing database. I have two questions that seem like they must have easy answers.
1. One database keeps track of individual families (screen shot). Each new family is assigned a stock number and stock name (among other variables). As part of each new family's record we need to include the stock numbers and names of the parents, which already appear earlier in the same database. Because each family will have two parents, I have these listed in a "parents" subtable (containing the parents' stock numbers) indexed to each new family record. To save time and effort I would like to have personnel simply type in the parents' stock numbers and have the corresponding stock names filled in automatically. I tried doing this by by defining a field in the parent's subtable to look up corresponding stock number and name in the main table but I can't get that to work; nor have I been able to get a self-referencing function to work right (e.g., by generating a second occurrence of the entire table).
2. In the second database, which keeps track of lines (=alleles), I would like to display through a portal all of the family stock numbers (plus some other info) in which that allele appears by accessing info in the first database (above). Unfortunately, each family record in the first database can have more than one allele and so these are contained separately in a subtable within the first database (indexed to stock number). So using this, I can lookup and display all occurrences of each allele and their associated stock numbers, but I can't access some of the other data (like stock names), which aren't used in the indexing of that subtable. When I include the other fields in the subtable it seems to complicate the indexing and data entry. The problem seems to boil down to whether or not I can engineer a way to lookup information that is itself being looked up by another table.
Thanks much for any advice