How To Create Value Sets Where One Segment Is Dependent On The Previous Segment Selection


How to create value sets so that one segment is dependent on the previous segment selection?

Example of $FLEX$ Syntax Here is an example of  using :$FLEX$.Value_Set_Name to set up value sets where one
segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment’s values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name Manufacturer
Value Set Name Car_Maker_Name_Value_Set
Validation Table CAR_MAKERS
Value Column MANUFACTURER_NAME
Description Column MANUFACTURER_DESCRIPTION
Hidden ID Column MANUFACTURER_ID
SQL Where Clause (none)

Segment Name Model
Value Set Name Car_Model_Name_Value_Set
Validation Table CAR_MODELS
Value Column MODEL_NAME
Description Column MODEL_DESCRIPTION
Hidden ID Column MODEL_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set

Segment Name Color
Value Set Name Car_Color_Name_Value_Set
Validation Table CAR_COLORS
Value Column COLOR_NAME
Description Column COLOR_DESCRIPTION
Hidden ID Column COLOR_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set AND
MODEL_ID = :$FLEX$.Car_Model_Name_Value_Set

In this example, MANUFACTURER_ID is the hidden ID column and
MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.