![]() |
![]() |
|
![]() |
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:
The library search routine in greater detail The requirement of a search routine allowing multiple search terms meant that two arrays were 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:
The routine involved in collecting all the hits in an array would involve the following steps:
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 endMethod2. 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) endmethod4. 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. ![]() |
![]() |
|