10 Replies Latest reply on Jul 27, 2011 11:14 AM by philmodjunk

    Help on Established Relationships

    NaturSalus

      Title

      Help on Established Relationships

      Post

      I have the Foods table were several characteristics of foods are recorded. One of the characteristic is the scientific name of each food.

      I have established the relationships shown on the attached picture. What I would like to achieve is to select the Genus for each food type and have FM to fill in all the related fields: Kingdom, Phylum/Division, Class, Order, Family, Genus. There is a one to many relationship for the fields previously listed.

      In the Foods layout I have, among many others; the following fields:

      Kingdom::KingdomName

      Phylum_Division::PhylumDivisionName

      Class::ClassName

      Order::OrderName

      Family::FamilyName

      Food:Genus attached to the Genus Value List:"Genus::__kpGenusID" and "Genus::Genus"

      Food::Scientific Name

      So I choose the value for the Food:Genus field and I expect that by doing so, FM will fill in the values for the following fields:

      Kingdom::KingdomName

      Phylum_Division::PhylumDivisionName

      Class::ClassName

      Order::OrderName

      Family::FamilyName

      Any hint about what is wrong in the established relationship or layout is much appreciated.

      Thanks

      food_database.jpg

        • 1. Re: Help on Established Relationships
          philmodjunk

          "So I chose the value for the Food:Genus field and I expect that by doing so, FM will fill in the values..."

          I see a table occurrence named GenusFood and another named Foods. I see a field named _kfGenusID in GenusFood. Did you mean to say that you selected a value in GenusFood::_kfGenusID?

          Where did you expect to see the taxonomic categories to appear? In the fields of same name in foods? On a layout? There is more than one way you might do this. How did you try to do it and what results, if any, did you get?

          Note that GenusFoods is a join table that facilitates a many to many relationship between Foods and the taxonomic category tables, if you are trying to copy values into the Foods table from these taxonomic tables, the many to many relationship will be a problem as there could be more than one record in the taxonomic tables that links to your food record.

          Example as I read your screen shot of relationships:

          An "apple pie" Foods record would link to the species info for the wheat in the pie crust, the apples in the filling, the sugar cane or sugar beet plant that provided the sugar, the plant that provided the vegetable shortening and the corn plant that provided the cornstarch used as a thickener....

          • 2. Re: Help on Established Relationships
            NaturSalus

            Where did you expect to see the taxonomic categories to appear?  In the fields of same name in foods? On a layout? 

            In a layout based on the Foods table.

            I see a table occurrence named GenusFood and another named Foods.

            The GenusFood table occurrence was created to split the many to many relationship between the Foods and Genus tables.

            I see a field named _kfGenusID in GenusFood. Did you mean to say that you selected a value in GenusFood::_kfGenusID?

            As a matter of fact as I have it set up I use the Genus::__kpGenusID to select the Genus for the Foods:Genus field.

            Example as I read your screen shot of relationships:

            An "apple pie" Foods record would link to the species info for the wheat in the pie crust, the apples in the filling, the sugar cane or sugar beet plant that provided the sugar, the plant that provided the vegetable shortening and the corn plant that provided the cornstarch used as a thickener..

            So far it is much simpler. For example:

            Food Name = Almond

            Kingdom = Plantae

            Division = Magnoliophyta

            Class = Magnoliopsida

            Order = Rosales

            Family = Rosaceae

            Genus = Prunus

            Food Scientific Name = Prunus dulcis

            Note that GenusFoods is a join table that facilitates a many to many relationship between Foods and the taxonomic category tables, if you are trying to copy values into the Foods table from these taxonomic tables, the many to many relationship will be a problem as there could be more than one record in the taxonomic tables that links to your food record.

            Are you saying that I sould be getting all the needed information form the GenusFood join table?

            There is more than one way you might do this. 

            What other venues do you suggest. The final purpose is to select just the Genus in a layout on the Foods table and have all the other related fields (Kingdom, Phylum/Division, Class, Order and Family) filled.


            Thanks as always for your feedback

            • 3. Re: Help on Established Relationships
              philmodjunk

              That "more than one way" statement I made is a reference to the fact that you can refer  to data in other tables dynamically by placing fields from the related  table on your current layout or in a static fashion where the  information is physically copied into fields in your current layout's  table.

              As a matter of fact as I have it set up I use the Genus::__kpGenusID to select the Genus for the Foods:Genus field.

              And there's your problem. There's no link between Foods::Genus and the various taxonomic level tables. The link is from GenusFoods::_kfGenusID on to each taxonomic table in many to one chain of relationships. Any value you enter into Foods::Genus will not be able to produce the results you've specified without changing the structure of your database.

              Don't you need to see more than one such set of information for a given Foods record? That's what your GenusFood join table implies here. Can't one "food" have many species amongst its list of ingredients?

              Try this approach and see if it works for you:

              Put a portal to GenusFood on your Foods layout. Format GenusFood::_kfGenusID as a drop down list or pop up menu of values from Genus::__kpGenusID. (Field 2 can be Genus::Genus in this value list.) Add these fields: Kingdom::KingdomName, PhylumDivision::PhylumDivisionName, Class::ClassName, Order::OrderName, to the portal. You may need to resize your portal's row to include all of this information. (And a calculation field defined in GenusFood can combine all of these in a list separated by commas if you want to more efficiently display this list in the portal row.)

              Not only will this work with your existing relationships, but it will enable you to list multiple species for a given food if you make multiple selections in this portal to GenusFood.

              • 4. Re: Help on Established Relationships
                NaturSalus

                Put a portal to GenusFood on your Foods layout. Format GenusFood::_kfGenusID as a drop down list or pop up menu of values from Genus::__kpGenusID. (Field 2 can be Genus::Genus in this value list.) Add these fields: Kingdom::KingdomName, PhylumDivision::PhylumDivisionName, Class::ClassName, Order::OrderName, to the portal.

                Okay as per you suggestion (unless I have omitted something) I have the following set up:

                Portal in the Foods layout to the GenusFood table.

                Portal fields:

                GenusFood::_kfGenusID attached to the Genus value list. Value list setup: First field = Genus::__kpGenusID. (Field 2 can be Genus::Genus in this value list.)

                Family::FamilyName

                Order::OrderName

                Class::ClassName

                Phylum_Division::PhylumDivisionName

                Kingdon::KingdonName

                However when I select the Genus value from the Genus value list, I only get the Family field filled in. The rest of the upper level taxonomic levels or tables (Order, Class, Phylum_Division and Kingdom) remain empty.

                I have checked the records for all the tables and they contain the correct values.

                For example if I select the Genus= Prunus I get Rosaceae for the Family field.

                In other words, for the record with Food Name = Almond and Food Scientific Name = Prunus dulcis I would expect that, after selecting the Genus = Prunus, FM would fill in all the taxonomic fields as follows:

                Kingdom = Plantae

                Division = Magnoliophyta

                Class = Magnoliopsida

                Order = Rosales

                Family = Rosaceae

                Certainly something in my current structure doesn't make sense to FM but I don't know what.

                Thanks

                • 5. Re: Help on Established Relationships
                  philmodjunk

                  Hmmm, in theory, it should work, but you are asking FileMaker to "tunnel" through a lot of relationships to get the entire list. I'm going to throw together a simple demo file and see if I get the same behavior you do.

                  • 6. Re: Help on Established Relationships
                    NaturSalus

                    I have no problem sending you the whole project. In this way you can check what I did wrong.

                    Thanks

                    • 7. Re: Help on Established Relationships
                      philmodjunk

                      Looks like you'll need to check your data and relationship details again. The demo file worked just fine for me: http://www.4shared.com/file/NMf55P1t/TaxonomicLevelsDemo.html

                      • 8. Re: Help on Established Relationships
                        NaturSalus

                        Hello Phil

                        Thanks for the demo.

                        I am trying to find out the differences between your demo and my  project.

                        The only difference that I have found between the two is that for each taxonomic level, the foreign key is manually entered in your demo and in my project it is selected from a value list based on the primary key of the upper taxonomic level.

                        Let me explain it.

                        The taxonomic levels from upper to lower are:

                        Kingdom

                        Phyla

                        Class

                        Order

                        Family

                        Genus

                        Species

                        In your demo there is only one record for the Phyla taxonomic level:

                        Phyla ID: 1

                        PhylaName: Magnoliophita

                        KingdomID :1

                        Let's assume that we want to enter the Pinophyta Phyla (Division for Plants) in your demo.

                        So we press New Record, and FM creates PhylaID:2

                        and presents two empty fields one for the PhylaName and another for the KingdomID (foreign key).

                        Okay we manually enter Pinophyta in the PhylaName field.

                        But what happens with the KingdomID (foreign) field value? In your demo that value is supposed to be entered manually? How do I know which value corresponds to the Kingdom to which the Pinophyta Phyla (Division) belongs?

                        In my project, I would enter the Pinophyta value for the PhylaName field and would select the value for the KingdomID (foreign) field from the Kingdom value list (First field: Kingdom::__kpKingdomID; Second Field: Kingdom::KingdomName)

                        This is the only difference that I can detect between your working demo and my nonworking project.

                        Shouldn't the foreign key of each taxonomic level be selected from a value list based on the primary key of the upper taxonomic level?

                        What is the correct way to select the foreign key of each taxonomic level?

                        Thanks fopr your patience and time.

                        • 9. Re: Help on Established Relationships
                          NaturSalus

                          Phil,

                          Finally, I got it working!

                          The solution was to delete all the records for all the taxonomic levels and entering them again.

                          Many thanks for your help.

                          Every time you answer one of my questions I learn something new.

                          • 10. Re: Help on Established Relationships
                            philmodjunk

                            The only difference that I have found between the two is that for each  taxonomic level, the foreign key is manually entered in your demo and in  my project it is selected from a value list based on the primary key of  the upper taxonomic level.

                            I did not manually enter any Ids in the demo file. I used the Kingdom layout to enter a single record in each subsequent related table by adding the name fields from the lower taxanomic levels to this table view and then typing a name in the column for each level. That only works to create the first set of taxonomic records for a given Kingdom record--but it was a fast way to make sure that the relationships were valid at each point and that properly matching values where entered in each record.

                            "Shouldn't the foreign key of each taxonomic level be selected from a  value list based on the primary key of the upper taxonomic level?"

                            Of course it should!

                            This demo was intended to make sure there were no instrinsic issues with including fields from such a long chain of related tables inside the portal row and it clearly verifies that this works. Thus, the issue lies with either the values entered in your taxonomic tables or in the field types (Fields on both sides of a relationship should be the same type--both text or both number.) You'll need to investigate and see where things go wrong. You may also need to review each of your value lists to make sure that you are correctly selecting from the correct table in each case and aren't accidentally entering, say the FamilyID when you should be entering the OrderID.

                            You might also use a portal from Kingdom to list/edit/create all Phyla/Divisions linked to that Kingdom and so forth down through each level.

                            This all takes me back to my college days taking biology/botany/zoology. An instructor taught me this mnemonic: King David/Philip Came Over For Good Sealed as a way to keep the taxonomic groups straight. Wink