1 Reply Latest reply on Aug 17, 2011 11:31 AM by philmodjunk

    Many to many relationships with drop downs

    ArtStascausky

      Title

      Many to many relationships with drop downs

      Post

      Assume the case of actors and movies. Each actor may work in many movies and a movie will have many actors. A portal would do the trick to relate the entities. This particular application requires a previous list of actors and movies, they would be drop downs or lists from the Actor Name field in the Actors table, and Movie names from the movie table. All new relationships should be done between these records.

       Writing a name in the portal creates a new record and a new relationship with the name written in the portal but not between original records.

       One solution would be to have two windows, one with the Actors table and the other with the Movies table, then you select the record you want to relate, press a button and execute a script that would write the right ID number in the key field in the joint entity.

       It seems to me there should be a better way to do it. Any comments would be appreciated. Thank you

       

        • 1. Re: Many to many relationships with drop downs
          philmodjunk

          Did you know that the FileMaker training series uses Actors and Movies to illustrate how to work with many to many relationships?

          That's what you have here.

          You need a third table so that any given actor can be linked to more than one movie and and any given movie can be linked to more than one actor.

          Movies----<Role>----Actors   (---< stands for "one to many")

          Movies::MovieID = Role::MovieID
          Actors::ActorID = Role::ActorID

          With this setup, a portal to Role on the Movies layout can be used to assign existing actor records to that movie. A portal to Role on the Actors layout can be used to assign existing Movie records to that actor. Fields defined in the Role table can document the actor's role in that movie (such as the character's name.)

          Drop downs or pop ups in the portal would be used to select the actor on the Movie layout and the Movie when on the actor layout. The value lists for these fields would list the ID number as field 1 and the name as field 2. The actor's name field from Actors can be added to this portal on the Movie layout and the Movie Title from Movies can be added to the portal on the Actors layout so that the "name" of the selected actor/movie is listed in the portal.