6 Posts
BobJ
6 years ago
1
Topic

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)

Table 1 = location

- id

- location_code

- location_name

Table 2 = location_facility

- id

- location_code

- location_facility_code

- location_facility_name

Table 3 = location_facility_area

- id

- location_code

- location_facility_code

- location_facility_area_code

- location_facility_area_name

Table 4 = location_facility_area_zone

- id

- location_code

- location_facility_code

- location_facility_area_code

- location_facility_area_zone_code

- location_facility_area_zone_name

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

Id location_code location_facility_code location_facility_area_code location_facility_area_name
1 CX C1 WH Warehouse
2 CX C2 UT Utilities
3 CX DM 59 Area 59
4 BV C1 WH Warehouse
5 BV DM 58 Area 58
6 BV DM 59 Area 59
7 BV DM 60 Area 60

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

Get a VIP membership
4229 Posts
Kadministrator
6 years ago
0
Level 1

I don't think there is anything more than [parent] in s.d.c.

Get a VIP membership