From: Show only Active from value list.

[CJinMadison]

To whom it may concern:

I'm brand new to this community. (Just joined this morning.) And, this is my first post.

Also (to make it challenging) I am relatively new to FileMaker. I've worked with MS Access for 10+ years.

So, I have created a table that lists Inspectors (people).

I have a field in that table called "Active". The allowable values for that field are "Y" or "N".

I am currently using this table as the source of values for a Value List. And, I am using this value list to populate a drop-down field in another form.

All of this (that I've described above) works just fine right now.

What I'd like to do is this: Some how train the value list to only select/display the values from the Inspectors table, WHERE ACTIVE = "Y". (Because I don't want my end user seeing a drop-down list that shows inactive inspectors.)

Thoughts? Any suggestions about how I can/should do that?

Thank you.

Cliff in Madison, WI

[bcooney]

Hi Cliff,

Welcome to FM and the forum.

Using a value list to populate a foreign key is a common technique. However, your choice of drop-down list is not recommended, as drop-down lists show the foreign key ID and not the much more user-friendly Inspector name. The preferred method is to use a popup menu since it allows you to hide the ID and just show a corresponding text value. However, if the popup menu gets too long, a popup selector window is used.

Limiting the value list to just "active" Inspectors requires an addtl field in the People table, flag_Active

Here's a demo of a filtered popup select that I've modified to include your need of only seeing active contacts.

PopupSelectNew2.fp7 2.zip

[Russell Barlow]

To whom it may concern:

I'm brand new to this community. (Just joined this morning.) And, this is my first post.

Also (to make it challenging) I am relatively new to FileMaker. I've worked with MS Access for 10+ years.

So, I have created a table that lists Inspectors (people).

I have a field in that table called "Active". The allowable values for that field are "Y" or "N".

I am currently using this table as the source of values for a Value List. And, I am using this value list to populate a drop-down field in another form.

All of this (that I've described above) works just fine right now.

What I'd like to do is this: Some how train the value list to only select/display the values from the Inspectors table, WHERE ACTIVE = "Y". (Because I don't want my end user seeing a drop-down list that shows inactive inspectors.)

Thoughts? Any suggestions about how I can/should do that?

Thank you.

Cliff in Madison, WI

You can search for Conditional Value Lists and find tons of different methods.

One way that will work would to use a constant key field in a relationship and only show related values using that relationship.

SomeTable::key_INSPECTORACTIVE and create a relationship between that field and your People::Active.

Then use that relationship to only show related values.

[bcooney]

It's not really a conditional value list, to me. Conditional value lists, as far as I know, are used when one field's choice affects the next field's choices. He needs, as I see it, a filtered value list.

Although I think that the popup selector is the best technique to master, to show only "Active" contacts in a popup menu field value list, he could create a new calc field in People, vlName.

vlName = If (flag_Active=1, Name, "").

Now make that calc field the second field in the value list (the first field is the hidden ID).

[CJinMadison]

Thx, bcooney. That is a nifty looking interaction that you zipped to me. I'll look it over and consider it.

Now, for what it's worth (kind of a separate topic; or a follow-up topic). But, I did get my drop-down list to behave in the way you described for the pop-up list. That is to say, I can get the drop-down list to both display the second field (InspectorName) and to hide the Key field (ID). (I am not typing this to try to show you up. I'm just nervous that I may have done something that I shouldn't have done.)

Thx for the help/direction.

Cj

Hi Cliff,

Welcome to FM and the forum.

Using a value list to populate a foreign key is a common technique. However, your choice of drop-down list is not recommended, as drop-down lists show the foreign key ID and not the much more user-friendly Inspector name. The preferred method is to use a popup menu since it allows you to hide the ID and just show a corresponding text value. However, if the popup menu gets too long, a popup selector window is used.

Limiting the value list to just "active" Inspectors requires an addtl field in the People table, flag_Active

Here's a demo of a filtered popup select that I've modified to include your need of only seeing active contacts.

[bcooney]

When you exit the field that is formatted to use a drop down list, the first field in the value list is what is stored in the field. And so, you should see the ID. Do you?

Some people use the technique of layering two fields. The bottom layer is the browse-enabled drop down list. The field on the top is a browse disabled related field (the text field). I hate layered fields, but I've done this.

[Lee Smith]

You can search for Conditional Value Lists and find tons of different methods.

That is an excellent suggestion.

It is strongly suggested that everyone who posts a new question perform a search for their Keywords, such as you suggested with Conditional Value list

for those not familiar with our search engine, you can get a better result if you use the Gear next to the Goole like window at the top of the page.

This search would look somehing like

Note that I added the pluses to each Keyword, this helps the search narrow things down.

[aesquer]

When you exit the field that is formatted to use a drop down list, the first field in the value list is what is stored in the field. And so, you should see the ID. Do you?

Some people use the technique of layering two fields. The bottom layer is the browse-enabled drop down list. The field on the top is a browse disabled related field (the text field). I hate layered fields, but I've done this.

If I may tag along on this thread, I'm in a similar situation. In this case, I absolutely want to use a popup menu, as I'm just showing a list of classes active during a current academic term, and it'll be a small list. Far as I can tell, I've set up everything as you said (except for my Active field being a number result—I'm trying to re-remember how to "mask" a 1/0 option as yes/no—but in my popup menu, only the name of the first class is showing up, despite three other options meeting the criteria as I'm testing it out. Any ideas/suggestions as to what may be wrong here?

[VincentO'B]

That is an excellent suggestion.

for those not familiar with our search engine, you can get a better result if you use the Gear next to the Goole like window at the top of the page.

Lee thanks for that tip, didn't realise that search existed, I had been using the Google one for weeks thinking surely there must be a better way to search this site Duh.

Vincent

[bcooney]

Why don't you post your file? You don't really need Yes/No, you just need Yes (1). Did you create a calc field that equals your text value if flag_Active =1? This is the second field in your value list.

[aesquer]

Why don't you post your file? You don't really need Yes/No, you just need Yes (1). Did you create a calc field that equals your text value if flag_Active =1? This is the second field in your value list.

I figured as much, I'm just using Yes/No because it was a value list I already had in place. I'm still fairly new to database design (at least on such a complex level), and I'm pretty much just learning as I go.

But never mind—I figured it out. Turns out my calculation was trying to form a number result for some reason. I could have sworn I had changed that, but that's what I get for working when my brain's already clocked out for the day. Thanks for the help!