value lists as "type" filters for records
Forgive my inexperience...
I'm trying to filter people into various groups according to their job type (job type is a value list) and then have another value list of people's names of that type. So when entering information about Barbara, I can indicate that she's in sales. On another layout based on another table I can select the sales people, and see a value list in which Barbara's name will appear -- and I would like those instances of Barbara to reference the same record.
Currently, I'm using a script and two additional tables to do this. The script essentially looks for people of that type, copies their name into the value list (sales) and copies the name and id number into a separate table that stores that information for people of that type.
I've run into a number of problems with this, and it seems to me there must be a more elegant solution. My understanding is that the essential problem is getting a particular value in a value list to relate to a record. Any advice about how to simplify this would be greatly appreciated.
Hope that makes sense.