14 Replies Latest reply on Jan 16, 2014 8:36 AM by philmodjunk

    Auto-populate child foreign key from parent key

    evanscl

      Title

      Auto-populate child foreign key from parent key

      Post

           I have three tables (parent, child, and grandchild tables) that are related to each other using primary and foreign keys.
            
      Parent Table: CollectionSite
      Child Table: TrapInfo
      Grandchild Table: TestInfo
            
           The screenshot below shows the relationship as follows:
            
      Parent Primary Key to Child Foreign Key:Location_ID_pkey to Location_ID_fkey
      Child Primary Key to Grandchild Foreign Key: TrapInfo_ID_pkey to TrapInfo_ID_fkey
            
           How do you automatically populate the child's foreign key (fKey) with the parent's primary key (pKey) after you select "New Record" in the toolbar to take you to the child's layout?
            
           Would you use the same method for automatically populating the grandchild's foreign key (fKey) with the child's primary key (pKey)?

      FMscreenshot_Relationships.jpg

        • 1. Re: Auto-populate child foreign key from parent key
          philmodjunk

               New Record doesn't change layouts automatically. That must be a button or custom menu set up for that purpose.

               The simplest method for creating child records is to use a portal with "allow creation of records via this relationship" enabled for the portal's table. you can then add records in the portal and the foreign key field of the new records are updated for you by FileMaker.

               With your method where you change layouts and then create a record on the child table's layout, you could use this script:

               Set Variable [$ID ; value: ParentTable::primaryKey ]
               Go to Layout ["Child" (ChildTable) ]
               New Record/Request
               Set field [Child::ForeignKey ; $ID ]

               A slightly more sophisticated option is to use:

               Set Variable [$$ID ; value: ParentTable::primaryKey ]
               Go to Layout ["Child" (ChildTable) ]
               New Record/Request

               and then define Child::ForeignKey with an auto-enter calculation:

               $$ID

               This allows you to change to the child layout and create as many new records as you want as each will automatically enter the value of $$ID as the foreign key value. The trick is to make sure that your users cannot get to the child table layout and start creating new records with out starting from the parent layout or you'll get disconnected records.

          • 2. Re: Auto-populate child foreign key from parent key
            evanscl

                 So, if I adopt the "slightly more sophisticated option" to a button that takes me to the related layout, would the script look like this?:

                 If [IsEmpty (Child::ForeignKey) // no related record]

            Set Variable [$$ID ; value: ParentTable::primaryKey ]

            Freeze Window

                 Go to Layout ["Child" (ChildTable) ]

                 New Record/Request

                 Set field [Child::ForeignKey ; $$ID ]

                 Else
                 Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                 End If
            • 3. Re: Auto-populate child foreign key from parent key
              philmodjunk

                   You might want to use the Set variable step to set $$ID with the go to related records step as well. That way, any new records created on the child layout will automatically link to the current Parent record on the original layout.

              • 4. Re: Auto-populate child foreign key from parent key
                evanscl

                     So, I should re-write the script like this?

                     If [IsEmpty (Child::ForeignKey) // no related record]

                Set Variable [$$ID ; value: ParentTable::primaryKey ]

                Freeze Window

                     Go to Layout ["Child" (ChildTable) ]

                     New Record/Request

                     Set field [Child::ForeignKey ; $$ID ]

                     Else
                     Set Variable [$$ID ; value: ParentTable::primaryKey ]
                      
                     Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                     End If
                • 5. Re: Auto-populate child foreign key from parent key
                  philmodjunk

                       or:

                       Set Variable [$$ID ; value: ParentTable::primaryKey ]

                       If [IsEmpty (Child::ForeignKey) // no related record]

                  Freeze Window

                       Go to Layout ["Child" (ChildTable) ]

                       New Record/Request

                       Else
                       Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                       End If
                        
                       And keep in mind that an auto-enter calculation is what is also needed to auto-enter the value of $$ID into each new Child record's foreign key field.
                  • 6. Re: Auto-populate child foreign key from parent key
                    evanscl

                         Thank you so much. The script works great!

                    • 7. Re: Auto-populate child foreign key from parent key
                      evanscl
                           The script below was written for a button, which when clicked will take you to a related record and auto-populate the child's foreign key with the parent's primary key. 
                            
                      Set Variable [$$ID ; value: ParentTable::primaryKey ]
                                 
                      If [IsEmpty (Child::ForeignKey) // no related record]
                                 
                      Freeze Window
                                 
                      Go to Layout ["Child" (ChildTable) ]
                                 
                      New Record/Request
                                 
                      Else
                      Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                      End If
                            
                           Originally, the button did not work if no Child record had been entered yet. So, the first IF statement -- If [IsEmpty (Child::ForeignKey) // no related record] -- was added so that if no child record existed yet, then the button would take you to the Child table and request a new record. Else, the button would take you to an existing Child record so that you could continue adding more Child records.
                            
                           The first statement -- Set Variable [$$ID ; value: ParentTable::primaryKey ] -- was written so that the Child's foreign key would equal the Parent's primary key, so that a relationship would be established between the Parent and Child tables. Within the Child table, the Child::ForeignKey (formatted as a Number) was set to auto-enter the calculation, Child::ForeignKey = $$ID.
                            
                           As is, the script is not working correctly. So far, I have two parent tables written, Parent_1 and Parent_2.
                            
                           The Parent_1 table has a related Child_1 table.
                            
                           In the Parent_2 table, when I click the button to take me to a child table that has not yet been entered, I am taken to the Child_1 table under the Parent_1 table rather than being taken to a new, empty child record under the Parent_2 table.
                            
                           How would you re-write the script to accomplish three things: (1) take me to a new, empty child record if one does not exist yet, (2) take me to an existing related child table so that I can create new child records, and (3) auto-populate the child's fKey with the value from the parent's pKey?
                      • 8. Re: Auto-populate child foreign key from parent key
                        philmodjunk

                             I can't quite parse the details here nor see why there is a problem with the script as written. Did you forget to define the needed auto-enter calculation? How exactly is it not working?

                             I would recommend that you repost this part:

                             
                                  As is, the script is not working correctly. So far, I have two parent tables written, Parent_1 and Parent_2.
                             
                                   
                             
                                  The Parent_1 table has a related Child_1 table.
                             
                                   
                             
                                  In the Parent_2 table, when I click the button to take me to a child table that has not yet been entered, I am taken to the Child_1 table under the Parent_1 table rather than being taken to a new, empty child record under the Parent_2 table.
                             
                                   
                             
                                  How would you re-write the script to accomplish three things: (1) take me to a new, empty child record if one does not exist yet, (2) take me to an existing related child table so that I can create new child records, and (3) auto-populate the child's fKey with the value from the parent's pKey?
                             But substituting the actual names shown in the screen shot in your original post so that I can match the terms up to the relationships that you have in place. As I read it now:
                             
                                  In the Parent_2 table, when I click the button to take me to a child table that has not yet been entered, I am taken to the Child_1 table
                             Simply indicates that your script is taking the user to the wrong layout and probably referencing fields from the wrong table. That seems too simple a problem however, as it can be fixed simply by setting up two scripts, one for the layout for Parent_1 and one for the layout for Parent_2.
                              
                             And yes, you could use one script for both, but you'd need to rewrite things to use different Go to Related Records steps and either separate Go to Layout steps or one of the "calculation" options in that step with a script parameter specified for the button in order to specify the correct layout.
                        • 9. Re: Auto-populate child foreign key from parent key
                          evanscl
                          Re-Posted with More Detailed Explanation:
                                     
                                    I have three tables (parent, child, and grandchild tables) that are related to each other using primary and foreign keys.
                                     
                          Parent Table: CollectionSite
                          Child Table: TrapInfo
                          Grandchild Table: TestInfo
                                     
                                    The relationships between the tables are as follows:
                                     
                          Parent Primary Key to Child Foreign Key: Location_ID_pkey to Location_ID_fkey
                          Child Primary Key to Grandchild Foreign Key: TrapInfo_ID_pkey to TrapInfo_ID_fkey
                                     
                                    There are three layouts:
                                      
                          1.                Collection Site
                          2.           
                          3.                Trap Info
                          4.           
                          5.                Test Info
                          6.      

                                    This is a database for testing mosquitoes for mosquito-borne viruses, and it consists of three parts:

                                      
                          1.                The first layout is the Parent Table and is called "Collection Site". Mosquitoes will be collected at different locations in the state; hence, there will be many Collection Sites (Parents) entered.
                          2.           
                          3.                The second layout is the Child Table and is called "Trap Info". At each of the collection sites, mosquitoes will be trapped, using various trap methods, on different dates throughout the year; hence, there will be many different trap dates (Children) in which mosquitoes are trapped at a particular Collection Site.
                          4.           
                          5.                The third layout is the Grandchild Table and is called "Test Info". For each of the dates trapped at a particular Collection Site, various mosquito species will be caught and tested for mosquito-borne viral diseases; hence, different mosquito species will be grouped into samples (Grandchildren) and tested together for viruses.
                          6.      

                                    In summary, different mosquito species/samples (Grandchildren) will be trapped on various dates throughout the year (Children) at different collection sites (Parents).

                                
                               The script below was written for a button, which when clicked, will take you to a related record and auto-populate the child's foreign key with the parent's primary key. 
                                
                          Generic Values:
                                
                          Set Variable [$$ID ; value: ParentTable::primaryKey ]
                                     
                          If [IsEmpty (Child::ForeignKey) // no related record]
                                     
                          Freeze Window
                                     
                          Go to Layout ["Child" (ChildTable) ]
                                     
                          New Record/Request
                                     
                          Else
                          Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                          End If
                                
                          My Actual Values for the Go to Related Record Script (from Parent Table to Child Table):
                          A button, when clicked, will take you from the Parent Table (Collection Site) to the Child Table (Trap Info)
                                
                          Set Variable [$$CollectionSiteID ; Value: CollectionSite::Location_ID_pKey ]
                                     
                          If [IsEmpty (TrapInfo::Location_ID_fKey) // no related record]
                                     
                          Freeze Window
                                     
                          Go to Layout ["TrapInfo" (TrapInfo) ]
                                     
                          New Record/Request
                                     
                          Else
                          Go to Related Record [From table: "TrapInfo": Using Layout: "TrapInfo"(TrapInfo)]
                          End If
                                
                          My Actual Values for the Go to Related Record Script (from Child Table to Grandchild Table):
                          A button, when clicked, will take you from the Child Table (Trap Info) to the Grandchild Table (Test Info)
                                     
                          Set Variable [$$TrapInfoID ; Value: TrapInfo::TrapInfo_ID_pKey ]
                                          
                          If [IsEmpty (TestInfo::TrapInfo_ID_fKey) // no related record]
                                          
                          Freeze Window
                                          
                          Go to Layout ["TestInfo" (TestInfo) ]
                                          
                          New Record/Request
                                          
                          Else
                          Go to Related Record [From table: "TestInfo": Using Layout: "TestInfo"(TestInfo)]
                          End If
                                
                               ****
                                
                               Originally, the button did not work if no Child record (TrapInfo)  had been entered yet. So, the first IF statement -- If [IsEmpty (TrapInfo::Location_ID_fKey) // no related record] -- was added so that if no child record (TrapInfo) existed yet, then the button would take you to the Child table (TrapInfo) and request a new record. Else, the button would take you to an existing Child record (TrapInfo) so that you could continue adding more Child records (TrapInfo records).
                                
                               The first statement -- Set Variable [$$CollectionSiteID ; Value: CollectionSite::Location_ID_pKey ] -- was written so that the Child's foreign key (TrapInfo's foreign key) would equal the Parent's primary key (CollectionSite's primary key), so that a relationship would be established between the Parent (Collection Site) and Child (TrapInfo) tables. Within the Child (TrapInfo) table, the Child::ForeignKey (TrapInfo::Location_ID_fkey), formatted as a Number, was set to auto-enter the calculation, Child::ForeignKey = $$ID (TrapInfo::Location_ID_fkey = $$CollectionSiteID).
                                
                               As is, the script is not working correctly. So far, I have two parent tables written, two Collection Sites (A mosquito collection site in Georgetown County and a mosquito collection site in Berkeley County).
                                
                               At the Georgetown County Collection Site (Parent_1), mosquitoes were collected on 8/26/2013 to 8/29/2013 (Child_1) and on 9/9/2013 to 9/13/2013 (Child_2). From the dates 8/26/2013 to 8/29/2013, three mosquito samples will be tested (Grandchild_1, Grandchild_2, and Grandchild_3). From the dates 9/9/2013 to 9/13/2013, two mosquito samples will be tested (Grandchild_1 and Grandchild_2).
                                
                               For the Berkeley County Collection Site (Parent_2), no mosquito trap dates have been entered yet (i.e., no Child Tables have been created yet). When I click the button to enter the trap dates at that Collection Site (i.e., take me to a related Child Table that does not have any records entered yet), I am taken to the Georgetown County/Trap Dates 8/26/2013 to 8/29/2013 (Child_1 table under the Parent_1 table) rather than being taken to a new, empty child record under the Berkeley County Collection Site (Parent_2 table).
                                
                               How would you re-write the script to accomplish three things: (1) take me to a new, empty child record if one does not exist yet, (2) take me to an existing related child table so that I can create new child records, and (3) auto-populate the child's fKey with the value from the parent's pKey?
                          • 10. Re: Auto-populate child foreign key from parent key
                            philmodjunk

                                 I see no need to change any part of your scripts. To repeat a question: Did you also set up the needed auto-enter field options on your fk fields?

                                 

                                      As is, the script is not working correctly.

                                 I don't see an explanation for HOW it's not working. What exact results do you get when you click this button? Do you get a new record that is not connected to a parent record? do you get no record? Do you get the wrong layout entirely? Do you stay on the same layout as the button?

                                 Each wrong result I have listed suggests a different issue to correct.

                                 To break down your request into specifics:

                                 (1) take me to a new, empty child record if one does not exist yet,

                            If [IsEmpty (TestInfo::TrapInfo_ID_fKey) // no related record]

                            Freeze Window
                            Go to Layout ["TestInfo" (TestInfo) ]
                            New Record/Request

                                 (2) take me to an existing related child table so that I can create new child records,

                            Else
                            Go to Related Record [From table: "TestInfo": Using Layout: "TestInfo"(TestInfo)]

                                 and (3) auto-populate the child's fKey with the value from the parent's pKey?

                                 From my very first comment posted here:

                                 

                                      and then define Child::ForeignKey with an auto-enter calculation:

                                 

                                      $$ID

                                 I think that you are missing this last step as you've made no mention of it in your very detailed response.

                                 Both Trap_info_ID_Fkey and Location_ID_Fkey need to be set up with this auto-enter calculation. And you may want to play it safe and use one global variable for the location ID and one for the Trap ID in both the scripts and the auto-enter calculations.

                                  

                            • 11. Re: Auto-populate child foreign key from parent key
                              evanscl

                              Setting Up the Auto-Enter Calculation for the Fkeys:

                                   

                              Within the Child (TrapInfo) table, the Child::ForeignKey (TrapInfo::Location_ID_fkey), formatted as a Number, was set to auto-enter the calculation, Child::ForeignKey = $$ID (TrapInfo::Location_ID_fkey = $$CollectionSiteID).

                                   I did set up the TrapInfoID_fkey and the TestInfoID_fKey with the auto-enter calculation. See the screenshot below. I will implement your suggestion to use just one global variable for both the TrapInfoID_fkey and the TestInfoID_fKey.

                              How The Script is Not Working:

                                   

                              For the Berkeley County Collection Site (Parent_2), no mosquito trap dates have been entered yet (i.e., no Child Tables have been created yet). When I click the button to enter the trap dates at that Collection Site (i.e., take me to a related Child Table that does not have any records entered yet), I am taken to the Georgetown County/Trap Dates 8/26/2013 to 8/29/2013 (Child_1 table under the Parent_1 table) rather than being taken to a new, empty child record under the Berkeley County Collection Site (Parent_2 table).

                                   I created 2 child records under Parent_1 (Georgetown County). For Parent_2 (Berkeley County), under which no child records exist yet, I click the button. I am taken to the Layout "Trap Info", which is correct. A new record appears, which is also correct; however, the new record is the 3rd record for Parent_1 (Georgetown County), which is not correct. My newly created child record should be the 1st child record under Parent_2 (Berkeley County).

                                   Similarly, I created 3 grandchild records under Parent_1, Child_1 for Georgetown County. When I try to enter the grandchild results for Berkeley County, I am taken to the 4th grandchild record under Georgetown County.

                                    

                              • 12. Re: Auto-populate child foreign key from parent key
                                philmodjunk
                                     

                                          I will implement your suggestion to use just one global variable for both the TrapInfoID_fkey and the TestInfoID_fKey.

                                     But I suggested the opposite. I suggested what you already have in place here. Sorry, but my eye and brain skipped right over the detail in your scripts where you are already using different global variables. In theory, just one global variable can be used here, but if anything went wrong, you might get a child record linked to the wrong parent record.

                                     I think that this is just a case of having records from the other site already in your found set. You can modify the script like this:

                                Set Variable [$$ID ; value: ParentTable::primaryKey ]
                                           
                                If [IsEmpty (Child::ForeignKey) // no related record]
                                           
                                Freeze Window
                                           
                                Go to Layout ["Child" (ChildTable) ]
                                           
                                New Record/Request
                                          Show All Records
                                          Omit Record
                                          Show Omitted Only

                                           
                                           
                                Else
                                Go to Related Record [From table: "Child": Using Layout: "ChildLayoutName"(Child)]
                                End If

                                     That change will drop the other records out of the current found set, leaving only the newly created record.

                                     But a word of caution: Your layouts can display any records linked to any parent. Any find, Show All records, Show Omitted Only action on the part of the user can bring up records linked to a different parent, but each new record that you create will still be automatically linked to the record specified in the global variable. (and You need to select the "show related records" option for your go to Related Records step also.)--which is not shown in your  scripts.

                                • 13. Re: Auto-populate child foreign key from parent key
                                  B_1

                                       Phil, 

                                       This seems to work for the exception that if you hit the unsort files button it could cause problems. 

                                       Is there a way to prevent users from un-sorting the files,besides the obvious "Don't press the unsort button " method. 

                                  • 14. Re: Auto-populate child foreign key from parent key
                                    philmodjunk

                                         Unsort records will not change what records are in your found set. This only changes the order in which they are listed if a sorted order has specified a different order from the "unsorted" order. But to repeat: "Any find, Show All records, Show Omitted Only action on the part of the user can bring up records linked to a different parent"

                                         If you are using FileMaker Advanced, you can give this layout a custom function that restricts the user's options for changing the found set by removing or disabling the menu options that can do this in your Records menu.

                                         Or you can use portals to this data from the parent record layout.