Likely, you have some songs that appear in more than one song book. Set up Three tables in your database file: SongBooks, Songs, Book_Song
Define at least these fields in SongBooks:
BookID (Auto-entered serial number)
BookTitle ( Text )
Define at least these fields in Songs:
SongID (auto-entered serial number )
Define these two fields in Book_Song
Relate the two tables:
SongBooks::BookID = Book_Song::BookID
Songs::SongID = Book_Song::SongID
You can then use a portal to Book_Song on your Book layout to list its songs and a portal to Book_Song on your songs layout can be used to list the books where its found.
Here's a demo: It matches up Contracts with Companies in the same manner as I've described matching up Songs with SongBooks.
Thank you sooo much. Your instructions plus the demo were quite helpful. I've now got it set up and everything looks pretty good except for one thing. When I'm in the books layout, and I go to the portal with the pop-up menu of song titles, and make the appropriate selections, everything looks fine there. However, if I then go to the Songs layout, that book is not displayed in the portal. I have to duplicate the action (ie, go to the portal with the pop-up menu of book titles, and make the appropriate selection). And the converse is also true -- if I make a selection on the songs layout, it doesn't automatically show up on the books layout. I've double-checked all the relationship definitions and other settings, but something must be wrong. I shouldn't have to enter this info twice, should I? Any ideas?
Sounds like the portal is based on the wrong table. It should be based on Book_Song. Compare it to the Demo file carefully. Double click the relationship lines in both your file and the demo and compare the settings. Also enter layout mode and double click the portals to bring up portal setup to see which table is listed in "show records from".
I've checked and double-checked everything. I think perhaps it's just not possible to make it work the way I had hoped. I'd love to test your demo to see if it would work there but I can't because there's not a portal on both layouts. At least, with your help, I was able to set up my database so I can get the two lists I wanted (all the books that contain a particular song, and all the songs that are in a particular book). I'll just have to make entries on both layouts, which is not a huge deal. Thanks for your help. I really appreciate it.
I thought that file was set up with portals on both layouts. The principle is the same--add a portal to the join table and include fields from the third table.
I've updated the file to show a portal on both layouts. Use the same link above to download it again and check it out.
Oh joy oh rapture -- it's working now! Thank you so much for adding the second portal to the demo. I was then able to test it and discovered that yes indeed the demo was working exactly the way I wanted my database to work. So, I did some further digging into all the settings, and discovered that where I had gone wrong was with the setting for "allow creation of records in this table via this relationship." I had it checked for all three tables, but your demo showed that it should only be checked for the join table. Once I fixed that, everything started working properly. Can't thank you enough. :)
My original copy of the demo had the wrong options selected for one of the tables also. I discovered that when I added the portal for the other table in the demo. ;-)
Again, I want to say... thank you! :)