sept 2001 cylaufoxpro chapter 61 chapter 6 searching records in a table
TRANSCRIPT
Sept 2001 cylau Foxpro Chapter 6 1
Chapter 6
Searching Records in a Table
Sept 2001 cylau Foxpro Chapter 6 2
The LOCATE command
Sequentially searches the table for the first record that matches the specified logical expression.
You say “first”. But “First” in what order?
In the order of whatever “index” is in effect, and in the order of record number if no “index” is in effect.
Sept 2001 cylau Foxpro Chapter 6 3
LOCATE
USE patient Further Practice 5A
LOCATE FOR sex=“F”The record pointer now points to the first
recordstarting from the very beginning of the
table if there are female Patients, and points to (1 + no of the last record) if there are none.
Sept 2001 cylau Foxpro Chapter 6 4
FOUND() and EOF()
USE patientLOCATE FOR sex=“F”? FOUND() output .T. if the desired record is found, output .F. if not found
? EOF() output .F. if the desired record is found,output .T. if not found
Sept 2001 cylau Foxpro Chapter 6 5
CONTINUE
LOCATE get you the first record matching the criteria starting from the beginning of the table.
To find the second one matching the criteria, use the command CONTINUE.
Sept 2001 cylau Foxpro Chapter 6 6
LOCATE & CONTINUE
USE patientLOCATE FOR sex=“F” && recno()=1CONTINUE && recno()=2CONTINUE && recno()=5? FOUND()&& if the output is .T. then
the record pointer is pointing at the third female’s record.
Sept 2001 cylau Foxpro Chapter 6 7
LOCATE & CONTINUE
USE patientLOCATE FOR sex=“F”CONTINUECONTINUELOCATE FOR sex=“F” && recno()=1 again
? FOUND()&& if the output is .T. then the record pointer is pointing at the first female’s record.
Sept 2001 cylau Foxpro Chapter 6 8
Equal or Not equal
Consider the recordPatient_id name dob sex1000 Yin Leonard 12/05/85 MSure enough FoxPro will consider the
criteria sex=“M” is met for this record.But will they consider the criteria
patient_id=“100” being met?It depends.
Sept 2001 cylau Foxpro Chapter 6 9
SET EXACT ON
There are three different sets of rules FoxPro use to judge whether two strings are equal.
For each of the following 3 cases, the set of rules used are all different
SET EXACT ONLOCATE FOR patient_id=‘100’ && recno()=55
SET EXACT OFFLOCATE FOR patient_id=‘100’ && recno()=40
LOCATE FOR patient_id==‘100’ && recno()=55 Note: There are 54 records in the file patient.dbf
Sept 2001 cylau Foxpro Chapter 6 10
== (exactly match)
Insists an exact match:To be considered equal, the two
strings must be of exactly the same length; and
character to character match for the entire string.
Sept 2001 cylau Foxpro Chapter 6 11
SET EXACT ON/OFF
The comparison is character by character.The strings are considered match if a tie
prevail through the end of comparison.The comparison ends either if
a mismatch occurs, (‘1010’ <> ‘100’)
the string on the right is exhausted (SET EXACT OFF) (‘1000’ = ‘100’)
both strings are exhausted (SET EXACT ON)(‘1000’ <> ‘100’)
Sept 2001 cylau Foxpro Chapter 6 12
SORTING
Sorting a table according to the value of a certain expression not only make the table nicer to look at, but also make the searching of a particular record much easier.
However, this process is quite expensive.
Sept 2001 cylau Foxpro Chapter 6 13
SORT a Whole File
USE patient SORT TO newpatient ON sex /DC, dob /AA new .dbf file newpatient will be
created, occupying as many space as patient.dbf
/D : descending/A : ascending/C : case insensitvie
Sept 2001 cylau Foxpro Chapter 6 14
SORTSelected Columns or Rows
USE patient && selected columnsSORT TO newlist ON sex FIELDS name,
sex
USE patient && selected rowsSORT TO youngpatient ON sex;FOR dob>{12/31/1989}
Sept 2001 cylau Foxpro Chapter 6 15
Disadvantages of SORT
Data redundancyUpdate is not automaticSlowConsume disk space
for new file during processing
Complicated procedure to sort original file
Only fields can be sorted, not expressions
Sept 2001 cylau Foxpro Chapter 6 16
INDEX files
An Index file contains only the key expressions and information about where the corresponding record is.
Size is usually much smaller than a separate sorted file.
Sept 2001 cylau Foxpro Chapter 6 17
An Index File
Use patientIndex on name to nameContent of name.idx will look like:Name (key expression) record_numberAu Gertrude 8Au Natalie 37Au Vanessa 50Cheung Quentin 42…Note: size is smaller before only two columns are
stored
Sept 2001 cylau Foxpro Chapter 6 18
Two Types of Index FilesIndependent index file with extension idx
More than one index file may be used An IDX file need to be re-indexed after editing
Compound index file with extension cdx A cdx file should have the same name as the
table. A CDX file may contain more than one index
expression called TAGS. A CDX file will be activated automatically
Sept 2001 cylau Foxpro Chapter 6 19
INDEX ON
USE patientINDEX ON sex TO/TAG sex GO TOP && Sai GertrudeSKIPSKIPAt which record? && Tse Beatrice
Sept 2001 cylau Foxpro Chapter 6 20
A dbf file may have more than one index file
USE patientINDEX ON name TO/TAG nameGO TOP && Au Gertrude SET INDEX/ORDER TO SEX will arrange the data
according to sex instead of name
Can we have descending order of name using index?
Sept 2001 cylau Foxpro Chapter 6 21
SET INDEX / ORDER TO
USE PatientINDEX ON name TO/TAG nameGO TOP && Au GertrudeINDEX ON sex TO/TAG sexGO TOP && Sai GertrudeSET INDEX/ORDER TO nameGO TOP && Au Gertrude SET INDEX/ORDER TO sexGO TOP && Sai Gertrude
Sept 2001 cylau Foxpro Chapter 6 22
FIND and SKIP
USE patientINDEX ON name TAG nameFIND Yeung && Yeung Karen? FOUND() && output .T.SKIP && Yeung Nora
Sept 2001 cylau Foxpro Chapter 6 23
SEEK and SKIP
USE patientINDEX ON name TAG nameSEEK Yeung && variable not
foundSEEK “Yeung” && Yeung Karen? FOUND() && output .T.SKIP && Yeung Nora
Sept 2001 cylau Foxpro Chapter 6 24
UNIQUE
USE patientINDEX ON sex TAG sex2 UNIQUELIST && only one record per sex will be shown
SET ORDER TOLIST && now the index is no longer in effect, so
all records show up
SET ORDER TO sex2LIST && only one record per sex will be shown
again
Sept 2001 cylau Foxpro Chapter 6 25
Macro Substitution &
Macro substitution treats the contents of a variable as a character string literal
Apple=‘patient’ &Apple is the same as ‘patient’
USE Apple && VFP complaints that file doesn’t exist. There is no file with name Apple
USE &Apple && USE patient
List && the contents of patient are shown.
Sept 2001 cylau Foxpro Chapter 6 26
Macro Substitution & (2)USE patient index nameLaw=‘Shut’LOCATE for name=Law && Shut Daphne
LOCATE for name=&Law && variable Shut is not found
LOCATE for name=‘Law’ && Law Beatrice
LOCATE for name=‘&Law’ && Shut Daphne
Note: LOCATE accepts logical expression. Content of variable is used
Sept 2001 cylau Foxpro Chapter 6 27
Macro Substitution & (3)
USE patient index nameLaw=‘Shut’ FIND Law && Law Beatrice FIND &Law && Shut Daphne
FIND ‘Law’ && Law Beatrice FIND ‘&Law’ && Shut Daphne
Note: FIND accepts charstring; name of variable is used, so avoid using variable as
target
Sept 2001 cylau Foxpro Chapter 6 28
Macro Substitution & (4)
USE patient index nameLaw=‘Shut’ SEEK Law && Shut Daphne
SEEK &Law && variable Shut is not found
SEEK ‘Law’ && Law Beatrice
SEEK ‘&Law’ && Shut Daphne
Note: SEEK accepts expressioncontent of variable is used
Sept 2001 cylau Foxpro Chapter 6 29
CompareLOCATE, FIND and SEEK
See p.185
Sept 2001 cylau Foxpro Chapter 6 30
MACRO
A macro records the keystrokesThe key defined for macro should be
a function key<Enter> key is not recorded, have to
type {ENTER}Press Control-A in the Command
Window to launch the macro, select the micro name, then press <enter>