Jump to content
  • 0

Load table field (conditionally) with value from another table



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:





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.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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.  

Link to comment
Share on other sites

  • 0

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!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...