7 Replies Latest reply on Nov 13, 2008 6:50 AM by TSGal

    Foreign key confusion

    akh

      Title

      Foreign key confusion

      Post

      I have a normalized relational database.  For example I have a particular type of Vendor associated with a Tissue type  (i.e. Lonza supplies a Tissue named Hela).

       

      The Tissue table has its own primary key, and the primary key for the Vendor table is a foreign key in the Tissue table.  There is also a similar primary key/foreign key relationship for Organisms (i.e. Homo sapiens, Mus musculus etc) in the Tissue table as well.

       

      My goal is to be able to create a composite sample entry point that would enable me to select the Vendor or Organism name while entering the Tissue specific information.  I have yet to be able to figure out how to do this.  I would expect that given the relation of the foreign key, I should be able to have it display the vendor/organism name from a drop down menu....again, I haven't figure this out either.

       

      Please..please...please help me!

       

      Andrew

       

       

        • 1. Re: Foreign key confusion
          TSGal

          akh:

           

          Thank you for your post.

           

          I'm not sure where you are entering the "Tissue specific information".  In another sentence, you say "There is also a similar primary key/foreign key relationship for Organisms in the Tissue table as well."  

           

          Let's assume you want a drop down menu that only shows the Organism name when you type in a key field.

           

          Once you set your relationship between the two tables, go to "Layout Mode" (View menu) and double-click the Organism field.  This brings up the Field Control/Setup window.  Change the "Display as" drop down menu from Edit Box to Pop-up Menu.  Next, click on the "Display values from" drop down menu and select "manage Value Lists..."

           

          At the next dialog box, click "New".  Give the Value List a name (not important), and check the option "Use values from field".  Click on the "Specify field..." button, and click on the "Use values from first field" drop down menu.  Choose the Tissues table and select the Organisms field froom the list.  Be sure to select "Include all values", and then exit by clicking OK to each open dialog box.

           

          You can now go to Browse mode, and when you enter a Tissue ID, only those Organism values for that Tissue ID will be displayed.

           

          Let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Foreign key confusion
            akh
              

            Ok, that helped.  I have actually been able to do that.  I guess I am just thinking about the data structure a bit differently.  Thank you so much for your timely response.

             

            Andrew

            • 3. Re: Foreign key confusion
              akh
                

              Ahhh, I just noticed one problem with this solution.  If you update the name of an Organism in the organism table....the update is not automatically done in the Tissue table.

               

              How do you get the change of an organism name to cascade to all of the other tables that might have this field?

              • 4. Re: Foreign key confusion
                Vaughan
                  

                "Ahhh, I just noticed one problem with this solution.  If you update the name of an Organism in the organism table....the update is not automatically done in the Tissue table."

                 

                It sounds as though your database isn't as normalised as you think. ;)

                 

                The Tissue table should be displaying the Organism name field from the organism table.

                • 5. Re: Foreign key confusion
                  akh
                    

                  It is normalized.  :P  That isn't the problem.

                   

                  If I create a record (i.e Record A) in the organism table, and use the pop-up menu that I have created in the Tissue table to select one of the records...that works just fine.  I end up with a Tissue table that has tissue specific information and a selected Record A for the Organism.  So everything seems fine up to this point.

                   

                  However, if I find out that Record A in the Organism table is actually mispelled, and should read Record AB.  If I modify the Organism Name in the Organism table to say RecordAB, the corresponding Organism Name field in the Tissue Table does not get updated to say RecordAB, it will still read RecordA.

                   

                  That is what I am trying to figure out how to fix.

                   

                  :)

                   

                  Andrew

                  • 6. Re: Foreign key confusion
                    akh
                      

                    Why wouldn't the updated field in one table cascade change to all the related tables in the database?  I have been looking online and such to find an answer and I haven't yet.  Any suggestions?

                     

                    Andrew

                    • 7. Re: Foreign key confusion
                      TSGal

                      Andrew:

                       

                      It sounds like you are trying to change the key field.  If the key field is changed, then you lose the link to any tables that use that key field.  You will need to change the key field in each of those tables to restore the link.

                       

                      TSGal

                      FileMaker, Inc.