Obtaining data from a database to use as a selection criteria

Hi Herbert,

I have put together this script to show you how to do it.  It is not something that is easily done within IDEA as you first have to select the field, then when the field is selected summarize it and then populate an array with the summary information and attach that to the dialog.  Definitely some advance scripting.  So here is an example, I have also attached it for you to download and play with.

Good luck.

'****************************************************************************************************
'* Script:	Select Account Demo.iss
'* Author:	Brian Element - brian.element@ideascripting.com
'* Date:		Nov 5, 2017
'* Purpose:	Demo to show how to populate a field with its contents through a drop-down
'* This script is provided without any warranty or guarantee.  Anybody using this script
'****************************************************************************************************

Option Explicit

Dim sFilename As String
Dim sField As String
Dim sAccount As String
Dim bExitScript As Boolean
Dim db As database
Dim table As table
Dim task As task
Dim field As field
Dim rs As RecordSet
Dim rec As Record

Sub Main
	Call getMenu()
	MsgBox "File: " & sFilename & " was selected"
	MsgBox "Field: " & sField & " was selected"
	MsgBox "Account: " & sAccount & " was selected"
End Sub

Function getMenu()
	Dim dlg As NewDialog
	Dim button As Integer
	'check to see if a database is open, if it is populate the sFilename
	'have to sue the on error resume next as if there are no databases open IDEA with throw an error.
	On Error Resume Next
	sFilename = Client.CurrentDatabase.Name
	button = Dialog(dlg)
End Function

Function DisplayIt(ControlID$, Action%, SuppValue%)
	Dim bExitMenu As Boolean
	
	Select Case Action%
		Case 1
			If sFilename <> "" Then 'populate the drop down
				Call getFields() 
				DlgListBoxArray "DropListBox1", listbox1$()
			End If
		Case 2
			Select Case ControlID$
				Case "PushButton1" 'get the file
					Call getFile()
					If sFilename <> "" Then 'populate the drop down
						Call getFields() 
						DlgListBoxArray "DropListBox1", listbox1$()
					End If
				Case "DropListBox1"
					sField = listbox1$(SuppValue%)
					If SuppValue% <> 0 Then 'not the first value
						Call getFieldContents() 'get the contets of the field and populate the DropListBox2
						DlgListBoxArray "DropListBox2", listbox2$()
					End If
				Case "CanelButton1"
					bExitMenu = True
					bExitScript = True
				Case "OKButton1"
					sField = listbox1$(NewDialog.DropListBox1)
					sAccount = listbox2$(NewDialog.DropListBox2)
					bExitMenu = True
			End Select
		
	End Select

	If sFilename = "" Then
		DlgText "Text2", "Please Select a file"
	Else
		DlgText "Text2", getFileName()
	End If
	
	If bExitMenu Then
		DisplayIt = 0 'exit menu
	Else
		DisplayIt = 1 'keep it open
	End If
End Function

Function getFieldContents() 'to get the field contents first summarize the file
	Dim dbName As String
	Dim sFieldType As String
	Set db = Client.OpenDatabase(sFilename)
		Set task = db.Summarization
			task.AddFieldToSummarize sField
			dbName = client.UniqueFilename("Summarization")
			task.OutputDBName = dbName
			task.CreatePercentField = FALSE
			task.PerformTask
		Set task = Nothing
	Set db = Nothing
	
	Dim i As Long
	'extract the items in the summary and place them in an array
	sFieldType = getFieldType() 'get the field type, i.e. Character, Numeric, Date, Time
	Set db = Client.OpenDatabase(dbName)
		ReDim listbox2$(db.Count- 1)
		Set rs = db.RecordSet
			rs.ToFirst
			For i = 1 To db.count
				rs.Next
				Set rec = rs.ActiveRecord
				If sFieldType = "C" Then
					listbox2$(i - 1) = rec.GetCharValue(sField)
				ElseIf sFieldType = "N" Then
					listbox2$(i - 1) = rec.GetNumValue(sField)
				ElseIf sFieldType = "D" Then
					listbox2$(i - 1) = rec.GetDateValue(sField)
				ElseIf sFieldType = "T" Then
					listbox2$(i - 1) = rec.GetTimeValue(sField)
				End If 
			Next i
		Set rs = Nothing
		db.close
	Set db = Nothing
	Client.DeleteDatabase dbName
End Function

Function getFieldType() As String
	Set db = Client.OpenDatabase(sFilename)
		Set table = db.TableDef
			Set field = table.GetField(sField)
				If field.IsCharacter Then
					getFieldType = "C"
				ElseIf field.IsNumeric Then
					getFieldType = "N"
				ElseIf field.IsDate Then
					getFieldType = "D"
				ElseIf field.IsTime Then
					getFieldType = "T"
				Else
					getFieldType = "U" 'unkown
				End If
			Set field = Nothing
		Set table = Nothing
	Set db = Nothing
End Function

Function getFile()
	Dim obj As Object
	Set obj = Client.CommonDialogs
		sFilename = obj.FileExplorer()
	Set obj = Nothing
End Function

Function getFileName() As String 'returns only the filename

	getFileName = iSplit(sFilename, "", "\", 1, 1)
End Function

Function getFields()
	Dim i As Integer
	Set db = Client.OpenDatabase(sFilename)
		Set table = db.TableDef
			ReDim listbox1$(table.Count) 'subtract 1 as array is 0 base will fields start at 1
			listbox1$(0) = "Select a Field"
			For i = 1 To table.Count
				Set field = table.GetFieldAt(i)
				listbox1$(i) = field.name	
			Next i
		Set table = Nothing
	Set db = Nothing
End Function

Dear Brain,
 
Thank you for your fast and complete reponse! This certainly looks a bit more advanced than i thought it would be. At the end of this week i will have some more time, than i will dive into it.