Extracting information from Portal

[kcep]

I'm stuck on two issues.

See my attached sample database. Go to the "Songs" layout - it should open on this layout. Read the "Notes" section on each of the seven records in the "Songs" layout.

Issue #1 - I think this will be fairly easy for a FM genius to solve.

Issue #2 - I wondering if it's possible. - maybe LaRetta, Lee, Comment or some other FM genius can figure this out :P

Songs.fp7.zip

Edited February 21, 2006 by Guest

[comment]

Here's a basis to get you started - hopefully it's clear enough how to finish it.

SongRights.fp7.zip

[kcep]

Hi Comment,

Thanks for pointing me in the right direction

You're great!

This will really help me get going on issue #2.

I hate to be pushy - any thoughts on issue #1 ? (a calc field in the "Songs" table that calculates the total percentage of each song controlled by PGM Administration) I didn't see that in your example.

[comment]

I'm afraid I don't understand this point too well. What does "control" mean in this context? Shouldn't this data be entered into the join table, along with the rest of the right-holders? If not, you need to explain what data is available to calculate such percentage - IOW, how would we do this if we did not have a computer?

[kcep]

One of the jobs of the Administrator is to collect the money that a song earns and distribute it between the songwriter(s) and publisher(s) and keep a small percent for themselves. The publisher(s) actually own the song - not the songwriter(s) (unless the songwriter and publisher are one in the same). Much of the music business works internally on a 200% scale (yes, I know - stupid). Since the publisher actually owns the song, the administrator answers to the publisher.

Example 1:

song title: "My Red Corvette" Recorded by Small Town Singer

Written by: Joe

© 2006 Big Publishing Company (admin. by ABC Administration)

OK, if "My Red Corvette" earns $100.00 in royalties, ABC Administration would collect all $100.00 from Small Town Singer and keep $10.00 for themself. ABC would send a check to Joe for $45.00 (which would be considered 100% of the writer's portion, because he is the only writer on the song). And ABC would send a check to Big Publishing Company for $45.00 (which would be considered 100% of the publisher's portion of the song). However, when ABC invoiced Small Town Singer for $100.00, ABC claimed 100% control (not 200%). This is because 200% doesn't make sense to most people.

Wow, Comment, I'm really surprised you're having a hard time understanding this . Who knows where this whole stupid thing got started.

Example 2:

song title: "My Blue Truck" Recorded by Lady Singer

Written by: Sue, Mike, Bob & Tom

© 1985 Big Publishing Company (admin. by ABC Administration) & Tom Publishing Co. (admin. by Tom Administration)

OK, let's say that all the writers are equal contributors to the song. Big Publishing Company owns Sue's, Mike's and Bob's portion. Tom has his own publishing and administration company. If "My Blue Truck" earns $100.00 of royalties from Lady Singer, ABC Administration would invoice Lady Singer for $75.00, and Tom Administation would invoice Lady Singer for $25.00. In the eyes of Lady Singer, ABC Admin. control 75% and Tom Admin. controls 25%.

Keep in mind that one Administrator can represent hundreds of publishers. This means that a single song could be owned by six different publishers, in six different cities, but that same song could be controlled by one administrator. Also, a single songwriter can have more than one publisher representing their interests on a single song.

Take another look at my original "Songs" database. Read the first paragraph in the notes section of each record in the "Songs" table. The amount of control that PGM Administration has on each song would correspond with the total percent of the publisher's percent of those publishers who are using PGM Administration. The calc field I'm looking for should never yield a result greater than 100%.

I hope this all makes sense. Thanks for your help!

kcep

Edited February 21, 2006 by Guest

[comment]

Question regarding Example 2: "Big Publishing Company owns Sue's, Mike's and Bob's portion." Does this need to be recorded in your database? Or is it enough to list Sue, Mike and Bob as writers (with no rights to revenue), and Big Publishing Company as the one who collects 75% of the royalties?

In the second case, it could be simple to split the 200% into 2 fields of 100% each: one field would represent the percentage of administration rights (which IIUC, is the right to collect); the other field would be the share in collected revenue.

In the first case, if the rights have to be cascaded according to who represents whom (i.e. tracking the history of the original rights) - well, let's just say I don't want to think about it...

[kcep]

My real database is somewhat complicated. (19 tables). It already produces a great royalty report for the songwriter and another report for the publisher. It also produces the invoice (refered to as a license in the music industry) for the artist.

On the invoice (or license) I need a field which tells the artist that this particular invoice is only for a certain percent of the song.

Currently, I have a script that finds all writer/publisher records relating to a specific song, then the script constrains the found set to show only the records that we control. At that point, I have a summary field that is giving me the percent that I need. Then the script copies and pastes that percent into the invoice. Which is exactly what I want. However, this percent is also used later to calculate different things. Since it was copied and pasted from the summary field, it does not change throughout the rest of the system. I will always continue to copy and paste it to the invoice because an invoice should not change after it is sent. However, if a publisher or administrator change to the song should happen to take place after the invoice, then this field in question needs to change throughout the rest of the system.

I'm looking for a single field that shows what percent of the total song that PGM Administration controls. It would be best for me if it was calculated from the way in which my sample database is constructed. This is because of a number of issues that take place in other tables.

I'm very grateful for your interest in helping so many of us by sharing your knowledge.

kcep

[kcep]

I've been trying to think of an easier way to explain what I'm trying to accomplish. Hopefully, this will help.

I have a certain column in a portal that I'm summarizing the total. Here's the hitch. I only want certain rows in the said column to be included in the summary total.

In my original sample database, the said column is column #5 (Publisher %). The rows of this column that I want summarized in the total have "PGM Administration" in that same row (to the right in column #6 of the portal).

I don't know if the answer would be displayed in a summary field or a calculation field.

Thanks, kcep

[comment]

This is getting more confusing as we go - but the last question is easy: just make a calculation field =

Case (AdministratorID = x ; Percentage )

Where x is the PGM's serialID in Contacts.

I've made another stab (in the dark) at this - see if it helps any.

SongRights2.fp7.zip

[kcep]

IT'S WORKING !

I just added another calc field outside of the portal that sums up the the data in the last column of the portal (the "cPGMPercentage" column). The result of the sum of the last column is the answer I've been needing!!! I hope I was correct in how I've been trying to explain all of this and where it would be used in my database.

COMMENT - YOU DID IT AGAIN !

Thanks a bunch

Edited February 22, 2006 by Guest