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

# Variable increments in self-joining relationship calculation?

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?

• ###### 1. Re: Variable increments in self-joining relationship calculation?

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 )

• ###### 2. Re: Variable increments in self-joining relationship calculation?

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?

• ###### 3. Re: Variable increments in self-joining relationship calculation?

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.

Let( [

n = MNL LMB ST ;

extra = If ( n = 2015 ; List ( 1000 ; 1250 ; 2500 )  ;

result = List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ) & extra

];

result

)

Then make a related value list based on this new calc field.

• ###### 4. Re: Variable increments in self-joining relationship calculation?

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 ).

• ###### 5. Re: Variable increments in self-joining relationship calculation?

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.

• ###### 6. Re: Variable increments in self-joining relationship calculation?

No, it's supposed to be a brand new calculation field.

"extra" is a calculation variable defined in within the Let() statement.

• ###### 7. Re: Variable increments in self-joining relationship calculation?

I guess I'm not sure how to apply it then.

• ###### 8. Re: Variable increments in self-joining relationship calculation?

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 ) )

)