Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Value list based on calculation field


steveald
 Share

Recommended Posts

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.

Link to comment
Share on other sites

Thanks for the direction. Unfortunately, the 3 "Basics" links are dead and "2.4 Value List Table" doesn't seem to apply. Pending further information here, I'll see what I can found out about conditional value lists.

Link to comment
Share on other sites

You can read about conditional value lists in the first half of this post. Then make a value list showing related values only via the relationship you set up, FROM the starting point of the table that is native to your layout.

Link to comment
Share on other sites

Thanks for the response, AHunter3. But I believe that still leaves me with the issue of trying to create a value list from an unstored field that can't be indexed, doesn't it? Most of the threads I found, dealt with lists of words, not calculation fields dependent upon other calculation fields.

Link to comment
Share on other sites

There is (unfortunately) absolutely no way to directly make a value list out of unstored calculation field values.

 

Indirectly you can do it (and lots of other cute valuelist things) using this trick.

Link to comment
Share on other sites

  • 2 weeks later...

I am stumped as to how to make the text-based examples I've been shown work when the condition is a numerical calculation.

Link to comment
Share on other sites

The field you stick numerical values into has to still be a text field. Example: you have values 3, 5, and 13 that are relevant to your context and you'd like a dropdown value list showing those values.

 

You set a text field to 3 & "¶" & 5 & "¶" & 13, thus

 

3

5

13

 

 

... then you reference that field through the special selfjoin relationship and show related values only of that field, which will be the values from this record only. Hence your dropdown value list now contains 3, 5, and 13 to pick from.

Link to comment
Share on other sites

Here's the thing. I'm not really finding anything that addresses my specific requirement or the error I encountered attempting to do this prior to hearing about conditional value lists - that being an unstored field that can't be indexed because it references an unstored calculation field. Everything I've seen so far covers value lists containing words - not numbers that can be applied to calculations.

 

Perhaps spelling out my need in greater detail will help. I actually require four separate conditional value lists in one database. Here are the simpler two of the four - simpler because the same value lists apply to every record (the Size field is global):

 

If Size > 6 and

If Size > 9 and

If Size > 99 and

If Size > 249 and

If Size > 499, then value list 1 must contain 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170 & 180 and value list 2 must contain 10, 15, 20, 25, 30, 35, 40, 45 & 50.

 

Once the user selects a value from a value list, that value is applied to a record-specific calculation that results in another value that will be displayed with the selected value - i.e. selecting 50 yields 50 and 9.5 (where 50 x .19 = 9.5) for one record, while selecting 50 yields 50 and 13.5 for another (50 x .27 = 13.5).

 

The other two value lists are even more complicated because the numbers that are included in each list depend on a calculation field value unique to each record.

Link to comment
Share on other sites

A very simple alternative showing a self join relationship and allowing calculations on input and output variables was provided on another FM forum here. But thanks for all the suggestions.

Link to comment
Share on other sites

  • 3 years later...

Hey

I hope there are some who can help me.

 

I would like to make a field with a drop-down list where the value list changes by content of another Feldt. How do I do it ?

 

regards Morten

Link to comment
Share on other sites

It's called a conditional value list and there are oddles upon oodles of threads in this forum about how ot cretae a conditional value list.

 

If you have specific questions after reading through some of them, because your value lists have to do something odd that isn't like the ones described, post again at that point, this time with specifics about your value list's desired values and the conditons on which it should display those values.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use