Jump to content

Variable increments in self-joining relationship calculation?


This topic is 2710 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Edited by steveald
Link to comment
Share on other sites

The logic here is not quite clear. Is the number 125 fixed or is it taken from another field and could be conceivably some other value? The same question applies to the minimum and to the increment.

Edited by comment
Link to comment
Share on other sites

Allow me to back up a bit then. I have a field (MNL LMB A Amounts) configured as a pop-up menu which needs to contain one of two sets of numbers depending on the value that appears in another field (MNL LMB ST). If calculations in a record result in 2012 appearing in MNL LMB ST, then the shorter list of numbers which starts at 125 and increments by 125 to 750 should appear. If, however, 2015 appears in MNL LMB ST, then the longer list of numbers should appear. That much is fairly basic. The issue is, while the longer list also starts at 125 and increments by 125 to 750, it then increments by 250 to 1250 and then increments by 1250 to 2500. Does that make more sense?

Using what I already know and have used several times before, I hoped there might be some way to configure MNL LMB A Inc to allow for multiple increments. If there is another better way to tackle this problem, I'd be glad to learn a new process.

Link to comment
Share on other sites

5 minutes ago, steveald said:

The issue is, while the longer list also starts at 125 and increments by 125 to 750, it then increments by 250 to 1250 and then increments by 1250 to 2500.

Another way to describe this "issue" is that the series do not follow any discernible pattern. Therefore the simplest method is to list the items explicitly. Try, for example =

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

You did not say what should happen when MNL LMB ST contains a value other than 2012 or 2015. The above will return the shorter list when MNL LMB ST contains 2012, and the longer list for any other value.

 

Link to comment
Share on other sites

There is only one other possible entry for MNL LMB ST - that being "No". It is a lookup field controlled by the State the record is set to. Records in eligible states are assigned either 2012 or 2015. Ineligible states are assigned "No" and those records have no use for the field with the pop-up menu. So your method works fine. It is possible that the state might be changed for a record though, so everything needs to take that into consideration - including the pop-up menu list.

I see what you did there. And that did the trick. Once I converted MNL LMB ST from a text field to a number field. And made the same change to the corresponding field in the lookup database.

The odd thing is, I can't seem to get all existing records to correctly show only 6 values when MNL LMB ST contains 2012. I tried Relookup, Replace Field Contents, as well as manually deleting and reentering data. It's not a serious issue as it involves only about 0.1% of the existing records and this field may never be used in those records. But, it's a little annoying that it's not working perfectly. Who knows how much trouble I ran into unnecessarily since the test record I was using is one of those that isn't working properly.

Thanks again for providing the needed solution, comment.

Link to comment
Share on other sites

2 hours ago, steveald said:

The odd thing is, I can't seem to get all existing records to correctly show only 6 values when MNL LMB ST contains 2012.

What do they show? And are you sure the field contains exactly 2012 and nothing else?

 

2 hours ago, steveald said:

Once I converted MNL LMB ST from a text field to a number field.

I am not quite sure why this would be necessary. In fact, if the field is allowed to contain "No", it should remain a Text field. For good practice the calculation should use "2012" instead of just 2012 - but that should not make a difference in terms of the result. This too leads me to suspect some abnormality in your data.

 

Link to comment
Share on other sites

That was the thing. After I applied your last formula, I checked it by switching the state for that record from one that showed 2015 to one that showed 2012. But the pop-up menu always showed the 9 value list. At first I thought "i" wasn't ever hitting 6. Then I looked at the fact that MNL LMB ST was a text field, as was the referenced field in the lookup database. First I tried modifying the formula, then I changed them to number fields. Somewhere in there I also thought to see if the issue only applied to existing records - which I have seen before. It worked correctly in a new record - and in some existing records. So, I tried the steps listed above to get all the existing records to work. (I need to be prepared for the possibility that I will want to use this field in an old, existing record - as well as new records.)

That's when I discovered it was only a handful of records that weren't working - including the first three in the database, which date back to 2003. I usually use the first record for all my testing. If something works there, it's sure to work everywhere else. Oddly, out of the over 1,200 records that are currently set to the states those first three records show, only a handful don't work - even after manually deleting and replacing values in both the state and MNL LMB ST fields. So, yes, I am sure the field contains exactly and only 2012.

I will switch the fields back to text since they can contain text and I now know this works and it won't make a difference. The possibility of file corruption, or "some abnormality,", had occurred to me - especially with the older records. If the company ever decides to shut down for a couple of weeks, I'll probably use the time to completely rebuild the databases from the ground up. ;-)

Link to comment
Share on other sites

1 hour ago, steveald said:

I may need to switch between lists something like "125, 250, ..., 1250, 2500" and "Y, N".

You didn't say what would be the criteria for switching. In general, you could do:

Let ( [
v = If ( test ; List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ; 1000 ; 1250 ; 2500 ) ; List ( "Y" ; "N" ) ) ;
...

Or, if you prefer:

Let ( [
vNum= List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ; 1000 ; 1250 ; 2500 ) ;
vBool = List ( "Y" ; "N" ) ;
v = If ( test ; vNum ; vBool ) ;
...

 

Edited by comment
Link to comment
Share on other sites

Just for ease of explanation let's say,  if MNL LMB ST does not equal "2012", then the Y / N list needs to appear (instead of the longer list of numbers).

Your example looks just like one of the formulas I tried myself. Unfortunately, the result is the list of numbers plus a question mark - whether MNL LMB ST is "2012" or "2015". 

Here's how I applied each version:

Quote

Let ( [
v = If ( Extend ( MNL LMB ST ) = "2012" ; List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ) ; List ( "Y" ; "N" ) ) ;
i = Get ( CalculationRepetitionNumber ) ;
n = If ( Extend ( MNL LMB ST ) = "2012" ; 6 ; 2 )
] ;
If ( i ≤ n ; GetValue ( v ; i ) )
)

 

Quote

Let ( [
vNum= List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ) ;
vBool = List ( "Y" ; "N" ) ;
v = If ( Extend ( MNL LMB ST ) = "2012" ; vNum ; vBool ) ;
i = Get ( CalculationRepetitionNumber ) ;
n = If ( Extend ( MNL LMB ST ) = "2012" ; 6 ; 2 )
] ;
If ( i ≤ n ; GetValue ( v ; i ) )
)

I'm understanding more than before. Just not enough yet to format solutions properly myself. I'm sure I missed something basic.

Edited by steveald
Link to comment
Share on other sites

10 minutes ago, steveald said:

Unfortunately, the result is the list of numbers plus a question mark - whether MNL LMB ST is "2012" or "2015". 

That's not the result I see. Both of your calculations work fine for me. I would check the value of MNL LMB ST only once:

Let ( [
vNum= List ( 125 ; 250 ; 375 ; 500 ; 625 ; 750 ; 1000 ; 1250 ; 2500 ) ;
vBool = List ( "Y" ; "N" ) ;
v = If ( Extend ( MNL LMB ST ) = 2012 ; vNum ; vBool ) ;
n = ValueCount ( v ) ;
i = Get ( CalculationRepetitionNumber ) 
] ;
If ( i ≤ n ; GetValue ( v ; i ) )
)

but that's a refinement that does not change the result.

Link to comment
Share on other sites

ValueCount makes more sense in this case. And your new formula works well - once I changed the Calculation Result to Text.

But my original test record got me again! I initially forgot to test this on a newer record. Oddly enough, where your new formula is working fine for almost all records (including two of the three that I said earlier were misbehaving), it's still not working right on the first record in the database. It's showing the full number list and then N and Y in the pop-up menu - for any value of MNL LMB ST. Durn abnormality!

Thanks again. I'll be able to use this many different ways, I think.

Link to comment
Share on other sites

8 minutes ago, steveald said:

It's showing the full number list and then N and Y in the pop-up menu - for any value of MNL LMB ST.

Wait a minute: are you testing this by looking at the popup menu? You should first put the repeating field on the layout and see what it contains. Your issue could be easily caused by having more than one related record - so check the contents of your matchfield.

 

Edited by comment
Link to comment
Share on other sites

I should be okay there. I first created a Calculation field MNL LMB A Amounts, which contains the formula we settled on above. From that, I created a self-joining Value List MNL LMB A Benefits, set to Use values from the field: "SameRecord::MNL LMB A Amounts". Finally, the designated field is formatted as a Pop-up menu using Values from: MNL LMB A Benefits. I create a new set of elements every time I set one of these up.

When added to the layout, the repeating field shows the correct values in every record I checked (either the list of numbers or Y and N) - including in that first record. But the pop-up menu in that first record (and in a handful of other old records) still shows the full list of numbers followed by N and Y (the N and the Y in reverse order). Weird.

Link to comment
Share on other sites

You nailed it!

I told you this was an old database. The SameRecord relationship is defined by a Serial Number field containing a value that is unique to each record. The trouble is, that first record and a handful of other records created years ago all have the same value - 0000. I'll have to see if I can override the values in those records with something unique to each one.

Thanks again.

Link to comment
Share on other sites

It was very fortunate you wrote earlier:

1 hour ago, steveald said:

it's still not working right on the first record in the database. It's showing the full number list and then N and Y in the pop-up menu - for any value of MNL LMB ST.

I was all set to declare this must be a corrupted file, when - at last moment - I noticed you said "in the pop-up menu". Then it clicked.

Link to comment
Share on other sites

  • 2 months later...
On 8/20/2016 at 9:08 AM, comment said:

Another way to describe this "issue" is that the series do not follow any discernible pattern. Therefore the simplest method is to list the items explicitly. Try, for example =


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

You did not say what should happen when MNL LMB ST contains a value other than 2012 or 2015. The above will return the shorter list when MNL LMB ST contains 2012, and the longer list for any other value.

 

Is there any way to control the order that the members of the List appear in the Pop-up Menu?

The reason I ask is - I am using this process frequently and, in some instances now, I need the numbers in the Pop-up Menu to appear in reverse order with the largest number at the top.

In the calculation above, it doesn't matter which way I have the numbers entered in v = List - 125 ; 250 ; ... ; 2500 or 2500 ; 1250 ; ... ; 125 or even scrambled. The numbers always appear in the Pop-up Menu in normal numerical order with 125 at the top and 2500 at the bottom.

That makes me think there is some function of Pop-up Menus that dictates the order in which a list appears. Does that mean there is a setting somewhere, or I function I can add to the calculation, to override the default order? 

Link to comment
Share on other sites

A value list using values from a field is sourced from the field's index and will always be sorted in ascending order (based on the field's type). Perhaps you could change the calculation's result type to Text and prepend spaces to the values so that they end up sorted the way you want them when sorted alphabetically. If you do so, you should also have an auto-entered calculation on the target field to remove those spaces.

Link to comment
Share on other sites

Thanks, webko. I saw where it talked about how to generate a de-incrementing list of numbers.

But I need to be able to specify the members of the list as they are often not equally incremented - like in the example comment posted above. Any thoughts on that?

Link to comment
Share on other sites

This topic is 2710 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.