First thing you're missing is upgrading to a supported version. 11 is beyond end of life, so upgrading to the current version (16) is recommended as soon as possible.
That said, value lists will filter out what is considered a "duplicate" value, so whatever value list settings you have are treating each of those contacts as a duplicate.
So maybe you are just selecting "last name", where you should be selecting something like "Last Name, First Name".
Or even better, how about "ID, Full Name".
To make a value list that has ID and Full Name, allowing for truly unique contacts that allow for two people with the same name to function properly:
Make sure you have an ID field in your contacts table, this can be a simple auto-enter serial number like so:
Then, make a text field "Full Name" in your contacts table, give it an auto-enter calculation of something like:
Contacts::First Name & " " & Contacts::Last Name
Now, in your value list, check the box that says "Also show value from second field", in the first column, select ID as the field. In the second column, select "full name".
You will now have a value list that shows "ID - Full Name", and selects the contact ID for your invoice.
As a point of info, you want to store the ID of the contact to properly relate invoices to a selected contact. DON'T do this by name because name is NOT unique.
THANK YOU for posting the fact that you are still using v11. That saves us from suggesting things that don't work in that much older version.
First, the relationship linking these two tables should link by a unique customer ID, not their name. If you don't have one, you should add one and change the relationship.
Then your value list should display the full name, but enter this ID into the invoice'a match field.
1) define a text field that auto-enters a calculation that combines first and last names. Add a unique values validation to this field.
2) set up a use values from field value list with the ID as the first field and this new full name field as field two.
That's the basic outline, but I've skimmed over a number of details so feel free to reply with more questions as needed.
And, if it's possible that you will have customer names that are completely identical (First AND Last), you may want to throw the ID in parentheses after the name in the auto-enter calc so that both show in the value list.
So, for #1 from philmodjunk's suggestion:
Make a Text field that auto-enters a calculation something like this:
YourLastNameField & ", " & YourFirstNameField & " (" & CustomerID & ")"
1 of 1 people found this helpful
you may want to throw the ID in parentheses after the name in the auto-enter calc so that both show in the value list.
In my opinion, that really doesn't work for the simple reason that the ID doesn't tell the user which name is the correct one to select. you get something like:
John Smith (1)
John Smith (2345)
And so you get two names in your value list, but that doesn't tell you whether (1) or (2345) is the one that you should select. Some systems merge in other info such as a phone number or an address. I prefer to set up a system that doesn't require unique names, but detects the presence of more than one record with the same name and lists all such with additional info such as an address, or phone number or other supplementary data in a new list where the user can then make a final selection.
If you check out the auto-complete value list examples in the following teaching file, you'll find a fully documented example of what I am talking about. (and it also allows auto-complete by name which makes working from large lists a lot more user friendly.)