Load table field (conditionally) with value from another table

[Aaron]

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.

[Aaron]

Ok I figured it out.  Sharing for anyone who might have a similar struggle in the future...   

I created a separate table called Commissions_by_User_and_Product_ID to store commission rates unique to each user and product.  In the sale table I have a field called Commission_Rate.  In the New_Sale_Form, I have this field as a cascading text field, which allows me to cascade from this separate table I made, using the Product_ID and also set the security of that cascade to match on the User_ID.  That did the trick.

Not sure why it took me so long to see that, but I'm still learning.  Hope this little breadcrumb helps another rookie like me someday.  

[Kurumi]

Hi @Aaron,

There's also another way to this. As for the Caspio's new releases, they have the new feature now called Calculated Values. This feature allows you to generate calculations which you can use to call data from another table by using SQL queries.  You can check these videos for more information:

I hope it helps!