Autoenter data from a field related through a separate relation

[Spod]

Hi folks. I am new to the board and look forward to being a member.

This initial problem I have undoubtedly has the simplest of solutions but I am staring at these data files totally lost and going stir crazy. I hope this is the appropriate section for this post.

OK...

I have a customer database which has a related table for the individual staff members of each respective customer in my database. i.e. the unique serialised 'Contact ID' field in the customer table is linked to the staff table with the same field name. So each customer can have multiple staff members. The email addresses and mobile phone numbers of each staff member are also stored along with their names. Easy stuff.

I also have a quotation database which is also related to the customer database in the same way. i.e. each customer can have multiple quotes with the Contact ID field being the linked field.

My need is as follows. In the quotes database, when I create a new quote for a company, I would like to present a pop up list of the staff of the company for which I am doing the quote so that I can target any individual quote to the staff member of that company who requested it. That's easy using a pop up menu with a value list from the customer database containing all the related staff members names. However when the staff member's name is selected I would like their individual mobile phone number to autoenter in to a field in the quotes database. [same goes for their email]. The aim of which is to look at a quote and have the staff members names and their individual phone/email information available in front of me.

For the life of me I can't see what is probably a quite simple solution.

I hope I have explained this in a clear manner!

[comment]

You need to define a relationship between Quotes and (another occurrence of) the Staff table, matching:

Quotes::StaffID = Staff 2::StaffID

Then place the phone field from Staff 2 on a layout of Quotes.

[Spod]

You need to define a relationship between Quotes and (another occurrence of) the Staff table, matching:

Quotes::StaffID = Staff 2::StaffID

Then place the phone field from Staff 2 on a layout of Quotes.

Thanks for the reply.

You were quite right - although specifically my issue was getting the mental link between the use of the drop down and the relationship. i.e. because the link between quotes and staff was based on a unique serialised 'staffid' and not the staff members name then I was stuck with user choosing by name but the link itself had to be an ID number. I didn't want the ID number visible for aesthetic reasons so I couldn't use the convoluted value list feature that drops down one list but enters the data from a different field.

In the end I made a new relationship with staff (staff 3) that had both the staff 3:contactid=quotes:contactid AND staff 3:name=quotes:name which allowed me to autoenter the staffid in to the quote and therefore find the phone number via lookup via staff 2.

Once again I appreciate your response.