5 Replies Latest reply on Oct 15, 2013 4:37 PM by philmodjunk

    Automatic foreign key populated from a primary key for several tables

    ngarnier

      Title

      Automatic foreign key populated from a primary key for several tables

      Post

           I am a complete Filemaker novice, so please bear with me.

            

           ISSUE: I have a database with lots of tables (like 30 tables), and I need the "subject number id" from the 1st table, the primary key, to automatically populate into a drop down or pop up on all the other tables (into the foreign keys) so that the new record on the next layout/table is related/associated to that particular subject number.

           ie - if I had 3 tables, I would need the foreign key field in the 2nd and 3rd table to pull in the primary key info from the 1st table as a drop down or pop up. 

           I want my end user to be able to click into the foreign key field and get a list of primary keys from the 1st table then can just choose.  I don't want them to have to click "new record" for every layout they go to to enter data.

           All of my foreign key text fields are unique and are related in the relationship graph to the one unique primary key field.

      database_relationships1.png

        • 1. Re: Automatic foreign key populated from a primary key for several tables
          philmodjunk

               You discuss two different requirements: 1) A drop down list for selecting the ID of a parent record in table 1 and not needing to Select New Record. Selecting New Record does nothing by itself to put the value of the primary key in to the new record's foreign key field....

               I think this is what you have in mind but it's a bit of a guess:

               Define a field with global storage. You'll be able to copy and paste this field into any layout based on any of the many child tables.

               Format it with a value list of all values from Table 1's Primary key field for field 1 and another field such as "cohort" for field 2 to enable the user to select the correct primary key from the drop down.

               Use the OnObjectSave script trigger (or onObjectModify if using pop up menu format), to perform this script:

               New Record/Request
               Set Field by Name [ Get ( ScriptParameter ) ; YourTable::GlobalFieldWDropDown]

               When you paste this control onto each layout, you'd update the script trigger on the newly pasted copy to pass this script trigger expression:

               GetFieldName ( Layout'sTableocccurrence::foreignKeyField ) //but use the actual field where I have used a place holder here.

               So that the correct reference for that layout's foreign key is passed to the script.

          • 2. Re: Automatic foreign key populated from a primary key for several tables
            ngarnier

                 OK. please note subject = patient, it's a people table

                  

                 Define a field with global storage. I turned the subject number field on the main page (1st table) into a global stage field and it gave every record the same number, not what I need.  every record should be a different patient.

                 You'll be able to copy and paste this field into any layout based on any of the many child tables. Do I need to copy this field to all the layouts?  Is it wrong the way I have it with the subject number field related to all the "subjectnumberfield" text fields in the other tables?

                 Format it with a value list of all values from Table 1's Primary key field for field 1 - I'm not sure how to do this. Allthe values will be entered in later, not sure what this means.  I see the choices for "value from last visited record", "calculated value", and looked-up value" should I use one of these?

                 and another field such as "cohort" for field 2 to enable the user to select the correct primary key from the drop down. ??

                 Use the OnObjectSave script trigger (or onObjectModify if using pop up menu format), to perform this script: where are I applying this script? to a field to the scripts , where am I putting this?

                 New Record/Request
                 Set Field by Name [ Get ( ScriptParameter ) ; YourTable::GlobalFieldWDropDown]

                 When you paste this control onto each layout, you'd update the script trigger on the newly pasted copy to pass this script trigger expression:

                 GetFieldName ( Layout'sTableocccurrence::foreignKeyField ) //but use the actual field where I have used a place holder here.

                 So that the correct reference for that layout's foreign key is passed to the script.

            I'm trying to get the same record, but different layout/table per patient. 1 record = 1 patient with data on many many tables

                  

            • 3. Re: Automatic foreign key populated from a primary key for several tables
              philmodjunk
                   

              I turned the subject number field on the main page (1st table) into a global ...

                   No. Define a NEW field and give it global storage. This field is not used as a key field, it's used as a holding place for the selected primary key's value until the script creates a new record and sets the foreign key field to it's value to link it to the correct record in the parent table.

                   

              Do I need to copy this field to all the layouts?

                   No, you can do this for any layout based on one of the related child tables where you want this capability.

                   

              I'm not sure how to do this...

                   Select Manage | Value LIsts. Click New. Give the new value list a name and select "Use values from a field". In the next dialog that appears, select Main Screen from the top, left drop down. Then click Subject Number in the list of fields that appear. Then click the "also use values from" check box and select a text field from the same table so that it will also appear in the value list. Each time you create a new record in Main Screen, the values from these two fields will be automatically added to your value list.

                   

              where are I applying this script? to a field to the scripts , where am I putting this?

                   Select this for the new global field that you have defined and have added to your layout with the drop down list or pop up menu format. Right Click it while in layout mode and select Scirpt Triggers from the menu that pops up.

                   One more thing about fields with global storage. A value stored in a global field is accessible from any layout and any record in your database file. That's what allows you to add this control to any layout based on one of the child tables where you want this capability.

              • 4. Re: Automatic foreign key populated from a primary key for several tables
                ngarnier

                     I think it worked!!!  I thought I was doing something wrong because the global storage field was just creating a new record every time I choose from the drop down list, but that's because I had it on the Main Screen layout.  On the other layouts that's exactly what I need it to do.

                     BUT, question, does my relationships graph matter at all?  Should I change the links or are they ok as is?  Like does it even matter/factor in?

                      

                     You just saved me from a lot of pain and tears, I've been banging my head against this for DAYS.  THANK YOU very much and God Bless. :)  cheeky

                • 5. Re: Automatic foreign key populated from a primary key for several tables
                  philmodjunk

                       The relationships are not used in this script, but chances are that you will need them for other reasons.

                       And this is just one of many methods that might be used to create new related records. It was my best guess as to what you wanted to do.