Trouble Finding a common field to link tables

Discussion created by sejax422 on Mar 29, 2017
Latest reply on Apr 6, 2017 by sejax422

I have a database with a few tables to keep track of scene continuity for a feature film.  I'd like to be able to link my "Continuity" table to a separate table called "Music Tracker."  I'm not sure exactly how to link them but here is what my goal is:


Table 1 is "Scene Continuity" - each record represents one scene.

(FIELDS: scene number, location, scene description, song title)


Table 2 is "Music Tracker" - each record represents a series of scenes.

(FIELDS: cue number, song title, artist, scenes used [a song could span 5 scenes])


I understand portals enough that I can create a portal in my Music Tracker to indicate all the scenes in which the song is being used in the Continuity table.  That is, if my process is to update the Song Title field in the Continuity table first.  I'd rather not do this.  Here's why:


Because of how often songs change during the edit, It's much simpler to update my Music Tracker table first.  As a song changes, I'd like it to link directly to the corresponding records (scenes) in the Continuity table.  Thus, if I update "SONG A" in the Music Tracker table and the Songs Used field = "1-5", then "SONG A" will auto-populate into the Continuity table under the Song Title field for records (scenes) 1-5.


How then, can I go about doing this, if each record in the Music Tracker table represents a series of scenes, rather than one scene? 


How do I link the records in the Music Tracker  to Continuity if the "Scenes Used" field = 1-5 and in the Continuity table the "Scene Number" field = "1," "2," "3," "4," and "5" separately?  Is there a calculation I can use?  Do I need to create a proxy field for linking?


Many thanks in advance for your help!