many-to-many relationship?, report problem

[kcep]

FileMaker can be a huge thrill or a big frustration. Right now, I'm a little frustrated. Please see the attached sample database. I need a royalty report that is specific to the songwriters earnings. If you press the red "Go To Royalty Report" on the songs layout, you can see that my two page report is displaying royalties as they relate to each song. However, I want it to go a step further - but similar in the way it looks. I want a report for each songwriter to show how much each songwriter has earned from each publisher that the songwriter is working with- (Yes, I realize that when it's all said and done, I will need calculation fields to figure out what percentage of the song's royalties go to each of the songwriter(s) and publisher(s). But for now, I'm concentrating on getting the report to find all of the songwriters.) I had it set up a different way, and it found songwriters. However, if the song had multiple songwriters, FileMaker found only one of them. Do I need a many-to-many relationship somewhere? Hopefully the solution is something simple that I just can't seem to see. Thanks, kcep

Royalty.fp7.zip

[bruceR]

Well one thing you're doing wrong is at the right end of the graph you need TWO instances of the Address book, one for Writer, one for Publisher. But the real matter is that you need to further break up your structure and have W-P_Team which then has a record for Writer and another for Publisher.

Edited August 18, 2006 by Guest

[kcep]

Hi BruceR, Thanks for the help! I fixed the first problem that you noticed. I don't know what you mean by needing to further break up my structure and have W-P_Team which then has a record for Writer and another for Publisher. I don't know what the terminology "W-P_Team" means. I attached what I've already fixed. Notice how my report only calculates royalties for the first songwriter that FileMaker finds per income line item. I need to have a report that calculates all of the songwriters for each income line item. However, the structure of the report is looking great. Now I just need a report on every songwriter and a report every publisher.

Royalty.fp7.zip

Edited August 19, 2006 by Guest

[bruceR]

And the only way to do what you want is to do what I said - split up the writer/publisher into separate records. If it really is necessary to define them as a team or group or whatever then you will need a higher level table to define the group/team record. WP_Team was just my suggested name for the "parent" record.

Edited August 19, 2006 by Guest

[kcep]

Hi BruceR, thanks again for the advice. Let me see if I'm understanding. Could my current table called "Royalty_Income" be the parent table? Then off of that parent table would I have two additional related tables: #1 ie: Songwriter, #2 ie: Publisher ? I was hoping not to have to create income history records in three tables - If a song has four songwriters and four publishers, then one recorded in the parent table would represent eight additional records between the Songwriters table and the Publisher table. Wow, that's a lot of records, and sort of makes it a pain if for some reason you need to delete a record in the parent table - the result would be to have to delete nine records across three tables. I'm not real comfortable using the "delete record" script step, but that might be just me. I was hoping there was a many-to-many way of doing this, but I must have been hoping for too much. Thanks again, KCEP

[bruceR]

What you say doesn't add up. You are hoping for a many/many relationship and then when it is provided you don't want to manage it. The deletes will take care of themselves if you have set up the relation properly.

Half as many tables. Songwriter or publisher is just an entry in the TYPE field of the record.

Edited August 20, 2006 by Guest

[kcep]

Hi Bruce, I really appreciate your time. I must be "out -to-lunch". I'm not getting how to set up the relationship you're refering to. This particular problem could be a very simple solution that I'm just not comprehending, or it may be above my level of FileMaker knowledge. Sorry for being a pain.

Edited August 20, 2006 by Guest

[bruceR]

Something like this.

Royalty1.zip

Edited August 21, 2006 by Guest

[kcep]

Hi BruceR, Thank you so much for taking your valuable time to try and educate me. I really am thankful. Kind regards, kcep ;)

Edited August 22, 2006 by Guest