Lookup: 2 fields into one?
So...how do I have a pop-up menu that shows only related records between company and people, that when selected creates a lookup of both the first and last name in the field (PA for example), and enters the full name into the field?
I have a layout based on a Job Table. I want to select a company from the Company ID field in the Job table and then select a related employee in a number of fields in the Job Table. The tables Company, Job and People are all related via company ID. The fields I have in the job table are PA, Copywriter, Director, etc. I have managed to show only records related between company and people for the jobs field, so I can only select employees, using a pop-up, from the chosen company. I created a value list to show related records from the people occurence connected to the Job table. Great!
My problem begins when a chosen person moves company and their company ID changes. As I'm using a value list based on First and Last name from the people table, only the first name remains.
I basically want to create a lookup that will remain the same in the job table even if a person changes company...I think?