Xref: alice comp.databases.oracle.tools:13082
Path: alice!news-feed.fnsi.net!news.idt.net!news-peer.gip.net!news.gsl.net!gip.net!portc01.blue.aol.com!audrey03.news.aol.com!not-for-mail
From: danhw@aol.com (DanHW)
Newsgroups: comp.databases.oracle.tools
Subject: Re: Dynamic LOVs
Lines: 47
Message-ID: <1998052905363800.BAA29286@ladder03.news.aol.com>
X-Admin: news@aol.com
Date: 29 May 1998 05:36:37 GMT
Organization: AOL http://www.aol.com
References: <356D4B9A.560D@domain-tech.com>

>I'm using FORMS 4.5 on an NT platform.  I have a series of 
>4 List Items.  The first List Item is populated via a 
>Record Group when the Form is opened.  The remaining 3
>List Items need to have their Lists created dynamically
>based on the selected values of the previous List Items.
>i.e.  After selecting a value for List Item 1, List Item 2
>now needs to contain a subset of values that are restricted 
>by the value of List Item 1.  After selecting a value for List 
>Item 2, List Item 3 now needs to contain a subset of values 
>that are restricted by the value of List Items 1 & 2.  
>
>For example, if you were selecting criteria for a map, the first
>item (country) would contain all countries.  Once a country
>was selected, the second item (state), should contain a list
>of only the states within the selected country.  Likewise, the 
>next item (city), should contain a list of only the cities within
>the selected country and state. 

I have done this many times on my LOVs - it works great. The way to do it is
simply put the form field name (:block.field) in the where clause in the query
that builds the record group fo rthe dependent LOV. There is also an option
that I believe is called 'auto-refresh', (I don't remember if it belongs to the
LOV or the record group) that must be set to tell Oracle to re-load the record
group each time  the LOV is invoked. That way you get the current selection. 
There is also a nifty trick I use with these...

On the dependent LOV (on state from your example), I also populate the country
field. The where clause in the dependen LOV uses the NVL function so that if no
country is provided, all states are displayed. If a country is provided, only
the appropriate states are provided. When a state is selected, both the country
and state are filled. To do this...

select  ... from ...
where
   nvl(:country, country_name) = country_name

If the form field :COUNTRY has a value, the NVL will return that value, and the
form field :COUNTRY must match the table column COUNTRY_NAME, applying the
correct where condition. 

If the form field :COUNTRY is null, then the NVL returns the database column
COUNTRY_NAME. This results in the effective where clause of COUNTRY_NAME =
COUNTRY_NAME, which is satisfied by every row.

Good luck

Dan