Some additional info:
1) I am using "refresh window[flush cached join]" in order to get my calc field to update every time I select from the popup. I think this step is screwing up the pop up and forcing it to become the first value of the value list. (i.e. the value is no longer in the value list, so it can't look up the referenced second field).
2) I tried using a catersian join with a text field from the parts table to cause a refresh without using the flush. This does cause the CALC field to update, but, the value list no longer dwindles.
3) I have to tag on a "-1" to the CALC field's result in order for it to show all UMs in the value list when the CALC field would normally be empty. (is there any fix for this?)
I assumed I want the CALC field to result in a "Number" because it is in a relationship with another number.
Since List is a text function, I've specified "text" as the result type when implementing this type of value list in my solutions. If you are getting the correct behavior, (the list dwindles with each new selection), then it seems we can set it up with either return type.
1) The issue with the number appearing in the pop up menu is a "nature of the beast" issue. You are correct that when the value is removed from the value list by the calcualtion and relationship, the pop up menu can no longer display anything but the ID number that is actually entered into the field. This is a case where directly entering the units instead of an ID number may make more sense. Otherwise, you'll need to add a field from a related occurrence of the same table that links by the selected ID number (not the dwindling value list calculation) to display the units.
2) I've been checking my own demo file where I have a dwindling value lists. It turns out that I used Refresh Window ["flush cached join results"] to update the value list in it as well. This is an undesirable script step as it can produce very slow screen refreshes, but I don't know of a work around for it for a dwindling value list.
3) Including a -1 (or some other value that never exists in the list of possible values) is how I handle this issue in my own solutions. It works well. Why is this a problem in yours? (You have to have at least one value in the calculation field or the relationship, even though based on "not equals" will not work.
Just did some experimenting with a method for avoiding Refresh/Flush that I Learned from Mark Gores and found that it also works for a dwindling value list. While this eliminates the need for this undesirable script step, it will not change the issue of the pop up menu displaying the ID number when you want the units label.
You can use this relationship for your dwindling value list:
Parts::gRefreshTrigger X Ums Available::Refresh AND
Parts::CALC_used UMs for... ≠ Ums Available::ID
Define gRefreshTrigger as a global field.
Define refresh to include this auto-enter expression:
Now, instead of Refresh Window [Flush
Set field [Parts::gRefreshTrigger ; 1 ]
One complication from this change is that, for some reason, gRefreshTrigger must contain a value in order to see any values in the value list so you may want to include a script that sets the value of this field each time you open the file.
I have updated the demo file where I have this setup: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
I'll try your last suggestion later today. I'm picky and I don't like having to make sure that the global field has a value in it, but maybe I'll just have to do it...
I'm ok with the pop up showing the ID now. I'll just put it behind another field that has a reference to the actual UM code. I had to do this somewhere else in my solution and it worked pretty well.
When you say that the flush step cause slow screen refreshes, what is the underlying reason? Is it related to # of records? Connection to the file (i.e. over filemaker network)? or a function of complexity of the complete file?
As always thanks for your help.
As I understand it. That option takes FileMaker all the way back to square one and requeries everything you see on your layout. Large numbers of related records, slow networks, FileMaker Go clients, etc can be presented with layouts that refresh very, very slowly. Thus, it's better to design your database so that this script step is not needed.
I've also noticed a very visible and some what irritating "flash" to the window with Refresh window [flush that does not occur when I use Set Field on the global field to update the dwindling value list.