Auto-updating calculated values from a related table
I have a relational database in Filemaker Pro 9, which I am using to enter biological specimen collection information.
I have it set up so there is a main “Specimen” table, where I enter all information about the specimen and where it was collected. There is also a “References” table, which is a glossary of all Scientific Names, which include Genus, Species, and the associated Species Author (the people that named the species).
My relationship is set up with a References_Species table occurrence, which relates to Genus AND Species in my Specimen table. In my Specimen table, I have A_Species Author as an auto-calculated value References_Species::Species Author, so it will automatically be populated when the corresponding Genus and Species name is entered. So if you enter Genus A, Species B, it will populate with Species Author C, when there is a match in the Reference table. Within the Specimen table, this Species Author field updates fine, as if I changed it from Species B to Species C, it would populate with the corresponding Species Author.
What I’d like to figure out , is if there is a way to have these auto-calculated fields but automatically updated in my Specimen table when there is a change to a field in the Reference Table. In other words, if Genus A, Species B now has Species Author D. When I change Species Author C to D in my Reference table, this change is not automatically reflected in the auto-calc field in my Specimen Table. Instead, I need to select the field, and go into Replace Field Contents to re-calculate to new Species Author D. I’d like for the new Species Author D to be automatically and immediately reflected in the Specimen table in all corresponding records.
Does this all make sense? Is there a setting or something that I am now aware of to do this? Any advice is much appreciated!