I apologize in advance if this is a trivial question, but I can't figure it out and I've read all the other threads on the topic; I'm hoping someone can tell me what I'm doing wrong.

I have a small database that's used by a healthcare provider to keep track of clients and treatments. It contains two main tables: "Client Info" and "Treatments". On the main layout for "Treatments", there is a drop-down menu that allows the user to select a client that the treatment relates to. This works pretty well, except that there are several hundred clients in the "Client Info" table and only 30-40 are active. We want to limit the contents of the drop-down list to only active clients.

To do this, I first added a new text field to the Client Info table, called "Active", which is filled by a radio button and a value list, as either "Yes" or "No". I went through and set this for all the clients; most are "No" but a few are "Yes".

Then, trying to follow the instructions from this question, I created a calculated text field called "Yes" which is filled by a calculation (the calculation is always "Yes"). Then I created a new instance of the "Client Info" table in the Relationships view, which I called "Client Info Active". I related "Client Info Active" to "Client Info" by requiring that Client Info::Active be equal to Client Info Active::Yes.

Then I went and defined a value list that's equal to the client names in "Client Info Active", and used this for the drop-down menu. (See attached)

However, the drop-down menu contains ALL the client names; it's not being limited by the "Active" relationship.

I've also tried messing around with the "Include only related values" option in the Define Value Lists box, but to no avail. Either I get nothing in the value list, or I get everything. I can't get it to only give me the client names and IDs whose "Active" field is "Yes".

Can anyone help me? I've included a screenshot of the Relationships dialog as it currently appears.

Please see the attached DB for help.

Cheers

Phillip

ValueList.zip

Sounds like what you want is usually referred to as a dwindling value list. "Comment" has posted a DwindlingVL file on here. I used it and thought it was relatively easy to set up.