A solution with commissions set in Table is definitely the way to go. Aside from providing transparency into how the commissions are set, it means that a non-admin can adjust the commissions and also most importantly you will want to bring down the commission rates into your sales detail file as look up fields and then create a snapshot field for each of the look up field so they are frozen when the sales detail is created. That way when the commission rates changed won't be changing the historical commission payments.
I will try to describe the gist of the solution here, but if you need more help, we can do it slowly hear back-and-forth or you may want to book me for two hours of one-on-one consulting time to get this working.
You will set up a table of commissions where the key field is a concatenation of the various factors that you want to pay commission on, delimited by say a colon : of the location and the products and depending on your set up, maybe the manager supervisor and the sales person. It wasn't clear to me from your post if those commission rates are fixed for each position.
The Key field of the commissions would be populated by a form rule which would take the value of the concatenated formula field and populated into a scalar data entry field.
So the result might be Atlanta:Widgets
And if that location has a fixed commission for each of the three positions, then you would have three fields for the commission rates.
Then you would build a corresponding formula field on the sales detail table, which would calculate that same; concatenated string and then look up the commission rates and then you would have three Snapshot fields to freeze the value of those look up fields at the time the sales detail got created. Like I said above, this is important because when the commission exchange you don't want to change history.
So basically, you would continue creating different commission records for different types of situations and the lookout fields and snapshot them.
Then on the item detail table, you would need to have a formula field to decide which commission rate to use as there could be multiple commission rates that might apply. might take the highest score the lowest or perhaps some kind of hierarchy to take the first one that is not blank.
Feel free to post back with questions and I won't answer the Best Buy or else if you want some more assistance, you can contact me directly at mark.shnier@gmail.com
