I have built a FM solution that I used to organize and write a weekly 2-hour music radio countdown show. I primarily wanted to have consistency with Songs/run times/word counts/ (thing important to a timed show and script). It consists of a few tables:
Artists (1 artist per record)
Songs (1 song per record)
Song History (as a new show's 22 songs are entered in main script, this is flushed and re-imported to create updated chart history. Record for every song and position. Thousands of records. )
Main Script - new record every week. (fields of importance: date, 22 song positions that get populated from "Songs" table pull-down that change weekly)
My question - what would be the most efficient way in FM to have a reference in my main script that would allow me to see the history of each particular song. Say for example - if SONG_A is in the #17 spot this week, but was at #19 last week, at #20 the week before that. How can I create an optional pop up view for every position in my script that gives me a quick history reference list. I need this historical data as I write the script. Creating the popup isn't the problem, the table relationship is where I need help.
(Think Casey Kasem counting down the hits...that's similar to what I have to write. "Moving up 2 this week...it's SONG_A")