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:
GuiXT
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
VB.NET
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