8 Replies Latest reply on Jan 4, 2009 8:08 PM by swj

    Linking

    smokey

      Title

      Linking

      Post

      A friend and I have developed a Music Publishing program and I'm stuck in a couple of places. We have Contact Management, but we also have a file for Contract-Briefs for our individual songwriters.  In another layout we have our songs.  We have the Song Title, then we have a portal with the writers, and publisher information, including the splits (percentage) of each writer and publisher......Once the song information has been entered, we need to link that song to the individual songwriters Contract-Brief. This is where we're stuck.

       

      Can you help me?  Scripts, what kind of field to link the song to, etc. We also need to keep a total of the percent the writer has for each song, but I think that is a simple Sum Calculation. 

       

      Thank you so much.

        • 1. Re: Linking
          Jens Teich
             I bet you need an additional table AUTHORS connecting SONGWRITERS and SONGS.

          Jens

          • 2. Re: Linking
            smokey
              

            Can you elaborate a little more on that. I'm pretty new at FileMaker and seem to need a little more help on some things.

             

            Thanks

            • 3. Re: Linking
              Jens Teich
                 See http://filemaker.com/help/05-Create%20a%20database2.html, 6.

              You have a similar case like the example with CLIENTS - ROUTES - TOURS but you are looking only at CLIENTS and ROUTES, TOURS is yet missing.

              You can't connect CLIENTS and TOURS directly because there may exist multiple TOURS per CLIENT as well as multiple CLIENTS per TOUR. This complicated case is call n:m relationship and is resolved in two less complicated 1:n relationship by introduction of o third table, the join table.

              Jens


              • 4. Re: Linking
                smokey
                  

                Well, I don't think you're understanding what I need. Since I'm new at this I don't know what things are called. Maybe Inserting Text is what I need. 

                 

                Here's what I need to do......When a songwriter writes a song, I need to insert the song information (Title, co-writers, date written and the writer percent) into a page of his Contract-Brief.  This way I'll have a list of the songs that he's written during a certain period of time and the total writers share of all of the songs.

                 

                Thanks for your help.

                • 5. Re: Linking
                  Jens Teich
                     Ok then please elaborate a bit on the tables and fields which are part of the problem. For example I am asking me whether ContactBrief is a separate table or part of the person (songwriter) table.

                  Jens
                  • 6. Re: Linking
                    smokey
                      

                    Hi Jen,

                     

                    Sorry to be so confusing about this.

                     

                    I have my Contact-Management, this is where I have the name, Company, address, phone, etc. of everybody that we deal with.  Then I have my Contract Briefs, which is where I have all of my writers that are contracted to write for us exclusively.  My Contract Briefs has all of the contractual information on our writers. This includes the contract date, termination date, option periods, etc.  During each term of the contract, each writer is to write X-amount of songs per year or term. (For instance, one writer may have to write 8 fully written songs a year.  If he writes with 2 other people that means that he has written 1/3 of a song, etc. On another song he might have only written with one other person, which means that he has 50% of a song.)

                     

                    Next I have a table for Compositions.  This is where I put the song title, then in the portal I put each of the writers name, publishing company, what percentage of the song each wrote, etc.

                     

                    What I need to do is insert a copy of this song title, along with the co-writers name and the percentage of the song that our writer wrote someplace into our writers individual Contract Brief.  I need to keep a running list of each song that our writer wrote and what his percentage is. That way I can go to his Contract Brief, look at the songs that he wrote during that period of time and keep a total to see how far he is from his quota of songs and have a list of the completed songs.

                     

                    Whee, I hope that wasn't too confusing to you.

                     

                    Thanks.

                    • 7. Re: Linking
                      Jens Teich
                         Sorry but I am going to repeat my advice from above. Maybe I should explain a bit deeper.

                      You have basicly two tables CONTRACTS (belonging to a special SONGWRITER) and SONGS.

                      Now obviously one songwriter can write more than one song. We hope he will do :).

                      What about the other direction. As you mentioned it is a common case that multiple songwriters are writing one song together.

                      This means 'complicated world' in both directions. The database term is 'n:m relationship'. The solution is to add an additional table called jointable. One record in this table is one songwriters participation in writing one song. We could call this table AUTHORS.

                      Another hint that you have to rethink your approach is that you ask how to copy information. This is against basic principles of database design. Very basic rule is to keep every piece of information exactly once and organize the structure of our database in a way that we have as few copies as possible.

                      Jens





                      • 8. Re: Linking
                        swj
                          

                        Hi smokey,

                        I copied your design as closely as I could from your description, and uploaded my result. I haven't tried the Windows Live SkyDrive before, but I think you can find my attempt at:

                         

                        Music_Publisher.fp7

                         

                        Let me know if that doesn't work.....

                         

                        You should be able to choose songs for Briefs, or writers for songs, and set percentages. Is that anything like what you have been trying to describe? What else did you want to achieve?