Help with setting up a flexible fee calculation relationship

[TysonB]

Hi all,

I support a FileMaker 22 Project Management database.  Recently, the project manager I'm working with started using the database to calculate his project management fee and generate his invoices.  His fee for each project is based on the total expenses accrued by the project.  He currently has five different "Expense Cases" that have been set up by the organization he is working with that determine the fee he charges.  Here is how it is currently structured:

Cost of Work

Case 1 -  Under $350K

Hourly Basis up to maximum of $15,000

Case 2 - $350K to $1,000K

$15,000 on first $350,000 and 3% on next $650,000

Case 3 - $1,000K to $2500K

$34,500 on first $1,000,000 and 2.50% on next $1,500,000

Case 4 - $2500K to $8,500K

$72,000 on first $2,500,000 and 1.3% on next $6,000,000

Case 5 -Over $8,500K

$150,000

When I initially set the calculation up to generate his project management fee, it was fairly easy to do using a Case statement that calculated based on the Total Expenses field.  

Then he wanted me to set it up so that we could change the ranges of the minimums and maximums....  That wasn't too bad, I just created a related table with fields in it for the minimums and maximums for each of the five cases, then replaced the hard coded numbers in my case statement with the fields for the minimums and maximums for each expense case.

Now, however, he's asked about whether we can set it up so that for each different project he could have more or less expense cases instead of just being hard-coded to 5 expense cases.  I COULD do what I did to change the ranges, but then create a much larger Case statement to have it evaluate, say, 10 different cases, and add more minimum and maximum fields in the related table for each of those 10 cases.  Then he could just use the number of minimum and maximum case fields he needs for a specific project.

But that just doesn't feel right, it reminds me of all those flat-file solutions from pre-FileMaker 7 days...  I'm looking for suggestions for how to better make this work...  

Thanks in advance

Tyson

[comment]

3 hours ago, TysonB said:

whether we can set it up so that for each different project he could have more or less expense cases

Yes. But you should have a record for each tier, not a field. Then you can simply lookup the values via a relationship using the ≥ operator.

Note that you only need a field for the minimum amount of each tier. The maximum is given by the next tier.

 

Edited January 6 by comment

[TysonB]

Thank you so much!   That was a direction I hadn't thought of going in, and it looks really promising!