I've come close, but I'm seeing behavior I don't understand. Instead of basing the lookup dropdown on StoryID and Name, I will use the two fields I added to the STORIES table. These are calculation fields are called StoryIDDynamic and NameDynamic. If Retired is false, StoryIDDynamic and NameDynamic are the same as their counterparts of StoryID and Name. If Retired is true, then I set StoryID to 0 and NameDynamic to "--". I would like the dynamic ones to be blank, but I need to return a value. I figure at least all the Retired ones will get reduced to "--". The odd thing is, of the 11 story records, it now shows only 4 of them.
I think I would do it this way:
In the SUBMISSIONS table, define a global text field gRetired. Define another relationship between SUBMISSIONS and STORIES (using a new occurrence of the STORIES table) as:
SUBMISSIONS::gRetired = STORIES 2::Retired
When gRetired contains 1, only retired stories will be related. When it contains 0, only active stories will be related. When it contains both (separated by a carriage return), all stories will be related. Note that this assumes the Retired field in STORIES is never empty.
Define your value list to show values from STORIES 2::StoryID.
Thank you for the input. Ultimately, I used the shadow field that was calculated but stored as an actual value. I simplified my original problem description, perhaps too much. I had a need to always display the records regardless of the setting. I was only hiding certain values from the dropdown based upon a user customization setting. Again, thank you.