Value list based on calculation field

[steveald]

I found several similar posts, but nothing that seems to fully address what I need.

To start with, I have a calculation field that determines a maximum value for each record. I need to create another field that provides a list of numbers with a specific starting point, specific increment, and that maximum value field as the last number. Then, I need to create a value list that uses the values from that list field.

The net result should be a pop-up menu that contains a list of numbers up to a maximum value associated with that record. i.e. record 1 will show the following list in the pop-up menu: 50, 60, 70, 80, 90; while record 2 will show: 50, 60, 70; while record 3 will show 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150.

I found a custom function here (http://www.briandunning.com/cf/1172) that should allow me to create the field containing the list of numbers, but I'm doing something wrong as it doesn't appear to work.

I also found this information (http://fmtechniques.blogspot.com/2009/12/quicktip-creating-drop-down-list-from.html) that addresses the issue of trying to create a value list from an unstored field that can't be indexed because it references an unstored calculation field which itself can't be stored or indexed for the same reason. But, that didn't help - perhaps because of the custom function not working.

If there is another, better way to get a value list that varies from one record to the next based on the original calculation field; I'm certainly open to suggestions.

Mac OS X 10.7, FMP 11, with the intent to use with FM GO.

[steveald]

No suggestions?

[comment]

How about this way?

RangedValueList.fp7.zip

[steveald]

That did it! Thank you!!! The only other responses I was getting elsewhere involved self join relationships with multiple tables and text value lists. I can create calculation fields to supply input data and I can even apply the value selected from the value list to other calculations. Perfect!

Mind if I share your solution?

[comment]

"Share" as in ... ?

[steveald]

Show another FileMaker forum a much simpler way to do what I asked.

[comment]

Feel free to point them here....

[steveald]

Good point. Thank you.

One more question. I also need to be able to include the number 0 as an option in the value list. However, the first non-zero number is larger than the increment amount. i.e. an example of a list would be 0, 20, 30, 40, ... (other than 0, 20 is the minimum number and the increment is 10). Can you tell me how to modify the calculation for cValuesR in your sample database to accommodate this?

[comment]

Change the last line to =

Case ( n ≤ Extend ( End ) ; n ; 0 )

[steveald]

Perfect! Thanks again.

[layhesim]

On 4/25/2012 at 10:43 PM, comment said:

How about this way?

RangedValueList.fp7.zip

Hi.  Your example was a great help but I would appreciate your help to clarify one point please.  Why is the list value empty when referenced from any other table than the one with the self join?  To provide some context, I am trying to setup a drop down list where the user is able to choose from a list which comprises a card coded text and all of the product descriptions in the product table.  I can easily calculate the list in a global field of a table using the List() function.  I want to use the list in a number of different tables but the list only works when accessed from the table in which the List() calculation resides.  Thanks, Simon

[comment]

On 10/3/2019 at 1:47 AM, layhesim said:

Why is the list value empty when referenced from any other table than the one with the self join?

The value list is defined to include only related values. This allows each record to have its own values, calculated from the contents of the Start, End and IncrementBy fields. If you want to use such value list from the context of another table, you use make sure the other table is related and that the starting TO is defined in such way that the other table can "see" the related values.

 

On 10/3/2019 at 1:47 AM, layhesim said:

I am trying to setup a drop down list where the user is able to choose from a list which comprises a card coded text and all of the product descriptions in the product table.

I am afraid I did not understand this part.