Value List and ID Key conundrum
Apologies in advance for my newbie question.....
First the background:
I am building an invoicing/customer database that contains two tables: Company and Person.
The Person table has fields for the person's name, contact details, the company ID for whom they work plus an ID for the person.
The Company table has fields for the ID of the person who deals with invoices and another ID for the person who deals with deliveries.
When browsing a Company record I'd like to present the user with two drop-down menus of the names of all people who work for the company to allow them to select which person deals with invoices and which with deliveries. This I can acheive by creating a relationship between the Person's Company ID field and the ID field in the Company table. A Value List can then be defined to contain only the Person Names that are related to the current Company ID. However, I don't have a field in the Company table for the invoice Person's name, only their ID. I then resolve and display their name by a relationship between the Company: Invoice Person Id and the ID for the individual in the Person table.
I can't quite figure out how best to solve this. It would appear that I need to use a drop-down menu of the Person IDs instead which, when selected, will allow the Company layout to resolve the person's name from the Person table. However this is extremely unhelpful for the user!
If I were to use the Person's Name as the key instead of the ID then this would solve the problem but I'm sure that I'm going to come accross this again and again (e.g. Product Names & IDs when I build the invoicing database) so I thought I should find out how to do it properly from the outset and retain numerical IDs as keys for all tables.