5 Replies Latest reply on Jan 17, 2012 2:06 PM by philmodjunk

    Auto-updating calculated values from a related table



      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!

      Thank you,

        • 1. Re: Auto-updating calculated values from a related table

          You can remove the auto-entered calcualtion field from your database. In each case where you have a layout that used it, replace it with the related Species Author field from the table where you originally entered this data. Now, since it is the same field, it will update automatically each time this data is changed.

          Design Note: I suggest that you NOT relate your tables by Genus and Species fields. While this pair of names should be unique in all cases, it requires that you perfectly enter these two names each time you add a new record. If a data entry error should take place when entering one of these two names and then related records based on this relationship, correcting the problem can be a challenge to do without breaking the link to related records when you correct the mistake. Also, the powers that be have been known to change the taxonomic nomenclature for a given species and this will create the same issue for you. (The brontosaurus, for example is now known as an Apatasaurus to give an example.)

          Instead, enter your genus and species names in one table where you also define a single serial number field as the Species ID. Use this field to link your table to other tables by genus and species.

          • 2. Re: Auto-updating calculated values from a related table

            Hi Phil,

            Thanks as always for your prompt reply.  I changed it to the related field from References_SpeciesAuthor and it works great.

            On the design note:  I understand that an error in entry of Genus and/or Species will result in a broken link.  In this, even if there is a 'space' following the Genus name, the Author will not populate.  I am okay with this because it will be a 'flag' to the data entry person that this record is one to double check or look at more closely.  However, what do you mean when you say correcting can be a challenge to do without breaking the link to related records when I fix it?  In this case, I just remove the 'space', and the Author is successfully populated.  Likewise, when I import records into the Specimen table (populating Genus & Species), the Species Author is automatically populated by pulling from the References table.  Likewise, when a taxonomic name is updated (as with brontosaurus), I can change the name of the record in the specimen table and have it re-auto enter the new Species Author.

            Can you clarify your 'instead'?  I have a ReferenceID that is automatically created in my Reference Table, and I have this linked to my Specimen Table's ReferenceID (not auto-populated, just a number field).  How else would I tie Genus and Species together without linking by them?

            Let me know if anything is unclear.

            Thanks again,

            • 3. Re: Auto-updating calculated values from a related table

              It's a basic issue for any field you use as a relationship key that comes from an external source instead of being auto-generated by FileMaker.

              Say you have a record in the parent table for "brontosaurus" and created 20 records in three related tables linked to it by genus and species. Then the powers that be decide that the Apatasaurus and Brontosaurus are one and the same animal and decree that "Apatasaurus" is the correct name to use. If you then find the record in your main table and change the name. the 20 related records are no longer linked to your main table record. To re-connect them, you have to find all of these records and update them with exactly the same names as that of your main table record. Correcting a typo in either name produces exactly the same potential scenario.

              If, on the other hand, you base your relationship on an FMP generated serial number, you'd just find the main table record and change the appropriate name field. All other tables that need to access the name should refer to the name fields in the main table record and since the relationship has not changed, updating the name in one record is all you need to do to deal with such a name change.

              Note that I'm making this recommendation with regards to what I think of as the "backbone" or data level relationships that define the central structure of your database. As a means to supplement that structure, especially for enhanced methods of searching out records in your database or computing specific subtotals, etc. a "name based" relationship may be necessary, but since these are not the basic "data level" relationships, the consequences of such a name change are not as severe and more easily handled than if it were part of a central "backbone" relationship. The key rule of thumb that I follow is to never use a name or other "meaning" based field for a relationship if there is a pratical alternative approach that uses the serial number field instead. (And with the advent of filtered portals in FileMaker 11, the need for such "search" relationships is greatly diminished.)

              • 4. Re: Auto-updating calculated values from a related table

                Hi Phil,

                I'm afraid I don't know how to do what I've done using just ReferenceIDs.  Apologies as I am still learning relational databases and all the capabilities of Filemaker!

                I have each name in my Reference Table having its own Reference ID, but I do not know how to get the correct Species Author to generate if I do not have a table occurrence based on References, relating Genus AND Species.  How do I get the correct Species Author to show up if I do not do this? 

                It would also be great if I could have the related ReferenceID display on my Specimen table, so that I could perform a find for the Reference ID and see all the related records...

                Thanks as always,

                • 5. Re: Auto-updating calculated values from a related table

                  It doesn't sound like "reference Id" is the right field to use.

                  I'm assuming that you have a table in your database that has one record for each unique combination of species and genus name used in your database. Ideally, the only fields for recording genus and species would be in this table. An auto-entered serial number field defined in this table would then uniquely identify each such record in this table. All other tables that need to relate by Genus/Species would link to this table by the serial number field defined in this table.

                  Making such a change in your database could be very easy or take quite some time to do. I can't tell you at this point as I do not know what tables you have defined in your database and how they are related to each other. The basic process involves adding the serial number value to this one central table first, then updating a matching field with the same value using your existing name based relationships. Once that is done, you can change the relationships over to the new serial number fields and remove the extra name fields, replacing them on layouts with fields from this central table of species whereever needed.