Hi all,
up to now I used scripted versions to set a mark flag on the fields that
I wanted to pop up in value lists.
But this time I tried once again to use popup values direcly and failed miserably.
What I want to do is e.g. a "people" database with the fields "state"
and "city".
The state field is entered first, taking its values from the state field itself (value list "state", from the entries of field "state").
City should be suggested, aaccording to the selected state.
Cities are within another table, taken e.g. from <https://gist.githubusercontent.com/Miserlou/11500b2345d3fe850c92/raw/e36859a9eef58c231865429ade1c142a2b75f16e/gistfile1.txt>
Relation "cities" is from people::state to cities::state
When I take people::town as the first field, cities::town as the second field, that is
* first field: people::town
* secondary field: cities::town
* include only related values starting from "cities"
* show values only from second field
Oh use a drop down list, which looks best in table view (and behaves the
same in ther views), while a popup list works exactly the same.
That's the closest thing to what I need. But when I got e.g. 20 people
from California already, the value list repeats each of the cities 11
times. 9 people from Colarado, the value list shows each city 3 times.
So it's ok only if there's just one state entry, resulting in one
offerend city
It does not depend on the number of found records how many repetitions I
see, but the number of records that do contain a matching state field.
When I use different setups,
* I do get e.g. all cities, once, but not filtered by state
* I do get all filtered cities, just once, but selecting one does
not enter the selected city, but its state only
Where is my mistake, how would I do it properly?
Or is that a FMP11 but which was fixed later on?
Thanks
Martin
I'm not sure quite why you need to bother with "first fields" and
"secondary fields". That's probably part of the weirdness you're seeing.
The usual option to have have a separate table, e.g. StatesCities,
which has two fields:
LookupState Text
LookupCity Text
Into this table you can import the needed data from the text file in your link.
Back in the People table you need a two user data enty fields:
UserState Text
UserCity Text
Now you need a relationship link that connects the data entered in
UserState to the list of appropriate cities from the StatesCities table:
rel_LookupCity People::UserState = StatesCities::LookupState
You also need to create two value lists:
vl_StateList values from field StatesCities::LookupState
include all values
vl_CityList values from field StatesCities::LookupCity
include only related values from rel_LookupCity
Drop-down lists using the appropriate value list.
i.e.
UserState Drop-down list, values from vl_StateList
UserCity Drop-down list, values from vl_CityList
When the user clicks on the UserState field they get a list of all the
states (alphabetically sorted) to choose from. Once they select a
state, the UserCity field will have a list of only the appropriate
cities (alphabetically sorted) for that state.
I'm not sure quite why you need to bother with "first fields" and
"secondary fields". That's probably part of the weirdness you're seeing.
The usual option to have have a separate table, e.g. StatesCities,
which has two fields:
LookupState Text
LookupCity Text
Into this table you can import the needed data from the text file in your link.
Back in the People table you need a two user data enty fields:
UserState Text
UserCity Text
Now you need a relationship link that connects the data entered in
UserState to the list of appropriate cities from the StatesCities table:
rel_LookupCity People::UserState = StatesCities::LookupState
You also need to create two value lists:
vl_StateList values from field StatesCities::LookupState
include all values
vl_CityList values from field StatesCities::LookupCity
include only related values from rel_LookupCity
Drop-down lists using the appropriate value list.
i.e.
UserState Drop-down list, values from vl_StateList
UserCity Drop-down list, values from vl_CityList
When the user clicks on the UserState field they get a list of all the
states (alphabetically sorted) to choose from. Once they select a
state, the UserCity field will have a list of only the appropriate
cities (alphabetically sorted) for that state.
I guess I did not understand the usefullness of secondary fields yet.
The state field is entered first, taking its values from the state field itself (value list "state", from the entries of field "state").
On 2023-09-15 08:44:37 +0000, Martin Τrautmann said:
I guess I did not understand the usefullness of secondary fields yet.
The secondary field in the value list definition means the City
drop-down menu could show both the city and the state.
For example, if the user enter the state as California, then the city drop-down menu would display:
Azusa California
Bakersfield California
Baldwin Park California
etc.
For this database example it is basically pointless since they will all
be the same state anyway, but displaying such a combined list can
sometimes be useful.
I think part of the weirdness you were seeing is possibly also due to
this, unless I'm reading it wrong:
If you get the value list from the People table, the drop-down list can
only show values that have previously been entered (again something
that is sometimes handy), but which would prove "difficult" if there
are no existing records since the list would be blank.
On Sat, 16 Sep 2023 11:06:23 +1200, Helpful Harry wrote:
On 2023-09-15 08:44:37 +0000, Martin Τrautmann said:
I guess I did not understand the usefullness of secondary fields yet.
The secondary field in the value list definition means the City
drop-down menu could show both the city and the state.
For example, if the user enter the state as California, then the city
drop-down menu would display:
Azusa California
Bakersfield California
Baldwin Park California
etc.
No, not if you would show the second field only, which is one of the
options.
For this database example it is basically pointless since they will all
be the same state anyway, but displaying such a combined list can
sometimes be useful.
It's probably for additional info, but selecting one of those will not
take the second value, but the first value, even if the first value is hidden.
I think part of the weirdness you were seeing is possibly also due to
this, unless I'm reading it wrong:
The weirdness is that there are multiple repetitions of the same values, depending on how many records do contain this value, but not a 1:1
match. I'd have to run some checks, but it's about the square root of
records - so for 10 records of California, the dropdown list would offer
Azusa
Azusa
Azusa
Bakersfield
Bakersfield
Bakersfield
...
If you get the value list from the People table, the drop-down list can
only show values that have previously been entered (again something
that is sometimes handy), but which would prove "difficult" if there
are no existing records since the list would be blank.
That had been the purpose here.
I think part of the weirdness you were seeing is possibly also due to
this, unless I'm reading it wrong:
The weirdness is that there are multiple repetitions of the same values,
depending on how many records do contain this value, but not a 1:1
match. I'd have to run some checks, but it's about the square root of
records - so for 10 records of California, the dropdown list would offer
Azusa
Azusa
Azusa
Bakersfield
Bakersfield
Bakersfield
...
I haven't tested it, but if the value list was just getting values
directly from the field, you'd probably get one entry from each
separate People table records.
But since the value list is getting it's values via a relationship link
to the field, you'll get an increasing number of values as each
spearate People table record finds more and more related records as new records are added.
That makes it difficult for users to enter a new City because it
doesn't have a previous existing record - value lists from fields can't
be edited like normal static lists can.
On Sun, 17 Sep 2023 09:58:02 +1200, Helpful Harry wrote:
I think part of the weirdness you were seeing is possibly also due to
this, unless I'm reading it wrong:
The weirdness is that there are multiple repetitions of the same values, >>> depending on how many records do contain this value, but not a 1:1
match. I'd have to run some checks, but it's about the square root of
records - so for 10 records of California, the dropdown list would offer >>>
Azusa
Azusa
Azusa
Bakersfield
Bakersfield
Bakersfield
...
I haven't tested it, but if the value list was just getting values
directly from the field, you'd probably get one entry from each
separate People table records.
But since the value list is getting it's values via a relationship link
to the same field, you'll get an increasing number of values as each
spearate People table record finds more and more related records as new
records are added.
I disagree. Why would there be any benefit in order to show the same
value multiple times - especially if it is not a 1:1 number of
repetitions, that you will have exactly the same number of matching
entries?
That makes it difficult for users to enter a new City because it
doesn't have a previous existing record - value lists from fields can't
be edited like normal static lists can.
A drop down list is a suggestion only. You still can edit other values
here, although those values shall not become part of the drop down suggestions. Usually this would be villages which muss less importance.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 297 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 105:28:43 |
| Calls: | 6,660 |
| Calls today: | 2 |
| Files: | 12,209 |
| Messages: | 5,335,314 |