6 Replies Latest reply on Feb 21, 2012 10:20 AM by karendweaver

    Dropdown list doesn't show all entries from linked table depending on sorting

    justinfleming

      I have the following set up -

       

      Customers [table]

      Customers::CustomerID (auto serial)

       

      Orders [table]

      Orders::CustomerID (linked to Customers::CustomerID)

       

       

      A new customer is created, then a new order, and the new order is then assigned to a customer using a dropdown list. The dropdown list in ORDERS shows the values from Customers::CustomerID.

       

      The first field in the dropdown is Customers::CustomersID

      It also shows a second field - Customers::Surname

       

      There are 3 customers who have the same surname, but obviously have different CustomerID's since this is the field that matters, the surname is purely cosmetic.

       

      If the dropdown is told to sort by the first field (Customers::CustomerID) then all 3 customer names show in the list along with their unique IDs.

      If the dropdown is told to sort by the second field (Customers::Surname) then only 1 of the customers with the same name shows in the list.

       

      I'm totally at a loss as to why this is happening or how.

      I can change the sorting backwards and forwards and it does the same thing consistantly.

       

      Any help appreciated.

       

      I'm using FM Pro Advanced 11 on PC and Mac.

        • 1. Re: Dropdown list doesn't show all entries from linked table depending on sorting
          thomas_staehli

          Hi Justin,

           

          As far as I know you can't really do anything about it. But you could maybe a calculation field in you customer table that would take Last Name & First Name. Then you you modify your value list by replacing the second field with the new calculated field.

           

          But of course if they have the exact same first name as well it still wouldn't work. But the chances of this happening are smaller.

          • 2. Re: Dropdown list doesn't show all entries from linked table depending on sorting
            justinfleming

            I'm considering automating the whole process from the Customer Table so you never go into the order table and then have to manually assign the order to a customer but rather set the local variable of the customer ID first and paste it in to the order::customerID field.

             

            Any idea WHY it fails to list all the entries from the other table though? Seems like a fundamental thing that has rather a large implication on systems.

            • 3. Re: Dropdown list doesn't show all entries from linked table depending on sorting
              thomas_staehli

              Well Filemaker seems to group the identical values (from the sorted field). It might seem unconvenient in this specific case, but it can be useful in some other cases where you wanna list some values and avoiding having dupplicates.

               

              What I do when I want a list sorted alphabetically, I just make sure that the second field is unique (by adding another information like the ID between between brackets or something else)

              • 4. Re: Dropdown list doesn't show all entries from linked table depending on sorting
                AlanStirling

                Hi Justin

                 

                There is probably a simple answer to this issue.

                 

                Firstly, do any of your customers with the same surname also share a first initial? (I hope not! - I've included a solution for that later)

                 

                Now make a new calculated field in Customers, called 'Surname Initial'.

                 

                The calc should be something like; "Customers::Surname & " " & Left(Customers::FirstName;1)".

                 

                Once you have added this field, substitute the second field in your Value List (currently 'Customers::Surname') with 'Customers:: Surname Initial'.

                 

                Now your list of customer names will include the first initial at the end of the surname.  This should show the three customers with the same surname as separate entries (if their first initials are all different).

                 

                A Value list, when sorted by the second field will only show unique values, so by adding the initial, you will have made the list of Customer names more likely to be unique …

                 

                If there are two customers with the same initial, then change the calculation above; "Customers::Surname & " " & Left(Customers::FirstName;X)". Replace the X (originally containing 1) with 2 or 3 to get more first name letters, so as to make the names in the list even more likely to be unique.

                 

                Best wishes - Alan Stirling, London UK.

                 

                 

                On 21 Feb 2012, at 09:19:45, justinfleming wrote;

                 

                 

                created by justinfleming in General - View the full discussion

                I have the following set up -

                 

                 

                Customers

                 

                Customers::CustomerID (auto serial)

                 

                 

                Orders

                 

                Orders::CustomerID (linked to Customers::CustomerID)

                 

                 

                 

                A new customer is created, then a new order, and the new order is then assigned to a customer using a dropdown list. The dropdown list in ORDERS shows the values from Customers::CustomerID.

                 

                 

                The first field in the dropdown is Customers::CustomersID

                 

                It also shows a second field - Customers::Surname

                 

                 

                There are 3 customers who have the same surname, but obviously have different CustomerID's since this is the field that matters, the surname is purely cosmetic.

                 

                 

                If the dropdown is told to sort by the first field (Customers::CustomerID) then all 3 customer names show in the list along with their unique IDs.

                 

                If the dropdown is told to sort by the second field (Customers::Surname) then only 1 of the customers with the same name shows in the list.

                 

                 

                I'm totally at a loss as to why this is happening or how.

                 

                I can change the sorting backwards and forwards and it does the same thing consistantly.

                 

                 

                Any help appreciated.

                 

                 

                I'm using FM Pro Advanced 11 on PC and Mac.

                 

                Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                Start a new discussion in General by email or at FileMaker Technical Network

                Manage your email preferences.

                 

                FileMaker Developer Conference 2012 • Miami, Florida • July 16-19 • www.filemaker.com/devcon

                 

                • 5. Re: Dropdown list doesn't show all entries from linked table depending on sorting
                  justinfleming

                  Thanks for the responses, particularly good to hear "A Value list, when sorted by the second field will only show unique values, so by adding the initial, you will have made the list of Customer names more likely to be unique …"

                   

                  I haven't encountered this so far because our other system deals with company names which are (so far) unique.

                   

                  The problem with names is they are subject to change and be modified - especially when initially dealing with women who often use their maiden name, then later inform you of their married name.

                   

                  I think I will have to run the whole thing from the Customers table and automate the creating of orders to copy across the customerID.

                   

                  The main thing is I now know of this issue and can allow for it!

                   

                  Thanks

                  • 6. Re: Dropdown list doesn't show all entries from linked table depending on sorting
                    karendweaver

                    Hi Justin

                     

                    I agree - you really don't want to pick from a value list of names.  There are SO many opportunities for this to break!  Plus, when you customer list gets longer than a couple of dozen names, all kinds of problems can kick up, including a lot of ugly performance problems.  Value lists are really not designed to be used with hundreds of entries.

                     

                    Starting from the Customers table is a good place, but eventually, your users will complain about the extra step.  If its a new customer, no biggie.  But what they usually want is to start a new order, pick the customer, create a new customer if the customer doesn't exist, and move straight into the order.  This is not as difficult as it may seem to accomplish -

                     

                    A. you could use a "conditional" value list - where the second field, such as a category, or last name field, is entered first, and then a second value list shows the unique values.

                     

                    Here's more info:   http://help.filemaker.com/app/answers/detail/a_id/5833/~/creating-conditional-value-lists

                     

                    B. You could use a "picker" window with either a portal based on a global or a quick find trigger that displays all the matching names based on some criteria that allows quick entry

                     

                    Here's one example from Kevn Franks:     http://www.filemakerhacks.com/?p=1731#more-1731

                     

                    warm regards,

                    Karen