I have a form for entering orders.  This has information like order number, total amount, date, etc. along with a dropdown to select the manufacturer from another table.

That table just stores manufacturer name and default commission rate for each.  I need to be able to put the default commission rate for the manufacturer the user has selected into a field in the orders form so that I can perform calculations with it.  I also need the user to be able to type over that rate with another number in case the commission rate on that order is different.

Unfortunately, commission rate doesn't vary in any predictable way, so I can't, for example, write a formula that will determine rate based on order size or anything like that.

I currently have all this logic working in a spreadsheet, but the spreadsheet doesn't give me the ability to generate the reports I need.  In that, I can just have the user type over the formula with the non-default value.

Anyone ever do anything comparable?