5 Replies Latest reply on Jul 28, 2011 9:50 AM by philmodjunk

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

    kenfromboston

      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

        • 1. Re: Problems getting value list based on calculation field to show all values
          philmodjunk

          Try it this way:

          Re-open the manage | value lists and set field 1 back up as People::Person_id and field 2 as People::full_name.

          Then select the "show values only from second field" option.

          That way, your user selects a name, but the value list enters the ID number.

          If you use the Pop up menu option, you'll still see the full name in this field after you exit the field. If you use a drop down list, you'll see the id number, but you can add the full name field to your layout and it will display the full name from the selected People Record provided you have set up this relationship:

          FullNameDropdownListTest::person_id = People::Person_id

          • 2. Re: Problems getting value list based on calculation field to show all values
            kenfromboston

            Hi  PhilModJunk,

            I apologize for neglecting to mention this in my original post, but my initial implementation of this was exactly what you suggested.  As you stated, the numbers don't appear in the popup menu, but I'm not getting all three names appearing in the popup; I'm only getting the first name of the three, "John A Smith".  That's why I was thinking that the problem wasn't with the value list itself, but with the fact that the sort field is a calculated field.  Actually, I just discovered that in Browse Table view, I'm unable to sort records based on calculated fields.  Based on what I've read online, I should be able to to this.  As a test, I added a new field to the People table called full_typed_name, in which I just typed the same full names that were calculated in the full_name field.  When I used thise field with the popup, everything worked fine.  So the problem seems to be related to my inability to sort on calculated fields, which seems to be a necessary part of the internal generation of the popup contents.  Is there any way that I can upload my database file here so you or others could look at it?

            Thanks,

            Ken

            • 3. Re: Problems getting value list based on calculation field to show all values
              philmodjunk

              You could, but I think I know the issue.

              Open up the calculation field's definition and check to see if the drop down in the lower left corner of the specify calculation dialog shows "text" or "number". It should be "text". These value lists run off of the field's index and the field's data type controls the type of values that get put in the index so I think this is the problem. (I've seen this one before but didn't think of it right away when I read your original post.)

              • 4. Re: Problems getting value list based on calculation field to show all values
                kenfromboston

                Hi  PhilModJunk,

                You were right, the calculated field's result types were set to number instead of text.  In all other database apps that I've used, trying to put long text strings into a numeric field would have generated some sort of error, which would have saved me about a day's work in trying to figure out what was going on.  I thought that Filemaker puts a question mark into fields where there's a data type mismatch, but I'm either mistaken or it doesn't happen that way all the time.  Thanks for point this out to me; it's amazing to me that an oversight like that wouldn't reveal itself until I was trying to base a value list on that field.

                Thanks again,

                Ken

                • 5. Re: Problems getting value list based on calculation field to show all values
                  philmodjunk

                  FileMaker uses very "loose" data typing. You can often use text and number type fields interchangeably for data entry, but then any action that impinges on the field's index generates chaos when you have numbers treated as text or vice versa. Sorting numbers in a text field is another common "newbie" mistake that treats "2" as greather than "10000" during the sort.

                  I've had FileMaker techs defend this design philosophy as a way to make the system "easier to use". I've always found it to be a way that makes it easier to get into trouble rather than "easier to use", but it's what we have to deal with when we work with FileMaker.