#1. If the relationship is via the contactid field, you have to be setting the customers::contact_id field to have the relationship connect. Simply typing or selecting the name will only work if the tables are related by the name field (a bad idea).
Have your value list based on contactid, use a second field of customer name, and select the box saying "show only values from second field"
In that way, you are selecting the name but actually setting the contact_id.
Take care that you are not actually changing the contact_id on the record, whether you be in find mode, or using a global field...don't mess with your primary key field.
Thanks for your suggestion Ninja. Where are you based?
I have my sales layout set up like that. It is nice the way I can open a pop up list and choose my customer from that list. But I would hope that I will have thousands of customers and contacts. So I would like to have a way of typing in the customer's name and then have a list of contact ids to choose from narrowing down the more letters I type.
See this demo file for an auto-complete drop down list that results in the ID being entered and used to link records:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
Note the subtle change in terminology...it makes a big difference.
You are asking for a "Pop Up"
We are talking about a "Drop Down"
They are two different things and they behave differently.
What you are asking for is done on a "Drop Down"...this may be the only thing you have left to change (& the "autocomplete" checkbox)
Thanks for that Phil. I'll play with that database later.
This is the way I set up the list. I can't check the autocomplete checkbox. It is not working for me. Can you see any mistakes?
Check the value list in the demo file. It's not set up that way. It uses a text field and the value list solely references name. The ID number is copied over via a looked up value field option and a script is in place to handle instances where there are two individuals with the same name.
I've just implemented that method Phil. Works well. Genius as ever. Thanks also Ninja.
I thought it was working well. But I've put it into practice with a different project I'm working on. I want to monitor what students are doing in their research studies. Students often do similar topics of studies. I want to be able to monitor which ones come up the most. I had a drop down list of all of the topics. But I've found that when I choose a previously done topic from the drop down list it creates a new research study id. I want it to use the previous id. Is this not working for me because I don't have a join table?
Can't tell from what you have described so far about this specific attempt to use this method.
I'm using the same pricinipal in my research study scenario as in my contacts. So I'll just explain my contacts scenario in more detail to avoid confusion.
Well It works the exact same way as the contacts scenario. I have a related table of contacts. I have a dropdown list of contacts using only related values from the contacts table. I also have a fk_serial field in my invoices layout. When I type in a contact's name that I have entered before I am currently able to see the contact's name suggested as I type. If that is the contact I want to use I can select that contact by pressing the return key. But instead of seeing a previously used fk_serial number I see a new one being generated.
I've imported your scripts from your simple name lookup demo. When I type in the name of a previously used contact it doesn't take me to a new layout to choose which one I want. It simply gives the new contact a new fk_serial.
Sounds like you need to take a closer look at the needed relationships. The Name LookUp demo uses two relationships to the table storing the names and generating IDs. One relationship matches by name fields and this is used to copy over the corresponding ID number. The script uses the name matching of this ancillary relationship to count the number of matching records, IF there are more than 1 match, it pulls them up in a list with additional info supplied and asks the user to select one.
But if the relationship is not correctly set up, the script detect cases where an existing record exists and then it will automatically create a new record.
Thats working now.