It would appear that you have a many to many relationship. One recording can be selected for more than one rehearsal and one rehearsal can select more than one recording. Correct?
If so, start with these relationships:
Rehearsals::Rehearsal_ID = Rehearsal_Recording::_fkRehearsalID
Recordings::Recording_ID = Rehearsal_Recording::_fkRecordingID
You can place a portal to Rehearsal_Recording on the Rehearsals layout to list and select Recordings records for each given Rehearsals record. Fields from Recordings can be included in the Portal to show additional info about each selected Recordings record and the _fkRecordingID field can be set up with a value list for selecting Recordings records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Note: This is value list for _fkRecordingID is a "beginner level" method for selecting values. More sophisticated methods--some using scripts and additional relationships are possible that can make it easier to select recordings--especially if you find that your are needing to scroll through a very long list of values to find the one you want, but set this up with this method to start to confirm that you have everything up and working and then, if you want, we can look at more sophisticated methods for selecting one or a group of recordings for your rehearsal session.
And here's a demo file that links Events to Contacts in a many to many relationship that you can examine for ideas to include in your database: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
It's an older format file but if you are using FileMaker 12 or newer, you can use Open from FileMaker's File menu to open this file and generate a copy converted to the newer file format.
Hello! Thanks, Phil.
I do have other similar relationships that are many-to-many, but this one is many-to-one. Meaning, a rehearsal can have many recordings, but a recording can only be associated with one rehearsal (since it was recorded at that rehearsal and no other time). I got as far as putting a rehearsal_id field in Recordings, and then making a Portal in rehearsals to display recordings like title, duration, etc.
Regarding the value list, you are saying that the Portal row will have a recordingId field, and that inputting one that already exists will assign that recording to be part of the portal? I will try that out to see if I can make it work with the one(rehearsal)-to-many(recording) relationship, too.
From the recording side, how would I assign the (singular) correct rehearsal, if I assigned it from that direction?
I am definitely interested in some of these more sophisticated methods you mentioned. I definitely will need to scroll through a very long list of values, since I have roughly 200 rehearsals and 1000 recordings so far.
I will also check out that demo file, thank you very much!
Since this is a one to many relationship you'll need to ignore my last post.
But when I re-read your first post, I am confused about what you are trying to set up. If each recording is specific to only one rehearsal and no other, I don't see why you need to select anything. It would seem a simple portal to Rehearsals based on this relationship would work:
Rehearsals::Rehearsal_ID = Recordings::_fkRehearsalID
Where you enable "allow creation of records via this relationship" for Recordings in the above relationship.
Then, in the portal, you simply fill in data into any field of the blank "add" row of the portal and FileMaker will create a new Recordings record and link it to the current Rehearsals record by copying the value of Rehearsal_ID into the new reocrd's _fkRehearsalID field.
Hi Phil, yes, I see how creating a new Recordings record from the portal will work, and how it would associate it with that current Rehearsal record.
But I'm specifically curious how, in that case, to choose an *existing* Recording record from that Recording portal that is in the Rehearsal record. This is in the case where the recording in question is not yet associated with a Rehearsal (or optionally, if it *is* associated with a different Rehearsal, choosing it would replace that association so that the Recording is now associated with the new Rehearsal but not the old one - but it's ok if that doesn't work).
Also, if a Recording is in a Rehearsal's Portal view, I want to be able to deselect it somehow - where it would still exist as a Recording record, but no longer be associated with that Rehearsal.
What puzzles me is why you would need to do any of these things since you've stated: "(since it was recorded at that rehearsal and no other time)". It doesn't look to me like a recording record should ever exist except as it is linked to the rehearsal where it was created. (I start to wonder if this might be many to many after all...)
Deselecting a recording record would simply require clearing the _fkRehearsalID field for that record. This can be done manually or by clicking a button to run a script that deletes the data in this field.
Selecting an existing recording record would get more complicated here as the normal methods would enter the Recording ID value into a field in Rehearsals--which is typical of a many to one relationship rather than one to many.
I really, really don't get why this is needed for the situation that you describe, but you could do it this way:
Define an additional relationship to a different table occurrence of Recordings:
Rehearsals::_fkSelectedRecording = Recordings|Selected::Recording_ID
Format _fkSelectedRecording as a drop down list or pop up menu of Recording ID's and some kind of Name or description field as the "second field" to better identify each recording record. A OnObjectSave or OnObjectModify script trigger can then perform this one line script to select it for the current rehearsal record each time that you select a Recording record from the value list:
Set Field [ Recordings|Selected::_fkRehearsalID ; Rehearsals::Rehearsal_ID ]
Note that this will both link the selected Recording to the current Rehearsal record and, because it will overwrite any existing data in the field, disconnect it from any Rehearsal record to which it was previously linked.
Hi Phil, one answer is because I'm importing from another data source that imports Rehearsals and Recordings, but no relationships. I will be building these relationships after the fact. So that's an example of why I will need to select multiple Recordings from a Rehearsal. There's also the chance that I might mistakenly assign an already-existing Recording to the wrong Rehearsal, so the question is how to adjust or correct these relationships from either side.
Thanks for that additional detail, I will try it out.
That could be very tedious to do. I'd look for any data in the imported data that could serve, even if temporarily to link a recording to a rehearsal so that I can use a script or Replace Field contents to assign the Rehearsal Id's to the Recordings records in a batch operation instead of one record at a time.