![[Opening Screen]](IntrodBASEIIIPlus_files/dbaseIII.reading.gif)
The dBASE III Plus commands described in this manual use a standard notation. Special delimiters such as parentheses, single quotes or double quotes are required where indicated. It should be noted that the dBASE error messages are not always self-explanatory. For example, invalid keyword may be caused by an invalid keyword (usually a spelling error) or by an error in command syntax (usually a missing delimiter).
The following points cover some important fundamentals of dBASE III Plus:
To start dBASE III Plus: if you are on the network, use the SOFTMENU command.
On the network, the default disk drive is Y:. Change the default disk drive to your disk, that is, A:, by selecting Set Drive under Tools in the Assist menu before you do anything else in dBASE III Plus.
dBASE III Plus does not support a mouse.
dBASE III Plus can be run in two modes: Assist Mode or Command Mode (also called Dot-Prompt Mode), as explained in the dBASE III Plus Basics section.
To exit dBASE III Plus when in Assist Mode, select the Quit dBASE III Plus option from the Set Up menu.
To exit when in Command Mode, type QUIT at the prompt.
You may enter Assist Mode from Command Mode by typing ASSIST at the prompt.
All database files will be properly closed and all information is saved automatically if dBASE III Plus is exited properly by either of the above methods. Information will be lost if you attempt to exit the program by any other means.
The dBASE III Plus help key is the F1 key. Help is context sensitive and will describe the current command or menu option.
If you make a mistake entering a command in Command Mode, dBASE III Plus will ask Do you want some help? (Y/N). If you type Y, a help screen explains the correct format and use of the command. At this point, you can press Escape to return to the dot-prompt or you can type another command name at the cursor for information about that command.
If dBASE III Plus is in a mode you do not recognize or that you do not wish to use, press Escape to leave that mode.
dBASE III Plus is not a particularly complicated package but the keywords and the function names may not be as obvious as those used in other packages. The problem is complicated by the fact that not all available commands can be found in the pull-down menu. Use the section Commands by Function at the end of this manual to locate the desired command when you cannot remember the keyword.
READ the screen! dBASE III Plus provides you with enough information to answer most of your questions.
dBASE III Plus can be run in two different modes. Assist Mode works through a pull-down menu system. It allows you to build a dBASE III Plus command clause by clause. At every point you are guided on the possible input choices and their meanings. Assist Mode is recommended for novice users.
dBASE III Plus can also be run in Command Mode. In this mode, you type dBASE commands at the prompt. If you are comfortable with dBASE III Plus commands, you will find this approach the most efficient. All commands that can be entered in Assist Mode can also be entered in Command Mode. The reverse is not true; some commands can only be run from Command Mode.
When you start dBASE III Plus, a copyright screen will appear. Press the Enter key to continue. The next screen will be the opening screen for Assist Mode.
The basic components of the Assist Mode screen are:
![[Components of Assist Mode]](IntrodBASEIIIPlus_files/dbaseIII.compon.gif)
In the Components and Layout of Assist Mode figure, the Position entry in the menu bar has been selected. The Position submenu has five options. The Locate option has been selected and the Locate submenu is shown to the right. Specify scope has been selected, followed by the option Next, and the value 10 has been entered in the prompt box. The action line, which appears below the submenu area, shows the dBASE III Plus command being generated by Assist Mode. In the figure, the command is LOCATE NEXT. When Enter is pressed after the value of 10, the command will be LOCATE NEXT 10.
The status bar is highlighted and divided into six areas, from left to right:
When a database is active, the record pointer points to the current record in the database. Commands which operate on only one record will execute on the current record. If the record pointer shows EOF (end of file), it indicates that this is the last record in the database.
Below the status bar is the navigation line, which indicates the keystrokes that you may enter and their effects. The navigation line always tells you what dBASE III Plus is expecting as input. In the opening menu, the up arrow and down arrow keys will move the selection bar in the pull-down menu. Pressing the Enter key will select the highlighted option. The right arrow and left arrow keys will move to a different selection in the menu bar. The F1 key will give Help, and you exit this mode by pressing the Escape button. In the Components and Layout sample menu, dBASE III Plus is expecting a value followed by Enter.
The message line gives a brief description of the highlighted option on the current menu, that is, a mini-help screen. The message line is also used as an area to enter data for certain commands.
dBASE III Plus can also be run in Command Mode, where you may type commands at the prompt. A sample Command Mode screen is shown in the Command Mode Screen figure. The screen is blank except for the action line (the prompt), the status bar, and the message line at the bottom of the screen.
To change from Command Mode to Assist Mode type the command ASSIST or press the F2 key. To change from Assist Mode to Command Mode, press the Escape key.
![[Command Mode Screen Figure]](IntrodBASEIIIPlus_files/dbaseIII.commode.gif)
Because varying types of information are used in a database application, different kinds of data structures are defined in DOS file types. The various file name extensions, and their meaning to dBASE III Plus are listed below. Where appropriate, the dBASE III Plus command that creates the file type is listed in parentheses beside the description.
A field is the smallest portion of a record that has a specific meaning or purpose. Five types of fields can be defined in dBASE III Plus:
Most dBASE III Plus commands which are related to record processing have the following format:
The scope of the command restricts the number of records to be processed. Typically, the default command scope is the current record. If you want the command to work on all records in the database be sure to specify ALL.
RECORD n command operates on record n only
NEXT n command operates on next n records only
ALL command operates all records
REST command operates on all records after the current record
The condition is a logical (simple or compound) expression. The result of a logical expression is either true or false. A simple logical expression has the form:
statement relational operator statement
where statement returns a numeric value and has the form
number mathematical operator number
where mathematical operator is one of
+ add
- subtract
* multiply
/ divide
** exponentiation
or
statement returns a string value and has the form
string string operator string
where string operator is one of
+ concatenate second string to first string
- concatenate but strip trailing blanks from first string
e.g. city + str( zip, 5 )
where relational operator: is one of
< less than
<= less than or equal to
> greater than
>= greater than or equal to
= equal to
<> not equal to
# not equal to
$ substring comparison -- returns true if first string
is identical to or is contained within the second string
simple expression logical operator simple expression
where simple espression is as above
where logical operator is one of
.NOT. not
.AND. logical and
.OR. logical or
NOTE: .T. is true, .F. is false
A database organizes data into a data hierarchy. A field is the smallest component that conveys a specific meaning or purpose. A record is a set of one or more fields concerning one individual or case. A file is a set of one or more related records, grouped together by purpose. A database is a set of related files. For example, the employee database for a company may consist of two files, one for payroll and another for job classification. The two files probably share no common fields other than an employee identification number or a job title. In the payroll file, there will be one record for each employee. Each record will consist of a number of fields and will probably include the employee identification number, the employee name, the job title, the employee's social security number, and a weekly salary.
In designing a database, you must know:
Decide first on the number and purpose of the files in the database. Many simple databases have only one file. Next, select the fields in the database(s). Can you answer all the required questions with the fields you have selected? When laying out your database, sketch the database structure. Always put the fields to be used most frequently at the beginning of the record. This order will allow you to see the most important data on the screen without scrolling.
A data structure defines each item of data in a database system. For each data item, the structure defines:
In dBASE III Plus, the data structure is defined by selecting the Database file option from Create in Assist Mode. When all the fields are defined and you terminate the data structure definition process, you will see the message Input data records now? (Y/N) at the bottom of the screen. If you press y, the program displays the data entry form for the structure you have just defined and you can start entering data. As each record is completed, a new data entry form is presented. You can choose to defer the entry of data until some later time. When you complete this process, the resulting structure (and any data entered) will be stored in the file filename.DBF. You will be asked to provide the filename.
The data structure may be viewed by:
The structure may be modified by selecting Structure under Modify in Assist Mode or typing MODIFY STRUCTURE in Command Mode. Modification can include defining new fields, deleting old fields, or changing field names, types and sizes. You may not change a field's name, type and/or size at the same time. The structure can be modified at any time: before, during, or after data has been entered into the database. Existing data will not be modified unless fields are deleted, field widths are decreased, or field types are changed.
Databases can only be modified or manipulated if they are loaded into one of the 10 work areas in dBASE III Plus. The ten work areas are numbered 1 to 10. The work areas also have an alias: the letters A to J, which correspond to the areas 1 through 10. Thus, work area 1 can also be referred to as work area A. The work area can also be referred to by name of the database loaded in the area. Up to 10 different databases can be loaded into the system. However, only one work area and consequently one database is considered to be the currently selected work area or the active database.
When you start dBASE III Plus, work area 1 is the selected work area and will contain the active database. A database is opened with the Use command. If another database is in the currently selected area, the USE command will close that database and then open the database indicated in the USE command.
A work area may be made current with the SELECT command. If the CREATE command has just been used, the newly defined database will be active in the current area, and no SELECT or USE command is needed. For example, if CUSTOMER.DBF is active in work area one, and EMPLOYEE.DBF is active in work area two, you may move from one to the other by typing
Data may be entered into, added to, or edited in an existing database using APPEND, BROWSE, or EDIT. All of these commands have identical data editing capabilities but different interfaces. All commands assume that you have an active database. You must open a database (USE) unless the structure was just created.
The APPEND command is used to enter data, one record at a time. The entry form for the data appears as a series of highlighted blocks the same width as the fields they represent beside the appropriate field name. Simply type your data into the blocks. When a record is complete, a new blank record form is presented automatically.
The EDIT command is used to modify existing data. EDIT uses the same entry format as APPEND, presenting one record at a time. Simply edit data in any highlighted block. (See the Edit Screen figure.)
![[Edit Screen Figure]](IntrodBASEIIIPlus_files/dbaseIII.edscr.gif)
The BROWSE command is used to enter or modify existing data. The BROWSE command presents multiple records on the screen in a two dimensional format similar to a spreadsheet. As you move around the screen, a field will be highlighted as the active field and the contents of this field can be modified. (See the Browse Screen figure).
![[Browse Screen Figure]](IntrodBASEIIIPlus_files/dbaseIII.brscr.gif)
The CREATE SCREEN command allows you to create a custom data entry form. A custom data entry form allows you to perform simple error checking on data as it is being entered. The custom form can also arrange the fields for easier data entry, or omit fields from the screen which are not entered manually.
Deleting a record is a two step process. The DELETE command simply marks the specified record for deletion. The fifth area in the status line will display DEL to indicate that the current record is marked for deletion. Records can be marked for deletion from within BROWSE and EDIT as well. Records marked for deletion are processed like any other records if SET DELETED OFF is used. If SET DELETED ON is used, then the records marked for deletion will be ignored. The RECALL command will unmark a record that was previously marked for deletion. The PACK command physically removes the records marked for deletion from the database. A record removed by PACK cannot be RECALLed. The ZAP command deletes all records from a database and is equivalent to a DELETE ALL followed by a PACK but much faster. ZAP is a very dangerous command.
The BROWSE, DISPLAY, LIST, and REPORT commands can be used to display selected records from the database. BROWSE, DISPLAY, and LIST are typically used for informal data viewing. Select the command based on the interface you prefer and your reason for displaying the data.
The REPORT command is used to produce a formatted report to be printed or stored on disk. The format for the report is defined by the CREATE REPORT command and is stored in a .FRM file. You must define the report format before producing the report.
The commands FIND, LOCATE, and SEEK search for a record that matches some specified condition. LOCATE works on unindexed databases while FIND works on indexed databases. These commands return the record number of the first record that matches the criteria. Use LIST to display the record.
The SORT and INDEX functions arrange the records in the database in the order that you specify. The SORT command physically arranges the records in the database based on one or more sort keys. The sort keys can be character, numeric or date fields. When sorting, dBASE III Plus creates a sorted copy of the original database and the new name for the sorted database file must be provided in the command.
The INDEX function creates a .NDX file, which is a list of pointers that keep track of the order of the records instead of actually rearranging the records. If a LIST command is issued on an indexed file, the records will be displayed in the desired (indexed) order but the record numbers will not appear in sequential numerical order. Like the SORT function, the INDEX command uses a key to sort the data. Although multiple fields may be joined to index the database, the fields must be of the same type. Therefore if a character and numeric field are to be used as the key for the index, the numeric field must be converted to characters by using the STR( ) function.
After a database has been indexed, the INDEX clause in the USE statement must be specified every time the database is opened. If the index is omitted and the database is changed, the .NDX file will not be updated, and the next time the database is used with the index, it will not be ordered properly. If this occurs, the .NDX file can be rebuilt by using the REINDEX command. A database can have multiple .NDX files providing the user with different ways of ordering the same database. All .NDX files must be specified in the USE statement when opening the database. If more than one .NDX file is specified, then the database will be ordered according to the first .NDX file specified, but all .NDX files will be updated when changes are made to the database. The SET ORDER TO command will change the primary index of the database to another .NDX file. Once a file is indexed, commands like FIND can be used to locate a particular record in the database.
A query file sets up a temporary filter for a database. This filter specifies a subset of the database, so that to the user the database appears only to have records that match these criteria. For example, a student database may contain all of the students in a school. A query file may be set up to include only records where the YEAR field is equal to 4. Thus, while the query is being used, the database appears only to have fourth year students. The CREATE QUERY statement is used to set up a .QRY file which specifies the filter. The SET FILTER TO queryname.QRY command activates a particular filter for the active database. Only one .QRY file may be used at a time.
The SUM, AVERAGE, and COUNT functions perform simple calculations. The SUM function will print a total for a field, the AVERAGE function provides an average for a field, and COUNT specifies the number of records in a file. The WHILE/FOR clauses can be used with all of these commands so that only a subset of the records are used for the calculations. For example, COUNT FOR CITY='WATERLOO' would provide the number of records where the field CITY is 'WATERLOO'.
The JOIN command allows two databases with at least one common field be combined to form a new database. The following sequence of commands demonstrates how this may be used:
SELECT 1
USE ENTRY
SELECT 2
USE STUDENTS
SELECT 1
JOIN WITH STUDENTS TO NEWDBASE FOR std_id = STUDENTS->std_id FIELDS
STUDENTS->std_name, std_id, entry_time
This example sets up the ENTRY database in area one, then sets up the STUDENTS database in area two. The ENTRY database is made active by issuing the second SELECT 1 command. The JOIN statement creates a new database called NEWDBASE with the fields specified in the FIELDS clause. Specifically, the record will contain the std_name from the STUDENTS database, std_id from ENTRY, and entry_time from ENTRY. For each record in the ENTRY database, the STUDENTS database is searched for records which have a std_id field that equals the std_id field in the ENTRY database. For each match found, a new record in the NEWDBASE database is created with the specified fields. The alias->field_name notation is used to allow access to fields of databases that are loaded but not active. The alias can be specified with the work area number (e.g. 2->std_id ), with the work area alias (e.g. B->std_id), or with the database name (e.g. STUDENTS->std_id).
Data may be brought into a database from an ASCII file or from a Lotus 1-2-3 file. Since most wordprocessors will output ASCII files and most spreadsheet and statistical packages will output ASCII files and/or Lotus 1-2-3 format files, it is relatively simple to transfer data into dBASE III Plus. In both cases, the APPEND FROM command is used. Note: APPEND FROM can only be accessed from Command Mode.
To output an ASCII file from a database, use the COPY command with either the SDF or DELIMIT options. The SDF option will create an ASCII file where the field lengths are preserved and the records are separated with a carriage return. SDF is the preferred format if the destination is a wordprocessor or a statistical package. dBASE III Plus can also produce Lotus 1-2-3 spreadsheet format files by using the type option WKS on the COPY command. Output from dBASE III Plus can easily be brought into most wordprocessors, spreadsheet packages, and statistical packages. Do not use Import and Export.
Sometimes it is useful to run a series of commands more than once. DO filename.PRG will sequentially execute all of the commands located in filename.PRG. Any editor can be used to create the ASCII filename.PRG file.
Section Shortcut:
[?]
[A-C]
[D-L]
[M-R]
[S]
[T-Z]
[Date
and Time Functions]
[Character
Functions]
[Mathematical
Functions]
[Other
Type Functions]
[Record
Pointer Functions]
Data display commands can be carried out without reference to a particular database. dBASE III Plus considers everything after the question mark as a query to the system.
Add records to the active database file.
Add records from another file to the active database file.
where type is one of:
Change to Control Center Mode from Command Mode.
Computes the arithmetic mean for all or for specified numeric fields in the default database. Output will include headings for all relevant fields with the average for each field displayed immediately beneath the column heading.
Presents a spreadsheet-like, full-screen view of records in the database for editing and appending records. Exit browse by pressing Control and End.
Close files that are currently in use. (Similar to the USE command with no operands.)
Positions the current record pointer to the next record meeting the condition specified in the LOCATE command. The command assumes that a LOCATE command has already been issued to find an item in a nonindexed database.
Duplicates all or part of an active database file, creating a new file. COPY is also used to export data to non-dBASE III Plus programs.
where extensions for exported files are:
Duplicates any file.
Determines the number of records that meet a specific condition. If no condition is specified, COUNT determines the number of records in the database.
Creates new files of various types.
Marks records in the active database file for deletion. See RECALL to remove the deletion marker. See PACK to physically remove the marked records. Records may also be marked for deletion while the user is in either EDIT or BROWSE. Pressing Control and U toggles between marking and unmarking records for deletion.
Deletes a file. The filename must include the extension and wildcards may not be used. You may not delete a file that is currently in use. Same as ERASE.
By default, lists database filenames with the extension .DBF on the current directory.
Displays the contents of a database file in an unformatted list.
Generates a listing of the data fields, their data type, their length, and decimal places (for numeric data types). The display pauses when a screen has been filled.
Executes the specified command file. Any editor can be used to create the ASCII .PRG file containing the commands.
Allows you to alter the contents of one or more records in the active database.
Removes a file from the disk directory. The filename must include the extension and wildcards may not be used. You may not delete a file that is currently in use. Same as DELETE FILE.
Locates the first record that meets the search condition in an indexed file. The index must have been constructed around the field in which the search string is located.
Creates an index (.NDX) from which records in a database file can be ordered alphabetically, chronologically, or numerically. The index (.NDX) file contains the key values and the corresponding record number for each record in the database file.
Adds a single record to the current database. By default, the record is added immediately after the current record. INSERT and INSERT BEFORE open the full-screen editing mode. INSERT BLANK creates a single empty record for later data addition and does not activate the editing mode.
Creates a new database file by merging the specified records and fields from two open database files.
Displays the contents of a database file in an unformatted list.
Searches the active database file for a record that matches the specified condition. LOCATE works on unindexed databases: sorted and unsorted.
The command can be used to modify database file structures, query, report, screen, and view files.
The command can be used to modify any ASCII file.
Physically removes the records that have been marked for deletion. Once the PACK command has been executed, the records can not be retrieved. If any indexes are in use, they will be updated. If an index file built on this database is not in use, it will have to be reindexed.
Quit closes all databases, index and other files properly and returns to DOS.
Removes the deletion marker from the specified records which are marked for deletion in the active database.
Rebuilds all active index (.NDX) files in the current work area based on the command used to construct the original index files.
Gives a new name to a file.
REPLACE changes the contents of the specified fields.
Prints information from the active database using a report form file created by CREATE/MODIFY REPORT. The report may be directed to the screen, the printer, or to a file.
Executes a DOS command or any program which can be executed by DOS, from within dBASE III Plus.
Used to switch between work areas in the dBASE III Plus system. There are ten work areas available and therefore up to 10 databases may be open at once. The last area selected contains the active database.
Produces a menu of changes that can be made to the dBASE III Plus programming environment. Changes can be made to the screen, to function key assignments, to the default drive, to the default files, to print characteristics, and to the display of decimal places.
When you enter data in edit, append, and browse, the default is to sound a bell when you enter the last character in the last column of a field. The SET BELL command can turn the bell on and off.
Changes the default drive dBASE III Plus uses to read and write files. On the network the default drive is Y:. With most dBASE III Plus commands, you can specify drives and paths when you want to reference files that are not on the default drive.
Determines whether records that are marked for deletion are included (OFF) or ignored (ON) by other dBASE III Plus commands. Default is off.
When searching on character fields, dBASE III Plus defaults to an inexact search. If a search is made for a string n characters long, any string which starts with the same n characters will be considered a match. If EXACT is ON, the string must match the specified string and be n characters long to be considered a match.
Limits the number of fields to be displayed to the screen to make editing and searching easier. By default, you will see the contents of all the fields in the records.
Limits the display of records to those that meet a specified condition.
Allows you to assign different values to the function keys to tailor the most commonly used commands for a given user. Function key F1 is assigned to the HELP function and cannot be reprogrammed. See the function key template at the end of this manual for default function key allocations.
Sets up any open index file as the master index, or removes control from all open indexes, without closing any . NDX files.
Creates a new database file sorted on the specified keys. By default, sorts are in ascending order and are case sensitive.
Stores an expression into one or more memory variables.
Computes the sum for all or for specified numeric fields in the default database. Output will include headings for all relevant fields with the sum for each field displayed immediately beneath the column heading.
Display the contents of an ASCII file.
Close previously open database or index files and place the specified database and index files into use for all subsequent commands. The file is indexed with respect to the first index file listed. To change the index file later in a session, use SET ORDER TO.
Removes all records from the active database file. BE CAREFUL. ZAP is equivalent to, but faster than, a DELETE ALL followed by a PACK.
Changes a character string that looks like a date to a date. A date cannot be entered directly at the prompt or from a file.
Changes a date expression to a character expression. Allows comparison to dates given in character format.
Gets the system date from DOS. If the system date was not entered correctly, date() will report the incorrect date.
Returns the numeric code for the day of the week.
Returns the numeric code for the month of the year.
Returns the name of the day of the week.
Returns the name of the month.
Changes all lowercase characters to upper case, while leaving the uppercase characters and nonalphabetic characters unchanged.
Changes all uppercase characters to lower case, while leaving the lowercase characters and nonalphabetic characters unchanged.
Remove blanks that occur before the first visible alphanumeric character. Useful for removing blanks that may have been entered by error.
Remove blanks that occur after the last visible alphanumeric character.
Takes the absolute value of the numeric expression.
Finds the maximum of two numeric expressions.
Finds the minimum of two numeric expressions.
Combines fields of dissimilar types for indexing, sorting, and searching operations. The first numeric expression is the number to be converted. The second numeric expression indicates the resulting string length (10 is the default for some operations). The third numeric expression indicates how many of the decimal places, if any, are to be carried into the string.
Converts character expressions to numeric expressions.
Returns the logical value .T. if the record pointed to by the record pointer has been marked for deletion.
Determines the number of the current record.
