Hi,

 

I am trying to implement a Country >> States list with only 3 countries (USA, Canada, Mexico). Instead of deleting dozens of entries from the DNN Country list (under Host >> Lists page), I created a new list called "Country2 and added 3 entries (USA, Canada, and Mexico)

 

I created anloth 'List' called 'State' and chose Country2 as the Parent list and added States for each Parent entry (USA, Canad, na Mexico)

 

Now I have the following lists under Host >> Lists

+Country2
---Country2.Canada:State
---Country2.Mexicao:State
---Country2.USA.State

I created a new combobox (called "Country3") and added the following SQL under SQL options:

select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'Country2' ORDER by QuestionOption ASC

It is correctly populating the three entries I created (in fact, four, including the 'select one' entry.

So far so good....

I created another Combobox and called it State. I used the  following SQL to populate the list.

select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'State' AND ParentID = '$(Country2)'.

 

It gives SQL error.

I replaced token with the actual Country value, USA (see the full SQL)

select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'State' AND ParentID = 'USA'

It still gives SQL Error.

 

Whewn I remove the AND clause, it works; but it list ALL the States from ALL the countries (as expected)

select Text as QuestionOption, Text as QuestionOptionValue from Lists where ListName= 'State' >>>> (this works)

Questions:

1. Should we define the lists with Parent Child relationship under Host >> Lists to implement the dependent lists?

2. When we use the 'built-in' DNN Country and DNN REgion fields in Dynamic Forms, it creates a Text field for the Region (instead of Combo box. bjut it changes dynamically to Combo box. Can we leverage the existing DNN Region lists and make it work with a new Country list with fewer entries?

 

basically I would like to know the best way to implement Country >> States lists  for 3 or 4 countries without deleting the existing Country list...

 

The SQL statements used in Demo11 are too complex without access to the exact data under Hosts >> Lists.

Can we use the existing DNN Country list and Region list as the source and implement our own Country combox with a SQL that will limit the countries to just few?

 

Any help is greatly appreciated.

HI, you didn't have to go through all that trouble. You can use the existing "LISTS" table to accomplish all of this. Create two combo box, one for country and one or state. Use the following sql query:

country sql: select text as questionoption, value as questionoptionvalue from Lists where listname = 'Country' and Text in ('Canada','Mexico','United States')

state sql: select text as questionoption,value as questionoptionvalue from Lists where listname = 'Region' and ParentID in ('221','36')

NOTE: 221 is states for United States and 36 is states for Canada
Try this out and see if it works for you. -- David

Hi David,

Great!!!

This should work.

I will try.

If I have to hard code the "ParentID" like this, then, how would DEmo 11 work with variable like  '$(comboboxname)'?

Let me see the other thread (you answered) and discuss it there

Thanks again,

VJ

Hi David,

Thank you. The Country list is fine. But the in the States, I don't want to show ALL the States of Both USA and Canada. I only want to show the States based on the Country selected. So, I want to use a variable for ParentID. This was my original question. Demo 11 uses such variables.

Would you please throw some light on this?

Thanks,

VJ

Hi David,

To make my question clear, I created a short form on BetaSprings.com.

http://www.betasprings.com/Dynamic-...fault.aspx

Page Name:  Dependent Lists

Module also : Dependent Lists

 

1. Added a combobox: Country (populated by a SQL: just three Countries: Canada, Mexico, USA): this is working fine.

2. Added a end Combobox: State. The SQL here is like this:

SELECT '-select State-' As QuestionOption ,'-1' As QuestionOptionValue
UNION ALL
select text as questionoption,value as questionoptionvalue from Lists where listname = 'Region' and ParentID = '$(Country)'

3. I made this filed as "hide until triggered by a question event"

4. Created a Question event (ShowState); chose "any response" for Country filed; selected "Sate" filed to be shown.

It is not working...

If you can make it work, it will help lost of people who are in the same situation as mine

Thanks,

VJ

DONE. Your SQL logic is slightly incorrect. On the Country Field, I changed the SQL to:

SELECT '-select Country-' As QuestionOption ,'-1' As QuestionOptionValue
UNION ALL
select text as questionoption, entryid as questionoptionvalue from Lists where listname = 'Country' and Text in ('Canada','Mexico','United States')

NOTE: Before, you had "value" as questionoptionvalue which would yield "CA,MX,US". However, on your State, you have:
SELECT '-select State-' As QuestionOption ,'-1' As QuestionOptionValue
UNION ALL
select text as questionoption, value as questionoptionvalue from Lists where listname = 'Region' and ParentID = '$(Country)'

but ParentID for the States will never be "CA,MX,US", since the parentid is a numeric number equal to the "EntryID" of the Lists table. -- David

Hi David,

You are awesome!

Thank you so much for helping me with this...

Now I want to try with my own lists (not using built-in DNN lists of Country and Region) and apply your suggestion.

 

Thanks again!!!

VJ

Hi David,

I am very gld that I am able to implement 3 level dependent lists based on your solution.

Thanks a lot!!

There is one small issue there:

http://www.betasprings.com/Dynamic-...fault.aspx

 

I created a 2nd set of fields: US Region, US State, and US center.

If I select Region01, The States List appears (from hidden stateus);  But the US Center list also is showing up though I chose thru a question event "ShowUSCenter", that any response from "US State" should make it visible. But even without clicking the US State combobox, the US Center list is being displayed.

Also, once I go thru all the three levels....when I change the top level (US Region), the US State list is reset  (showing the "select a state"); but the US Center still keep shsowing the old selection.

 

I must be doing something wrong in settings??

Please throw some light on this. Thanks again !!

VJ

I believe there's a bug where question event won't work for nested question events, just like the way you have it. -- David