Create a value list using values from field.
Select the ID in the left pane and also 'show values from second field' selecting the serial number in the right pane. Below, enable 'show values only from second field.'
Attach a pop-up menu instead of a drop-down list and point to this value list. It then displays the serial for selection but inserts the ID into the equipment ID field.
I have the drop dowb menu working fine, the problem is the amount in the dropdown menu. Using the foreign key in the FSR table enable me to go directly to the serial number in the drop down menu once a customer is chosen.
FSR table links to Customer and Equipment table
The foreign key that is generated in the FSR table makes it easier to choose what equipment I want in the drop menu. What i want to do is not use teh foreign key but use serial number to access the menu.
FSR foreign key is 10 represents serial number 987654OK in the machine table
What i want to do instead is type in the serial number 987654OK instead of the foreign key of 10 to access the serial number of the drop down menu.
I tried adding a global key in the FSR table and creating a relationship between global field and serial number in the machine table but that did not work when I added new records it did not show any data, it also cleared out the serial field on the previous records.
We need to see your file. Upload it somewhere (a free site such as 4shared.com if nothing else) and then provide a link to it here.
On the image attached top left it say Facility this is my Customer Table
Below on the right it says serial number field is blue. Right above the Serial number is a field with the number 1 this number is also right next to the serial number it is the FSR table foreign key.
What i do is first choose the Facility [Customer] which give a list of only that customers equipment. The tan box with the number 1 in it
I type in the foreign key in this case the number 1 without using the
drop down menu. When I put the number 1 in the tan box it shows me the right serial number [95864fd].
What I am trying to do, if it is even possible is to type the serial number in the tan field 95864fd instead of the number 1.
Sometimes a scripted approach provides a more userfriendly option for dealing with large value lists while maintaining correct use of primary and foreign keys in you relationship.
This demo file demonstrates several such options: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html