You can do this with a conditional value list.
Set your value list to list contact names from Contacts
and select the "Include only related values starting from Invoice", option.
Once you've selected a company ID in your invoice layout, this value list should only list that company's contacts.
That worked really well, thanks.
One more question...
Now that I can choose the names from the pick list, is there a way to populate the person's phone number and email based on the contact info?
Yes and there are two different ways to go about it depending one what you want.
SInce such contact info can change over time, you'll probably want to copy the information into matching fields in your invoices record. That gives you a record of the contact's info at the time the invoice was created. With this approach, changes made to the info in contacts do not auotmatically update the contact info in invoices.
Both methods require a relationship from Invoices to contacts without companies as an "in between" table so that your invoice record links directly to contacts.
Contacts, should have a ContactID serial number field since it's possible for two contacts to have the same name.
Add a contact ID field to invoices and make it your conditional value list, but modify the value list so that contact ID is in column 1 and name in column 2. Use this contactID field in place of the contact name.
Make a new relationship linking invoices directly to contacts. (Click contacts in Manage | Database | Relationships and then click the button with two + symbols to make a new table occurrence of contacts.)
Invoices::ContactID = ContactsByID::ContactsID (ContactsByID is a new table occurrence of contacts.)
Define a matching field for each data field in contacts that you need and use the looked up value options to look up values from contactsByID.
If you don't want to copy the data, don't bother with these fields and looked up values. Just place the fields from ContactsByID that you need on your invoice layout. In this option, edits to the contacts data will automatically appear on all invoices that link to that contact. (Most businesses want to see the contact info that was current at the time the invoice was created, but it's your choice as to which is the preferred option here.)
I have one more question related to this...
I have the Contact Field in the invoices table looking up the F_Name in the Contacts table using the first method you gave me. I'm now being asked if I can make the last name populate as well as the F_Name. I checked in the value list that I wanted a second category and now it displays both the first and last name, but when I select a person from the drop down list, it only copies the first name into the field.
Is there a simple way that it will display both the first and last name together in one field?
Add a calculation field that combines the first and last names such as:
Fname & " " & Lname
Remove the individual name fields from the layout, replacing them with this calculation field.
You can put the same type of calculation in your contacts table and refer to it, for column 2, keeping contactID as column 1.
(You really don't want to link these by a person's name, it's very easy to have two people with the same name and sometimes they change their names.)
Will this cause problems if someone has a middle initial?
And I put the calculation field in all three tables (Invoice, Companies, Contacts)?
Not really, you just need to include the middle name/initial field in your calculations if you have a separate field for that.
You can include this calculation field in all tables where you use looked up values (You don't need this in tables where you don't look up a name.), or you can just define it in contacts and look up the full name calculation into a matching text field. I prefer separate name fields as it gives me a few more options for sorting and searching tables when using name fields. (Entering "John" in a first name field may find a different group of records than does entering "John" in a combined name field.
Keep in mind that you have another way to display a combined name that doesn't require any calculation field at all.
You can place merge text such as <<Tablename::FName>> <<TableName::MName>> <<TableName::LName>> on your layout.
That aproach has some limitations, but it often meets the need without having to define an extra calculation field.
Grandchild issue: If you have structure of:
Invoices::CompanyID >--- Companies::CompanyID ---> Contacts::CompanyID
... and you select your ContactID using conditional value list, it will insert the correct ContactID into your invoice BUT ... if you use lookup, calculation or simply display the contact's fields on your layout, you will always get the FIRST contact.. It is a perspective issue and very difficult to understand. Some consider it a bug and others think it acts like it should. I don't care - but it can bite you when least expected.
So, as suggested, use a new relationship from Contacts::ContactID = Invoices::SalesRepID