Dynamic lookup of related table values to filter the list
I think this is simple, maybe I'm just the approach to do this. I want to dynamically filter which records to show in a dropdown box lookup.
I have the following TABLE/field:
Retired (number as boolean)
StoryLookup (calculation ==> from Submissions, =Lookup( Stories::Name )
The above works great for being able to specify a Story to associate with a Submission. However, some of the stories become obsolete and Retired is set to true. I would like to set a global variable called $$HideRetired to True and have the lookup list be filtered to show only the unretired Stories in the dropdown on the Submission layout. The reason for the global is that for some users, they want to see all stories, while other users will want to hide the retired stories. I can easily capture that in a global.
I did not see how I could dynamically build a list via script. Ultimately, I would like this to work in a runtime solution.
I am use FMP 10.0v3 on Mac OS 10.6.2 (Snow Leopard).