Re: Dynamic LOVs

>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 Received on Fri May 29 1998 - 07:36:37 CEST