1 of 1 people found this helpful
It sounds like the classic use case for a 'join' table that sits between the scenes and the music. Each record in that table would represent a track for one scene. Multiple records for the same track indicate that it is used in more than one scene.
Wim's call is spot on.. you definately need a join table.
As far as "link" fields each table should have a primary key. A field whos value is never empty, is unique for all records in the table, has no real meaning to the data, and is not ever editable by the user. Auto increment serial number is an option as is get UUID.
A join table should have its primary key field and two foreign key fields that house the values from the related tables primary key field.
See attached example
scenes.fmp12.zip 65.3 K
I will disagree. Unless you need to link more than one song to the same scene, you do not need a join table. And even if you do need a join table, it does not solve the update problem created by changing a song title.
The solution to that is simple. Don't link your records by the name of the song. Define a field in Music Tracker that is either a number field that auto-enters a serial number or a text field that auto-enters get(UUID). Use this field to link a song to either a scene record or, if you really need it, a join table record . Now you can edit the song title whenever needed and your scenes remain linked to the new song.
This is an example of why you should not use names as match fields in relationships.
PS: value lists can be set up for selecting a song by name that enter the ID. Other selection widgets that do this are also possible.
Thanks philmodjunk! But what fields am I linking the serial number to? So, in Music Tracker, if I create an ID number for each song, "SONG_ID" and each song has an ID number of "SONG_001," "SONG_002," etc, what field in my Scene Continuity am I linking to so that it matches? Do I have to create another ID field in Scene Continuity so that they connect somehow?
Yes, you need another ID field, but PLEASE do NOT use "Song_001" as an ID. Use just a serial number, 1, 2 , 3 ... or a UUID entered into a text field for your primary key. Don't complicate an ID field that need never be visible to the user on any of your layouts with additional data and formatting requirements. Keep it as simple and straight forward an ID as possible.
You can use your current relationship that matches by name to assign the new ID's to your existing continuity records before you then update your relationships to match by the new field.
Thanks Everyone. I was able to successfully link Songs to Scenes by using a join table. Maybe there's another way as suggested above that I didn't need a join table, but I was able to understand how to use a Join Table and it's working so far for what I need.