4 Replies Latest reply on Jul 13, 2017 3:18 PM by philmodjunk

    problem with drop down menu


      I hope that some one can help me

      I have file maker PRO 11 advanced and have a large database. One area (or file) is the master database names address the usual stuff. Another file I have the invoice. The main database allows me to have duplicate last name so on board I my have John SMITH, another Mary SMITH and example Mike SMITH. This is no problem as the file maker allows this. But when I go to the invoice and use the drop down list it will not display any other SMITH other than the first entry. The only way I can get them is deleting all but the file with the correct First names, then I can see it, and add it the invoice, print it etc. So if I wanted Mike Smith to appear in the drop down list, I would have to delete both John and Mary SMITH, I have tried doing a custom selection and that only bring up there first name. So maybe some one knows the answer, Have tried the Missing Manual so (sorry pun intended) so I am missing something!!!

        • 1. Re: problem with drop down menu

          First thing you're missing is upgrading to a supported version. 11 is beyond end of life, so upgrading to the current version (16) is recommended as soon as possible.


          That said, value lists will filter out what is considered a "duplicate" value, so whatever value list settings you have are treating each of those contacts as a duplicate.


          So maybe you are just selecting "last name", where you should be selecting something like "Last Name, First Name".


          Or even better, how about "ID, Full Name".


          To make a value list that has ID and Full Name, allowing for truly unique contacts that allow for two people with the same name to function properly:


          Make sure you have an ID field in your contacts table, this can be a simple auto-enter serial number like so:

          FileMaker Tutorial - 084 - Serial Numbers pt 1 - YouTube


          Then, make a text field "Full Name" in your contacts table, give it an auto-enter calculation of something like:

          Contacts::First Name & " " & Contacts::Last Name

          FileMaker Tutorial - 087 - Calculated Auto Enter pt 1 - YouTube


          Now, in your value list, check the box that says "Also show value from second field", in the first column, select ID as the field. In the second column, select "full name".


          You will now have a value list that shows "ID - Full Name", and selects the contact ID for your invoice.


          As a point of info, you want to store the ID of the contact to properly relate invoices to a selected contact. DON'T do this by name because name is NOT unique.

          • 2. Re: problem with drop down menu

            THANK YOU for posting the fact that you are still using v11. That saves us from suggesting things that don't work in that much older version.


            First, the relationship linking these two tables should link by a unique customer ID, not their name. If you don't have one, you should add one and change the relationship.


            Then your value list should display the full name, but enter this ID into the invoice'a match field.

            1) define a text field that auto-enters a calculation that combines first and last names. Add a unique values validation to this field.

            2) set up a use values from field value list with the ID as the first field and this new full name field as field two.


            That's the basic outline, but I've skimmed over a number of details so feel free to reply with more questions as needed.

            • 3. Re: problem with drop down menu

              And, if it's possible that you will have customer names that are completely identical (First AND Last), you may want to throw the ID in parentheses after the name in the auto-enter calc so that both show in the value list.

              So, for #1 from philmodjunk's suggestion:

              Make a Text field that auto-enters a calculation something like this:


              YourLastNameField & ", " & YourFirstNameField & " (" & CustomerID & ")"

              • 4. Re: problem with drop down menu

                you may want to throw the ID in parentheses after the name in the auto-enter calc so that both show in the value list.


                In my opinion, that really doesn't work for the simple reason that the ID doesn't tell the user which name is the correct one to select. you get something like:


                John Smith (1)

                John Smith (2345)


                And so you get two names in your value list, but that doesn't tell you whether (1) or (2345) is the one that you should select. Some systems merge in other info such as a phone number or an address. I prefer to set up a system that doesn't require unique names, but detects the presence of more than one record with the same name and lists all such with additional info such as an address, or phone number or other supplementary data in a new list where the user can then make a final selection.


                If you check out the auto-complete value list examples in the following teaching file, you'll find a fully documented example of what I am talking about. (and it also allows auto-complete by name which makes working from large lists a lot more user friendly.)


                Adventures in FileMaking #2-enhanced value selection

                1 of 1 people found this helpful