AnsweredAssumed Answered

Problems getting value list based on calculation field to show all values

Question asked by kenfromboston on Jul 27, 2011
Latest reply on Jul 28, 2011 by philmodjunk

Title

Problems getting value list based on calculation field to show all values

Post

HI,

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:

=====================================

person_id

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.

Thanks,

Ken

Outcomes