7 Replies Latest reply on Apr 22, 2014 9:34 AM by philmodjunk

    One to Many Relationship



      One to Many Relationship


           In one table i'd like to store the name of a play and all the roles that the play has, i.e

           Role 1
           Role 2
           Role 3

           In the next table each record is an event i.e the play referenced above but i'd like to store the names of the actors that played those roles. So Play 1, which it's name and Role 1 - Role 3 are stored in table 1. Table 2 will store the date of the play and the names of the actors i.e

           11/11/11 Henry (Role 1) Thomas (Role 2) Billy (Role 3)

           12/12/12 Smith (Role1) John (Role 2) Bertie (Role 3)


           I'd like then to produce a report which will show this, but I am having trouble working out how to structure the database to allow me to do this so the report shows the actors name represents each record in alphabetical order, i.e

           Bertie (Role 3)
           Billy (Role 3)
           Henry (Role 1)
           John (Role 2)
           Smith (Role 1)
           Thomas (Role 2)


           Really appreciate any help someone might be able to give me.

        • 1. Re: One to Many Relationship

               Set up these tables and relationships:


               Actors::__pkActorID = Roles::_fkActorID
               Performances::__pkPerformanceID = Roles::_fkPerformanceID

               A "role" field can be defined in Roles to record the role placed by a specific actor in a specific performance.

               A list view layout based on Roles can show the Performance info in the header, but list the actor names and roles in the body. You would perform a find or use Go to Related Records to pull up a found set on this layout of just the Roles for a given performance.

          • 2. Re: One to Many Relationship

                 The list will need to show all the actors that have ever played, so all plays and all roles, is that possible with this structure? Really appreciate your help.

            • 3. Re: One to Many Relationship

                   Yes, it is possible. but so far we have discussed this only in terms of performances, not plays. You may need an additional table linked to performances that lists each of the plays in your database.

                   Layouts based on Roles can be set up in a variety of different ways--including listing roles from multiple plays and/or multiple performances in the same report.

              • 4. Re: One to Many Relationship

                     To reiterate,

                     I need to see a list of all the cast members, the record, will show the name of the cast member the role they played (the role being the one and the cast name being the many) and the date of the event.

                     In basic terms it looks a bit like this:

                     PLAY TABLE
                     Fields: Name of play, Role 1, Role 2 etc

                     EVENT TABLE
                     Fields: Various fields relating to specifics of the event, date (Perhaps in a separate table), cast member name with relationship to the play

                     This of course the theoretical layout, my current file looks quite different. Please will you therefore explain in greater detail how I should structure this and in layman terms any relationships, calculations and scripts that I will need to use.

                     I really appreciate your help.



                • 5. Re: One to Many Relationship

                       What I see here, in terms of FileMaker is really two different reports. Report one lists the play and all roles specified for that play.  Report two lists the event (performance) and each actor participating in that event and the role that they played in that event.

                       The second part is the one that I had in mind when I said it could be printed from Roles. The first is also possible, but I think you'll want to add another table for that one, which in turn provides an easier option for that first part of the report.

                       Please note that I am renaming the original "Roles" table for better clarity here:


                       Actor_Role documents a specific Role played by a specific actor in a specific performance (Event). The new Roles table lists each role needed for a specific Play. The Play to Roles Relationship can be used to set up a conditional value list to use for selecting an Actor's Role in the Actor_Role table so that the only roles listed are those specified for the performance's specified play. ("Macbeth thus won't appear in the value list of Roles to select from for a performance of "cat on a hot tin roof"...)

                       For the first part of your report, you have two options: You can print it from Roles with a list view report with fields from Play put in the header and the role field placed in the body or you can use a portal to Roles placed in the header or a leading grand summary part on the layout that I am about to describe for the second part--listing the actors and roles for a given performance (event). To print out your list of roles and details about the play, you can either set up a button that uses Go to Related Records or a scripted find to find all records in Roles linked to the current record in Play.

                       To List the actors and roles for a specific event ( performance), You can use the same technique from a selected Performances record to pull up all the actor_role records linked to that performance. (It's also possible to list all performances (events) for a given play or just certain performances (such as those falling in a specified date range) instead of just 1 performance if you want.)

                       This would be a list view report based on Actor_Role with the Actor name from actors and the Role field from actor_role in the body of your layout.

                  • 6. Re: One to Many Relationship

                         Hi Phil,

                         Is there any chance that you could send me a screenshot of the relationships and some labels to describe the conditional value list, I'm slightly lost in your instructions, apologies for being slow here.

                         Thank you so much

                    • 7. Re: One to Many Relationship

                           First things first. We need to make sure that your relationships are correctly set up:


                           Actors::__pkActorID = Actor_Role::_fkActorID
                           Performances::__pkPerformanceID = Actor_Role::_fkPerformanceID
                           Play::__pkPlayID = Performances::_fkPlayID
                           Play::__pkPlayID = Roels::_fkPlayID

                           You will probably also need to add in a second Tutorial: What are Table Occurrences? of Roles that you link directly to Actor_Role so that you can include info about the role (such as the character's name) on your layouts and reports that refer to records in Actor_Role. But get the above relationships in place first.

                           For an explanation (with screen shot examples) of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained