Visual Fox Pro Ch 5 by CHANG YU

Visual Fox. Pro Ch 5 by CHANG YU Chapter Five Index 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. 1

Concept Visual Fox. Pro Ch 5 by CHANG YU Records name are ordered by name. 2

Visual Fox. Pro Ch 5 by CHANG YU Concept Records are ordered by pay. 3

Visual Fox. Pro Ch 5 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 4 Bottom.

Visual Fox. Pro Ch 5 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. 5

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File Create an index file • Index on <Expression> To <IDXFile. Name> Index key– is a column name or expression of columns. Note: Index file name (*. idx) 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. 6

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File E. g. Use person 1 index on name to name. idx browse display status use Close table and index file. clear dir *. idx Index file exists already. 7

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File Open an index file • Use < Table. Name> [ Index <IDXFilelist> ] The command will open the table and its index file together. – use person 1 index name, pay • Set index to <IDXFile. List> – Use person 1 Set index to name, pay Name. idx is master index. • Set order to < n > | < IDXFilename > Convert to another master index. – Use person 1 index name, pay, birth Set order to 2 ( = set order to pay) display status 8

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File • Index on <Expression> To <IDXFile. Name> is a column name or column expression. – Open database staff 1 use person 1 Major sort key index on departno To departno Minor sort key browse index on departno+name To depname Browse last display status the type of departno and name must be matched. 9

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File • STR ( <n. Expression>[ , <nlength> [, <n. Decimal. Place> ] ] ) Decimal number Numeric expression Length of character This function will convert numeric type to character. • DTOC( ) will convert the “date” to “character”. 10

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File • use person 1 index on departno + str(pay, 6) to deppay browse ? Str(1234. 567, 6, 1) ? Str (1234. 567, 6) • use person 1 index on departno+DTOC(birthday) to depbirth browse 11

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File Reindex • 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 person 1 delete record 14 pack browse last set index to pay browse last reindex Error message 12

Visual Fox. Pro Ch 5 by CHANG YU Standard Single-Entry Index File Close an index file • Close index • Set index to Note: Those commands will close the index file. But the table is still open. 13

Visual Fox. Pro Ch 5 by CHANG YU Compact Single-Entry Index File • Index on name To name Compact This command will create a compact singleentry index file. (*. idx) – index on tel to tel compact display status 14

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

Visual Fox. Pro Ch 5 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. 16

Visual Fox. Pro Ch 5 by CHANG YU Structural Compound Index File Create 1. Index on <Expression> Tag <Tag. Name> [ Ascending | Descending ] – use person 1 index on no Tag no display status clear browse 1. Create a person 1. cdx file. 2. Add a no tag index in the. cdx file. 3. “No” becomes the master index at once. 4. Display records order by ascending or descending number. 17

Visual Fox. Pro Ch 5 by CHANG YU Structural Compound Index File Create 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. 18

Visual Fox. Pro Ch 5 by CHANG YU Structural Compound Index File Create • Type of tag indices: – – Regular Index: Unique Index: Primary Index: Candidate Index: 19

Visual Fox. Pro Ch 5 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. 20

Visual Fox. Pro Ch 5 by CHANG YU Non-Structural Compound Index File • Index on <Expression> Tag <Tag. Name> Of <CDXFile. Name> [ Ascending | Descending ] – use person 1 index on tel Tag tel of personns display status Tel is the master index. 21

Visual Fox. Pro Ch 5 by CHANG YU Index File (*. cdx ) • Index on <Expression> To <IDXFile. Name> | Tag <Tag. Name> [ Of <cdxfile. Name> ] [ 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) 22

Visual Fox. Pro Ch 5 by CHANG YU Index File Standard Single-Entry Index File. *. idx file Index on <expression> To <Idxfilename> Compact Single-Entry Index File. Index on <expression> To <Idxfilename> compact 23

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

Using Index Files Visual Fox. Pro Ch 5 by CHANG YU open • Open the structural index file: – Use person 1 It opens the table “person 1. dbf” and “person 1. 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 person 1 index name, birth, personns – Display status Open person 1. cdx, personns. cdx, name. idx and birth. idx. “name” is the master index. 25

Visual Fox. Pro Ch 5 by CHANG YU Using Index Files Set the master index • Open *. cdx file : does not set any master index. – Use person 1 index personns display status • Open *. idx file : the file which is at the first position is the master index. – use person 1 index name, personns, birth display status “name” is the master index. 26

Visual Fox. Pro Ch 5 by CHANG YU Using Index Files Set the master index • Open *. cdx and *. idx at a same time. – use person 1 index name, personns, birth Display status “name” is the master index. – use person 1 index personns, name, birth display status Does not set any master index. 27

Visual Fox. Pro Ch 5 by CHANG YU Using Index Files convert the master index – use person 1 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 28

Visual Fox. Pro Ch 5 by CHANG YU Set order to Use person 1 index name, birth, personns Set order to 2 = Use person 1 Set index to name, birth, personns order 2 = Use person 1 index name, birth, personns order 2 29

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

Search Visual Fox. Pro Ch 5 by CHANG YU • 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. 31

Visual Fox. Pro Ch 5 by CHANG YU Locate • Locate [ For <IExpression> ] [ <scope> ] Go top Filter condition Range – 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) 32

Visual Fox. Pro Ch 5 by CHANG YU Locate – use person display status clear locate for name = “Tommy” browse locate for pay = 12000 all browse last If we want to find the next data continue with the same condition. ? Found( ) locate for birthday={^1969. 04. 29} and sex=. f. browse last 33

Visual Fox. Pro Ch 5 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. 34

Visual Fox. Pro Ch 5 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 35

Visual Fox. Pro Ch 5 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( ) 36

Visual Fox. Pro Ch 5 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. 37

Visual Fox. Pro Ch 5 by CHANG YU Find – Use person order name find “Tommy” browse last set order to pay Type match find “ 12000” browse last Error message. No variables x = 2000 find 12000 + x browse last Can not search date. set order to birth find {^1960. 02. 11} browse last 38

Visual Fox. Pro Ch 5 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). 39