Chapter Five Index and Search

1. Visual FoxPro Ch5 by CHANG YU Chapter FiveIndex and Search • The concept of index. • Create , open and close index file. • Type of index files. • Set the master index. • Locate, seek and find. • Filter the record of tables.

2. Visual FoxPro Ch5 by CHANG YU Concept Records are ordered by name. name

3. Visual FoxPro Ch5 by CHANG YU Concept Records are ordered by pay.

4. Visual FoxPro Ch5 by CHANG YU Concepts • The original table is ordered by entry number. • It is difficult to find a record in a big table. • It is easy and quick to search a record if we use index file. • A table is allowed to have many index files. Each index file has an index key and the table will be ordered by index key value. If you want the table to be displayed in an order by some index, you must set a master index key. • Many commands will operate based on the new order by the master index when the index file is created. E.g. List, Display, Skip, Go top, Go Bottom.

5. Visual FoxPro Ch5 by CHANG YU The type of index file • Type of index files: • Standard Single-Entry Index File. • Compact Single-Entry Index File. • Structural Compound Index File. • Non-Structural Compound Index File. • We will introduce how to create, open , close an index file and set a master index.

6. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index FileCreate an index file • Index on <Expression> To <IDXFileName> Note: 1. Open the table first. 2. Create an index file. 3. Index key becomes the master index at once. 4. Records are ordered by master index. 5. Table (*.dbf) remains as before but is displayed by master index only. Index key– is a column name or expression of columns. Index file name (*.idx)

7. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index File E.g . Use person1 index on name to name.idx browse display status use clear dir *.idx Close table and index file. Index file exists already.

8. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index FileOpen an index file • Use < TableName> [ Index <IDXFilelist> ] The command will open the table and its index file together. • use person1 index name, pay • Set index to <IDXFileList> • Use person1 Set index to name, pay • Set order to < n > | < IDXFilename > Convert to another master index. • Use person1 index name, pay, birth Set order to 2 ( = set order to pay) display status Name.idx is master index.

9. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index File • Index on <Expression> To <IDXFileName> • Open database staff1 use person1 index on departno To departno browse index on departno+name To depname Browse last display status is a column name or column expression . Major sort key Minor sort key the type of departno and name must be matched.

10. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index File • STR ( <nExpression>[ , <nlength> [, <nDecimalPlace> ] ] ) This function will convert numeric type to character . • DTOC( ) will convert the “date” to “character”. Decimal number Numeric expression Length of character

11. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index File • use person1 index on departno + str(pay, 6) to deppay browse ? Str(1234.567,6,1) ? Str (1234.567, 6) • use person1 index on departno+DTOC(birthday) to depbirth browse

12. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index FileReindex • If we delete, modify and insert in a table without opening its index file, we should use the “Reindex” command to rearrange them. • use person 1 index pay browse replace pay with 13000 browse last use use person1 delete record 14 pack browse last set index to pay browse last reindex Error message

13. Visual FoxPro Ch5 by CHANG YU Standard Single-Entry Index FileClose an index file • Close index • Set index to Note: Those commands will close the index file. But the table is still open.

14. Visual FoxPro Ch5 by CHANG YU Compact Single-Entry Index File • Index on name To name Compact This command will create a compact single-entry index file. (*.idx) • index on tel to tel compact display status

15. Visual FoxPro Ch5 by CHANG YU Structural Compound Index File • This kind of index files (*.cdx) consist of many compact single-entry index files (*.idx). Person1.cdx name.idx pay.idx tel.idx birth.idx departno.idx departpay.idx Compact single-entry index file. Index tag

16. Visual FoxPro Ch5 by CHANG YU Structural Compound Index File • The advantage of using structural compound index files: • easy to maintain. • easy to open. Using the [use] command to open the table and its index file together.

17. Visual FoxPro Ch5 by CHANG YU Structural Compound Index FileCreate • Index on <Expression> Tag <TagName> [ Ascending | Descending ] • use person1 index on no Tag no display status clear browse • Create a person1.cdx file. • Add a no tag index in the .cdx file. • “No” becomes the master index at once. • Display records order by ascending or descending number.

18. Visual FoxPro Ch5 by CHANG YU Structural Compound Index FileCreate 2. Create a .cdx file in the table designer. • Open table  call the table designer  press the page tab  Order: descending or ascending. Name: name of index tag. Expression: expression of index key. Type : regular index, unique index, primary index and candidate index.

19. Visual FoxPro Ch5 by CHANG YU Structural Compound Index FileCreate • Type of tag indices: • Regular Index: • Unique Index: • Primary Index: • Candidate Index:

20. Visual FoxPro Ch5 by CHANG YU Non-Structural Compound Index File • SCIF and NSCIF • Many index tags can be saved in two kinds of .cdx files. • The name of SCIF must be same as the table name. But the name of NSCIF must not be same as the table name. • The SCIF(*.cdx) file will be opened with the table together. But the NSCIF .cdx file cannot be opened with the table by default. • A table can have only one SCIF .cdx file, but can have many NSCIF .cdx files.

21. Visual FoxPro Ch5 by CHANG YU Non-Structural Compound Index File • Index on <Expression> Tag <TagName> Of <CDXFileName> [ Ascending | Descending ] • use person1 index on tel Tag tel of personns display status Tel is the master index.

22. Visual FoxPro Ch5 by CHANG YU Index File (*.cdx ) • Index on <Expression> To <IDXFileName> | Tag <TagName> [ Of <cdxfileName> ] [ compact ] [ Ascending | Descending ] Standard Single-Entry Index File(*.idx) Compact Single-Entry Index File(*.idx) Structural Compound Index File(*.cdx) Non-structural Compact Index File(*.cdx)

23. Visual FoxPro Ch5 by CHANG YU Index File Standard Single-Entry Index File. Compact Single-Entry Index File. Index on <expression> To <Idxfilename> *.idx file Index on <expression> To <Idxfilename> compact

24. Visual FoxPro Ch5 by CHANG YU Index File *.cdx file: Structural Compound Index File Non-Structural Compound Index File Index on <expression> Tag <Tagname> [Ascending | Descending ] Index on <expression> Tag <Tagname> Of <cdxfilename>

25. Visual FoxPro Ch5 by CHANG YU Using Index Filesopen • Open the structural index file: • Use person1 It opens the table “person1.dbf” and “person1.cdx”. But it does not set any master index. • Open the standard single-entry index file , compact single-entry index file and non-structural compound index file : • Use person1 index name, birth, personns • Display status Open person1.cdx, personns.cdx, name.idx and birth.idx . “name” is the master index.

26. Visual FoxPro Ch5 by CHANG YU Using Index FilesSet the master index • Open *.cdx file : does not set any master index. • Use person1 index personns display status • Open *.idx file : the file which is at the first position is the master index. • use person1 index name, personns, birth display status “name” is the master index.

27. Visual FoxPro Ch5 by CHANG YU Using Index FilesSet the master index • Open *.cdx and *.idx at a same time. • use person1 index name, personns, birth Display status • use person1 index personns, name, birth display status “name” is the master index. Does not set any master index.

28. Visual FoxPro Ch5 by CHANG YU Using Index Filesconvert the master index • use person1 index name, birth, personns set order to 2 browse set order to 6 ? display status set order to name of personns display status set order to no descending

29. Visual FoxPro Ch5 by CHANG YU Set order to Use person1 index name, birth, personns Set order to 2 Use person1 Set index to name, birth, personns Set order to 2 Use person1 Set index to name, birth, personns order 2 Use person1 index name, birth, personns order 2 = = =

30. Visual FoxPro Ch5 by CHANG YU Close the Index File • USE , CLOSE ALL, CLOSE DATABASE. • Set index to • Close index Close *.idx and NSCIF *.cdx only.

31. Visual FoxPro Ch5 by CHANG YU Search • Locate: It can search data not only directly in the original table, but also using an index file (in a quicker way). • Seek : must search using an index file. • Find : must search using an index file. • Set filter to go top / browse: filter the records in the table.

32. Visual FoxPro Ch5 by CHANG YU Locate • Locate [ For <IExpression> ] [ <scope> ] • Search data without opening any index file: The pointer will go to the top first, and then find the data one by one (a slow process). • Search data with an index file: A very quick process to find the data. (No need to set any master index) Go top Filter condition Range

33. Visual FoxPro Ch5 by CHANG YU Locate • use person display status clear locate for name = “Tommy” browse locate for pay = 12000 all browse last continue ?Found( ) locate for birthday={^1969.04.29} and sex=.f. browse last If we want to find the next data with the same condition.

34. Visual FoxPro Ch5 by CHANG YU Seek • Seek <Expression> • We should create a *.cdx file or a *.idx file and set the master index. • <Expression> is any type of expression. • <Expression> can include some variable. • Seek cannot find data whose type is general or memo .

35. Visual FoxPro Ch5 by CHANG YU Seek • Use person set order to name seek “Tommy” browse seek “Mary” browse last set order to pay seek 12000 browse last x = 2000 seek 12000 + x browse last

36. Visual FoxPro Ch5 by CHANG YU Seek • use person order birth seek {^1960.02.11} browse index on name +SRT(pay,6) to namepay compact display status seek “tommy” + str(36000,6) browse last skip ? Found( )

37. Visual FoxPro Ch5 by CHANG YU Find • Find < Expression> • Find command cannot find data whose types are memo, general, logical, or date. It can find constant and string only. • <Expression> does not include any variable.

38. Visual FoxPro Ch5 by CHANG YU Find • Use person order name find “Tommy” browse last set order to pay find “12000” browse last x = 2000 find 12000 + x browse last set order to birth find {^1960.02.11} browse last Type match Error message. No variables Can not search date.

39. Visual FoxPro Ch5 by CHANG YU Set Filter To Set Filter to [<IExpression>] + Go top | Browse • Open database staff use person set filter to sex = .f. display no,name,sex browse set filter to pay > 30000 go top • This command can operate together with the “browse”, “go top”, and other commands of moving pointer. • If we want to search quickly, we must create an index tag (no need to set any master index).