I have a requirement to use Select Dynamic Cascade with 4 tables containing list option/values to populate values for a form.

The four tables representing the hierarchy are as follows.

(For all tables the _code is 2 char alpha)

Problem - I need to add a second value to the where clause starting at Field 3 below, because if I use only the parent value it's possible to not return unique results. In the table example below you can see that "location_facility_area" table in rows 3 and 6, has the value DM for both location_facility_code and 59 for location_facility_area_code... The Uniqueness requires the Location_code field values to ensure there isn't an invalid entry in the cascade.

EXAMPLE DATA FOR TABLE location_facility_area

The Dynamic Cascade for the Form is

Field 1 - Location

Query=Construction

Behavior=Start

Group Identifier=locationgroup

Table=location

Options Name=location_name

Options Value=location_code

Where=(Nothing Entered here)

Field 2 - Facility

Query=Free

Behavior=in between

Group Identifier=locationgroup

SQL Query= SELECT location_facility_name AS text, location_facility_code AS value FROM location_facility WHERE location_code = "[parent]"

Options Value=location_facility_code

Parent=location_code

Field 3 - Area

Query=Free

Behavior=in between

Group Identifier=locationgroup

SQL Query=SELECT location_facility_area_name AS text, location_facility_area_code AS value FROM location_facility_area WHERE location_facility_code = "[parent]"

SELECT location_facility_area_name AS text, location_facility_area_code AS value FROM location_facility_area WHERE location_facility_code = "[parent]"

Options Value=location_facility_area_code

Parent=location_facility_code

HOW TO SET

SQL Query=SELECT location_facility_area_name AS text, location_facility_area_code AS value FROM location_facility_area WHERE location_facility_code = "[parent]" AND location_code = location_code from the select in Field 2

Possible Problem the field names are the same in all tables ex. location.location_code, location_facility.location_code, Location_facility_area.location_code

Field 4 - Zone

Query=Construction

Behavior=end

Group Identifier=locationgroup

Table=location_facility_area_zone

Options Name=location_facility_area_zone_name

Options Value=location_facility_area_zone_code

Where=(Nothing Entered here)

Parent=otip_location_facility_area_code

HOW TO SET Where (or use Free) to avaluate where for 3 items

WHERE location_facility_area_code = "[parent]"

AND location_code = location_code from the select in Field 2

AND location_facility_code = location_facility_code from select in Field 3