2 Replies Latest reply on Nov 16, 2011 11:49 PM by NorbertKowalski

    Problem working with value lists

    NorbertKowalski

      Title

      Problem working with value lists

      Post

      Or more likely, problem understaning how value lists work, since I cannot get the behaviour I want using only two simplest tables.

      1. The setup.

      I have two tables, with following fields

      People

      - person_id - numeric, auto generated
      - name - text
      - surname - text
      - surname_name - calculation surname & " " & name
      - some more data to be pulled as info to other table

      Cars

      - car_id - numeric, auto generated
      - serial - text
      - person_id - int

      There is a single relation people::person_id = cars::person_id
      In people table I have four records:

      1;John;Doe;John Doe
      2;John;Smith;John Smith
      3;Mike;Brown;Mike Brown
      4;Mike;Smith;Mike Smith

      2. What I want to get.

      I want to have a dropdown list in layout related to cars table, with all surname_name from people, so I can select who gets which car.

      3. Problems.

      I created a value list, but cannot get it to work the way I need it to. The ideal way would be to show only the calculated people::surname_name field in list, display it after selection, and to store the people::person_id. However, with all my attempts I got the following results:

      first field: people::person_id
      second field: people::surname_name
      Show only values from second field: unchecked
      Sort values using: first field
      Result: shows all the records (good), sorted by id (bad), stores id (good), displays id in list (bad), displays id in field (bad)

      first field: people::surname_name
      second field: people::person_id
      Show only values from second field: unchecked
      Sort values using: first field
      Result: shows all the records (good), sorted by name (good), stores name (bad), displays id in list after the name (bad), displays name in field (good)

      first field: people::surname_name
      second field: unused
      Sort values using: first field
      Result: shows only first record (bad), sorted by name (not relevant as there is only one result), stores surname_name (bad), displays only surname_name in list (good), displays surname_name in field (good)

      first field: people::person_id
      second field: people::surname_name
      Show only values from second field: checked
      Result: shows only first record (bad), sorted by name (not relevant as there is only one result), stores id (good), displays only surname_name in list (good), displays id in field (bad)

      first field: people::person_id
      second field: people::surname
      Show only values from second field: checked
      Result: shows only three records - omits the duplicated Smith (bad), sorted by name (good), stores id (good), displays id (bad)

      Plus couple other variants, all not working. At this point it is pretty clear to me, that the problem lies in my approach to the issue, but I am stuck in current way of thinking, and cannot work another solution. So, any help would be more than welcome.

      I know i can work around the "store id but display the name" with reducing the width of selection list to only show the down arrow, and by placing a text field that gets the name from the proper table based on stored id. But I wonder if there is a better solution than such hack job.

      Or maybe I should use another way of selecting the records, since the dropdown lists will not be usefull with couple hundred people to select from. However, the main problem is that the solution needs to work with instant web publish, so the spotlight-picker (which would be great here) is not an option.

      I am using FM Pro Advanced 11.

        • 1. Re: Problem working with value lists
          philmodjunk

          This one doesn't track:

          first field: people::person_id
          second field: people::surname_name
          Show only values from second field: checked
          Result: shows only first record (bad), sorted by name (not relevant as there is only one result), stores id (good), displays only surname_name in list (good), displays id in field (bad)

          With this option, you should see all the records in your example. If it does not, check your result type for surname_name and make sure that its result type is set as "text" and not as "number".

          This still is likely to be a problem, however. If you get two people named "John Smith", you'll only see the first instance listed in the value list. This will be true anytime you either hide the ID in field 1 or sort the values on Field 2. The issue is more with your data, however, than in FileMaker's handling of value lists.

          Consider:

          If you saw this in your value list:

          John Doe
          John Smith
          John Smith
          Mike Brown
          Mike Smith

          How would the user know which "John Smith" is the correct one to choose? The solution is to include more data in that second field than just the name (such as an address, to give one possible example) with a validation rule to ensure that it will be unique. That way, both "John Smith" entries will be listed and with enough data that the user can make tell which John Smith to select.

          If you don't want to resize the drop down list to just an arrow, you have two other options. You can place the name field from the related table on top of the drop down list field and use field behavior to prohibit access to this name field. When the user clicks on this field, the drop down list field behind it pops to the front and deploys. You can also use a pop up menu instead of the drop down list and then the field 2 value will be displayed when you exit the field. If you don't like the "blocky" appearance of a pop up menu, you can give it 0 width or transparent borders.

          All of this is best for value lists of a limited size. To work with larger lists of values I suggest one of two approaches:

          Use a conditional value list where you select a category in drop down field A to limit the values listed in the drop down for field B. Let me know if this interests you and you want to learn more.

          Or you invest in some scripting to set up a script supported method for enhanced value selection. These approaches allow you to use auto complete or other text based techniques to narrow down your list of options and the script can handle issues with duplicate values.

          I have a demo file that illustrates several different options for enhanced value selection that you can consider: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Problem working with value lists
            NorbertKowalski

            Thank you very much. Indeed, the problem was with the calculation value returning number and not text. Correcting it fixes the problem in this case. I worked around the two John Smiths issue by chaning the calculated field to include person_id at the end.

            I will look into the examples you provided, and if I need more help or tutoring I will post here again.