AnsweredAssumed Answered

Problem working with value lists

Question asked by NorbertKowalski on Nov 16, 2011
Latest reply on Nov 16, 2011 by 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.

Outcomes