Calculating Hollywood Box Office Bonuses: a dilemma

[Jhestler]

HI All!

I need to generate a report that reflects the monetary bonuses which are payable to actors when certain thresholds of box office revenue are generated by ticket sales of a motion picture. This one has had me tearing my hair out all day...

1. The thresholds are a concatenation of various factors:

a. The Production Cost of the motion picture. This may be expressed as 1x Cost, 1.5x Cost, 2x Cost, etc. This Production Cost field is merely a number (in the given example, the field values would be 1, 1.5 and 2 respectively).

b. The cost of prints and advertising (P&A). Along with the prior factor, the threshold may not be expressed as: 1x Cost plus 1x P&A, 1.5x Cost plus 1x P&A, 2x Cost plus 2x P&A, etc. As with the Production Cost field, P&A is merely a number field. In the given examples, the P&A values would be 1, 1 and 2 respectively.

c. The third factor in merely an arbitrary Cash Increase. Compounded with the 2 prior factors, the final expression of the threshold that triggers the threshold may be expressed as follows: 1x Cost plus 1x P&A plus $10,000,000, 1.5x Cost plus 1x P&A plus $20,000,000, etc. This is a number field, formatted as currency. In the given example, the Cash Increase values would be $10,000,000 and $20,000,000.

An example of a completely formulated threshold could be stated as follows:

When the box office revenues of the motion picture reached the threshold equal to 1x the Production Cost plus 1x the P&A plus $10,000,000, the movie company will pay the actor $10,000 cash (the Bonus). An additional bonus of $10,000 may kick in at 1x Production Cost plus 1x P&A plus $20,000,000, and so on.

My dilemma is this:

I want the report to show me the aggregate of individual bonuses payable at each threshold. The thresholds should be subsummarized, so that I can see how much total money will be paid about among the various individuals in the body of the report at each threshold. And there should be a grand summary of all bonuses across all th

For this reason, I was using a subsummary when sorted by a calc field called Threshold Order. Threshold Order is defined simply as "Production Cost & P&A & Cash Increase."

Unfortunately, this arrangement doesn't work.

The solution properly reports and groups the data just fine. But the problem is that the ascending order of each threshold is improperly sorted because the Production Cost and P&A fields can both reflect decimal values.

For example:

Record A: 0x Production Cost plus 0x P&A plus 10,000,000 = a Threshold Order value of 0010,000.

Record B: 0x Production Cost plus 1.5x P&A plus $10,000,000 = a Threshold Order value of 01.510,000

If you sort these two records in ascending order, the bonus allocated to Record B will be incorrectly shown as being payable first because the calculated Threshold Order for Record B is smaller than that of Record A.

Imagine the mess that results when both Production Cost and P&A are decimals (e.g., 1.5).

Your help would be much appreciated; thank you!!!

[Vaughan]

You haven't given much clue as to your data structure. However I'd have a table for Actors, a table for Productions, and another table for Bonuses.

Each Production record needs to have a field for Production_Cost and PrintAdvertising_Cost. These appear to be the main data required for the calculation.

The Bonuses records joins the Actors and the Productions. There needs to be as many Bonuses records are there are bonuses each actor can possibly have for each production.

The Bonuses records needs a field that multiplies the production costs, and another for the p&a costs, plus another for the cash increase. Let's call these Multiply_Production, Mulitply_PrintAdvertising, and Cash_Increase.

Using these, each Bonuses record should have a calculation field in it that calculates the Threshold:

Bonuses::Multiply_Production * Productions::Production_Cost +

Bonuses::Multiply_PrintAdvertising * Productions::PrintAdvertising_Cost +

Bonuses::Cash_Increase

We would then have a field in Productions called Box_Office where the income is entered. If the Threshold is less then this then the bonus is paid.

[comment]

Threshold Order is defined simply as "Production Cost & P&A & Cash Increase."

That's not possible - because "P&A" is not a valid field name.

Assuming the field is named "PandA", try changing the Threshold Order calculation to =

List ( Production Cost ; PandA ; Cash Increase )

Make sure the result type is set to Text.

The solution properly reports and groups the data just fine. But the problem is that the ascending order of each threshold is improperly sorted

See another option here:

http://www.filemaker.com/11help/html/find_sort.5.23.html#1029044

[Jhestler]

Comment, I was genuinely thinking of you when I posted this topic. I thought: I hope that Comment is still hanging out in here. And on the heels of that thought: what am I going to do if it turns out that Comment is a mortal human being and thus the time he has on this earth will impose a limitation upon the extent to which I can avail myself of his genius? I'm not easy to impress, but you're nothing short of amazing. You are not a human, you're a mutant. A lovable benign genius of a mutant. Thank you, sir!!!

I actually looked at the list functionality as a possibity, but i'd never used it before and so I thought i'd post here and see what came back. I still don't fully understand it, and especially don't understand how converting the calc to text plopped the sorting functionality into place. But it works. IT WORKS!!!! Thank you!!!