6 Replies Latest reply on Mar 12, 2012 2:14 PM by ScottJones

    problem with join table in many to many relationship



      problem with join table in many to many relationship


      I have recently started using FileMaker Pro again to create some work-related databases.  (I am a lawyer in a small firm.)  For reference the last time I created a database before my recent purchase of version 11, I used either a 2.x or 3.x version; so, it has been a while.

      I have looked at a number of posts related to my issue, and I just cannot see what I am doing wrong.  I think that I am so deep in the forest that I am not seeing the tree right in front of me.  Because the database I am havibg a problem with had quite a few "moving parts," I created a smaller sample to see if in the complexity of my larger database, I had just missed something. My smaller sample has the same problem.

      My sample database has 3 tables: characters, documents, and join.  The characters table has a character name field and a characterID field.  The documents table has a document name field and a documentID field.  The join table has the 2 id fields.  My problems is that when I create a new document in the document table, there is not one created in the join table.  I have attached a screen shot of the manage tables screen.  I will try to upload otehr screen shots later, if that will help, but I am late for a meeting right now.  I hope someone can tell me the simple mistake I am making.

      By the way, this is a great forum.  As I get back up to speed, I will try to contribute.


        • 1. Re: problem with join table in many to many relationship

          What you describe sounds like expected behavior. Creating a new record in one table does not automatically create a new record in another except in very special cases. You'd need to put a script in place to do that.

          Why should creating a new Document record automatically create a new "join" table record? Are you adding a new Document record that you want to immediately link to the current character record? (You don't need to create a join record until you need to link a document record to a character record.)

          If that's the case, put a portal to Join on your characters layout. You can include fields from documents in the portal row. Enable "allow creation of records via this relationship" for Join in the Characters to Join relationship. Put a button on this layout for adding new document records and linking them to the current characters record that performs a script like this:

          Set variable [$CharacterID ; value: Characters::__pk_CharacterID ]
          Freeze Window
          Go To Layout [ Documents ]
          New Record/Request
          Set Variable [$DocumentID ; value: Documents::__pk_DocumentID ]
          Go to Layout [Join]
          New Record/Request
          Set Field [Join::_fk_DocumentID ; $DocumentID]
          Set Field [Join::_fk_CharacterID ; $CharacterID]
          Go to Layout [original layout]

          Here's a demo file that contains a version of this script and that also demonstrates some different interface options for working with many to many relationships: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: problem with join table in many to many relationship


            Thanks.  I should have put some more information in my original post.  I did create a portal to the join table in the characters table.  I told it to allow creation of records and the field I put in the portal is the join::documentID field.  

            Here is what I envision.  Some documents are already input into the db, but others have not been.  When I had a new character, I want to be able to link him to an already input document if that is the proper link, but I also want to be able to add a new document if the document I need has not been input yet.  Characters can have links to multiple documents, and documents can have links to mutiple characters.  

            When I click on the portal in the character layout, nothing pops up.  I tried layering another field (documents::document name) on top of the join::documentID field, but this did not work.  I did not expect it to because there are more documents in the document table than are "seen" in the join table.

            Does my question even make sense at this point, or do I neeed to provide more details about my set-up?  I had looked at the file you uploaded earlier, but I will study it better later this afternoon.

            I really do appreciate your help.



            • 3. Re: problem with join table in many to many relationship

              The demo file shows how to select an existing document from a drop down list or pop up menu and the above script shows how to add a new document record. Keep in mind that the new document record will be completely blank except for the ID number until you enter some data so it will appear blank in the drop down. You must either include fields, such as a document title field from documents in the portal so that you can enter this data for just added/linked document records, or you script should use one method or another to collect that data from you when the script executes. (It can use show custom dialog with input fields to get a document name from you, it can switch to the document layout for you to enter data for the new document....)

              Bottom line: you select from a value list to link a character record to an existing document, but run a script and enter data to add and link the character record to a new document record.

              • 4. Re: problem with join table in many to many relationship


                Thanks again.  I think I am closer to "getting my mind right," but I am still not all the way there.  I see the problem I had with my earlier analysis.  If a join table had done what I was thinking, every character would be linked to each and every document - clearly not what I need.  

                I understand (I think) the scripted add button, but I am going to delay adding that button until I understand one more basic issue.  

                I have added 7 characters and 5 documents to my dummy database, and I have tried to set up my portal to the join table as you did in your "basic setup." In the portal on my character table I have a drop down list for Join::document id and next to id I have a text box for Documents:: document name.  My problem is that my drop down list is blank.  I know (I think) that I have to add a scripted button to add additional documents via this portal, but I thought I could choose from the existing documents via this dropdown list.  Indeed, on your basic setup, the dropdown box shows the character id number and character name.  

                Having talked novices through what seemed like basic computer issues in the distant and more recent past, I really do apreciate your patience in trying to help me.


                • 5. Re: problem with join table in many to many relationship

                  You should be able to select existing documents from the drop down. You'll need to look at what you have specified for the value list for this drop down to see the list of existing documents. What options have you specified in Manage | Value lists for your drop down list?

                  • 6. Re: problem with join table in many to many relationship

                    I had join::documentid.  After I read your post, I changed it to documents::documentID, and that seems to work.  I will keep at it.  I may be back with more questions later in the week, but I will be out for at least a day now.  I have it working, but I want ton think about it and make sure my understanding of the why and the how are correct.

                    Thanks again.