4 Replies Latest reply on Nov 13, 2013 8:39 AM by philmodjunk

    Many-to-Many relationship for Movies

    RobertBeauchamp

      Title

      Many-to-Many relationship for Movies

      Post

           I have been trying for a couple days to get the many-to-many relationship for a movie database, as described in the FileMaker training materials to work.

           I have set up Tables for Movie, Role, Actor, and set up the relationships as described in the training package.

           I've placed a portal in the Movies layout that has the role::character_name, and the actors::actor_name.  I am using a pop-up menu to control names entered for Actor.

           Everything works ok for creating new roles on the portal, and when adding a new actor (an actor name that has not been used yet) it works fine.  When I use an actor name that has been already used in a different movie it appears to work just fine in that layout, but examining the Actors table reveals that FileMaker created another instance of the same Actor_Name with a different primary key.

           I've tried quite a few different ways to get this to work right with no success, Any help would be very appreciated.

           Thanks!

        • 1. Re: Many-to-Many relationship for Movies
          davidanders

               A many to many relationship requires a Join Table to manage the confusion.

               If you have a Join Table between two many tables, sometimes you need another join table and a joining table for the join tables.

               A screen capture of your Relationships in your Manage Database would be helpful.

          • 2. Re: Many-to-Many relationship for Movies
            philmodjunk

                 How do you select an actor in your portal to the join table?

                 Do you use the Role::actorID field formatted as a drop down or pop up menu to select such? (my field name may differ from yours, but this field should be the field in Role not a field in Actors.)

                 I usually use a button placed outside the portal for adding new fields in the "far" table (actors in your case). It uses a script to create a new record in the far table and then a linking record in the join table. It might bring up a screen for filling in info on that new actor record or I might have sufficient fields from Actor in my portal row to fill in the information needed.

            • 3. Re: Many-to-Many relationship for Movies
              RobertBeauchamp

                   Thanks to both of you for taking the time to reply.

                   I had hoped that I could do this by selecting the actor_name field in actor, but it doesn't seem to work this way.  Time for me to get back to studying the tutorial.  I'll get back to this part of setting up my database after I know more, especially about scripts.

                   Thanks again,

                   Robert.

              • 4. Re: Many-to-Many relationship for Movies
                philmodjunk

                     I had hoped that I could do this by selecting the actor_name field in actor

                     This is indeed possible. But this is not how it is set up in the training series lesson to which you have been referring.