5 Replies Latest reply on May 30, 2017 2:36 PM by philmodjunk

    Drop down list using values from another field

    eshute

      Hi,

       

      I am using a drop down list on one of my layouts to display Customer Names so that the user can just pick a customer from the list easily. The value list uses values from another field (customer names in another table). Once the name has been selected it then populates other fields for me automatically on the layout such as address etc It seemed to be working ok except when I noticed that where I have two customers with the same surname then it only shows up one in the value list. How can I get around this? THanks

        • 1. Re: Drop down list using values from another field
          philmodjunk

          You haven't really described which options you selected for your value list, but it appears that you have a use values from field value list with a customer ID as field 1 and the customer surname as field 2 and show only values from second field selected. You might have something different so please clarify if I have guessed wrong.

           

          A value list can only display unique values. If you have two values in your list that are not unique, you have two basic choices:

           

          Change what value is used in the "show" field so that the values really are unique. This can involve a text field with an auto-enter calculation that combines data from several fields (first name, last name and then maybe an address and/or phone number, for example) with a "unique values" validation option to catch any instances where the data entered is not unique.

           

          Use a different selection method. There are several other approaches--some that use a value list and some that use a selection portal where entering/selecting a value pulls up all records that match the data entered/selected with additional fields (phone number, address, etc) in some kind of list or portal where the user can then further refine their search or select from this short list by clicking one of the listed matches.

           

          For working examples of an auto-complete enabled name based value list that still links data by ID, but which includes a way to handle duplicates as well as selection portals that can handle duplicate names by listing multiple matches in a portal, see this teaching file that comes with complete documentation on how each example works and how it was set up:

           

          Adventures in FileMaking #2-enhanced value selection

          1 of 1 people found this helpful
          • 2. Re: Drop down list using values from another field
            eshute

            Hi, thats great - thank you. I have added a customer ID field now and used that instead of the surname so that it is unique and it works just how I want it to now. But just out of interest, I have imported my customers from our Quickbooks accounting software and now have over 1500+ records already in my customer database. Is there a quick way of assigning them all a unique ID number without having to go through them individually and adding them - sure there must be!

            • 3. Re: Drop down list using values from another field
              philmodjunk

              Making the customer ID a visible part of your value list doesn't seem like a user friendly way to go here.

               

              Replace Field Contents can be used to assign serial numbers, UUIDs or other values to records in your found set.

              1 of 1 people found this helpful
              • 4. Re: Drop down list using values from another field
                eshute

                OK thanks - I can see its not the best way but I'm a complete novice at this stuff and just trying to do my best in a short timeframe. I don't have time at present to look at the other way you sent me a link to but I will do later for sure as I'd like to get it right so thanks for sending that info.

                Just need a work around in the meantime as I'm being hassled to complete the app for our staff to use!

                 

                I've now replaced the field contents for the customer ID with serial numbers incremented by 1 but is there a way of using a calculation so that the field uses the first 4 letters of a surname and then a serial number incremented by 1 by any chance? Thanks

                • 5. Re: Drop down list using values from another field
                  philmodjunk

                  It's possible, but if this a field to use to link records in a relationship, it's not ideal. Keep in mind that names change and names--especially this abbreviated form, are not unique.

                   

                  Please go back and review my original post again.

                   

                  I also suggested including data from other fields in order to produce a value list with enough info to be able to tell one person from another when their names are the same.