Displaying names with a set value in drop down list

[Otis Reeves]

I have a database that tracks jobs.. Under each job I select which employee worked on that job using a Value list of my Personnel table. The personnel table has 200 names.. but only 50  still work for me. the Table has a Current employee field with values "0" for former employee and "1" for current employee. I want to only be able to select from employee in that table that has a value of "1" What is the best way to do this?

The Jobs table is linked with my Employee table Via "Employee ID#" 

After reading some of the post on this site I have decided maybe the best way to do with is to create Calculation field in the table to Calculate and Full Name only if the Current Employee Value is "1" but I am stuck with making this calculation work.. Am I on the right track or is there a better way?

 

 

[DanShockley]

In your Personnel table, assuming that table already has a Full Name calc field and the Current Employee Value field is a Number field with a 1 for active employees, create a calc field named Full Name Current with the following calc: 

If ( Current Employee Value
  ; Full Name
  ; ""
  )

Then, have your Current Employee Names value list use that calculated field as the second field (with Employee ID# as the first field for the value list) and sort on that second field, instead of the normal Full Name field that has a value for every record. Use all values - no need to have the value list depend on a relationship. This way that value list is available from anywhere, since it depends only on its own table. 

[eos]

create Calculation field in the table to Calculate and Full Name only if the Current Employee Value is "1" but I am stuck with making this calculation work.

 

A calculation field (result type text!) like cNameFullNameCurrentEmployee would (including comments) read:

 

Case ( 

  currentEmployee ;

  // your status field name; 1=True, 0/""=False 

  cNameFull ;

 // a pre-existing full name calculation field 

)

 

Am I on the right track or is there a better way?

 

But that's wasteful, since you're duplicating data that must be stored (in order to work in a value list); a simpler method with less overhead:

 

In your Jobs table, create a global calculation field with the result 1 (just the number); use that field as a match field against the currentStatus field to create a relationship to your Employees table.

 

Then create a value list with the employeeID and the name; select the option “Include only related values starting from“ with your Jobs table as the starting table (TO).

 

Use all values - no need to have the value list depend on a relationship. This way that value list is available from anywhere, since it depends only on its own table. 

 

As it is if you create via related values; just don't let that relationship break.