Dynamic GuiXT scripting techniques: Dependent drop down lists

Purpose
Create dependent drop down lists: the value selected in one dropdown changes the value list of other dropdowns. In addition, the value selection in one dropdown automatically selects a value in another dropdown if only one possible choice remains.

Solution
Use the process= option of the DropDownList command in order to build up dynamic value lists for other dropdowns.

Example
We want to let the user enter the following organizational sales data

Sales Organization
Distribution Channel
Division
Sales Office
Sales Group

We use a dropdown for each entry field:

SAP customizing tables define the valid combinations of these values. For example, the sales office selection depends on the sales organization. Without selecting a particular sales organization a large list of sales offices is available:

When the user first selects a sales organization, for example "Sales Eastern", only the two Eastern offices are shown in the drop down list:

Similarly if he chooses "Sales Western":

A user does not need to start with the first dropdown. For example, he can start by selecting "Mr Ruud" in the last drop down, "Sales Group":

This automatically fills in several other organizational fields, since "Mr Ruud" uniquely determines these fields:

Remark
There are essentially three different approaches to implement drop down lists that logically depend on each other:

(a) Independent dropdowns

The user chooses the values independent from each other and the system checks the final combination.

Advantages: Easy implementation. No need for a "Reset" button since all values are always visible.

Disadvantages: The user has to select a value in each drop down. The selected value combination might be rejected by the system.

(b) Cascading dropdowns

The dropdowns represent a hierarchical structure where a selection in one dropdown determines the value list in the following, subordinated ones.

Advantages: Restricted subordinated value lists. Only valid combinations can be selected if the user processes the dropdowns in the right sequence.

Disadvantages: The user needs to select values in the right sequence (top to bottom). No efficient data input if subordinated values as known without the upper levels being known.

(c) Interdependent dropdowns

The dropdowns represent a net structure where a selection in one dropdown can determine the value list in any other dropdown.

Advantages: Efficient user input. Only valid combinations can be selected without forcing the user to follow a certain order in the value selection.

Disadvantages: A "Reset" button is needed, otherwise the user can lock himself with a wrong selection. Relatively ambitious implementation.

For this example we use approach (c).

Implementation
The implementation consists of three parts:

A GuiXT script that defines the user interface and calls up the other two parts.
An "include" script that reads all possible values of sales organizations, distribution channels and so on from the SAP customizing tables. No dependencies between the dropdowns are observed in this part.
An InputScript that filters the complete value lists depending on the user input. It also determines the automatically chosen value if only one value remains in a dropdown after the filter operation.

For database access we use the function module /guixt/dbselect.

Performance
The algorithm for building up of the filtered dropdown lists needs to be relatively fast, for example will need below 0.2 seconds, otherwise the delay will affect the data entry efficiency. If larger value lists are expected we need to avoid any "quadratic effects" in our implementation. This is achieved in the implementation given below by using GuiXT variables with dynamically built names. Since the access to GuiXT variables is based on a hash table it remains fast even for a large number of variables. We also use the "distinct" option of the database in order to receive a list of entities (sales organizations, offices and so on) without duplicates.

GuiXT Script

GuiXT

// read all vkorg, vtweg, spart, vkbur, vkgrp
if not V[organization_data_read]
  include "read_organizational_data.txt"

 // filter data according to user input
 Enter process="filter_organizational_data.txt"
 Stop
endif

Offset (5,100)

Box (0,0) (8,62) "Organizational Data"
Text (1,1) "Sales Organization" size=20 -label
DropDownList (1,22) "vkorglist" width=40 refer="V[vkorg]" _
  process="filter_organizational_data.txt"

Text (2,1) "Distribution Channel" size=20 -label
DropDownList (2,22) "vtweglist" width=40 refer="V[vtweg]" _
  process="filter_organizational_data.txt"

Text (3,1) "Division" size=20 -label
DropDownList (3,22) "spartlist" width=40 refer="V[spart]" _
  process="filter_organizational_data.txt"

Text (4,1) "Sales Office" size=20 -label
DropDownList (4,22) "vkburlist" width=40 refer="V[vkbur]" _
  process="filter_organizational_data.txt"

Text (5,1) "Sales Group" size=20 -label
DropDownList (5,22) "vkgrplist" width=40 refer="V[vkgrp]" _
  process="filter_organizational_data.txt"

Pushbutton (7,48) "@TS@Clear input" process="clear_organizational_data.txt"

Include "read_organizational_data.txt"

GuiXT

// read "vkorg" values (sales organizations)
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVKOT" _
  in.fields="VKORG,VTEXT" _
  in.condition="SPRAS = '&V[_language]'" _
  table.values="r"

Set V[k] 0
label read_vkorg
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkorg" line="&V[k]"
  if Q[ok]
     Set V[k] &V[k] + 1
    CopyText fromText="r" toString="myvtext" line="&V[k]"
      Set V[dropdownitem_vkorg_&V[myvkorg]] _ 
		"&V[myvkorg]=&V[myvkorg] &V[myvtext]"
      goto read_vkorg
  endif

// read "vtweg" values (distribution channels)
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVTWT" _
  in.fields="VTWEG,VTEXT" _
  in.condition="SPRAS = '&V[_language]'" _
  table.values="r"

Set V[k] 0
label read_vtweg
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvtweg" line="&V[k]"
  if Q[ok]
      Set V[k] &V[k] + 1
   CopyText fromText="r" toString="myvtext" line="&V[k]"
     Set V[dropdownitem_vtweg_&V[myvtweg]] _ 
		"&V[myvtweg]=&V[myvtweg] &V[myvtext]"
     goto read_vtweg
endif

// read "spart" values (divisions)
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TSPAT" _
  in.fields="SPART,VTEXT" _
  in.condition="SPRAS = '&V[_language]'" _
  table.values="r"

Set V[k] 0
label read_spart
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myspart" line="&V[k]"
  if Q[ok]
      Set V[k] &V[k] + 1
   CopyText fromText="r" toString="myvtext" line="&V[k]"
     Set V[dropdownitem_spart_&V[myspart]] _ 
		"&V[myspart]=&V[myspart] &V[myvtext]"
     goto read_spart
endif

// read "vkbur" values (sales offices)
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVKBT" _
  in.fields="VKBUR,BEZEI" _
  in.condition="SPRAS = '&V[_language]'" _
  table.values="r"

Set V[k] 0
label read_vkbur
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkbur" line="&V[k]"
  if Q[ok]
       Set V[k] &V[k] + 1
    CopyText fromText="r" toString="mybezei" line="&V[k]"
       Set V[dropdownitem_vkbur_&V[myvkbur]] _ 
		"&V[myvkbur]=&V[myvkbur] &V[mybezei]"
      goto read_vkbur
  endif

// read "vkgrp" values (sales groups)
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVGRT" _
  in.fields="VKGRP,BEZEI" _
  in.condition="SPRAS = '&V[_language]'" _
  table.values="r"

Set V[k] 0
label read_vkgrp
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkgrp" line="&V[k]"
  if Q[ok]
       Set V[k] &V[k] + 1
    CopyText fromText="r" toString="mybezei" line="&V[k]"
       Set V[dropdownitem_vkgrp_&V[myvkgrp]] _ 
		"&V[myvkgrp]=&V[myvkgrp] &V[mybezei]"
    goto read_vkgrp
  endif

// indicate: organizational data read
Set V[organization_data_read] "X"

InputScript "filter_organizational_data.txt"

GuiXT

// filter all dropdown lists according to user input

// if a  unique value is set automatically we need to repeat the selection
label repeat_selection

// build up condition for vkorg
Set V[condition] "TVKBZ~VKORG NE SPACE"
if V[vtweg]
  Set V[condition] "TVKBZ~VTWEG = '&V[vtweg]' AND &V[condition]"
endif

if V[spart]
  Set V[condition] "TVKBZ~SPART = '&V[spart]' AND &V[condition]
endif

if V[vkbur]
  Set V[condition] "TVKBZ~VKBUR = '&V[vkbur]' AND &V[condition]"
endif

if V[vkgrp]
  Set V[condition] "TVBVK~VKGRP = '&V[vkgrp]' AND &V[condition]"
endif

// determine the filtered "vkorg" values
Clear text[vkorglist]
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVKBZ join TVBVK on TVKBZ~VKBUR = TVBVK~VKBUR" _
  in.fields="TVKBZ~VKORG" _
  in.distinct="X" _
  in.condition="&V[condition]" _
  table.values="r"

Set V[k] 0
label filter_vkorg
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkorg" line="&V[k]"
  if Q[ok]
      if V[dropdownitem_vkorg_&V[myvkorg]]
        CopyText fromString="dropdownitem_vkorg_&V[myvkorg]" _
         toText="vkorglist" -appendLine
        Set V[save_vkorg] "&V[myvkorg]"
     endif
     goto filter_vkorg
  endif

// build up condition for vtweg
Set V[condition] "TVKBZ~VTWEG NE SPACE"
if V[vkorg]
  Set V[condition] "TVKBZ~VKORG = '&V[vkorg]' AND &V[condition]"
endif

if V[spart]
  Set V[condition] "TVKBZ~SPART = '&V[spart]' AND &V[condition]"
endif

if V[vkbur]
  Set V[condition] "TVKBZ~VKBUR = '&V[vkbur]' AND &V[condition]"
endif

if V[vkgrp]
  Set V[condition] "TVBVK~VKGRP = '&V[vkgrp]' AND &V[condition]"
endif

// determine the filtered "vtweg" values
Clear text[vtweglist]
Clear text[r]

Call /GuiXT/dbselect _
  in.table="TVKBZ join TVBVK on TVKBZ~VKBUR = TVBVK~VKBUR" _
  in.fields="TVKBZ~VTWEG" _
  in.distinct="X" _
  in.condition="&V[condition]" _
  table.values="r"

Set V[k] 0
label filter_vtweg
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvtweg" line="&V[k]"
  if Q[ok]
      if V[dropdownitem_vtweg_&V[myvtweg]]
         CopyText fromString="dropdownitem_vtweg_&V[myvtweg]" _
         toText="vtweglist" -appendLine
         Set V[save_vtweg] "&V[myvtweg]"
    endif
      goto filter_vtweg
  endif

// build up condition for spart
Set V[condition] "TVKBZ~SPART NE SPACE"
if V[vkorg]
  Set V[condition] "TVKBZ~VKORG = '&V[vkorg]' AND &V[condition]"
endif

if V[vtweg]
  Set V[condition] "TVKBZ~VTWEG = '&V[vtweg]' AND &V[condition]"
endif

if V[vkbur]
  Set V[condition] "TVKBZ~VKBUR = '&V[vkbur]' AND &V[condition]"
endif

if V[vkgrp]
  Set V[condition] "TVBVK~VKGRP = '&V[vkgrp]' AND &V[condition]"
endif

// determine the filtered "spart" values
Clear text[spartlist]
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVKBZ join TVBVK on TVKBZ~VKBUR = TVBVK~VKBUR" _
  in.fields="TVKBZ~SPART" _
  in.distinct="X" _
  in.condition="&V[condition]" _
  table.values="r"

Set V[k] 0
label filter_spart
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myspart" line="&V[k]"
  if Q[ok]
      if V[dropdownitem_spart_&V[myspart]]
         CopyText fromString="dropdownitem_spart_&V[myspart]" _
          toText="spartlist" -appendLine
         Set V[save_spart] "&V[myspart]"
    endif
      goto filter_spart
  endif

// build up condition for vkbur
Set V[condition] "TVKBZ~VKBUR NE SPACE"
if V[vkorg]
  Set V[condition] "TVKBZ~VKORG = '&V[vkorg]' AND &V[condition]"
endif

if V[vtweg]
  Set V[condition] "TVKBZ~VTWEG = '&V[vtweg]' AND &V[condition]
endif

if V[spart]
  Set V[condition] "TVKBZ~SPART = '&V[spart]' AND &V[condition]"
endif

if V[vkgrp]
  Set V[condition] "TVBVK~VKGRP = '&V[vkgrp]' AND &V[condition]"
endif

// determine the filtered "vkbur" values
Clear text[vkburlist]
Clear text[r]

Call /GuiXT/dbselect _
  in.table="TVKBZ join TVBVK on TVKBZ~VKBUR = TVBVK~VKBUR" _
  in.fields="TVKBZ~VKBUR" _
  in.distinct="X" _
  in.condition="&V[condition]" _
  table.values="r"

Set V[k] 0
label filter_vkbur
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkbur" line="&V[k]"
  if Q[ok]
     if V[dropdownitem_vkbur_&V[myvkbur]]
        CopyText fromString="dropdownitem_vkbur_&V[myvkbur]" _
        toText="vkburlist" -appendLine
        Set V[save_vkbur] "&V[myvkbur]"
   endif
    goto filter_vkbur
  endif

 

// build up condition for vkgrp
Set V[condition] "TVBVK~VKGRP NE SPACE"
if V[vkorg]
  Set V[condition] "TVKBZ~VKORG = '&V[vkorg]' AND &V[condition]"
endif

if V[vtweg]
  Set V[condition] "TVKBZ~VTWEG = '&V[vtweg]' AND &V[condition]"
endif

if V[spart]
  Set V[condition] "TVKBZ~SPART = '&V[spart]' AND &V[condition]"
endif

if V[vkbur]
  Set V[condition] "TVKBZ~VKBUR = '&V[vkbur]' AND &V[condition]"
endif

// determine the filtered "vkgrp" values
Clear text[vkgrplist]
Clear text[r]
Call /GuiXT/dbselect _
  in.table="TVKBZ join TVBVK on TVKBZ~VKBUR = TVBVK~VKBUR" _
  in.fields="TVBVK~VKGRP" _
  in.distinct="X" _
  in.condition="&V[condition]" _
  table.values="r"

Set V[k] 0
label filter_vkgrp
  Set V[k] &V[k] + 1
  CopyText fromText="r" toString="myvkgrp" line="&V[k]"
  if Q[ok]
      if V[dropdownitem_vkgrp_&V[myvkgrp]]
         CopyText fromString="dropdownitem_vkgrp_&V[myvkgrp]" _
        toText="vkgrplist" -appendLine
        Set V[save_vkgrp] "&V[myvkgrp]"
    endif
      goto filter_vkgrp
endif

Clear V[repeat]

// vkorg now unique?
if V[save_vkorg] and not V[vkorg]
  CopyText fromText="vkorglist" toString="s" line=2
  if not Q[ok]
      Set V[vkorg] "&V[save_vkorg]"
    Set V[repeat] "X"
   endif
endif

// vtweg now unique?
if V[save_vtweg] and not V[vtweg]
  CopyText fromText="vtweglist" toString="s" line=2
  if not Q[ok]
     Set V[vtweg] "&V[save_vtweg]"
   Set V[repeat] "X"
  endif
endif

// spart now unique?
if V[save_spart] and not V[spart]
   CopyText fromText="spartlist" toString="s" line=2
  if not Q[ok]
    Set V[spart] "&V[save_spart]"
    Set V[repeat] "X"
   endif
endif

// vkbur now unique?
if V[save_vkbur] and not V[vkbur]
  CopyText fromText="vkburlist" toString="s" line=2
  if not Q[ok]
     Set V[vkbur] "&V[save_vkbur]"
   Set V[repeat] "X"
   endif
endif

// vkgrp now unique?
if V[save_vkgrp] and not V[vkgrp]
   CopyText fromText="vkgrplist" toString="s" line=2
  if not Q[ok]
     Set V[vkgrp] "&V[save_vkgrp]"
   Set V[repeat] "X"
  endif
endif

// repeat selection due to new unique values?
if V[repeat]
  goto repeat_selection
endif

Return

InputScript "clear_organizational_data.txt"

GuiXT

// clear input (organizational data)
Clear V[vkorg]
Clear V[vtweg]
Clear V[spart]
Clear V[vkbur]
Clear V[vkgrp]
 

include "filter_organizational_data.txt"

Components
InputAssistant