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.
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.
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)
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::CustomerID (auto serial)
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
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!
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.
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