I think I'm over thinking things here. So I need help.

In tableA that has 2 fields Name, Status

In tableB, I have 2 fields. The first field has a value list that shows ALL TableA:Name.

I want the 2nd field to show TableA:Name where Status = "Active"

How do I do that?

Thanks in Advance.

Scott I hope you are ready for some tedious work. It's pretty annoying to achieve what you are asking because filemaker doesn't allow you to create relationships based on a constant.

Alright first thing you need to do is to create a field in TableB and make it a calculation whose result is "Active". Make sure you have the function evaluating to text. I always for get to change the stupid thing.

Once this is done you are going to have to create a relationship to TableA from TableB through this new field to the field TableA::Status. You are going to need a new Table Occurence to do this so just name it something like ActiveTableA.

Now that your have your relationship setup all you need to do is create the value list and select the field ActiveTableA::Name as what you would like to display.

It's a little convoluted but basically you create yourself a relationship to all the active TableA records and then display the name values. Good Luck!

What if you have a table for Staff with a field called status (active, inactive). And that the staff code is entered in a number of tables (such as timesheets, payroll, holidays etc). For each of these layouts, only want active staff in the value list to display. If I understand your solution, do I have to create a TO (and therefore value lists) of active staff for each of the tables/layouts that use active staff codes?

Is there some other way where I can use just the one value list of active staff no matter which layout it is used on?

Thanks - Brent

Well what SixFriedRice said worked! Thanks for your help.

Hi. I need to take this another step forward.

I have the filtered value list as described above. is there any way to have the filtered list be sorted the way I want it?

I thought I could add (to tableA) a number field "Order" and then have the list sort by Order. But I have to display "order" in int list, which will confuse people.

Hope that makes sense.

Thanks