7 Replies Latest reply on Feb 21, 2010 5:43 PM by comment_1

    Repeating fields issue

    onamac

      Title

      Repeating fields issue

      Post

      Hi. I have a table listing "Songs" written. Each record will be a new song. In this layout I had placed repeating fields to list musicians that performed on the song, and their fees. Each field is laid out in a row, like a spreadsheet, with a repeat of 5 to accommodate 5 musicians:

       

      SomeSong 

       

      Musician ID     Date      Musician Name     Part Used      Fee Paid 

      1             12-12-09        John Doe                  Yes               $200

      2             12-14-09       Tom Smith                 Yes               $100

       

       

      Secondly, I have a table called "Musicians" (with a separate layout) that lists the musician main details like address, phone etc. I also have a portal bringing in these fields (plus song ID and title) from songs, to give me a synopsis of what songs each musician has performed. It would look like this:

       

      John Doe 

       

      Date        Song ID      Song Title     Fee Paid 

      12-12-09      2            Whatever         $200

      12-29-09      4            Somesong        $150

       

       

      Each field in this portal is displaying its data from "Songs". (songs::date) etc. 

       

      In each person's record, this portal is correctly listing the songs that each musician had played on, so when I go from record to record, the song titles and quantity of them changes per musician, and they are accurate. However, the other fields (such as fees and dates) display only the data from whatever is in the first repeating field in "songs". They should be reflecting the date and the fee paid to a particular musician for that particular song.

       

        • 1. Re: Repeating fields issue
          comment_1
             This is not a good structure. Instead of repeating fields, use a third table to record the performances.
          • 2. Re: Repeating fields issue
            onamac
              

            Thank you. I did that and it will work. However, I'm wondering exactly what you had in mind? The reason I ask is because I made a new table as you suggested, with an entry layout for it. Before using it, it made sense to me. But once I started to enter the data, something felt awkward:

             

            I make new record. I enter musician #1 for song #1 and the details.

            I make a new record. I enter musician #2 for song #1 and the details.

            I make a new record. I enter musician #3 for song #1...

            etc.

             

            Doing it this way, there will be a lot of individual records for the same song just so that musicians can show up together in a portal. This is why I think you may have had something else in mind... maybe not using a separate layout? I feel like there's an solution I am missing but I can't think of it.

             

            Unfortunately repeated fields have limitations and can mess up the data, particularly for lengthy lists. The thing I liked about the repeated fields was that on one song record, 5 musicians could be entered and listed as having performed on that one song; it was like writing them down underneath each other on note paper. Is there a method that can achieve the simplicity of this but do it the correct way to which you refer? Thanks.

            • 3. Re: Repeating fields issue
              comment_1
                

              onamac wrote:

              it was like writing them down underneath each other on note paper. Is there a method that can achieve the simplicity of this but do it the correct way to which you refer?


              Sure: place a portal to the join table on the Songs layout, and allow the relationship to automatically create join records.


              • 4. Re: Repeating fields issue
                onamac
                  

                Thank you. I'm afraid I need pointed in the right direction here. I read up on join tables and am thinking of this much like line items between Invoices and Product; at least I think it is.

                 

                I made a table called Musician Schedule (with no layout of its own), as the join table (bridge between Musicians and Songs). The table occurances look like this in Relationships:

                 

                Musicians - Musician Schedule - Songs.

                 

                Musician ID is the field that links from Musicians to Musician Schedule to Songs. I allowed "creation of records" in the Musician Schedule side.

                 

                In the portal, the fields are from "Musician Schedule", and I placed this portal in a second tab on the "Songs" layout. However, when I go into browse mode, there are no fields in which I could enter anything, so obviously this has not been done correctly.  

                 

                EDIT: I've since tried using the fields from a different source, etc. but still in Browse mode, the portal does not appear. When I move the fields out of the portal, they do appear. Maybe this info further identifies my problem?

                • 5. Re: Repeating fields issue
                  comment_1
                    

                  I don't know - based on your description, it should work. Perhaps you should look at the demo posted here:

                  http://www.fmforums.com/forum/showpost.php?post/246136/

                  and compare it with your file.

                   

                   

                  Note: the join table should have at least one layout - otherwise you won't be able to address it in a script, or even inspect the join records directly.

                  • 6. Re: Repeating fields issue
                    onamac
                      

                    EDIT: I had to go back to a previously saved backup, which portals are now working. I don't know what was wrong with the other one but it was easier to continue on with a backup than troubleshoot.

                     

                    I am using the portal on the Songs layout, and when entering info in the portal, it does create the record in "Musician schedule" table. However, by the time I got to the third song, I noticed that at that point, when I entered the musician for that song's record, it duplicated that musician's info automatically in the previous song record. If that musician had not been listed in the previous Song record, it added him to it; and if he was listed, it changed what had already been entered for him there. If I go back to the previous record to correct that info, it then takes that info and puts it in the current record.

                     

                    I had moved away from using repeated fields to avoid the very thing that this is doing now!

                     

                    Having this work properly is the last thing that needs to be done on this file.

                    • 7. Re: Repeating fields issue
                      comment_1
                         Sorry, I haven't seen this until now. However, I am afraid I can only repeat what I said earlier: compare your file to the demo.