I have a Products Table with products and sales information.
Includes the fields Net Sales
Then I have a Royalty Group Table and a Royalty Table
The idea is that I can set up different Royalty schemes for different products.
Royalty Group 1 can have following Tiers:
TIER - AMOUNT FROM - AMOUNT TO - ROYALTY PERCENT
Tier 1 - 0.00 - 15,000 - 25%
Tier 2 - 15,001 - 50,000 - 50%
Tier 3 - 50,001 - 100,000 - 75%
I have hooked my Products Table up with the Royalty Group Table, which again is hooked correctly up to the Royalty Table
In the Products Layout I can see all the correct Royalty Tiers in the Portal, so it seems to be hooked up correctly.
How to I calculate the following:
If Product::Net Sales = 55,000
Then I need a calculation that goes through each Tier of the Royalty Group.
The result should be:
(Tier 1) 15,000 * 25% (3,750) +
(Tier 2) (50,000 - 15,000) * 50% (17,500) +
(Tier 3) (55,000 - 50,000) * 75% (3,750)
So something like
If ( Products:Net Sales > Tier 1, Tier 1 AMOUNT TO * ROYALTY PERCENT ) +
... and this is where I stall...
Well, thanks in advance for anyone who can assist me here.
Feel free to ask if you need additional info!