Newbie questions about lookups/relationships
I am a complete novice running FileMaker Pro 7 (I know, it's ancient, but that's what I bought a while ago and I'm stuck with it for now) on Mac OS 10.5. I am setting up a music library database with two tables: "Repertoire" (each record is a specific piece of music) and "Performances" (each record is a specific concert on a particular date). Here is what I am trying to set up:
-On each Performance record, I want to select a list of repertoire performed on that concert. I got as far as creating 20 fields for Repertoire (the most I could imagine needing on any performance) on the Performance table, and each of those fields has a pop-up menu that lists all the Repertoire records I've created. So I can make the repertoire list look right.
-The part I can't figure out is how to to pull in additional data from the Repertoire record I select, such as duration and instrumentation. The idea would be that when I add a new Repertoire item to the Performance from that pop-up menu, the duration and instrumentation would also transfer over for that Repertoire. So let's say in Repertoire 1 (the first of those 20 repertoire lines) I select "The Star Spangled Banner" from the pop-up menu. I want Duration 1 to fill in with the correct duration from the repertoire record. And if I then change Repertoire 1 to "America the Beautiful" I want the duration to update, too. I don't know if that is a relationship, a lookup, a script or what - that's where I'm stumped.
-I also want each Repertoire record to have a list of performance dates. So for any Performance record on which I add that Repertoire, the date would get added to a list back on the Repertoire record. If I remove the repertoire from the Performance, I want the date to go away. It should be a dynamic list of dates, based on any performance on which that repertoire shows up on the repertoire list.
Basically, I want a bunch of stuff to happen when I select the repertoire on a performance: Fill in the duration and instrumentation, tally the total duration and instrumentation for the performance, and add a note to the Repertoire record that the performance is scheduled.. Can anyone give me a tip on where to start with these questions? I may be in way over my head here!