Hi everyone,

I'm a first time user to FM, using FM8.5 having worked with Access and some VB previously.

I am wondering if I am able to have a dropdown list that shows only a select set of values from a table. Basically, I have a list of drugs that are either "active" or "not active", this value is captured in one field. What I would like to do is show only the "active" drugs for my users to pick from.

I've gone through some of the forum topics regarding conditional value lists and tested/rebuilt some myself. Those solutions refer to having the user first choose a value then having subsequent dropdown lists to display the filtered list. For this value list, I would like to avoid giving the users the ability to pick either active or inactive.

Any help is greatly appreciated.

You might have misunderstood the examples you found: wether the record is marked as active manually or by calculation is not really important.

For instance, let's say you have a calculated field that returns 1 if the drug is active, and on the list items side a calculated number field =1 if the item must be shown in the active list. Drag a relationship from the former to the latter, and set a value list based on this relationship.

So my advice is: choose the active drug :(

"Certified Dependency Rebuilder" eh? I guess you'd know your drugs.

I'd use a globally stored text field containing the text "Active". The relationship would be from this field in the local table to the Status field in the Drug table. When the relationship is established, your conditional value list will show only the Active Drug records in the list.

Why a global? Party it's my convention to use globals for this rather than unstored calcs, but mainly because it ensures the relationship will work in Find mode.

Thanks for the tips everyone! I'm still learning to understand how to make use of globals fields and variables but I think I'm going to try to create a global variable and have it fill with "ACTIVE" whenever the database is open.

Thanks again

Thanks for the tips everyone! I'm still learning to understand how to make use of globals fields and variables but I think I'm going to try to create a global variable and have it fill with "ACTIVE" whenever the database is open.

No you want to set a global FIELD; not a global variable. As Ender stated, you will need to have a relationship setup from the global field to the status field. You can not do this with a variable.

The basic concept of this is that you are matching all the record in the child table with the value setup in this global field in the parent table. If the value in that field is "Not Active" then it will display all the inactive records. If the value is something weird like "Blah" then it will not show you anything because no records match the criteria.