1 Reply Latest reply on May 12, 2017 9:56 AM by philmodjunk

    Value list with first and second field skips duplicates of second field




      I have a database of companies and one of my layouts is for personnel. So I have tables Vendor and Person. There's also an intermediate joining table VendorPerson which links IDs from both tables.


      On the person layout is a portal for related VendorPerson, and I have a field vendor_fkID which links it to the vendor. For this field, I've set up a value list which picks up the ID from Vendor (as a first field) and actually shows the Name from Vendor (as the second field). This works most of the time.


      The problem is this. Several vendors have the same name. If I display the ID (first field) ONLY in the value list, I see all the appropriate entries.

      If I display the name as well, or ONLY display the name, I only see ONE entry per name. It skips any duplicate names.


      Is there any way to force it to display ALL the entries, even if the names are duplicated, or is this a necessary feature of value lists?




        • 1. Re: Value list with first and second field skips duplicates of second field

          This is a built in limitation of field based value lists. But there are alternative approaches that you can use. Note that with any approach that you use with this data, the duplicate names will be a problem. In general use, if the user sees:


          1   John Smith

          2   John Smith

          3   John Smith


          There really isn't enough info for the user to reliably select the correct John Smith so even if you could force all three duplicate "John Smith" entries, it's not really going to work anyway.


          One alternative is to combine several features: A name based relationship that supplements the ID based relationship that you already have. An autocomplete value list that only lists the names. An auto-enter field option that copies over the ID so that you can link by ID after picking the name and a script that checks for multiple matches by name and pops up a list with the names plus additional info such that the user can correctly select the one that they need.


          Another option is to set up a search portal in a popover where you enter search text and click to select a value from the portal's list of values that satisfy the search text partial match. The portal can then list more info than just the name or you can add additional search fields--say a phone number field that can also be used to narrow the list down to a single match. Note that because you are selecting values for a portal record, such a popover with portal has to be located outside of the portal row--with a regular button and scripting used to both open the popover and link it to the correct portal row once a value has been selected.


          Working examples of both methods may be found in this teaching file. Each example in this file comes with detailed documentation of how they were set up and how they work:


          Adventures in FileMaking #2--enhanced value selection


          And please note the "geek busking" info on the intro page.