In the specify field window, have you tried checking the "show values only from second field" box?
Yes, and that is when it shows only one record from the client table. If there are two John Doe's in the client table only one shows up in the drop down list on the Session Layout.
There must be a distinction between your two John Doe's...whether it be Company, Address, ... , something.
create a field in your client table that combines the name and the other distinguishing characteristic
Calc = FirstName & " " & LastName & " " & Company & " " & Location (or other distinguishing fields)
...then use this field as your second field in the value list.
In this way it would still sort by name but you'd be able to discern between your two John Doe's since field2 would be different between them.
Would this work for you?
Sorry, I misunderstood your original post. I like Ninja's idea - even if you have all of the "John Does" displayed in the dropdown, you will need additional information in order to select the correct client.If you'd rather try the separate window to populate the foreign key fields, here is the basic idea:Create a new layout "clientsearch" with whatever fields are appropriate (name, address, company, etc.)create a script "client id search" with these steps:New Window Go to Layout ["clientsearch"]create a script "add client id" with these steps:Set variable [$clientid; Value:client::clientid]Close Window [Current Window}Set Field [Session::clientid; $clientid]add a button to the session layout that performs the "client id search" scriptadd a button to the clientsearch layout that performs the "add client id" script
Another option (takes a bit of extra work, but can be very nice for the user):
Add a global field and format it to list all your names only (no ID field) and enable the auto-complete feature so that the user can "prune" a long list of names down to just a few by entering the first few letters. Set up a script trigger to perform a script when the user exits the field.
- Write the script so that it freezes the window, switches layouts to layout listing the same records and performs a find for all records with the selected name.
- Use get (foundcount) to count the number of records that match the name. If there's just one, use set variable and set field to copy the ID number of this record into the record on your original layout.
- If there's more than one found, pop up a list of the matching records in a small window where the user can see a little more info (such as some address fields) to distinguish between entries with the same name.
- The user clicks the name they want from the list and the script then puts the selected record's ID number into your form and closes the pop-up window.