Unfortunately, @William_B, you’re going to have to set your “Payrates” table up horizontally instead of vertically.
Think of each Record (row) in the “Payrates” table as a “Pay Schedule”. Initially, you only need one, your current pay schedule/rates. You’ll need to create a Field (column) for each “Tier” that could be matched against, and it’s percentage value as the Value for that “Pay Schedule”. The advantage of this is that you can change your pay structure in the future by adding a new “Pay Schedule” record, and using that new record on all new sales items going forward – this will preserve your historical data if you change your payout rates.
So in your “Sales Log”, you’ll have to link each sale to a Pay Schedule record from the Payrates table. Then, tediously, you will have to pull in each value from your Pay Schedule with Lookups. Lastly, you can now create a nested “IF()” formula to find the appropriate payout rate for the sale, based on the percentage.
Here’s a simple demo base you are welcome to join and poke around in to get the idea:
I’ve used this same type of structure for “Fee Schedules” before too – businesses that sell goods or services, and need the flexibility in their system to be able to change their pricing structure. This is the best way to achieve that in a way that does not alter historical data when schedules are changed.
Let me know if you need further help.