Filtered Value Lists

[Sally1]

I have been struggling over something that I am sure has an easy solution. I am trying to create a filtered value list for the employees table. I have a table with active and inactive employees, some of whom are salespeople and some are not. I have created a calculated field to determine which employees are active salespeople and created a self-join in the employees table based on the calculated field on one side and the primary key on the other. I want the value list to show only those active salespeople, and I want to record the primary key for the employee, but only show the full name on the value list. I have tried every possible combination, including global fields for the calculation and nothing seems to work. I have isolated the employees table and shown a couple of the value list tries and relationship tries. Any help would be appreciated.

Filtered_Employees_Copy.fp7.zip

[bcooney]

In order to show a filtered value list, you need another table. I've added a new table to this demo so you can see how a filtered value list is created.

I see that you are prefixing your EmployeeID with "EE0". It's not the best approach. See this thread as to why: Serial IDs and Prefixes If you need "EE" before the serial, create a dsp_EmployeeID that is a calc. This is not used in relationships, though.

Also, I've renamed some of your fields. I follow the FMI naming conventions on keys, using "__kP_" as a prefix for all primary keys. That's two underscores preceding the "kP". This will sort it to the top when sorted alphabetically.

Filtered_Employees2.fp7.zip

[Sally1]

Thanks for the reply. As for the naming conventions, this table was one of the first I added to the database. The others all follow your examples. I am also going to be removing the EE from the ID because I will eventually be importing data from a Microsoft Access database that has a number as the ID, so this is just temporary.

As to the solution. I can see that it works and I thought of something similar with using a global field as a constant of one and then relating that to the status field. That didn't work. One question, on your solution -- your additional table has 3 fields - an ID, the constant and then the employee ID. How does the employee ID field on that table get populated? What am I missing?

[bcooney]

The EmployeeID is populated when you select an Employee from the popup menu. The field is set to use a value list. The value list is defined to consist of two fields, ID and Name, but only display the second field.

Look at the Value List definitions.