6 Replies Latest reply on Nov 11, 2016 5:14 AM by mikebeargie

    Value list sort issue


      I have two tables Orders and Contacts and I'm trying to use a popup list for a field named FullName on my orders form. I want to populate the FullName field from the Contacts table. I have setup the relationship and I can get the list to populate but when it pops up it is in sort order by CustomerID and I want it to be in order by LastName, FirstName (which is a calculation in my contacts table). Am I having this issue because the FullName field is a calculation?


      Is it possible to have this popup list sort by LastName, FirstName? Please see attached picture.

        • 1. Re: Value list sort issue

          That field should be an auto-enter text field, not a calculation. It only references it's own table, so there's no reason to have it as a calc type.


          Also, there is an option to sort by the second field only in a two part value list, you need to use that otherwise it will sort by the first ID field you have.

          • 2. Re: Value list sort issue

            Sorry, I'm still new to this and a little confused.


            I set the Field to TEXT and then go to Auto-Enter and then what? I thought that I had to click on calculated value... am I supposed to use Looked-up value?

            • 3. Re: Value list sort issue

              Change to text field

              Under field options (double click the field) check off “calculation”

              In the calculation field use:

                If ( not isempty( LastName & FirstName ) ; LastName & “, “ & FirstName ; ID )

              Uncheck the box that says “do not replace contents if value exists”



              This will replace the contents of your text field with LastName, FirstName whenever first or last name changes, and first and last name are both entered. Otherwise it just returns the ID.

              • 4. Re: Value list sort issue

                Thank you, it worked... I hope to be as knowledgeable as you guys some time in the future.

                • 5. Re: Value list sort issue

                  Hi Mike,


                  Just wondering your reasons for checking for content in the name fields and if empty returning the ID instead?

                  • 6. Re: Value list sort issue

                    Sometimes imported data is missing fields, and if you have the same blank value for a number of records, it can mess with a two-part value list since essentially you have a duplicate blank value in the second field for any number of records.


                    In this case too, I usually add some sort of indicator for people with the exact same name (EG street name and zip code).