I am wondering if I am missing the obvious here … it is to do with getting a Valuelist to display child table items. I can do what I need to do with setValueListItems() but I am wondering if I am doing it the hard way.
I have a Contacts table that has 2 fields in it defining the Country and the Subregion (Province or State). These fields are type integer and are called country_id and subregion_id.
When the user selects the country_id (via a routine Valuelist that shows the Country Name and returns the country_id) the next field is the subregion field. I would like to show only the subset of regions for a given country. Canada would show a dozen or so records, USA 50 or so, Germany’s 16 states and so on.
User screen:
Country [country_id ] … Valuelist shows country names
Subregion [subregion_id ] … need to show related provinces/states
City [city … ]
User would select the Country (country_id in the foundset current record now) and then Valuelist for Subregion would show only the related records.
I was thinking that I could use a Valuelist (based on a relation between countries and subregions). The valuelist would know that I want to use the country_id field (it is the only field in the current record buffer for Contacts that makes any sense in the context of the relationship) as the “parent” to the “child” record (in subregions).
Is this an appropriate use of a Valuelist or am I attempting to use it for something it wasn’t meant for?
All I get is a blank valuelist popup when I try to use it this way. Version 3.5 final.
Thanks, Michael
I am not sure that I understand what exactly you do, but what you can do is define a relation from contacts.country_id to subregions.country_id and use that to define a value list that shows the subregion and returns the subregion_id. Your description sounds a bit as if you missed that you need to define that relation from contacts and not from countries.
Hope this helps.
Thanks, Patrick, but not quite what I was hoping to achieve. If I add the suggested relation I can indeed see the (one) subregion that was keyed originally but not the list of all all available subregions.
I think this is what the feature intends: the child_id (housed by the parent table, Contacts) should only show the one related child record (by virtue of the Contacts → Subregions relationship).
I would actually need a Right Outer Join with the where condition set by the Country_ID (Subregions are then effectively filtered).
The Prov/State field will alter depending on what I choose for a Country. Here is a screen shot of a couple of cases (data cut down for JPEG) and an actual portion of a screen:
Sorry Michael, I don’t get it. Why do you see only one subregion? Let’s suppose you have this table setup
table countries
country_id
country
table subregions
subregion_id
subregion
country_id
table contacts
…
country_id
subregion_id
Now you have a relation from contacts.country_id to subregions.country_id. Then, if a user chooses Germany, the relation points to all subregions with Germany’s country_id, thus being able to offer all German subregions. What you describe sounds like you have created a relation from contacts.subregion_id to subregions.subregion_id. That, of course, shows you only one subregion…
What am I getting wrong here?
I think that your problem is called a n-m relation
Your must have at least 3 tables and 3 relations
Table Contacts or Adressess (in this case contact)
Fields Country_id and State_Id
Table Countries
Field Country_Id
Table States
Fields State_Id and Country_Id
Relations
Contact_to_country (country_id based)
Contact_to_state (state_id based)
Country_to_state (country_id based)
Now you can create Two Lists:
List_country : All database value from table Countries
List_state_by_country : Related Value (Contact_to_country ) and in the second relation combo (country_to_state)
You’ll see that when you select a country Servoy will filter your state liste
Maybe it’s too early for me or we have posted in the same minute ![:?]( "Confused")
. Isn’t this somehow what I said? And: I don’t really see where the N:M happens. country to subregion is 1:N, contacts to country is 1:1, contacts to subregions per country is 1:N and contacts to subregions is 1:1…
Hi Patrick,
It was a clear-cut case of I couldn’t see the wood for the trees of the Black Forest!
I had the wrong relation running ![:oops:]( "Embarassed")
and you pointed out the correct one (Contacts.Country_ID to Subregions.Country_ID).
Your assistance is appreciated - there’s nothing like a second set of eyes (ie, the forum and willing/helpful participants) for helping resolve these things.
It works great now!
Best, Michael
glad to hear that works right.
