4 Replies Latest reply on Feb 27, 2013 4:37 PM by philmodjunk

    converting a one-way relationship into a join-table

    JamesPickett

      Title

      converting a one-way relationship into a join-table

      Post

      I have a field in one of my talbes that I originally related to another table via a single field and one-way relationship.  Later on I realized that I needed a many-to-many relationship and set up a join table.  The result is a redundancy; older records are linked via the one-to-many relationship, and new records via the join table.  

       

      Is there a way to automatically add an entry to the join table for all of my previous records so that I can simply delete the now outdated one-to-many field relationship?

        • 1. Re: converting a one-way relationship into a join-table
          philmodjunk

          A looping script can do that fairly easily. Just pull up your records that need updating in a found set and then do the following.

          Note: I'll use these relationships for the example:

          MainTable::__pkMainTableID = OriginalTO::_fkMaintableID

          MainTable::__pkMainTableID = JoinTable::_fkMainTableID   (''allow creation of records via this relationship" is enabled for JoinTable)
          NewTO::__pkNewTOID = JoinTable::_fkNewTOID

          NewTO and OriginalTo are different Table Occurrences (TO) of the same data source table.

          #Perform find first either in script or manually on the MainTable layout.
          Go to Record/Request/Page [first]
          Loop
             Set Field [JoinTable::_fkMainTableID ; MainTable::__pkMainTableID ]
             Set Field [JoinTable::_fkNewTOID ; OriginalTO::__pkNewTOID ]
             Go to Record/Request/Page [Next ; exit after last]
          End Loop

          • 2. Re: converting a one-way relationship into a join-table
            JamesPickett

                 It's been a while since I posed this query; I got frustrated and never did figure out how to do it.  But I would like to give it another try.

                 I basically understand most of the shorthand at this point, but some things require a bit more clarification.  In my database, I have a table tracking individuals "Individuals" (which corresponds to MainTable above, I believe) which has a field "Source" which keys to another table "Sources."  I am trying to automatically transfer the relationships in the "Source" field into entries on a many-many join table called "Multiple Sources."

                 Will the above script allow me to automate that process?  Also, in the above example I do not understand the following:

                   
            •           Are NewTO and OriginalTo supposed to be new instances of the "Individuals" table?
            •      
            •           What does "#Perform find first either in script or manually on the MainTable layout" mean?  I want this script to hit every single entry in my "Individuals" table such that when I am done with the script my original field "Source" will be entirely unnecessary and redundant because that information will have been transfered to the join table "Multiple Sources."

                 Any help is most appreciated.

                  

                  

                  

            • 3. Re: converting a one-way relationship into a join-table
              JamesPickett

                   Or perhaps there is an easier (but slightly more labor-intensive) way to go about this?  If I perform a search to bring up all of the records only designated by the original "Source" field, is there a way to simply batch-add a Join Table entry of my choosing?  In Excel this would amount to simpy dragging to highlight all of the fields or copying and pasting, but I am not sure how to mass-edit in FileMaker.

              • 4. Re: converting a one-way relationship into a join-table
                philmodjunk

                     Translating to use your table names:

                     "Individuals" (which corresponds to MainTable above, I believe) which has a field "Source" which keys to another table "Sources."

                     Individuals::Source = Sources::Source

                     Linking in the new join table:Multiple Sources

                     results in this:

                     Individuals::__pkIndividualID = Multiple Sources::_fkIndividualID
                     Sources 2::Source = Multiple Sources::Source

                     Sources 2 is a new table occurrence (what you called an "instance" of your original Sources table).

                     #Perform find first either in script or manually on the Individuals layout.
                     Go to Record/Request/Page [first]
                     Loop
                        Set Field [Multiple Sources::_fkIndividualID ; Individuals::__pkIndividualID ]
                        Set Field [Multiple Sources::Source ; Sources::Source ]
                        Go to Record/Request/Page [Next ; exit after last]
                     End Loop

                     #Perform find first either in script or manually on the Individuals layout.

                     Means exactly what it says. Before performing this script, you need a found set of all the records that you want to use to create the new records in the join table. You could perform a find either by hand or by adding script steps that peform a find. In your case, you've clarified that you want to do this for all records so all you need to do is select Show All Records before running the script.

                     Since your original post, I've started linking in a thread on how to decode the notation I am using.

                     See the first post of this thread for an explanation of the notation used: Common Forum Relationship and Field Notations Explained

                     Ps. I came across this post by accident. It's sufficiently old that it no longer appears in the "recent items list" when a post to it is added. You may want to start a new thread to make sure that I see it should you post any follow up comments or questions.