2 Replies Latest reply on Jun 10, 2014 9:46 AM by philmodjunk

    Automatic population of fields using multiple search criteria?

    ChristopherKessler

      Title

      Automatic population of fields using multiple search criteria?

      Post

           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
      •      
      •           Gender
      •      
      •           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:

             
      1.           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.
                   
      2.      
      3.           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.

        • 1. Re: Automatic population of fields using multiple search criteria?
          ChristopherKessler

               In addiiton, I generally change the Cage ID for an animal in records on the Animal Info table. However, is it possible to do this, but also have the ability to have FM change the cage ID for an animal that I choose to manually enter in the "Female Parent ID" or "Male Parent ID" fields of the Cage Information table?

               Having this ability work together with the two schemes I outlined above would be nothing short of fantastic.

          • 2. Re: Automatic population of fields using multiple search criteria?
            philmodjunk

                 1. What you can use here is a conditional value list. See "option 1" in this link:Forum Tutorial: Custom Value List?

                 2. Do you have FileMaker 12 or newer? If so, you can use some calculation fields with ExecuteSQL to get a count of the number of Male and Female animals in the cage. If not, you can use some calculation fields as match fields to added table occurrences of the Animal Info table for relationships that match to only male and only female animals in the current cage. Either method can be used in a validation calculation or in a script performed by OnObjectValidate to ensure that you can't select "source" unless there is at least one male and one female.