2 Replies Latest reply on Oct 8, 2013 1:47 PM by wendyj

    value lists question



      value lists question


           I’m a newbie to Filemaker Pro development, so forgive me if this is pretty basic.  But I can’t get my database to display names of two students with the same last name in an pop-up value list.

           The value list is set up to display fields from the table “Students:” __StudentIDpk and NameLast, with only the last name displaying.  That seems generally to be working well, except in the case of these two students with the same last name (call them “Smith), in which case only one Smith is displaying.  What am I missing?

        • 1. Re: value lists question

               FileMaker is working exactly as it was defined. When a value list is sorted on the name field, duplicates are automatically excluded from the list.

               But think for a moment. Let's say you temporarily change your value list setting so that you can see the student Id and the last name. If you then enable sorting on the ID field instead of last name, you'll then see all names included in your value list even though they are no longer in alphabetical order. Let's further pretend that your two student records with duplicate last names have "Smith" as the last name.

               If so, then your value list might look like this:

               3456    Smith
               3457    Jones
               3459    Smith

               How can you tell which "Smith" would be the correct "Smith" to choose? There's not enough information to tell the user whether they should choose the Smith with ID code 3456 or the Smith with an ID of 3459.

               That leads us to one solution for this issue. Define a new text field with an auto-enter calculation that combines the Student name with additional information such as an address. Set a unique values validation on this text field to ensure that each record stores a unique value in this field. Then use this field in place of Last Name as the second field in your value list.

               There is also another method that uses a script to detect duplicate names and to then bring up a list of matching records where the user further refines their choice of which value to select. This method still enters and ID number in the appropriate field. Here's a demo file of that method:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


          • 2. Re: value lists question

                 Thank you, Phil, for this solution.  I chose the first route (creating a text field with an auto-enter calculation, etc.), and it's working perfectly.  Students with the same last name now show up.