Hi Zak. My recommendation would be to create a Products table that holds the product id/name/etc and its tax treatment. Then switch out your single select for a link field to Products. Then you can add a lookup field on Products to pull in the tax rate for that product into the table where you are calculating commission.
It will give you easier extensibility in the future. If you need more explanation, let me know.
The only potential issue with this method is that there are only 6 products in the stack, and with 15 tables running in the base, is it worth a new table for those few data?
Albeit, thinking out loud, those products do span across a few tables so I guess I could link to the Products table whenever they are being used in another table as opposed to a single select/multi-select.
I have a ‘sales ledger’ table along with a ‘quotes’ table and decided on inputting commission percentages manually as they can change fairly regularly.
All 6 Products are grouped into 2 categories, one subject to tax and the other not. Maybe I’m simply better having a Product Type 1 Commission Amount field, and a Product Type 2 Commission Amount field along with a Total Commission Amount field to get around the issue.
Having views for each Product Type allows me to hide the redundant Commission Amount field for each type.