2 Replies Latest reply on Oct 15, 2014 6:17 PM by NickCohen

    Can't figure out how to relate these tables



      Can't figure out how to relate these tables


      I'm a massive opera lover and am trying to create a database to keep track of the opera productions I've seen. The main things I want to keep track of are the different operas I've seen, when I've seen them, and which singers sang which roles. In my mind, the main tables I would view would be Operas, to see an overview of all operas I've seen; Performances, to keep track of the operas I've seen multiple times; Singers, to keep track of which singers I've seen, and what roles they've performed; and Roles, to keep track of which roles I've seen performed by which singers. So as of now, the tables I have set up are Operas, Performances, Roles, Singers, and Castings (which is a join table of Roles and Singers - think Singer X as Role Y). However, one casting may be used for many performances - and each performance has many castings. I'm struggling to find a way to link everything together so that there won't be any many-to-many relationships and I'll easily be able to see the information I want while looking through the database.

      I'm fairly new to FileMaker, so any help would be greatly apprecaited!

        • 1. Re: Can't figure out how to relate these tables

          I can imagine two options:  ( -----< means "one to many" )

          Operas----<Performances-------<Performance_Singer>-----Singers-----<Castings>------Roles>------Operas 2


          Operas----<Performances-------<Performance_Singer>-----Singers-----<Castings>------Roles>------Performances 2>----Operas 2

          You'd need to decide if the roles are specific to the specific opera or a particular performance of the opera.

          The Exploring the use of a $Path Variable in Scripts names shown with a "2" are a second occurrence of the same table (Operas and Operas 2 have the same data source table)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Can't figure out how to relate these tables

            Thanks so much! I created everything and it's set up according to your first method. However, I still can't quite get it to work. I read your post on the topic and made the sample database but can't quite comprehend how it applies here. This is what I have set up so far. I entered a couple roles to try to link them back to the opera through a portal, but I can't quite figure it out. Do I have to use separate layouts? Ideally, I'd just like a portal on the main Opera layout that lists all the roles for that specific opera with their voice-type. The roles table would be used to record role name, voice-type, range, etc. Does that make sense? Thanks again for pointing me in the right direction.