Change your n calculation as follows
n = Case ( Get ( CalculationRepetitionNumber ) < 7 ; Extend ( MNL LMB A Min ) + Extend ( MNL LMB A Inc ) * ( Get ( CalculationRepetitionNumber ) - 1 ) ; Get ( CalculationRepetitionNumber ) < 8 ; 750 + ( Get ( CalculationRepetitionNumber ) - 6 ) * 250 ; 2500 )
Thanks, philipHPG. I think we're close.
I ended up with the same list of numbers for both scenarios: 125, 250, 375, 500, 625, 750, 1000, 2500
Two too many numbers for one and missing 1250 for the other.
I set both MNL LMB A Min and MNL LMB A Inc to 125 and changed MNL LMB A Amounts to the following based on your suggestion.
Let ( [ n = Case ( Get ( CalculationRepetitionNumber ) < 7 ; Extend ( MNL LMB A Min ) + Extend ( MNL LMB A Inc ) * ( Get ( CalculationRepetitionNumber ) - 1 ) ; Get ( CalculationRepetitionNumber ) < 8 ; 750 + ( Get ( CalculationRepetitionNumber ) - 6 ) * 250 ; 2500 ) ] ;
Case ( n ≤ Extend ( MNL LMB A Max ) ; n ))
Did I miss something?
I'm wondering if using repeating fields here is the best idea.
Regardless, I would make a calculation field that gives all the possible values for that record's value list. I would probably use a custom function to make it easier to update. At some point you may want to store these values in a separate table/ fields.
n = MNL LMB ST ;
extra = If ( n = 2015 ; List ( 1000 ; 1250 ; 2500 ) ;
result = List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ) & extra
Then make a related value list based on this new calc field.
Correction, I changed the 8 in your calculation to a 9 and got the missing 1250 to appear.
But I need the list to stop at 750 when MNL LMB ST = 2012.
MNL LMB A Max is currently set to:
If ( MNL LMB ST = "2012" ; 750 ; 2500 ).
David, is the calculation you provided meant to go in MNL LMB A Amounts? Entered as is, I got an error that the field "extra" couldn't be found. Perhaps I misunderstood your application.
No, it's supposed to be a brand new calculation field.
"extra" is a calculation variable defined in within the Let() statement.
I guess I'm not sure how to apply it then.
I got the solution over on FMForums. You had the right idea, David Jondreau. The trick was not to try to define a pattern, but to list the items explicitly. Here is the formula used in the self-joining field.
Let ( [
v = List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ; 1000 ; 1250 ; 2500 ) ;
i = Get ( CalculationRepetitionNumber ) ;
n = If ( Extend ( MNL LMB ST ) = 2012 ; 6 ; 9 )
If ( i ≤ n ; GetValue ( v ; i ) )