Relationship table -Do I need another join table?
Using FM 11Pro, I have a database with seven tables (see attached .png file). I have three tables, "Country", "Origin" and "PlaceNow" that I want to feed information up to my "Library" table and through the Library table to a table called "LibraryItems" by which many LibraryItems are linked individual Library records.
I have created a join table, "JoinCountryOrigin", that is linked to my Library table. The purpose of this table is that each record in the CoinLibrary table must be linked to a unique combination of a Country and Origin source. I am able to successfully build a layout to make the appropriate link to the JoinCountryOrigin table to work on both the Library and LibraryItems tables.
My challenge is that I want to do the same for the records in the PlaceNow table. My simple solution which worked (but did not make my layout idiot-proof), was to simply make a link from the PlaceNow table to a linked PlaceNowIDLink field in the LibraryItems table; that worked fine except that when making the link on the layout, the drop-down list consisted of all PlaceNow table records. However, I want to limit the selection of which records can be linked to a LibraryItems record based upon what Country may be linked to a PlaceNow record (that Country, of course, being the Country noted from the JoinCountryOrigin table). My first thought was to link the PlaceNow table to the Country table via a"JoinCountryPlaceNow" table - many PlaceNow table records can be linked to many Country table records (in the attached diagram, I have since broken the relationship links as I struggle to find the right approach). Note that linking the PlaceNow table to the existing JoinCountryOrigin table is not useful as that join table is too limiting to the Country/Origin combination). My next challenge is that the records in the PlaceNow table should be linked at the LibraryItems table (not the Library table, as multiple PlaceNow records can be linked to any one Library record made up of multiple LibraryItems records).
Bottom line: what I am trying to do is to get a link to the PlaceNow table for a LibraryItems record by which a selection is made whereby the selection for such link is based on the Country that is linked to Library table. Any ideas would be helpful!