For the purposes of this discussion, I'm going to assume that the name of the table occurrence you're working with is called Source.
Create a global calculation field in Source whose value is "active".
On the Relationships tab of the Manage Database window, create a new table occurrence called Target (or whatever you want to call it) for the table that has your members' names and statuses.
Create a relationship between Source and Target with the condition that the global "active" field in Source must match the status field in Target.
in the Edit Value List window, click on "Specify field...", and, in the window that pops up, under "Use values from first field", select Target from the pop-up menu. In the pane below that, click on the field with your members' names. Below that, select the "Include only related values starting from:" radiobutton, and select Source.
If you want to be able to vary which members are displayed in your list (say, for example, you sometimes want a list for deceased members instead), then instead of linking to a global calculation field, use a global text field. Changing its value will then change which members appear in your value list.
Nothing wrong with that approach though you don't have to specify global storage for the calculation field. You can specify normal storage options and get the same result.
You can also use "Option 1" in the following tutorial if you prefer not setting up a relationship just for the one "hardwired" conditional value list:
> you don't have to specify global storage
Well, it'll use fewer resources that way. ;-)
And you'll almost certainly want global if you decide to use a dynamic filter (as described in the last paragraph of my previous post). Otherwise you'll have to make sure the filter is set correctly every time you switch records.
> You can also use "Option 1" in the following tutorial
Sure, but that method's harder to change if you want to alter the value list, and since the original poster mentioned using portals already, I figured setting up relationships wasn't foreign territory.
You're right, though. It is simpler, and there's definitely something to be said for that.
Global storage makes sense if you want to make the field a data field. Global calculation fields, in my experience, do not update like they should--especially over a network where I've seen some very strange results. (And I'm not talking about the way global fields changed by a client do not retain the chnaged value here.) An Unstored calculation, on the other hand works just fine for this.
For most uses, I dont' think the difference in how many resources are used will be significant for this kind of calculation.
Well our poster did say that he wanted simple didn't he? (Nothing wrong with using your approach and I prefer it in most cases over using a calculation field like this. Though it really isn't difficult to switch to the other, relationship based approach--just takes some time we don't have to spend if we set it up that way to begin with. )
> Global calculation fields, in my experience, do not update like they should--especially over a network where I've seen some very strange results.
Oops! I forgot about network applications. Yeah, that could definitely make a difference.
> An Unstored calculation, on the other hand works just fine for this.
> For most uses, I dont' think the difference in how many resources are used will be significant for this kind of calculation.
Hence the wink in my previous comment, but you've invalidated that by suggesting an unstored calculation. So much for my attempts at a joke.
> switch to the other, relationship based approach--just takes some time we don't have to spend if we set it up that way to begin with.
I'm a huge advocate of frontloading work. I'd much rather do all the heavy lifting in the beginning of a job, when only a general concept is expected, than later, after the project is being used, and somebody is panicking and needs results right now! Also, if you decide you need to make a change several months from now, and if you haven't built your solution for expansion, it's much harder to re-acquaint yourself with how your database is structured and how to accomplish what you want.
But like I said, simpler definitely has its merits as well.