Hi Gang,
I've been suffering in silence for a few days trying to figure this out. I'm sure it's a simple answer, but I'm just not seeing it...
I have the following tables:
User_Table
Product_Type
Sales_Table
When creating a new sale (via a submission form), I need to populate a Commission_Rate field in the Sales_Table with a value from a Commission_Rate field stored in the User_Table depending on which Product_Type the user selects.
For example, the User_Table might have an object that looks like this:
User_Name: John Smith
Product_1_Commission_Rate: 10%
Product_2_Commission_Rate: 12%
Product_3_Commission_Rate: 14%
So when creating a new sale via a form, I'm trying to find a way to conditionally (based on which "Product" is being sold) populate a Commission_Rate field in the Sale_Table with the corresponding value in the User_Table. When John Smith sells Product_2, the commission rate for that particular sale needs to be 12%. And when Julie Smith sells Product_2, perhaps her commission rate would be 14%. It needs to follow the user and the product.
Perhaps there's another way to accomplish this altogether, using a different table structure. I'm open to changing it. Basically, I need to store inside each sale the commission percentage that's assigned to the user AND product. I simply can't find a way to do this, but I'm sure it can be done, and I'm probably making it harder than it needs to be.
Any help would be greatly appreciated.