I'd handle it as a many to many relationship (songs can have many instruments, instruments can appear in many songs).
Instrument_Type (i.e. Woodwind,String,Brass,Percussion,etc.)
And yes, you could use a portal to shows related rows from Song_Instruments on a "Song_Detail" type layout. The musical instrument ID could be a pop up to the musical instrument table in the portal itself. This is slight overkill from what you specifically asked but could do the job but I haven't tested it myself.
You could also include another related table for composer and/or artist since you may have more than one song by the same person or group. The basic idea here is to structure related tables so that you don't need to enter the same information more than one time, and it allows you to then list (report) all the songs by the same composer/artist and later calculate how often that composer uses the same instruments and which ones are played for longer amount of time. Lots of possibilities.
Dave S, thanks. I'm trying to piece it together, but I need to ask you what must be rudimentary stuff about the relationships. I connected them like so:
How far off am I?
Can I ask what the __kp and __kf designations are? I'm sure I'm going about it wrong and there's a clue in your naming scheme that I'm not picking up on.
TECman, great suggestions. I think, however, I'll have to learn to crawl before I walk, but I can see all manner of cool things I could do with your ideas. Luckily, I only have to track the music of one composer in particular. The real point of this database is to track how much of each instrument plays in a film score, so I can help plan for time needed in recording sessions. For example, each "song" is really a cue, and I need to enter data for each instrument to be overdubbed. I appreciate the input—it helps me figure out what I really want this thing to do.
Okay, I've done a little fiddling and I'm very close to achieving my basic goal. The one thing I'm trying to get the db to do is to display in the portal every record present in the table Musical_Instrument. In other words, if I create 6 records in Musical_Instrument, I want the portal in my main data entry layout to display the list of each of those records no matter what song (or cue) record I'm looking at. Furthermore, when I create a new song (cue) record, I want it to display the list of every musical instrument I have set up. What happens now is that if I want to display all of my 6 instruments, I have to fill in the music instrument ID number in the portal for each cue, from a blank portal.
Here are screenshots of how it looks at the moment.
Daniel, you are close, but not quite there. The kp_Cue_ID should be an automatically generated number which is an option for the field. That will make that step much easier. Inst ID should also be automatically created as you add portal fields. The instruments table could, for now, be edited on a separate layout and available to your portal as a pull down menu which then allows you to add a field or two from that table into the portal every time you select that instrument.
The __kp_MusicalInstrument_ID should also be automatically created.
Personal note: unless you are planning to share this database with other Filemaker developers, you really don't need to use long field names or underlines. Example: kp_Cue_ID for your use could simply be Cue ID ... and so on.
kp stands for primary key. kf stands for foreign key. A field, typically a serial number field in FileMaker systems, that uniquely identifies each record in a table is your Primary Key. The field it links to in a child table, (such as your instruments table), is a foreign key field. (It's a key but the value is "foreign" to the table in which the foreign key's field is defined.)
This naming convention is very useful even if you do not share this file or descriptions of it with other users. The _kf and __kp are designed so that when you sort your fields names alphabetically, the primary key will be listed first with any foreign keys listed immediately afterward. This can be very handy when working with them on the relationships tab in Manage database as well as in other dialogs that list fields by name in your database. (If you choose to sort fields in Manage | Database | Fields, this sorts them in the same order on the Relationships tab and in a number of other dialogs in FileMaker.)
Just keep in mind that it's a naming convention to help you use the system more easily and to know the purpose of these fields when you are selecting them in script steps and calculations. The presence or absence of these characters in the field names won't affect how they function and there is no law that states you have to use this convention.
PhilModJunk is exactly correct. Naming conventions and consistency is important and does help keep everything in order. I sometimes simplify my naming of fields especially for small solutions, and instead focus more on the business and result aspects of my thinking. PhilModJunk's explanation is well stated and worth remembering.
Great rudimentary info that I would do well to internalize—thanks.
Still working on this, have some more questions in a bit.
Okay, I've had time to return to this project and there is one major thing I'm trying to achieve that I haven't been able to and I'mhoping you can help. An update:
1. I can create cues in the "Cue" layout. Each cue gets assigned an auto-generated 3-digit ID number ("001, 002, 003").
2. I can create instruments in the "Instrument" layout. Each instrument is assigned a unique 4-digit ID ("1001, 1002, 1003").
3. The way I total up the times works fine.
What I need is a way to display in a portal (or set of related fields) each instrument record that exists, and to display the timings for the current cue. What's happening now is that every time I create a cue record, the portal goes blank. What I want is for every cue record to display, as though globally, each instrument record and then I can enter data pertaining to each record directly in the portal.
The only way I can get each cue record's portal to display the Instrument records is to enter their Inst ID number directly (I have to type "1001" for example), then each instrument fills up a line in the portal, but this is too tedious.
The reason for making this relational in the first place is this: If I had 4 instruments I needed to keep track the length of, it would be easy to simply create a flat file db with 4 time fields and then create 4 summary fields to display each of the total times. The problem is that I have up to a hundred instruments to track and for each project, they will vary, so a relational db is the right thing. How can I constantly display all those related records, automatically?
Thanks again, here's what it looks like now: