4 Replies Latest reply on Dec 30, 2013 6:33 AM by philmodjunk

    I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks...

    DavidParichy

      Post

           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

           Dave

      db1.jpg

        • 1. Re: I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks...
          philmodjunk

               1) The lookup you describe should work--especially if set up with a self join relationship. You'll need to make a more detailed description of that (this could be one thread all its own here in the forum) before we could spot why it didn't work for you.

               2) I think that you have a many to many relationship here. Am I correct that an Allele can be part of the genome for multiple families and each family can list multiple allele's?

               Assuming that I am correct, you'd need something like this:

               Families-----<Family_Allele>-----Alleles

               Families::__pkFamilyID = Family_Allele::_fkFamilyID
               Alleles::__pkAlleleID = Family_Allele::_fkAlleleID

               You can place a portal to Family_Allele on the Families layout to list and select  Alleles records for each given Families record. Fields from Alleles can be included in the Portal to show additional info about each selected Alleles record and the _fkAlleleID field can be set up with a value list for selecting Alleles records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks...
            DavidParichy

                 Thanks much for the quick reply. Will study your suggestions and try out in the next couple days.

                  

            • 3. Re: I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks...
              DavidParichy

                   Hey

                   The self-join relationship (#1) seems to be working now. (I'm not quite sure what I did differently.)

                   I still can't solve the other look-up problem (#2), in which I want to display data in the Allele table the relevant data that had been entered in the Family table.  I think I understand the relationships and nomenclature but it still isn't pulling out all the information I need and the problem seems to be in the Family_Allele table not being able to retrieve the data from its parent Family table (I'm assuming that if this is resolved then the existing portal to Family_Allele in the Allele table will be able to look-up the look-up data).

                   I've attached a screen shot. The problem relationships are on the left. In the image


                   "Family_Allele" = "_BreedingLog_Alleles"

                   "Family" = "_BreedingLog"

                   "Allele" = "_Fish"

                   In "_BreedingLog_Alleles", Stock Name, Fertilization, Initials and Date are all set to look-up corresponding fields in "_BreedingLog".

                   I did set up Allele in "_BreedingLog_Alleles" as a drop down menu (with value list) but I'm not sure how to use this in the look-up feature (or perhaps I misunderstood and I need to use a script to generate the value list?).

                   Thanks much

                    

              • 4. Re: I'm using FM Pro to construct a couple of databases for maintaining several hundred genetic stocks...
                philmodjunk
                     

                          I did set up Allele in "_BreedingLog_Alleles" as a drop down menu

                     While you can use that to link a record in _Fish to a record in _BreedingLog_Alleles that may not be sufficient to do your "look up". Once you have used the join table to link records in _Fish and _BreedingLog, Either a portal to BreedingLog_Alleles or a portal to BreedingLog can be used to display data from the BreedingLog table.

                     Aggregate data such as a count, sum or average is also possible by defining calculation functions in _Fish that reference fields in _BreedingLog.

                     If you are using one of these methods and the data is not appearing, then it is most likely the case that the needed join table record with correctly matching values does not exist.