Chapter 6

1. Chapter 6 Searching Records in a Table 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. Foxpro Chapter 6

3. LOCATE USE patient Further Practice 5A LOCATE FOR sex=“F” The record pointer now points to the first record starting 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. Foxpro Chapter 6

4. FOUND() and EOF() USE patient LOCATE 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 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. Foxpro Chapter 6

6. LOCATE & CONTINUE USE patient LOCATE FOR sex=“F” && recno()=1 CONTINUE && recno()=2 CONTINUE && recno()=5 ? FOUND() && if the output is .T. then the record pointer is pointing at the third female’s record. Foxpro Chapter 6

7. LOCATE & CONTINUE USE patient LOCATE FOR sex=“F” CONTINUE CONTINUE LOCATE FOR sex=“F” && recno()=1 again ? FOUND() && if the output is .T. then the record pointer is pointing at the first female’s record. Foxpro Chapter 6

8. Equal or Not equal Consider the record Patient_id name dob sex 1000 Yin Leonard 12/05/85 M • Sure 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. 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 ON LOCATE FOR patient_id=‘100’ && recno()=55 • SET EXACT OFF LOCATE FOR patient_id=‘100’&& recno()=40 LOCATE FOR patient_id==‘100’&& recno()=55 Note: There are 54 records in the file patient.dbf 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. 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’) 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. Foxpro Chapter 6

13. SORT a Whole File USE patient SORT TO newpatient ON sex /DC, dob /A • A new .dbf file newpatient will be created, occupying as many space as patient.dbf /D : descending /A : ascending /C : case insensitvie Foxpro Chapter 6

14. SORTSelected Columns or Rows USE patient && selected columns SORT TO newlist ON sex FIELDS name, sex USE patient && selected rows SORT TO youngpatient ON sex; FOR dob>{12/31/1989} Foxpro Chapter 6

15. Disadvantages of SORT • Data redundancy • Update is not automatic • Slow • Consume disk space • for new file • during processing • Complicated procedure to sort original file • Only fields can be sorted, not expressions 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. Foxpro Chapter 6

17. An Index File Use patient Index on name to name Content of name.idx will look like: Name (key expression) record_number Au Gertrude 8 Au Natalie 37 Au Vanessa 50 Cheung Quentin 42 … Note: size is smaller before only two columns are stored Foxpro Chapter 6

18. Two Types of Index Files • Independent 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 Foxpro Chapter 6

19. INDEX ON USE patient INDEX ON sex TO/TAG sex GO TOP && Sai Gertrude SKIP SKIP At which record? && Tse Beatrice Foxpro Chapter 6

20. A dbf file may have more than one index file USE patient INDEX ON name TO/TAG name GO 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? Foxpro Chapter 6

21. SET INDEX / ORDER TO USE Patient INDEX ON name TO/TAG name GO TOP && Au Gertrude INDEX ON sex TO/TAG sex GO TOP && Sai Gertrude SET INDEX/ORDER TO name GO TOP && Au Gertrude SET INDEX/ORDER TO sex GO TOP && Sai Gertrude Foxpro Chapter 6

22. FIND and SKIP USE patient INDEX ON name TAG name FIND Yeung && Yeung Karen ? FOUND() && output .T. SKIP && Yeung Nora Foxpro Chapter 6

23. SEEK and SKIP USE patient INDEX ON name TAG name SEEK Yeung && variable not found SEEK “Yeung” && Yeung Karen ? FOUND() && output .T. SKIP && Yeung Nora Foxpro Chapter 6

24. UNIQUE USE patient INDEX ON sex TAG sex2 UNIQUE LIST && only one record per sex will be shown SET ORDER TO LIST && now the index is no longer in effect, so all records show up SET ORDER TO sex2 LIST && only one record per sex will be shown again 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. Foxpro Chapter 6

26. Macro Substitution & (2) USE patient index name Law=‘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 Foxpro Chapter 6

27. Macro Substitution & (3) USE patient index name Law=‘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 Foxpro Chapter 6

28. Macro Substitution & (4) USE patient index name Law=‘Shut’ SEEK Law && Shut Daphne SEEK &Law && variable Shut is not found SEEK ‘Law’ && Law Beatrice SEEK ‘&Law’ && Shut Daphne Note: SEEK accepts expression content of variable is used Foxpro Chapter 6

29. CompareLOCATE, FIND and SEEK • See p.185 Foxpro Chapter 6

30. MACRO • A macro records the keystrokes • The 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> Foxpro Chapter 6