Hi, I am just learning FMpro, so forgive me if this is all very simple.
I want to understand many to many relationships and how to handle them cleanly in FM. So, two tables; Concerts and Songs. Obviously a concert can contain many songs, and also a song can be used at many concerts. The table for concerts has fields for the date of the concert and so on, and the table for songs includes words for the songs etc. So I want to link Concerts and Songs TOs so that a layout based on Concerts will show me the songs used at a particular concert. Also I want to look at a song in a layout based on Songs and see which concerts it has been played at.
Sounds simple. I have read that since this is a many to many relationship I should create a table between these two, perhaps called playlists. Yet that doesn't seem to help as a playlist would have many songs and a song would be on many playlists. Is is necessary to create another table, or can I link Concerts to Songs in a relationship based on a primary key in Concerts and a foreign key in Songs?
Thanks for any help you can give.