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.

Unlike the previous example we want to read the values from an excel file.

This example will make use of the following techniques:

- Read data from an excel file used as a database (OCDB driver)
- Join and select datasets in VB.NET
- Read and write GuiXT variables in VB.NET

Solution
Use callVB in order to build up dynamic value lists for other dropdowns.

Video:
 

GuiXT Script:

del G[Organizational Data]

 

// Build/Update dropdown lists

 

set V[filename_dropdowns] "D:\excel\organizational_data.xls"

callvb return = DynamicDropdownsNET.dropdownclass.initDropDowns  "&V[filename_dropdowns]"

 

if V[return]

  message  "E:&V[return]"  -statusline

  stop

endif

 

Box (1,1) (11,65) "Organizational Data"

 

// Input Fields

 

Text       (4,2)       "Sales Organization"       size=30 -label

pos         F[TVAKT-BEZEI]             (2,30)

pos         F[Order Type]    (2,2)

Text       (4,32)     "empty"

DropDownList     (4.1,25.2)             "va_codes_vkorg"            _

width=40 refer="V[va01_vkorg]" process="return.txt"

 

Text       (5,2)       "Distribution Channel"     size=30 -label

Text       (5,25)     "empty"

 

DropDownList     (5.1,25.2) "va_codes_vtweg" _

width=40 refer="V[va01_vtweg]" process="return.txt"

 

Text       (6,2)       "Division"             size=30 -label

Text       (6,25)     "empty"

DropDownList     (6.1,25.2)             "va_codes_spart" _

width=40 refer="V[va01_spart]" process="return.txt"

 

Text       (7,2)       "Sales Office"    size=30 -label

Text       (7,25)     "empty"

DropDownList     (7.1,25.2) "va_codes_vkbur" _

width=40              refer="V[va01_vkbur]" process="return.txt"

 

 

Text       (8,2)       "Sales Group"    size=30 -labelsdsd

Text       (8,25)     "empty"

DropDownList     (8.1,25.2) "va_codes_vkgrp" _

width=40 refer="V[va01_vkgrp]" process="return.txt"

 

Pushbutton         (10,51)   "@TS@Clear input" _

process="va01_reset_dropdowns.txt"

 

// Developer Info

Text       (12,1)     _

"(Runtime to build dropdowns: &V[build_dropdowns_runtime] ms)"  _

-intensified  -fixedfont  -border

 

 

VB.NET Coding: dropdownclass.vb

Imports guinet

Public Class dropdownclass

    Public myguixt As guixt
    Private myDtSet1 As DataSet

    Public Function initDropDowns(filename As String) As String

        Dim excelfile As String = filename
        Dim MyConnection As OleDb.OleDbConnection
        Dim errorMessage As String = ""
        Dim sw As New Stopwatch

        If myguixt Is Nothing Then
            myguixt = New guixt
        End If

        Try
            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Read data from Excel File (one time)
            If myDtSet1 Is Nothing Then

                Dim MyCommand As OleDb.OleDbDataAdapter
                Dim sqlcondition As String = ""
                myDtSet1 = New DataSet

                MyConnection = New OleDb.OleDbConnection _
                        ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
                         & excelfile _
                         & "';Extended Properties=Excel 8.0")

                Dim columNames As String() = {"salesorganization", _
                                              "distributionchannel", "division", _
                                              "salesgroup", "salesoffice"}

                ' Read the data into a datatable object within the dataset
                For Each column In columNames

                    sqlcondition = "select * from [" & column & "$] "

                    MyCommand = New OleDb.OleDbDataAdapter _
                        (sqlcondition, MyConnection)

                    MyCommand.Fill(myDtSet1, column)
                Next

                MyConnection.Close()

            End If

            ' Start stopwatch to measure the runtime
            sw.Start()

            ' Chosen values in Dropdown (user selection)
            Dim u_vkorg As String = myguixt.GetVariable("va01_vkorg").Trim
            Dim u_vtweg As String = myguixt.GetVariable("va01_vtweg").Trim
            Dim u_spart As String = myguixt.GetVariable("va01_spart").Trim
            Dim u_vkbur As String = myguixt.GetVariable("va01_vkbur").Trim
            Dim u_vkgrp As String = myguixt.GetVariable("va01_vkgrp").Trim


            Dim isInitital As Boolean = True
            If u_vkorg <> "" Or u_vtweg <> "" _
                Or u_spart <> "" Or u_vkbur <> "" _
                Or u_vkgrp <> "" Then

                isInitital = False

            End If


            ' Data from excel file in datatables
            Dim salesorganization As DataTable = myDtSet1.Tables("salesorganization")
            Dim distributionchannel As DataTable = myDtSet1.Tables("distributionchannel")
            Dim division As DataTable = myDtSet1.Tables("division")
            Dim salesgroup As DataTable = myDtSet1.Tables("salesgroup")
            Dim salesoffice As DataTable = myDtSet1.Tables("salesoffice")


            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Build a where condition for the select statement
            Dim cond As String = ""

            If u_vkorg.Trim <> "" Then
                ' User selected a value for sales office
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If

            If u_vtweg.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If
                cond &= "DChl = " & "'" & u_vtweg & "'"
            End If

            If u_spart.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If
                cond &= "Dv = " & "'" & u_spart & "'"

            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Excecute the select statement 
            Dim salesoffice_filtered As DataRow() = salesoffice.Select(cond)
            '''''''''''''''''''''''''
            '''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            cond = ""
            If u_vkorg.Trim <> "" Then
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If
            Dim distributionchannel_filtered As DataRow() = distributionchannel.Select(cond)

            cond = ""
            If u_vkorg.Trim <> "" Then
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If

            If u_vtweg.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If

                cond &= "DChl = " & "'" & u_vtweg & "'"
            End If

            Dim division_filtered As DataRow() = division.Select(cond)
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            cond = ""
            If u_vkbur.Trim <> "" Then
                cond = "Soff = " & "'" & u_vkbur & "'"
            End If

            If u_vkgrp.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If

                cond &= "SGrp = " & "'" & u_vkgrp & "'"
            End If


            Dim salesgroup_filtered As DataRow() = salesgroup.Select(cond)
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Read a joined table to build the new dropdown values

            Dim query_vkorg

            If isInitital Then

                query_vkorg = _
               From sorg In salesorganization
               Select New With _
             { _
               Key .vkorg = sorg.Field(Of String)("Sorg"), _
               .vkorg_t = sorg.Field(Of String)("Name")
             }

            Else

                query_vkorg = _
               From sorg In salesorganization
               Join soff In salesoffice_filtered
               On sorg.Field(Of String)("Sorg") Equals soff.Field(Of String)("Sorg")
               Join grp In salesgroup_filtered
               On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkorg = sorg.Field(Of String)("Sorg"), _
               .vkorg_t = sorg.Field(Of String)("Name")
             }


            End If

            ' Buld a dictionary for the dropdown values
            Dim d_vkorg As New Dictionary(Of String, String)
            For Each v In query_vkorg
                d_vkorg.Item(v.vkorg) = v.vkorg & " " & v.vkorg_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vkbur

            If isInitital Then
                query_vkbur = _
             From soff In salesoffice_filtered
             Select New With _
           { _
             Key .vkbur = soff.Field(Of String)("Soff"), _
             .vkbur_t = soff.Field(Of String)("Description")
           }
            Else
                query_vkbur = _
               From sorg In salesorganization
               Join soff In salesoffice_filtered
               On sorg.Field(Of String)("Sorg") Equals soff.Field(Of String)("Sorg")
               Join grp In salesgroup_filtered
               On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkbur = soff.Field(Of String)("Soff"), _
               .vkbur_t = soff.Field(Of String)("Description")
             }
            End If


            Dim d_vkbur As New Dictionary(Of String, String)
            For Each v In query_vkbur
                d_vkbur.Item(v.vkbur) = v.vkbur & " " & v.vkbur_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vtweg
            If isInitital Then
                query_vtweg = _
              From dist In distributionchannel_filtered
              Select New With _
            { _
              Key .vtweg = dist.Field(Of String)("DChl"), _
              .vtweg_t = dist.Field(Of String)("Name")
            }

            Else
                query_vtweg = _
              From dist In distributionchannel_filtered
              Join soff In salesoffice_filtered
              On dist.Field(Of String)("DChl") Equals soff.Field(Of String)("DChl")
              Join grp In salesgroup_filtered
              On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
              Select New With _
            { _
              Key .vtweg = dist.Field(Of String)("DChl"), _
              .vtweg_t = dist.Field(Of String)("Name")
            }

            End If

            Dim d_vtweg As New Dictionary(Of String, String)
            For Each v In query_vtweg
                d_vtweg.Item(v.vtweg) = v.vtweg & " " & v.vtweg_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_spart
            If isInitital Then
                query_spart = _
              From div In division_filtered
              Select New With _
            { _
              Key .spart = div.Field(Of String)("Dv"), _
              .spart_t = div.Field(Of String)("Name")
            }
            Else
                query_spart = _
              From div In division_filtered
              Join soff In salesoffice_filtered
              On div.Field(Of String)("Dv") Equals soff.Field(Of String)("Dv")
              Join grp In salesgroup_filtered
              On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
              Select New With _
            { _
              Key .spart = div.Field(Of String)("Dv"), _
              .spart_t = div.Field(Of String)("Name")
            }
            End If


            Dim d_spart As New Dictionary(Of String, String)
            For Each v In query_spart
                d_spart.Item(v.spart) = v.spart & " " & v.spart_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vkgrp
            If isInitital Then
                query_vkgrp = _
               From grp In salesgroup_filtered
               Select New With _
             { _
               Key .vkgrp = grp.Field(Of String)("SGrp"), _
               .vkgrp_t = grp.Field(Of String)("Description")
             }
            Else
                query_vkgrp = _
               From grp In salesgroup_filtered
               Join soff In salesoffice_filtered
               On grp.Field(Of String)("Soff") Equals soff.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkgrp = grp.Field(Of String)("SGrp"), _
               .vkgrp_t = grp.Field(Of String)("Description")
             }
            End If


            Dim d_vkgrp As New Dictionary(Of String, String)
            For Each v In query_vkgrp
                d_vkgrp.Item(v.vkgrp) = v.vkgrp & " " & v.vkgrp_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            'Create dropdowns -> fill GuiXT variables'
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkorgDropdown = ""
            If d_vkorg.Keys.Count > 1 Then
                vkorgDropdown = "= Please choose sales organization" & vbNewLine
            End If

            For Each v In d_vkorg.Keys
                vkorgDropdown &= v & "=" & d_vkorg.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkorg", vkorgDropdown)

            ' Select the value, of only one is available
            If d_vkorg.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkorg", d_vkorg.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vtwegDropdown = ""
            If d_vtweg.Keys.Count > 1 Then
                vtwegDropdown = "= Please choose distribution channel" & vbNewLine
            End If

            For Each v In d_vtweg.Keys
                vtwegDropdown &= v & "=" & d_vtweg.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vtweg", vtwegDropdown)

            If d_vtweg.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vtweg", d_vtweg.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim spartDropdown = ""
            If d_spart.Keys.Count > 1 Then
                spartDropdown = "= Please choose division" & vbNewLine
            End If

            For Each v In d_spart.Keys
                spartDropdown &= v & "=" & d_spart.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_spart", spartDropdown)

            If d_spart.Keys.Count = 1 Then
                myguixt.SetVariable("va01_spart", d_spart.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''



            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkburDropdown = ""
            If d_vkbur.Keys.Count > 1 Then
                vkburDropdown = "= Please choose sales office" & vbNewLine
            End If

            For Each v In d_vkbur.Keys
                vkburDropdown &= v & "=" & d_vkbur.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkbur", vkburDropdown)

            If d_vkbur.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkbur", d_vkbur.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkgrpDropdown = ""
            If d_vkgrp.Keys.Count > 1 Then
                vkgrpDropdown = "= Please choose sales group" & vbNewLine
            End If

            For Each v In d_vkgrp.Keys
                vkgrpDropdown &= v & "=" & d_vkgrp.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkgrp", vkgrpDropdown)

            If d_vkgrp.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkgrp", d_vkgrp.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ' Measured runtime
            sw.Stop()
            myguixt.SetVariable("build_dropdowns_runtime", sw.ElapsedMilliseconds.ToString)

        Catch ex As Exception
            Dim st As New StackTrace(True)
            st = New StackTrace(ex, True)
            errorMessage = ex.Message & " " & "Method: " _
                & System.Reflection.MethodInfo.GetCurrentMethod().Name() _
                & ", Line " & st.GetFrame(0).GetFileLineNumber().ToString

        End Try

        ' "" if no error occured
        Return errorMessage


    End Function


End Class

Download

You can download the needed files here: dynamic_dropdowns.zip

The .zip files contains:

- the VB.NET project (needs to be compiled so it can be called via callVB)
- Excel example file
- GuiXT scripts (used transaction: VA01)

Components
Controls