PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 1/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS In every database application, there are several important components: 1. The Database block is essential in every database operation. It is the area where all your information is located. Each database block consists of the following: a. A row of field names which describe each field in your database. These field names must always be located in the first row of your database block, just above the first record in your database. Each field name should be unique, no longer than 15 characters in length, and should not contain blanks. If you wish to add a space between two words, use the underscore symbol (_). b. The remaining row(s) in your database block are record(s). In a phone list, for example, a record would consists of each person's name and phone number. c. Each column of your database block is a field. Field(s) are individual pieces of information that fit within a record. In a phone list, for example, one field may be the person's name; another field may be the person's phone number. If you are linking to an external database file (that is, a Paradox, Reflex, or dBASE file which is not currently open in a Quattro Pro window), you can specify the database block using the following syntax: [FILENAME]A1..A2, where FILENAME is the name of the external database you wish to query. This syntax allows you to modify records in the external database without being forced to redefine your database block each time. Note that you cannot do a Locate or Delete on an external database, it is read only. If linking to an external spreadsheet database (such as Lotus, Quattro, Symphony, or Surpass), you must link to the entire database block (eg. [FILENAME]A1..G58]). PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 2/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS 2. The criteria table is also essential in every database application. It is in the criteria table that you specify a set of rules which will determine what records you wish to examine in the main database block. Each criteria table consists of the following: a. The first row of your criteria table will always consist of field names. These field names must all be spelled exactly as they appear in the database (use to copy the field names directly from the database into the criteria table; this ensures that there will be no typos). It is not necessary to place all of the database's field names in the criteria table; you only need to include the field names which contain a criteria you are using for your query. b. The second row, and any rows following, contain the search criteria. It is here that you can specify what rules you wish to adhere to for extracting records. A criteria table must always have at least one of these rows in addition to the field names. The search criteria consists of one of the following: o A Value, such as 25.50. Quattro Pro will look for this value in the field whose name is located above the criteria in the first row of the criteria table. o A Label, such as Cleveland. The Labels can contain wildcards, C* for example would query for all labels which begin with the letter C. Quattro Pro will look for this label in the field whose name is located above the criteria in the first row of the criteria table. o A Logical Search Formula. A search formula is a logical formula which returns either a one or zero, such as +AGE>30. PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 3/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS o An empty cell, which acts as a place holder, and indicates no criteria in this cell (in other words, all records). Note that no row in your criteria table should consist of only blank cells, because if it does, you are telling Quattro Pro to query ALL records in the database. 3. The last component of your database, the output block, is only necessary if you are using the database operations Extract or Unique. It is not necessary if you are using the Locate or Delete commands. Each output block consists of the following: a. The first row contains field names which are taken from the field names in the database block. It is not necessary to place all the field names in the database block into this table, only the names of the fields you wish to extract into the output block. These field names must all be spelled exactly as they appear in the database (use to copy the field names directly from the database into the criteria table; this ensures that there will be no typos). b. Any other rows can be empty, since they are place holders for where you wish the extracted data to be placed. If your output block is only one row in size, then Quattro Pro will delete everything below the output block and place as many of the records as it can underneath. If you wish to prevent this, specify an output block with more than one row. Quattro Pro will try to fit the extracted records into only the block you specify, and will return an error message ("Too Many Records For Output Block") if they do not all fit. PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 4/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS Trouble Shooting If you are having problems with your database application, use the following list to make sure everything is configured properly. Databases are very precise, and small mistakes can produce some pretty strange results. Before running through this list, go into the menu, and jot down the following information. It will help you skim the checklist more easily. 1. The block range defined as the database block (). 2. The block range defined as the criteria table (). 3. The block range defined as the output block, if one is necessary (). If you are not querying an external database file: 1. Does the first row of the database contain field names which are no more than 15 characters long, and unique? 2. Is the first record located in the second row of your database block? 3. Does each field in the database consist of a column of labels or a column of values (make sure they are not mixed)? 4. Have you issued the command to establish the field names in your database? If you are unsure, or have changed any aspects of the database, then issue this command again. 5. Have you indicated a criteria table using the command? PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 5/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS 6. In your criteria table: a. Does the first row contain field names which are entered exactly like the field names in your database? b. Does each cell in the row(s) below the first row of the criteria table contain one of the following: o A Value, such as 123? o A Label, such as Address? o A Label containing a wildcard, such as ABC*? o A Logical Search Formula which returns the value 1 or 0, such as +SALES>300, or +SALES>$A$1? Make sure that all cell addresses in the search formula which are not field names are absolute cell addresses (such as $A$1). o An Empty Cell (tap on the Del key to make sure a cell is empty)? No row in your criteria table should consist of only blank cells, because if it does, you are telling Quattro Pro to query ALL records in the database. 7. If you are performing an Extract, Unique, or Delete operation: a. Have you used the command to indicate an output block? 8. Does the first row of your output block contain field names which are spelled exactly like the field names in your database? If you are querying an external database (which is a Paradox, Reflex, or dBASE file which is not currently open in a Quattro Pro Window): PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 6/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS 1. Is the database block linked to the database in the format of [DATABASE]A1..A2? 2. Is the file a Paradox, Reflex, or dBASE file? 3. Are the field names in your search formulas linked to the external database? For example, +[DATABASE]SALES>300. PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 7/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS Databasics Quick Reference: Database Definitions The Database Block A B C <ФФФФФ Column Letter ЩЭЭЭЭЭЭЭЭЭЭЭЭЭЭЭЫЭЭЭЭЫЭЭЭЭЭЭЭЭЭЭЛ 1 КNAME КAGE КPHONE_NO К <ФФФФФ Field Names (Note underscore) ЬЭЭЭЭЭЭЭЭЭЭЭЭЭЭЭЮЭЭЭЭЮЭЭЭЭЭЭЭЭЭЭЙ 2 КJohn Doe К12 К000-0000 К <ФФФФФП 3 КNick Derpich К24 К111-1111 К <ФФФФФХФФ Records 4 КWilhelm Stroup К12 К222-2222 К <ФФФФФй Г ШЭЭЭЭЭЭЭЭЭЭЭЭЭЭЭЪЭЭЭЭЪЭЭЭЭЭЭЭЭЭЭМ Г Г Г Г Г Г Г Г Г РФФФФФФФФФФХФФФФФФФФФй Г Г Г РФФФ Row # Fields The database block above would be entered under as A1..C4. Criteria Table Definitions A Criteria Table demonstrating a logical OR of two criteria D E <ФФФФФ Column Letter ЩЭЭЭЭЭЭЭЭЭЭЭЫЭЭЭЭЭЛ 1 К NAME К AGE К <ФФФФФ The Field Names ЬЭЭЭЭЭЭЭЭЭЭЭЮЭЭЭЭЭЙ 2 КJohn Doe К К <ФФФП 3 К К 0 К <ФФФСФ The Search Criteria Г ШЭЭЭЭЭЭЭЭЭЭЭЪЭЭЭЭЭМ Г Г РФФФ Row # Г PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 8/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS РФФФФФФФФФФ This is the result of the formula +AGE>30, which is entered in this cell. In some cases, it may return a 1, depending on what records you have in the database block. Logical search formulas will always return a 1 or 0 in the cell. The above criteria table tells Quattro Pro to look for all records with the label John Doe in the field NAME, OR records where the value in the field AGE is greater than 30. Note that the empty cells in this criteria table merely act as place holders, so that Quattro Pro does not think you wish to use some criteria in those cells. This criteria table would be entered under as D1..E3. A Criteria Table demonstrating a logical AND of two criteria D E <ФФФФФ Column Letter ЩЭЭЭЭЭЭЭЭЭЭЭЫЭЭЭЭЭЛ 1 К NAME К AGE К <ФФФФФ The Field Names ЬЭЭЭЭЭЭЭЭЭЭЭЮЭЭЭЭЭЙ 2 КJohn Doe К 1 К <ФФФФФ The Search Criteria Г ШЭЭЭЭЭЭЭЭЭЭЭЪЭЭЭЭЭМ Г Г РФФФ Row # Г РФФФФФФФФФФ This is the result of the formula +AGE>30, which is entered in this cell. In some cases, it may return a 0, depending on what records you have in the database block. Logical search formulas will PRODUCT : QUATTRO PRO NUMBER : 391 VERSION : ALL OS : DOS DATE : April 18, 1991 PAGE : 9/8 TITLE : DATABASICS: TROUBLESHOOTING DATABASE APPLICATIONS always return a 1 or 0 in the cell. The above criteria table tells Quattro Pro to look for all records with the label John Doe in the field NAME, AND values in the field AGE which are greater than 30. It would be entered under as D1..E2. A Criteria Table demonstrating a logical OR of multiple criteria D <ФФФФФФ Column Letter ЩЭЭЭЭЭЭЭЭЭЭЭЛ 1 К NAME К <ФФФФФФ The Field Name ЬЭЭЭЭЭЭЭЭЭЭЭЙ 2 КJohn Doe К <ФФФФП 3 КJane Grey К <ФФФФД 4 КBill Dill К <ФФФФД 5 КJoe No К <ФФФФСФ The Search Criteria Г ШЭЭЭЭЭЭЭЭЭЭЭМ Г РФФФ Row # The above criteria table tells Quattro Pro to look for all records with the label John Doe, Jane Grey, Bill Dill, OR Joe No in the field NAME. It would be entered under as D1..D5.