2 Replies Latest reply on Jul 4, 2010 5:03 AM by comment_1

    value list from field, showing repetitions



      value list from field, showing repetitions


      Hello, thanks for any help with this question!

      Basic info.

      Filemaker version: 11

      Operating system: XP

      Database info.: value list "DonorID" is from field. It uses values from first field Donors::DonorID. It also displays values from second field Donors::Last_Name "Includes All Values" and sorts using the second field.

      My level of experience: Filemaker newbie, competent with SQL and  relational databases

      What I am trying to accomplish is a drop down list using this value list. If I sort using the first field (DonorID), it works fine. But I'd like to sort using the last name, so I specify to sort using the second field. This also works fine unless there is more than one instance of a last name. If so, only the first instance appears. For instance, if there are two last names "Smith", only one will appear in the drop down. If I change one last name to "Smithy" , both will appear.  So I guess that I need to allow repetition somehow. BTW the purpose of the drop down list is to add related records (for instance event attendance by this donor.)



      Edit: Thanks all! Yes, it appears that my best option is to include the first name and donorID in the calculation, and to display the second field only in the value list. That way my calculation looks like this:

      Last_Name & ", " & First_Name & " " & "(" & Donor_ID & ")"


        • 1. Re: value list from field, showing repetitions

          Hi Jim,

          The behaviour you've described is by design because a Value List is compiled of unique values, not unique records.

          As a workaround, you could create a calculation field that concatenates the last name and first name of the Donors:

          Donors::Last_Name & ", " & Donors::First_Name


          This calculation field will generate results that look like this:

          Smith, John
          Smith, David
          Smith, Robert
          Smythe, William
          Stanley, Robert

          If you use then this calculation field as the "second field" when setting up your Value List, you'll see the individual Smiths.

          However, if you have more than one John Smith, for example, you'll still run into the same problem as before - only one "Smith, John" value will be shown in the Value List. But at least the odds of two (or more) of your Donors having the same first name and last name is far less than the odds of several Donors having just the same last name.

          Hope this helps!



          • 2. Re: value list from field, showing repetitions

            If you have duplicate values, then a drop down is not a good selection method - unless you expect the user to recognize the correct Smith by DonorID (in which case you could append the ID to the name). Consider selecting from a portal or a list instead.