Value list or no value list?

[marcchn]

I'm trying to do, what seems like, a very simple task. I have a task management database in which, among other things, assigns individuals to various project tasks. I have a table called 'Personnel' which has fields such as first and last names, and status. Status takes its values from a value list called 'personnel_status' which has two choices: Active, Inactive (inactive means they are no longer an employee). On my Task layout, I have a drop down field (the foreign key from the Personnel table) which takes its values from a value list called 'assignment' in which the list is populated by the the primary key of the personnel table (a 2nd field--the employees full name--is displayed). Currently, the drop down displays every name (active and inactive). I would like the drop down to display just the names of the active employees.

Any help would be greatly appreciated.

[eos]

Here's one way to do it:

In Personnel, create a calculation field (e.g. gStatusFilter) = "Active" with global storage, type text. Create a self-join from Personnel to (say) Personnel_SelfActivePersonnel, with gStatusFilter = Status. Create a related value list which shows PersonnelID and cFullName from Personnel_SelfActivePersonnel, starting from Personnel. This is your filtered value list.

One slight problem: The value list relationship is used to resolve the ID into the name for the display of the popup. This means that when you set someone who has been assigned in Tasks before to status Inactive, the relationship isn't valid anymore, and though the ID field still holds the Personnel ID, it won't be resolved into the name anymore, so you see just the numeric value.

You can work around the problem by utilizing (or adding) a relationship from Tasks to Personnel which uses only PersonnelID on both sides, not the status. Then make the foreign ID field with the popup menu very small and beside it show the related "cFullName" field from Personnel via the new, unqualified relationship. Make cFullName non-enterable (as well it should be) and put a script (or script step) on it that simply Selects/Performs the ID field; this will open the popup for selection. The related field will always show the name belonging to the foreign ID, regardless of that person's status.

Hope this all makes sense; if not, let us know.

[marcchn]

Thanks for the reply. I tried everything you suggested. However, now the drop-down list is blank (no names appear). I'm at a loss.

[eos]

Thanks for the reply. I tried everything you suggested. However, now the drop-down list is blank (no names appear). I'm at a loss.

Hmm, I guess you must make the lookup from Task, not Personnel…

Anyway, here's a very barebones example DB which should get you started:

SimpleFilteredValueList_eos.fp7.zip

[marcchn]

Got it to work using your example. Thank you very much for your help.