Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Re: [FMP11] value lists and secondary fields - shows secondary value multiple times
6 views
Skip to first unread message
Helpful Harry
Sep 15, 2023, 2:02:38 AM9/15/23
to
On 2023-09-12 12:59:32 +0000, Martin Τrautmann said:
>
> 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
Put the two data entry fields on the layout and format them as
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
All done.
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.
Helpful Harry :o)
>
> 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
Put the two data entry fields on the layout and format them as
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
All done.
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.
Helpful Harry :o)
Helpful Harry
Sep 15, 2023, 7:06:27 PM9/15/23
to
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:
> The state field is entered first, taking its values from the state field
> itself (value list "state", from the entries of field "state").
That seems to be saying the State field is getting its value list data
from the same field (i.e. People table), but the state value list needs
to get its values from the Cities table rather than the People table.
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.
Helpful Harry :o)
>
> 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:
> The state field is entered first, taking its values from the state field
> itself (value list "state", from the entries of field "state").
from the same field (i.e. People table), but the state value list needs
to get its values from the Cities table rather than the People table.
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.
Helpful Harry :o)
Helpful Harry
Sep 16, 2023, 5:58:06 PM9/16/23
to
On 2023-09-16 08:39:46 +0000, Martin Τrautmann said:
> options.
True, which is occasionally handy if you need to group the values
differently to normal alphabetically. For example, you could have all
the US cities in the drop-down menu, but have them grouped
alphabetically by state (i.e. all the California cities would be higher
up the list than all the Florida cities).
Although, not displaying that second field can make it confusing for
users since they might be looking for a city starting with D and it
isn't near the top. Without actually scrolling down to see there are
other sub-sorted lists, they won't know it is there. Whereas with the
State also displayed, they know to look further down for the State's
cities they want.
>> 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.
It's really only a cosmetic and user-friendly option, especially if, as
above, you want to group the values non-alphabetically for some reason..
>> 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.
>> 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.
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.
Helpful Harry :o)
> 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
>> 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.
>
> options.
True, which is occasionally handy if you need to group the values
differently to normal alphabetically. For example, you could have all
the US cities in the drop-down menu, but have them grouped
alphabetically by state (i.e. all the California cities would be higher
up the list than all the Florida cities).
Although, not displaying that second field can make it confusing for
users since they might be looking for a city starting with D and it
isn't near the top. Without actually scrolling down to see there are
other sub-sorted lists, they won't know it is there. Whereas with the
State also displayed, they know to look further down for the State's
cities they want.
>> 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.
>
> take the second value, but the first value, even if the first value is
> hidden.
It's really only a cosmetic and user-friendly option, especially if, as
above, you want to group the values non-alphabetically for some reason..
>> I think part of the weirdness you were seeing is possibly also due to
>> this, unless I'm reading it wrong:
>
> 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.
>> 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 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.
Helpful Harry :o)
Helpful Harry
Sep 17, 2023, 6:28:13 PM9/17/23
to
On 2023-09-17 07:16:05 +0000, Martin Τrautmann said:
> 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?
There probably is no benefit, which is why you don't really want to do
it this way. :o)
The number of values is not 1:1 because the relationship is not 1:1,
but exponential. If you have two records with "ABC" then you would get
two relationship links.
i.e. Record 1 -> Record 2
Record 2 -> Record 1
(Without testing it, I'm not sure if you would also get Record 1 ->
Record 1 and Record 2 -> Record 2.)
If you have three records with "ABC" then you would get six relationship links.
i.e. Record 1 -> Record 2
Record 1 -> Record 3
Record 2 -> Record 1
Record 2 -> Record 3
Record 3 -> Record 1
Record 3 -> Record 2
Each of those related data values will appear in the drop-down list
seperately ... which is what you were seeing happen.
>> 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.
Except if someone edits the data in People::City, then that data will
also appear in the value list because of the circular relationship link
to the field itself.
Perhaps a better way would be to have an "Other" option in the value
list (from the Cities table) and an OtherCity field to fill in. You
will of course need to add an "Other" city for every state in the
Cities table.
Helpful Harry :o)
> 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
>>>> 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
>> 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
>> records are added.
>
> 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?
There probably is no benefit, which is why you don't really want to do
it this way. :o)
The number of values is not 1:1 because the relationship is not 1:1,
but exponential. If you have two records with "ABC" then you would get
two relationship links.
i.e. Record 1 -> Record 2
Record 2 -> Record 1
(Without testing it, I'm not sure if you would also get Record 1 ->
Record 1 and Record 2 -> Record 2.)
If you have three records with "ABC" then you would get six relationship links.
i.e. Record 1 -> Record 2
Record 1 -> Record 3
Record 2 -> Record 1
Record 2 -> Record 3
Record 3 -> Record 1
Record 3 -> Record 2
Each of those related data values will appear in the drop-down list
seperately ... which is what you were seeing happen.
>> 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.
>
> here, although those values shall not become part of the drop down
> suggestions. Usually this would be villages which muss less importance.
Except if someone edits the data in People::City, then that data will
also appear in the value list because of the circular relationship link
to the field itself.
Perhaps a better way would be to have an "Other" option in the value
list (from the Cities table) and an OtherCity field to fill in. You
will of course need to add an "Other" city for every state in the
Cities table.
Helpful Harry :o)
0 new messages