8 Replies Latest reply on Aug 27, 2010 11:56 AM by kaye7m

    Newbie needs help setting up songbook database

    kaye7m

      Title

      Newbie needs help setting up songbook database

      Post

      I have over a hundred different songbooks, and over a hundred different songs that are included in these songbooks. I would like to create a database that will allow me to (1) select a song and see which book(s) it appears in, and (2) select a songbook and see which songs are included in that book. Can someone give me some advice on how to set up a database that will allow for this sort of cross-referencing?

        • 1. Re: Newbie needs help setting up songbook database
          philmodjunk

          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 )
          SongTitle (text)

          Define these two fields in Book_Song
          BookID (number)
          SongID (number)

          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.

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Newbie needs help setting up songbook database
            kaye7m

            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?

            • 3. Re: Newbie needs help setting up songbook database
              philmodjunk

              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".

              • 4. Re: Newbie needs help setting up songbook database
                kaye7m

                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.

                • 5. Re: Newbie needs help setting up songbook database
                  philmodjunk

                  Apologies,

                  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.

                  • 6. Re: Newbie needs help setting up songbook database
                    kaye7m

                    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. :)

                    • 7. Re: Newbie needs help setting up songbook database
                      philmodjunk

                      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. ;-)

                      • 8. Re: Newbie needs help setting up songbook database
                        kaye7m

                        Again, I want to say... thank you! :)