Problems getting value list based on calculation field to show all values
I'm trying to set up a value list based on a calculated field in a table, and I'm having problems getting all of the values to display. Here's what I have set up:
"People" Table. Fields are:
person_id (key field)
first_name (text field)
middle_name (text field)
last_name (text field)
full_name (calculation field)
The calculation formula for the full_name field is:
Substitute(first_name & " " & middle_name & " " & last_name; " "; " ")
This table currently has three records with the data contents as follows (fields delimited by "|" chars):
4 | John | A | Smith | John A Smith
5 | John | B | Smith | John B Smith
6 | John | C | Smith | John C Smith
"FullNameDropdownListTest" Table. Fields are:
In a separate layout, named Test, I created a single field that is associated with the FullNameDropdownListTest::person_id field. I have this field configured as a popup menu, getting its values from a value list called "People".
The value list named "People" uses values from People::person_id and People::full_name (I checked the box for "Also display values from second field")..
As currently implemented, this works fine. If I click on the field in the Test layout, a popup appears, with the following values:
4 John A Smith
5 John B Smith
6 John C Smith
This is exactly what I would expect. But I don't want the ID numbers to appear, so I reset the parameters of the "People" value list to only display the names (I unchecked the "Also display values from second field" box and changed the first field from person_id to full_name).
After making this change, I expected that the popup would show me the following:
John A Smith
John B Smith
John C Smith
But what I got was this:
John A Smith
I can't figure this out. Am I doing something wrong, or is this some sort of bug? MIght this have to do with some issue related to sorting calculation field values (in the working example, it appears that the numeric field was the one that was sorted).
Any suggestions would be much appreciated.