that seems odd, but how would your user know which item to pick if the part numbers are identical?
For better or for worse, this is how FileMaker has always worked -- when you show values by the second field, it won't show any duplicate values. Same thing if you show values by the first field... it won't show any duplicate values. So you'll have to design this in a different way for your users.
There are dozens of different ways to approach this, but one very cool user-friendly way to do this is: have them pick the value from a portal instead, which you place within a little popover button on your layout. Then use a script to set the ID.
Or, for a more basic approach, create a concatenated calculation field that combines the part number with the serial number, and have them choose from a value list based on this calculation field instead.
I was just puzzled by the missing items in the list, now I have to check other lists.
Indeed the user should only need to select a unique PN.
Thanks scottworld, I am trying different ways with the value list. It's probably more useful to filter out duplicate values especially when there is a lot of them.
A portal in a popup is probably the way to go, more info can be displayed to help with the selection.
You can also set up a value list based on just the second field. The user picks a value and a relationship based on the second field looks up the first field values into the correct field. A script trigger runs a script at this point that checks for duplicate values in the related table. If any are found, a popover or floating window opens listing the matching records with additional fields shown to assist the user in selecting the correct value from the duplicates.
just in case you don't already have this in your "toolbox", find PartNumber of ! should find all duplicate part numbers.