5 Replies Latest reply on Jun 10, 2017 1:53 PM by fleng

    Help calculating through multiple related records

    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!