Skip to main content

DropList issue

Hello to all,

New user here, and thoroughly enjoy this site! It has helped me learn this software better than anywhere else I have found.

I am currently having an issue with a script that I am developing. I have added a DropListBox and at the moment, it is for fiscal quarters, so even though I am selecting 'Q4' from the array, the box is still displaying 'Q1'. Any help would be greatly appreciated! Here is a slimmed down version of my current script:

Option Explicit
Dim filename1 As String
Dim filename2 As String
Dim FYQTR As String
Dim VER As String
Dim working_directory As String
Dim exitScript As Boolean

Sub Main
working_directory = Client.WorkingDirectory
Call QTRtesting()
If Not exitScript Then
Call Testing
End If
client.refreshFileExplorer
End Sub

Function QTRtesting()
Dim dlg As dlgQuarterly
Dim button As Integer
Dim filebar1 As Object
Dim filebar2 As Object
Dim exitDialog As Boolean
Dim source As Object
Dim table As Object
Dim fields As Integer
Dim i, j As Integer
Dim field As Object
Dim FYQuarters(4) As String
Dim VER(2) As String

Do

button = Dialog(dlg)

Select Case button
Case -1 'ok button
If dlg.DropListBox1 > -1 Then
VER = ListBox1$(VER())
Else
VER = ""
End If

If dlg.DropListBox2 > -1 Then
FYQTR = ListBox2$(FYQuarters())
Else
FYQTR = ""
End If

If validateQTRtesting() Then exitDialog = TRUE
Case 0 ' cancel button
exitDialog = TRUE
exitScript = TRUE
Case 1 'filename1 select button
Set filebar1 = CreateObject("ideaex.fileexplorer")
filebar1.displaydialog
filename1 = filebar1.selectedfile
Case 2 'filename2 select button
Set filebar2 = CreateObject("ideaex.fileexplorer")
filebar2.displaydialog
filename2 = filebar2.selectedfile

End Select
Loop While exitDialog = FALSE

Set source = Nothing
Set table = Nothing
Set field = Nothing
End Function

Function DisplayIt(ControlID$, Action%, SuppValue%)

Dim dlg As dlgQuarterly
Dim VER(2) As String
VER(1) = "V.1"
VER(2) = "V.2"
Dim FYQuarters(4) As String
FYQuarters(1) = "Qtr 1"
FYQuarters(2) = "Qtr 2"
FYQuarters(3) = "Qtr 3"
FYQuarters(4) = "Qtr 4"

DlgListBoxArray "DropListBox1", VER()

DlgListBoxArray "DropListBox2", FYQuarters()

If filename1 = "" Then
DlgText "txtFilename1", "No file selected"
Else
DlgText "txtFilename1", "File: " & getFileName(filename1, 0)
End If

If filename2 = "" Then
DlgText "txtFilename2", "No file selected"
Else
DlgText "txtFilename2", "File: " & getFileName(filename2, 0)
End If

End Function

Brian Element Tue, 08/09/2016 - 11:14

Hi Kyle and welcome to the site, glad you are enjoying it and learning stuff.

You will need to make a few changes to your script.  Right now you have your arrays that hold the drop down information named the same as your variables that hold your selection.  So I would change them after the Option Explicit:

Dim FYQTR as String to Dim sFYQTR as string

Dim VER as String to Dim sVer as String

Just to keep the variables separate.

I would also move your arrays from the functions to a global array, the reason being is that you need them in both the QTRTesting and the DisplayIt functions.

So under Option Explicit add:

Dim FYQuarters(4) As String
Dim VER(2) As String

And remove them from the functions.

The next change is obtaining the contents of the dialog as you are trying to get the info from the ListBox1$ and ListBox2$ but you are not using those arrays as you have created your own to hold the information.  So change the following

VER = ListBox1$(VER()) to sVER = VER(dlg.DropListBox1) and

FYQTR = ListBox2$(FYQuarters()) to sFYQTR = FYQuarters(dlg.DropListBox2)

I think with those changes that will get the script working.

Let me know how it goes.

Brian

kkeller Tue, 08/09/2016 - 11:40

Thanks Brian! I have made the suggested changes, and everything runs fine, but I am having the same issue with the droplistbox not displaying the proper selection. Also, when I run it, file naming that should include sVer and sFYQTR show blank " ". Updated version:

Option Explicit
Dim filename1 As String
Dim filename2 As String
Dim sFYQTR As String
Dim sVer As String
Dim FYQuarters(4) As String
Dim VER(2) As String
Dim working_directory As String
Dim exitScript As Boolean

Sub Main
working_directory = Client.WorkingDirectory
Call QTRtesting()
If Not exitScript Then
Call Testing
End If
client.refreshFileExplorer
End Sub

Function QTRtesting()
Dim dlg As dlgQuarterly
Dim button As Integer
Dim filebar1 As Object
Dim filebar2 As Object
Dim exitDialog As Boolean
Dim source As Object
Dim table As Object
Dim fields As Integer
Dim i, j As Integer
Dim field As Object

Do

button = Dialog(dlg)

Select Case button
Case -1 'ok button
If dlg.DropListBox1 > -1 Then
sVer = VER(dlg.DropListBox1)
Else
sVer = ""
End If

If dlg.DropListBox2 > -1 Then
sFYQTR = FYQuarters(dlg.DropListBox2)
Else
sFYQTR = ""
End If

If validateQTRtesting() Then exitDialog = TRUE
Case 0 ' cancel button
exitDialog = TRUE
exitScript = TRUE
Case 1 'filename1 select button
Set filebar1 = CreateObject("ideaex.fileexplorer")
filebar1.displaydialog
filename1 = filebar1.selectedfile
Case 2 'filename2 select button
Set filebar2 = CreateObject("ideaex.fileexplorer")
filebar2.displaydialog
filename2 = filebar2.selectedfile

End Select
Loop While exitDialog = FALSE

Set source = Nothing
Set table = Nothing
Set field = Nothing
End Function

Function validateQTRtesting() As Boolean
validateQTRtesting = TRUE
If filename1 = "" Then
MsgBox "Please select a file", MB_ICONEXCLAMATION, "Error"
validateQTRtesting = FALSE
End If
If filename2 = "" Then
MsgBox "Please select a file", MB_ICONEXCLAMATION, "Error"
validateQTRtesting = FALSE
End If
End Function

Function DisplayIt(ControlID$, Action%, SuppValue%)

Dim dlg As dlgQuarterly
Dim VER(2) As String
VER(1) = "V.1"
VER(2) = "V.2"
Dim FYQuarters(4) As String
FYQuarters(1) = "Qtr 1"
FYQuarters(2) = "Qtr 2"
FYQuarters(3) = "Qtr 3"
FYQuarters(4) = "Qtr 4"

DlgListBoxArray "DropListBox1", VER()

DlgListBoxArray "DropListBox2", FYQuarters()

If filename1 = "" Then
DlgText "txtFilename1", "No file selected"
Else
DlgText "txtFilename1", "File: " & getFileName(filename1, 0)
End If

If filename2 = "" Then
DlgText "txtFilename2", "No file selected"
Else
DlgText "txtFilename2", "File: " & getFileName(filename2, 0)
End If

End Function

I also tried changing:
If dlg.DropListBox2 > -1 Then
sFYQTR = FYQuarters(dlg.DropListBox2)
Else
sFYQTR = ""
End If
to...
sFYQTR = FYQuarters(dlg.DropListBox2)
but no luck there either. Thanks again for all your help.
Kyle

kkeller Thu, 04/27/2017 - 10:34

Hi Brian, I am in the process of updating a few scripts and was looking to add a dropdown, that i want populated from the document i have selected in my script. I have attempted this, but have had not success to date. I was hoping that you would be able to point me in the right direction? Thanks for all the great help.
Kyle

Brian Element Fri, 04/28/2017 - 11:25

Hi Kyle, here is an example of a script with one drop down.  The script will see if you have a file already open, if so it will populate the drop-down with the fields from that file.  You can also select another file.

Let me know if you have any questions or need some clarrification on what I was doing.  I have also attached the file.

Thanks

Brian


Dim listbox1$() AS STRING

Begin Dialog NewDialog 50,49,270,150,"Drop Down Demo", .DisplayIt
  Text 9,6,15,14, "File:", .Text1
  Text 34,5,179,15, "Text", .Text2
  PushButton 225,5,10,10, "...", .PushButton1
  GroupBox 30,1,188,23, .GroupBox1
  OKButton 20,67,40,14, "OK", .OKButton1
  CancelButton 77,67,40,14, "Cancel", .CancelButton1
  DropListBox 13,32,202,10, listbox1$(), .DropListBox1
End Dialog
Option Explicit
Dim sFilename As String 'to hold the filename
Dim sFields() As String 'to hold the field names
Dim working_directory  As String
Dim bExitScript As Boolean 'flag in case user hits cancel, the script will be exited

Sub Main
	working_directory = Client.WorkingDirectory()
	Call menu()

End Sub

Function menu()
	Dim button As Integer
	Dim db As database
	Dim dlg As NewDialog
	On Error Resume Next
	Set db = Client.CurrentDatabase()
	If err.number = 0 Then
		sFilename = db.name
	Else
		sFilename = ""
	End If
	
	Set db = Nothing
	button = Dialog(dlg)
End Function

Function displayIt(ControlID$, Action%, SuppValue%)
	Dim bExitFun As Boolean

	Select Case Action%
		Case 1
			If sFilename <> "" Then 'if the file is open then get the fields
				Call getFields() 'get the fields
				DlgListBoxArray "DropListBox1", sFields() 'add the fields to the drop down
			End If
		Case 2
		
			Select Case ControlId$
				Case "PushButton1"
					sFilename = getFile()
					If sFilename <> "" Then 'if the file is open then get the fields
						Call getFields() 'get the fields
						DlgListBoxArray "DropListBox1", sFields() 'add the fields to the drop down
					End If
					
				Case "OKButton1"
					bExitFun = True
				Case "CancelButton1"
					bExitFun = True
			End Select
	End Select
	
	If sFilename = "" Then
		DlgText "Text2", "Please select a file"
	Else
		DlgText "Text2", getFileName(sFilename, 0)
	End If
	
	If bExitFun Then
		displayIt = 0
	Else 
		displayIt = 1
	End If

End Function

'gets the fields from the selected database
Function getFields()
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	Dim count As Integer
	
	Set db = Client.OpenDatabase(sFilename)
		Set table = db.TableDef
			count = table.Count
			ReDim sFields(count)
			For i = 1 To count
				Set field = table.GetFieldAt(i)
					sFields(i) = field.name
				Set field = Nothing
			Next i
		Set table = Nothing
	Set db = Nothing
	Call sortArray(sFields())
End Function

'****************************************************************************************************
'	Name:		sortArray
'	Description:	Routine to sort an array
'	Last Update:	
'	Accepts:		A one dimensional array
'	Returns:		Same array sorted 
'****************************************************************************************************
Private Function sortArray(MyArray() As String)
	Dim lLoop, lLoop2 As Integer
	Dim str1, str2 As String

	For lLoop = 1 To UBound(MyArray)
	
		For lLoop2 = lLoop To UBound(MyArray)
		
			If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then
			
				str1 = MyArray(lLoop)
				str2 = MyArray(lLoop2)
				MyArray(lLoop) = str2
				MyArray(lLoop2) = str1
			
			End If
		
		Next lLoop2
	
	Next lLoop
	MyArray(0) = "Select one"
End Function


Function getFileName(temp_filename As String, temp_type As Boolean) '1 if get the name with any folder info, 0 if only the name
	Dim temp_length As Integer
	Dim temp_len_wd As Integer
	Dim temp_difference As Integer
	Dim temp_char As String
	Dim tempfilename As String
	
	If temp_type Then
		temp_len_wd  = Len(working_directory )  + 1'get the lenght of the working directory
		temp_length = Len(temp_filename) 'get the lenght of the file along with the working directory
		temp_difference = temp_length - temp_len_wd  + 1'get the lenght of just the filename
		getFileName = Mid(temp_filename, temp_len_wd, temp_difference)	
	Else
		temp_length  = Len(temp_filename )
		Do 
			temp_char = Mid(temp_filename, temp_length , 1)
			temp_length = temp_length  - 1 
			If temp_char <> "\" Then
				tempfilename = temp_char & tempfilename
			End If
		Loop Until temp_char = "\" Or temp_length = 0
		getFileName = tempfilename
	End If
End Function

Function getFile()
               Dim filebar As Object
               Set filebar =CreateObject ("ideaex.FileExplorer")
               ' Display the File Explorer
               filebar.DisplayDialog
               ' Set Variable to store Select File Name
               getFile = filebar.SelectedFile
End Function

kkeller Thu, 05/04/2017 - 21:41

Hi Brian, this was a ton of help! I have successfully gotten my dropdowns to work properly within my menu window, I believe, but I am not sure, as my script is not yet fully functioning properly. I believe I have an issue with either: 1) Attaching each dropdown to a constant, or 2) my use of the constant values within the equations in my calculation area of the script. I have attached the .iss file. Any help with my errors would be greatly appreciated, I've given it a number of attempts but I am still learning. Thanks again!

Brian Element Fri, 05/05/2017 - 07:39

In reply to by kkeller

Hi Kyle,

Glad that what I posted was helpful.  I looked at your script and made a few changes / additions to it.  I added some code that it now picks-up if a file is already open.  I also fixed your problem of items not being returned when you select the drop-down, basically you needed to be using the sFields() variable.  I commented all the lines that I changed / added so you can see what I did.

Good luck with your project.

Brian