9 Replies Latest reply on Apr 8, 2012 4:25 PM by mcgeo52

    Probably a simple problem

    mcgeo52

      I am a newbie at FM.

       

      Working in FM Pro 11 I have set up a database using two tables (Venues and Songs) imported from Bento.

       

      The objective is to be able to create and review set list of what I have played at various venues, and when each song was played. I have all of this in the Bento database,but FM Pro will mke it easier to use.

       

      I need to have a link table, Performances, between Venues and Songs since the relationship between Venues and Songs is many to many.

       

      Everything is set up in the database I have attached.

       

      The problem is that neither the pop up menu for Venue nor the drop down list for Song will populate.

       

      Anybody care to help a newbie?

        • 1. Re: Probably a simple problem
          flusheddata

          You are right and your database structure is ok.

          In Performance table occurrence you have both Song ID and Venue ID, right? Those are foreing keys.

          The problem is that the fields you have in Performance presentation are not those but the primary keys from their respective table occurrences (Songs and Venues).

          Also the Valuelist Venue list was not set up.

          Kind regards,

          • 2. Re: Probably a simple problem
            mcgeo52

            Ok, you understand the problem. I do not undersatnd your answer.

             

            What do I need to do to make the Venues work, step by step. I said I'm a newbie.

             

            Let's get Venues working and I should hopefully be able to do Songs on my own.Specifically:

             

            Which Table::Field do I select for "Display data from"?

             

            I assume I need a value list.

             

            Which Table::Field do I select for the first field in VenuesList?

             

            Which Table::Field do I select (if any) for the second field in VenuesList?

             

            Is there anything else I need to do to make this work?

            • 3. Re: Probably a simple problem
              PSI

              Hi mcgeo52,

               

              Welcome!

               

              The song list didn't display because the field on your layout is the SongID from songs not from Performances. the same is true for venues plus the venue value list lost it's table...i make the changes see attached.

               

              John Morina

              Pueblo Systems, Inc.

              CCQ-FM, Inc.

              mcgeo52 wrote:

               

              I am a newbie at FM.

               

              Working in FM Pro 11 I have set up a database using two tables (Venues and Songs) imported from Bento.

               

              The objective is to be able to create and review set list of what I have played at various venues, and when each song was played. I have all of this in the Bento database,but FM Pro will mke it easier to use.

               

              I need to have a link table, Performances, between Venues and Songs since the relationship between Venues and Songs is many to many.

               

              Everything is set up in the database I have attached.

               

              The problem is that neither the pop up menu for Venue nor the drop down list for Song will populate.

               

              Anybody care to help a newbie?

              1 of 1 people found this helpful
              • 4. Re: Probably a simple problem
                mcgeo52

                Thanks to both of you for your time.

                 

                Special thanks to PSI for explaining my problem in a way I understood.

                 

                IT WORKS! ;-)

                • 5. Re: Probably a simple problem
                  PSI

                  I was thinking about it and with your current design you can only associate one song with a performance. you will need one more table. In order to associate multiple songs with a performance you will need a performance song table. Venues and songs both act as lookup tables.

                   

                   

                   

                  For an interesting way to create related records check out http://www.pueblo-systems.com/CreateRecords.zip

                   

                   

                   

                  John Morina

                   

                  Pueblo Systems, Inc.

                   

                  CCQ-FM Inc.

                  • 6. Re: Probably a simple problem
                    flusheddata

                    First of all excuse me for my poor English. Obviously it's not my native language.

                    Your database structure is ok. To get a many to many relationship between Songs and Venues you need your "Performances" table. You may create as many records in the Performances table as you wish.

                    I disagree with PSI when says "I was thinking about it and with your current design you can only associate one song with a performance".

                    In fact you may associate one song with, let's say, ten performances just by creating ten records with the same Song ID and the given Venue ID.

                    Also, in order to create Performance records i'd recommend you doing it from the Venue layout via portal.

                    I attach a modified version of your file in order for you to check it out. Note that I've set File Options to go to Venues layout at startup.

                    I hope this helps a bit further.

                    Again excuse me for my English. I need some practice.

                    Best Regards,

                    flusheddata

                    • 7. Re: Probably a simple problem
                      PSI

                      Hi Flushdata,

                       

                       

                       

                      Your English is fine…

                       

                       

                       

                      Creating duplicate performance records for a single performance doesn’t make sense. For any given date there is only one performance at one venue but there are many songs. From the stand point of Venues if you were to display the performances in a portal it would be very confusing to see 30 performances on the same date each with its own song. The relation of Performances to song is one to many that’s why I suggested a performance song table.

                       

                       

                       

                      John Morina

                       

                      Pueblo Systems, Inc.

                       

                      CCQ-FM Inc.

                       

                      john@pueblo-systems.com

                      • 8. Re: Probably a simple problem
                        flusheddata

                        Hi,

                        I apologize for misunderstanding you.

                        As you said the join table should be for Performances and Songs quite the same as invoice>line items<Products.

                        Let me suggest play_list as its name, hehehehe.

                        Kind regards,

                        Miguel Fernandez

                        • 9. Re: Probably a simple problem
                          mcgeo52

                          Thanks for your help.

                           

                          I have three tables all working properly:

                          • Venues
                          • Songs
                          • Performances
                            • venueID
                            • songID
                            • date
                            • notes

                          Now on to other parts of this project to make it really useful.