Filtered Value Lists

[OPS]

I have a table called Employees. These are either Current, Freelance, or No Longer Employed.

I want a list available to select current employees. I have a filed called Currentcalc which is a 1 if the employee status is current. All I want to do is to go to an unrelated table and be able to select current employees from a lost. I simply cannot get my head around how to filter lists. I have tried every possible combination of settings and cannot understand the logic of filtered lists.

This should be so simple but I have now wasted an entire day to it. I cannot find a simple explanation on how this works, anywhere. Can anyone explain to me how drop down lists, based upon a calculation filed work?

[comment]

A filtered value list requires a relationship. If you don't want to add a relationship between the target table and the Employees table (a relationship that would only include current employees), you can get the same result using a different method:

Define a calculation field in the Employees =

If ( Status = "Current" ; EmployeeID )

and  define your value list to use values from this field (I am assuming, of course, you will be selecting the EmployeeID and showing the employee's name as the second field in the value list).

 

1 hour ago, OPS said:

I have a filed called Currentcalc which is a 1 if the employee status is current.

I am not sure why you would need such field.