7 Replies Latest reply on Mar 26, 2014 11:36 AM by philmodjunk

    Choosing related records?



      Choosing related records?


           Hi, apologies for the beginner questions.  I am a Bento refugee and I'm trying to mimic some of the basic functionality I was used to in Bento.  This mostly has to do with relationships.  It looks like I have them set up correctly, I'm just trying to figure out some UI stuff.

           1) If I am in a layout (call it "Rehearsals") with a Portal that can display many "Recordings", how can I choose multiple recordings?  In other words, I want select 8-10 existing "Recordings" records and associate them with the "Rehearsal" I am viewing.  How can I pull up something like a multi-select chooser?  This is a relationship of many Recordings to one Rehearsal.  Even though the Recordings have recording_id, and the Recordings table has a rehearsal_id, I'd rather select them in a way that is friendlier than going to each Recording record and typing in the numerical rehearsal_id.

           2) I'm pretty sure I figured out how to "Go to related record" - I just put a button on the portal row and hooked it up to that script.  After clicking the button, though, is there a way to go "back" to the original record, from that record?

           3) In the reverse direction, is there a UI-friendly way to start from the "Recording" record, and pick the one Rehearsal that it is associated with?

           4) Corollary of #1, is there an easy way to pull up the list of associated Recordings (from the Recordings portal in the Rehearsals layout) and unassociate one of them?  By de-selecting or something?

           5) Finally, is it yet possible to turn on a horizontal scrollbar in Portals so I can display more columns that can fit in the Portal width?




        • 1. Re: Choosing related records?

               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.

          • 2. Re: Choosing related records?

                 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!


            • 3. Re: Choosing related records?

                   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.

              • 4. Re: Choosing related records?

                     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.

                • 5. Re: Choosing related records?

                       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.

                  • 6. Re: Choosing related records?

                         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.

                    • 7. Re: Choosing related records?

                           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.