Text Search
The text search script gives you an alternatvie interface to performing a text search from within IDEA. The script allows you to select up to 10 different search terms (you can use the wildcards), it also allows you decide if the search is case sensitve or not and the type of search, i.e. is it an AND or OR type search. One thing the script does is it allows you to create a text file that contains your search terms and import the text file. So instead of reentering the data each time you can use notepad to create the text file with the terms you are looking for. For the text file to work it must follow the IDEA syntax that is listed in the Search help.
The text search has been updated (August 17, 2017), the code behind the dialogs has been rewritten and the import text files now use the open dialog.
The Idea “Text Search” script simplifies searching for text in character fields. An “Advanced Search” option allows for predefined search terms to be executed from the selected text file.
A maximum of 10 search terms may be entered. A text file may also be selected. The search term entry boxes may be used in conjunction with the “Advanced Search” option.
Search Procedure
The first step to conduct a text search is to select the file to be tested. Files from within the current IDEA working folder are presented as options. The second step is to select the fields that you wish to search within. The double right arrow button “>>” will select all the fields.
Enter up to 10 search terms in the search boxes in the sequence in which you wish to find the text. You may use the asterisk (*) and question mark (?) wild card characters in the search term boxes. The IDEAScript is hardcoded with the quotes for explicit multi word searches, for example, to search for “New York City”, only the words need to be entered and not the quotes as they are hardcoded.
The “Search Type” defaults to OR so that any matches to any of the search terms will be displayed. By selecting AND, matches to all of the search terms must be meet before any results are displayed.
By selecting the “Advanced Search” button, a text file of predefined search terms may be executed. The IDEAScript asks if the text file is located in the current IDEA working directory. If Yes is selected, then all text files in the working directory are offered for selection. If No is selected, then you must browse the selected drive for the folder where the text file resides.
Predefined terms in text files must follow the rules outlined by IDEA. The “Enter” key or “Carriage Return” character must not be used. Additional information including tips, features and syntax rules are detailed in the IDEA Search help area, as depicted below.
The search term area may be used in isolation or in combination with a text file accessed via the “Advanced Search” button. The advanced search feature can also be used in isolation.
Numerous predefined text files may be prepared and saved. For example, one file may be a required step in an audit plan while another file may contain search terms that are unique to a geographic area or industry. A library of search terms may be developed to be shared amongst staff.
After you click OK to run the IDEAScript, a new data file with the results is created using the default prefix of “TS”. This prefix may be changed prior to running the script. If a file already exists with the same name, the prefix is updated and a new file name is given.
Hi, the updated script works
Hi, the updated script works fine, no errors. But I can't get my mind around the code. I'am trying to get it into my code and understand how it work. But it seems that methodes i used in menu's/dialogs won't work the same as in the displayit with the case Action/ case controlid. I keep getting an error about an Expected array and sometimes IDEA crashes when a subscript is out of range. I use IDEA 10.3.0.1139 (X86 ).
Hi, I tried to implement your
Hi, I tried to implement your code of the listboxes in to my code, but now i get an error "expected array". I do not understand the error, mainly because i am quite new to this. This is the code:
Dim Listbox1$() AS string
Dim Listbox2$() AS string
Begin Dialog Dialog_Menu_1 300,100,271,221,"Zoektermen", .DisplayIt
OKButton 5,185,40,14, "OK", .OKButton1
CancelButton 215,185,40,13, "Exit", .CancelButton1
Text 5,4,73,12, "Voer de zoektermen in:", .Text0
Text 5,20,40,14, "Zoekterm 1:", .tekst1a
Text 5,40,40,14, "Zoekterm 2:", .tekst1b
Text 5,60,40,14, "Zoekterm 3:", .tekst1c
Text 5,80,40,14, "Zoekterm 4:", .tekst1d
Text 5,100,40,14, "Zoekterm 5:", .tekst1e
Text 135,20,40,14, "Zoekterm 6:", .tekst1f
Text 135,40,40,14, "Zoekterm 7:", .tekst1g
Text 135,60,40,14, "Zoekterm 8:", .tekst1h
Text 135,80,40,14, "Zoekterm 9:", .tekst1i
Text 135,100,40,14, "Zoekterm 10:", .tekst1j
TextBox 50,20,75,14, .tekst_1
TextBox 50,40,75,13, .tekst_2
TextBox 50,60,75,13, .tekst_3
TextBox 50,80,75,13, .tekst_4
TextBox 50,100,75,13, .tekst_5
TextBox 180,20,75,13, .tekst_6
TextBox 180,40,75,13, .tekst_7
TextBox 180,60,75,13, .tekst_8
TextBox 180,80,75,12, .tekst_9
TextBox 180,100,75,11, .tekst_10
Text 5,130,100,11, "Selecteer hier de database:", .tekst2a
PushButton 5,145,100,14, "Select File", .PushButton1
Text 5,165,100,12, "Text", .Text2a
Text 160,130,100,14, "Voer nieuwe database naam in:", .teskt2b
TextBox 160,145,100,20, .new_database_name
End Dialog
Begin Dialog Dialog_Menu_2 300,100,300,200,"Selecteer velden", .funSelectFields
OKButton 5,160,40,13, "OK", .OKButton1
CancelButton 245,160,40,14, "Back", .CancelButton1
ListBox 5,5,125,150, Listbox1$(), .ListBox1
ListBox 160,5,125,150, Listbox2$(), .ListBox2
PushButton 135,10,20,20, ">", .PushButton1
PushButton 135,40,20,20, "<", .PushButton2
PushButton 135,100,20,20, ">>", .PushButton3
PushButton 135,125,20,20, "<<", .PushButton4
PushButton 125,160,40,14, "Back", .PushButton5
End Dialog
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Option Explicit
Dim Exit_Script As Boolean
Dim Exit_Dialog_0 As Boolean
Dim Exit_Dialog_1 As Boolean
Dim Exit_Dialog_2 As Boolean
Dim Button_Menu_1 As Boolean
Dim woord1 As String
Dim woord2 As String
Dim woord3 As String
Dim woord4 As String
Dim woord5 As String
Dim woord6 As String
Dim woord7 As String
Dim woord8 As String
Dim woord9 As String
Dim woord10 As String
Dim databasename As String
Dim new_database_name As String
Dim working_directory As String
Dim charFields() As String
Dim filename As String
Dim tempListbox1() As String
Dim tempListBox2() As String
Dim tempListSelect1 As Integer
Dim tempListSelect2 As Integer
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub Main
working_directory = Client.WorkingDirectory
Do While Exit_Dialog_0 = False
Call menu1()
If Button_Menu_1 Then
Call menu2()
End If
Loop
If Exit_Script = False Then
Call Search()
Call RenameDatabase()
End If
End Sub
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function menu1()
Dim dlg As Dialog_Menu_1
Dim button As Integer
Dim filebar As Object
Dim source As database
Dim table As table
Dim fields As Integer
Dim field As field
Dim i, j, x As Integer
Dim searchTermNotEmpty As Boolean
Do
button = Dialog(dlg)
Select Case button
Case 0
Exit_Dialog_0 = TRUE
Exit_Dialog_1 = TRUE
Exit_Script = TRUE
Case -1
woord1 = dlg.tekst_1
woord2 = dlg.tekst_2
woord3 = dlg.tekst_3
woord4 = dlg.tekst_4
woord5 = dlg.tekst_5
woord6 = dlg.tekst_6
woord7 = dlg.tekst_7
woord8 = dlg.tekst_8
woord9 = dlg.tekst_9
woord10 = dlg.tekst_10
new_database_name = dlg.new_database_name
If validateMenu() Then Button_Menu_1 = TRUE
If validateMenu() Then Exit_Dialog_1 = TRUE
Case 1
Set filebar = CreateObject("ideaex.fileexplorer")
filebar.dialog
databasename = filebar.selectedfile
If databasename<> "" Then
Set source=client.opendatabase(databasename)
Set table=source.tabledef
fields=table.count
ReDim charFields(fields)
ReDim listbox1$(fields)
ReDim listbox2$(0)
j=0
For i=1 To fields
Set field=table.getfieldat(i)
If field.IsCharacter Then
listbox1$(j)=field.name
charFields(j) =field.name
j=j+1
End If
Next i
If j = 0 Then
MsgBox "Er zijn geen character velden aanwezig in deze database, selecteer een andere database"
End If
ReDim Preserve listbox1$(j - 1)
ReDim Preserve charFields(j - 1)
Call sortArray(listbox1$)
Call sortArray(charFields)
End If
End Select
Loop While Exit_Dialog_1 = FALSE
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function menu2()
Dim dlg As Dialog_Menu_2
Dim button As Integer
button = Dialog(dlg)
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function displayIt(ControlID$, Action%, SuppValue%)
Dim button As Integer
Dim chosenfile As Integer
Dim x As Integer
Dim i As Integer
Dim j As Integer
Dim k, l As Integer
Dim fields As Integer
Dim field As field
Dim ListBox2Set As Boolean
x = UBound(charFields)
Select Case action
Case 1
ReDim tempListbox1(0)
ReDim tempListbox2(0)
l = 0
k = 0
Select Case button
Case 2
Select Case ControlId$
Case "OKButton1"
If validateMenu2() Then Exit_Dialog_0 = TRUE
If validateMenu2() Then Exit_Dialog_2 = TRUE
Case "CancelButton1"
Exit_Dialog_2 = TRUE
Exit_Dialog_0 = TRUE
Exit_Script = TRUE
Case "PushButton1" 'Button >
ReDim Preserve tempListbox2(UBound(tempListbox2) + 1)
tempListbox2(UBound(tempListbox2)) = tempListbox1(tempListSelect1)
tempListbox1(tempListSelect1 ) = ""
Call sortArray(tempListbox1)
Call sortArray(tempListbox2)
Call removeBlanksFromArray(1)
Call removeBlanksFromArray(2)
DlgListBoxArray "ListBox2", tempListbox2()
DlgListBoxArray "ListBox1", tempListbox1()
tempListSelect1 = 0
Exit_Dialog_2 = FALSE
Case "PushButton3" 'Button >>
ReDim tempListbox2(UBound(fields))
ReDim tempListbox1(0)
For i = 0 To UBound(fields)
tempListbox2(i) = fields(i)
Next i
Call sortArray(tempListbox2)
Call removeBlanksFromArray(2)
DlgListBoxArray "ListBox2", tempListbox2()
DlgListBoxArray "ListBox1", tempListbox1()
Exit_Dialog_2 = FALSE
Case "PushButton2" 'Button <
ReDim Preserve tempListbox1(UBound(tempListbox1) + 1)
tempListbox1(UBound(tempListbox1)) = tempListbox2(tempListSelect2)
tempListbox2(tempListSelect2 ) = ""
Call sortArray(tempListbox1)
Call sortArray(tempListbox2)
Call removeBlanksFromArray(1)
Call removeBlanksFromArray(2)
DlgListBoxArray "ListBox2", tempListbox2()
DlgListBoxArray "ListBox1", tempListbox1()
tempListSelect2 = 0
Exit_Dialog_2 = FALSE
Case "PushButton4" 'Button <<
ReDim tempListbox1(UBound(fields))
ReDim tempListbox2(0)
For i = 0 To UBound(fields)
tempListbox1(i) = fields(i)
Next i
Call sortArray(tempListbox1)
Call removeBlanksFromArray(1)
DlgListBoxArray "ListBox2", tempListbox2()
DlgListBoxArray "ListBox1", tempListbox1()
Exit_Dialog_2 = FALSE
Case 5
Exit_Dialog_2 = TRUE
Exit_Dialog_0 = False
Case "ListBox1"
tempListSelect1 = SuppValue%
Case "ListBox2"
tempListSelect2 = SuppValue%
End Select
End Select
If tempListbox1(0) = "" Then
DlgEnable "PushButton1", 0
DlgEnable "PushButton3", 0
DlgEnable "PushButton2", 1
DlgEnable "PushButton4", 1
ElseIf tempListbox2(0) ="" Then
DlgEnable "PushButton1", 1
DlgEnable "PushButton3", 1
DlgEnable "PushButton2", 0
DlgEnable "PushButton4", 0
Else
DlgEnable "PushButton1", 1
DlgEnable "PushButton3", 1
DlgEnable "PushButton2", 1
DlgEnable "PushButton4", 1
End If
If databasename = "" Then
DlgText "Text2a", "Geen database geselecteerd"
Else
DlgText "Text2a", "Database: " & getDbname(databasename, 0)
End If
End Select
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function validateMenu() As Boolean
validateMenu = TRUE
If woord1 = "" Then
MsgBox "Voer tenminste één zoekterm in", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord1, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 1: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord2, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 2: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord3, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 3: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord4, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 4: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord5, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 5: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord6, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 6: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord7, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 7: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord8, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 8: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord9, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 9: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(woord10, "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in zoekterm 10: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If databasename = "" Then
MsgBox "Selecteer database", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If new_database_name = "" Then
MsgBox "Voer een nieuwe database naam in", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
If checkForSpecialChar(new_database_name , "\/:*?""<>[]|") Then
MsgBox "Gebruik het volgende niet in de nieuwe database naam: - \/:*?""<>[]|", MB_ICONEXCLAMATION, "Error"
validateMenu = FALSE
End If
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function validateMenu2() As Boolean
validateMenu2= TRUE
If UBound(listbox2$) = 0 And listbox2$(0) = "" Then
MsgBox "Selecteer ten minste één veld"
End If
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function checkForSpecialChar(temp_string As String, temp_list As String) As Boolean
Dim strLen As Integer
Dim tempChar As String
Dim i As Integer
Dim pos As Integer
checkForSpecialChar = FALSE
strlen = Len(temp_list)
For i = 1 To strLen
tempChar = Mid(temp_list, i, 1)
pos = InStr(1, temp_string, tempChar)
If pos > 0 Then
checkForSpecialChar = TRUE
End If
Next i
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function getDbName(temp_filename As String, temp_type As Boolean)
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
temp_length = Len(temp_filename)
temp_difference = temp_length - temp_len_wd + 1
getDbName = 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
getDbName = tempfilename
End If
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function Search
Dim db As database
Dim task As task
Dim dbName As String
Dim i As Integer
Set db = Client.OpenDatabase (databasename)
Set task = db.Search
For i = 0 To UBound(listbox2$)
task.AddFieldToInc listbox2$(i)
Next i
task.RecordFilesPrefix = "A"
If woord1 = "" Then woord1 = "~"
If woord2 = "" Then woord2 = "~"
If woord3 = "" Then woord3 = "~"
If woord4 = "" Then woord4 = "~"
If woord5 = "" Then woord5 = "~"
If woord6 = "" Then woord6 = "~"
If woord7 = "" Then woord7 = "~"
If woord8 = "" Then woord8 = "~"
If woord9 = "" Then woord9 = "~"
If woord10 = "" Then woord10 = "~"
task.PerformTask "*" & woord1 & "*" & " OR " & "*" & woord2 & "*" & " OR " & "*" & woord3 & "*" & " OR " & "*" & woord4 & "*" & " OR " & "*" & woord5 & "*" & " OR " & "*" & woord6 & "*" & " OR " & "*" & woord7 & "*"& " OR " & "*" & woord8 & "*" & " OR " & "*" & woord9 & "*" & " OR " & "*" & woord10 & "*", 0, 0, 1
dbname = ""
'Client.OpenDatabase(dbName)
Set task = Nothing
Set db = Nothing
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function RenameDatabase
Dim ProjectManagement As Task
Dim temp_database_name As String
temp_database_name = "A-" & getDbname(databasename, 0)
Set ProjectManagement = client.ProjectManagement
ProjectManagement.RenameDatabase temp_database_name , new_database_name
Set ProjectManagement = Nothing
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function sortArray(MyArray() As String)
Dim lLoop, lLoop2 As Integer
Dim str1, str2 As String
For lLoop = 0 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
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function removeBlanksFromArray()
Dim tempArray() As String
Dim i, ILoop As Integer
ReDim tempArray(0)
For ILoop = 0 To UBound(listbox1$)
If listbox1$(ILoop) <> "" Then
tempArray(UBound(tempArray)) = listbox1$(ILoop)
ReDim preserve tempArray(UBound(tempArray) + 1)
End If
Next ILoop
i = UBound(tempArray)
Erase listbox1$
ReDim listbox1$(i)
For ILoop = 0 To UBound(tempArray)
listbox1$(ILoop) = tempArray(ILoop)
Next ILoop
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function removeBlanksFromArray2()
Dim tempArray() As String
Dim i, ILoop As Integer
ReDim tempArray(0)
For ILoop = 0 To UBound(listbox2$)
If listbox2$(ILoop) <> "" Then
tempArray(UBound(tempArray)) = listbox2$(ILoop)
ReDim preserve tempArray(UBound(tempArray) + 1)
End If
Next ILoop
i = UBound(tempArray)
Erase listbox2$
ReDim listbox2$(i)
For ILoop = 0 To UBound(tempArray)
listbox2$(ILoop) = tempArray(ILoop)
Next ILoop
End Function
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hi Robert,
Hi Robert,
The first problem I see is that you have the code for the second dialog in the function for the first dialog (DisplayIt), the second dialog is called funSelectFields so you need that code in that function.
Also the array problem is you never populate the fields array with the name of all the fields to list in the listbox.
Error on COMPILE
Hello all,
New here and trying this code. I'm a complete VBA novice, and am getting an "Error on line 89 - compile error" and "Syntax error".
I'm using IDEA v11, and on reading about VBA errors, my understanding is that there might be an undeclared variable somewhere?
Is version 2 the lastest version of this code?
Many thanks.
ARC
Text Search
Hi, i used some parts of the code and got it working, but discovered a error. I tried it on your Text Search.iss and got the same result error.
I select a db file, put in one search term and then select the fields. I select two fields with the > button and return one field with the < button to exclude form the search. I press OK and again press OK in the main menu. Then i get an error on line 295:Line 294: For i = 0 To UBound(listbox2$)Line 295: task.AddFieldToInc listbox2$(i)Line 296: Next iDo you know how to fix this?