Describe your value list in more detail. Are you using one column or a two column list with the first column hidden? How are your tables related? by a serial ID number?
Let's pretend we've fixed this problem for a moment: When you pull down a menu and see John Doe listed several times, how is the user to know which John Doe is the correct choice?
Could you clarify your database structure, like list the tables and their fields, and what the relationships are?
The value list is one column and shows results from: table= 'contacts' field= 'name'
there is another filed in the table 'contacts' called 'company'. This is used as a relation point to table= 'company' field= 'name'
with this my value list shows only related entries (when a company is choosen the contact name value list populates to show the contacts only for that company)
It works right except when two companies have a contact with the same name.
"with this my value list shows only related entries."
Then you shouldn't see names from other companies in your value list. The relationship should eliminate them from the list.
It looks like the problem lies with your relationship between the two tables. I think you have a relationship based on the person's name and you are using this relationship to refer to data in your company table. In that case, when you select a name, the system will refer to the oldest such matching record in your contacts table unless you specify a sort order in your relationship.
You need a serial ID field in contacts that uniquely identifies each person. You should then use this relationship to lookup or refer to data when selecting a person from your value list.
Your field should be an ID field formatted with a two column value list. Column 1 is your ID number and column 2 is the person's name. With this set up, selecting a contact ID number will match to only one contact record and you'll be able to refer to the correct data.
I was a little mistaken in the error in my database. Here is an update to what I am seeing.
a little background:
I have three tables relevant to this issue.
table relationships are as follows:
• jobs:customer -> customers:name
• customers:id -> contacts:id
• contacts:name -> jobs 2:contact
customer:id is an auto enter serial and contacts:id is a number field set to match customer:id when a new record is created in the contacts table.
In the table 'jobs' there is a calculation field jobs:contactphone
this pulls in the phone number from contacts:phone
Here is my issue. When two customers have contacts with the same name the correct customer/contact is being displayed in the value list (I checked this by displaying the id field for both tables and they are matching correctly) but when you choose the contact and the phone field fills in, the phone number which comes first sorted by record number is loaded.
1st case example - contact:name=john contact:id=12 jobs:contactphone calculates to contact:phone for contact:id=12
2nd case example - contact:name=john contact:id=97 jobs:contactphone calculates to contact:phone for contact:id=12
PS - What's witht the smilies?
I created a 2 fields jobs:cid and contacts:cid
both are calculations contact name & contact id
then I made this the related field between jobs and contact 2