Restrict LOV based on multiple LOV's-CodePudding

I have a city column with cities: (New York, Miami).
And an area column with areas: (North, South, West, east).
And shops column: (Shop a, shop b, shop c)

Suppose that I have the following records

1- (New York, North, Shop a) 
2- (Miami, North, shop c)
3-  (New York, south, Shop b)

I need to create three LOV’s, the first one is city, the second one is area and the last is shop. If I select New York in the city LOV from the database, I need the area options in the area LOV to be north and south and, if I select North I want only shop a to appear in the shop LOV.

CodePudding user response：

Where do you have those "following records"? In a table? I presume.

Basically, you have to create a cascading list of lists of values.

The first LoV (for e.g. P1_CITY item) would contain cities:

select distinct city d, city r
from your_table
order by city

The second (for P1_AREA item) would then select areas that belong to selected city:

select distinct area d, area r
from your_table
where city = :P1_CITY
order by area

Do not forget to set Cascading LoV property for the 2nd LoV! Should be P1_CITY item.

The same goes for shops:

select distinct shop d, shop r
from your_table
where city = :P1_CITY
  and area = :P1_AREA
order by shop

by setting Cascading LoV to both P1_CITY, P1_AREA.