Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Paradox® Searching normalised tables (overcoming the problem of having to search for IDs)
© 2002 Luuk Houwen

Introduction

Having recently largely normalised my database I was confronted with the problem of having to search for ID-numbers rather than the proper contents of certain fields. In my bibliographical tables, for example, instead of being able to search for an author’s name, the Paradox built-in search routine would now only let me search for an author-ID number. I decided to write a new search routine myself which I would like to present here briefly. Of course I would be very happy to receive comments and criticism.

Since I needed a search routine for all my forms and often for as many as 4 or 5 different fields in each form, the routine had to be placed in a library. I also decided I wanted a routine that would allow me to go forwards as well as backwards and would allow pattern matching. Finally, I wanted a routine that allowed searches for multiple values. For example, a user searching the bibliographical database might want to see the works written by all people called 'Johnson'.


Outline of order of events

The actual order of events is the following:
  1. focussing on a form with searchable fields sends those variables that are constant for all searches in that form to the library: (code):
    • the name of the master table
    • the table name of the field containing the recordID
    • the form name of the field containing the record_ID (if different from the table name, which in my case it is)
    • the actual name of the form
    • the displayed name of the form (to cater for open and attach operations).
  2. hitting Ctrl-Z in the field to be searched (code):
    • sends the name of the underlying table to the library (this is the table from which the various search terms are to be collected)
    • sends the field name to be searched of the underlying table to the library
    • sends the name of the recordID field of the underlying table to the library
    • opens the search form
  3. the search field of the search form is filled in and the user presses the search button (code)
    • the main library search routine is started; this is accomplished with a switch (v = 1) to distinguish it from the code that allows moving forwards and backwards through the hits and which is part of the same library search routine (one could consider separating these)
  4. the main library search routine runs its course (code)
    • by means of the forward (v = 3) and backward (v = 2) buttons on the search form the user can move through the hits

The library search routine in greater detail

The requirement of a search routine allowing multiple search terms meant that two arrays were called for:
  • array 1: collects the IDs of all the search terms
  • array 2: collects all the results of the actual search
Imagine a bibliographical database in which the table of works is linked to a table of authors. In order to find all works written by 'Johnson' the first search would compile an array based on the authors table of all the 'Johnson' entries by their authorIDs. This array would then serve as the starting point for a second search of the works table by authorID. The results of this search (by workID) would populate the second array. Since one would never know how large these arrays might become, they would have to be resizable arrays. Moreover, since moving both forward and backward in the second array should be possible, 3 actual search routines are called for.

Collecting the search terms for array 1

The routine involved in collecting the relevant search terms in an array would involve the following steps:
  • hitting the SEARCH button on the search form sends a switch (v = 1) to the library search routine on the basis of which the basic search routine is started
  • opening the underlying table to which the field to be searched is linked and collecting all matching search terms. If an author field is searched, for example, it would involve opening the AUTHORS table and searching for all authors with the same last name. If a locatePattern routine is used the user is not required to enter the full name.
  • copying the associated ID to a resizable array.
  • repeating this search until the search term is no longer found (using a WHILE...ENDWHILE routine). Using the authors example again, this part of the routine would copy the AuthorIDs of all authors with the same last name to an array.
Collecting the hits for array 2

The routine involved in collecting all the hits in an array would involve the following steps:
  • making sure the form from which the search was started is still open
  • opening the table to be searched and searching it on the basis of every search term collected in array 1 and placing all the hits in a new array (again using a WHILE...ENDWHILE routine)
Displaying the result

Once the search routine is finished the first hit is displayed in the form by locating it on the form: f.(strVar6).locate(strVar5, arTID[1]), where strVar6=MasterRecord_ID, strVar5=MasterRecordID and arTID[1]=first ID in hits array.

Moving through the array of 'hits'

A counter linked with a locate function attached to the forward and backward buttons on the search form (see below) allows the user to move forward and backward through the hits (code).


The Search Form

The search form depicted below is called up every time a user hits Ctrl-Z.




The code

1. Focussing on a form to be searched
;MASTERRECORD_ID is name of the RecordID field on the form and is to be
;distinguished from the MASTERRECORDID which is the name of the recordID
;in the underlying master table
method setFocus(var eventInfo Event)
if eventInfo.isPreFilter() then
  ;// This code executes for each object on the form
else
  lib.Max()
  strVar2     = "MASTERTABLE.DB"
  lib.PassVar2(strVar2)
  strVar5     = "MASTERRECORDID"
  lib.PassVar5(strVar5)
  strVar6     = "MASTERRECORD_ID"
  lib.PassVar6(strVar6)
  strVar7     = "FORM.FSL"
  lib.PassVar7(strVar7)
  strVar8     = "FORM NAME"
  lib.PassVar8(strVar8)
endIf
endMethod
2. Hitting Ctrl-Z
method keyPhysical(var eventInfo KeyEvent)
if eventinfo.iscontrolkeydown() and eventinfo.vChar() = "Z"  then
  DisableDefault
  strVar1     = "SOURCETABLE.DB"
  lib.PassVar1(strVar1)
  strVar3     = "SOURCETABLEFIELDNAME"
  lib.PassVar3(strVar3)
  strVar4     = "SOURCETABLERECORDID"
  lib.PassVar4(strVar4)
  lib.searchForm()
endIf
endMethod

3. The search button on the search form
;The forward and backward buttons send 3 and 2 respectively.
method pushButton(var eventInfo Event)
  v = 1
  lib.searchID(v)
endmethod
4. The main library search routine
method searchID(v smallInt)
ignoreCaseInLocate(Yes)
f1.attach("Search form")
if v = 1 then
  arSID.empty()
  arTID.empty()
  Counter = 1
  aVar = f1.SearchFor
  ;
  ;avoid problems with the case of search strings by
  ;"ignoreCaseInLocate(Yes)". Attach the search form from which
  ;the search string is to be taken, and make sure the arrays are
  ;empty at the beginning of the search. The actual contents of
  ;the search form is assigned to an alpha-numeric variable
  ;(aVar). The v=1 functions as a switch between the initial
  ;search routine and later forward and backward movement
  ;
  if tc.open(strVar1) then
  ;
  ;strVar1=source table. Table names, field names, etc. are all
  ;variables, the actual values of which are passed to the
  ;library either when the form is opened (for those variables
  ;which apply to all fields to be searched on this form) or when
  ;the search routine is started (when they apply to that
  ;particular field)
  ;
    if tc.locatePattern(strVar3, aVar) then
    ;
    ;locates the search term in the relevant field (strVar3) of the source
    ;table
    ;
      f1.HitsGroup.Visible = true
      ;
      ;field on search form in which total number of hits
      ;are displayed when the search is successful
      ;
      iVar = tc.(strVar4)
      arSID.grow(1)
      arSID[arSID.size()] = iVar
      ;
      ;assign the sourcefieldID (strVar4) to integer
      ;variable iVar, add one record to the array and
      ;give it the value of iVar
      ;
      while tc.locateNextPattern(strVar3, aVar)
        iVar = tc.(strVar4)
        arSID.grow(1)
        arSID[arSID.size()] = iVar
      endWhile
      ;
      ;repeat this until no further search terms are
      ;found
      ;
      if not f.attach(strVar8) then
        f.open(strVar7, WinStyleDefault+WinStyleHidden)
      endIf
      ;
      ;check if the form from which the search was started is still
      ;attached, if not, open it. Then (below) open the master table
      ;and search it using the criteria gathered in arSID. Note that it
      ;is assumed that strVar4 has the same name in the source and
      ;target table
      ;
      if tc.open(strVar2) then
        tc.home()
        for x from 1 to arSID.size()
          if tc.locate(strVar4, arSID[Counter]) then
            iVar = tc.(strVar5)
            arTID.grow(1)
            arTID[arTID.size()] = iVar
            while tc.locateNext(strVar4, arSID[Counter])
              iVar = tc.(strVar5)
              arTID.grow(1)
              arTID[arTID.size()] = iVar
            endWhile
          else
            message("Could not locate pattern")
          endIf
          Counter = Counter + 1
        endFor
      else
        message("Could not open table")
      endIf
      f1.Hit = 1
      f1.Hits = arTID.size()
      f.(strVar6).locate(strVar5, arTID[1])
      f.bringToTop()
      Counter = 1
      ;
      ;set the "Hits" field on the search form to 1 of X; locate
      ;the first hit on the form and bring the form to the top
      ;
    else
      message("Could not locate pattern")
    endIf
  else
    message("Could not open table")
  endIf
  tc.close()
endIf


;
;v=3 moves one record forward in the array of hits
;
if v = 3 then
  if not f.attach(strVar8) then
    f.open(strVar7, WinStyleDefault+WinStyleHidden)
  endIf

  if Counter < arTID.size() then
    Counter = Counter + 1
    f.(strVar6).locate(strVar5, arTID[Counter])
    f1.Hit = Counter
    f.bringToTop()
  else
    message("This is the last hit")
  endIf
endIf

;
;v=2 moves one record back in the array of hits
;
if v = 2 then
  if not f.attach(strVar8) then
    f.open(strVar7, WinStyleDefault+WinStyleHidden)
  endIf

  if Counter > 1 then
    Counter = Counter - 1
    f.(strVar6).locate(strVar5, arTID[Counter])
    f1.Hit = Counter
    f.bringToTop()
  else
    message("This is the first hit")
  endIf
endIf

endMethod


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.