8 Replies Latest reply on Aug 21, 2016 7:50 PM by steveald

    Variable increments in self-joining relationship calculation?

    steveald

      XPOST

      I need the options that appear in the pop-up menu for a field (MNL LMB A Amounts) to change based on the value in a separate field (MNL LMB ST) which is unique to each record. Specifically:

      If MNL LMB ST = 2012, then the pop-up menu needs to show: 125, 250, 375, 500, 625, 750

      If MNL LMB ST = 2015, then the pop-up menu needs to show: 125, 250, 375, 500, 625, 750, 1000, 1250, 2500 (note the changing increments)

       

      As done previously, MNL LMB A Amounts contains the following calculation and makes use of a self-joining relationship in Value Lists to generate the desired options:

       

      Let ( [ n = Extend ( MNL LMB A Min ) + Extend ( MNL LMB A Inc ) * ( Get ( CalculationRepetitionNumber ) - 1 ) ] ;

      Case ( n ≤ Extend ( MNL LMB A Max ) ; n ))

       

      Where:

      MNL LMB A Min = 125

      MNL LMB A Max = If ( MNL LMB ST = 2012 ; 750 ; 2500 )

       

      My problem lies with MNL LMB A Inc which is easily defined as 125 when MNL LMB ST = 2012; but when MNL LMB ST = 2015, it needs to be 125 first, then 250 when 750 is reached, and then 1250 when 1250 is reached.

       

      Is there a calculation for MNL LMB A Inc that will accomplish this? Or another method to achieve the overall goal that I am not aware of?