Filtered Value List

[RyanESmith7]

I would like very much to setup a Value List that is filtered. I have several popup menus throughout my DB that use the Employees table and it displays the "Full Name" field. However, I would like to limit it to just active employees (where Inactive = 0).

 

How do I do this?

 

http://prntscr.com/19y4ik

 

Thank you,

Ryan Smith

 

[doughemi]

First you have to populate the active field with a 1 for active employees.  In FileMaker, both 0 and an empty field are evaluated as a Boolean False.

 

Create a calculation field ActiveKey = 1.

Create a new Table Occurrence of your table.  Make the relationship

 

YourTable::ActiveKey = NewTO::Active

 

Set the Value List to show only related records from NewTO.

[RyanESmith7]

Thank you very much for the reply.

 

I followed your instructions and they worked on my test layout. But when I tried to implement them on my main form, they did not work. Does there have to be a relationship between the layout table and the value list table?

 

The main form has relationships to the Employee table through three different TOs, but no relationship to the Employee table itself. Do I need to create a Value List for each one of those other TOs?

 

Thanks again,

Ryan

[RyanESmith7]

Nevermind, I kind of answered my own question. Thank you, you've helped immensely.

[RyanESmith7]

OK, I'm going to try to resurrect this. It kind of works. A little background... I have an Employees table and a Jobs table. There are several different kinds of Employees assigned to a job (think Project Manager, Salesman, etc...). Because there are multiple relationships, I created one TO for each relationship between Jobs and Employees. Now I have another TO for the "Active Employees". 

 

So... the above is working IF an employee is already assigned for the respective field. It does NOT work if the field is blank.

 

Just to clarify:

 

Jobs::CS_Assigned -> Emp_Jobs_CS_TO::User ID

Where Emp_Jobs_CS_TO is a table occurrence of Employees.

Emp_Jobs_CS_TO_Active is a table occurrence of Emp_Jobs_CS_TO with the ActiveKey relationship as defined in the previous post. 

Jobs::CS_Assigned is setup as a popup menu based on Emp_Jobs_CS_TO_Active related to Emp_Jobs_CS_TO. 

 

If Jobs::CS_Assigned has a user id in it, the popup list is correct. If it is blank, it shows <no values defined>

 

Short of making a bogus user and auto-assigning it, is there any other solution? Am I doing something wrong?

 

Thanks!