fleng

Help calculating through multiple related records

Discussion created by fleng on Jun 8, 2017
Latest reply on Jun 10, 2017 by fleng

Hi all,

 

The question:

 

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.

 

MY QUESTION

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)

= 25.000

 

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!

Outcomes