Introduction to dBASE III Plus

This document is an introduction to the basic concepts of creating, editing, and managing databases using dBASE III Plus. The function key template included at the end of this manual provides shortcuts to commonly used functions in dBASE III Plus.

CONTENTS

  1. NOTATION
  2. INTRODUCTION
  3. dBASE III Plus BASICS
    Learn about the two modes in which dBASE III Plus can operate as well as how to interpret the information given on the screen.
  4. CREATING AND HANDLING A DATABASE
  5. Command Mode COMMANDS
    Any dBASE III Plus command, function, or other reserved word can be abbreviated to the first four characters. Filenames cannot be abbreviated. This section gives a listing of commands and their format.
  6. Assist Mode COMMANDS
    The Assistant prompts the user throught many dBASE III Plus commands. There are eight groups of commands.
  7. COMMANDS BY FUNCTION

NOTATION

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).

UPPER CASE
keywords

lower case
values are supplied by user

bold
keywords in the pull-down menu

[ ]
optional values; the square brackets are not part of the command

{ }
one of the enclosed elements must be chosen; the brace brackets are not part of the command

filename
name of a file including the drive and path if required

field
name of field in the active database

field list
list of field names in the active database separated by commas

variable
name (maximum 10 characters) assigned to a memory location; used to hold a data value that is typically an intermediate result from a calculation but is not part of the database

variable list
list of variable names separated by commas

condition
specifies a limited range for the command; see Command Structure section for details

scope
expression to specify how much of the database to use in the command; see Command Structure section for details

key
specific value in a field or a specific field


INTRODUCTION

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 BASICS

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.

Reading the Screen

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.

[Opening Screen]

Assist Mode Screen

The basic components of the Assist Mode screen are:

These last three lines are always present while you are using dBASE III Plus.

[Components of Assist Mode]

Components and Layout of Assist Mode

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:

  1. mode
  2. current disk drive
  3. file in use
  4. record pointer
  5. status of Ins and Del
  6. status of Caps Lock

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]

Command Mode Screen

File Types

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.

.DBF
database records (CREATE)

.DBT
database memo fields, has same filename as the associated database file (.DBF) (CREATE)

.FMT
entry screen format commands for custom entry forms (CREATE/MODIFY SCREEN)

.FRM
report format (CREATE/MODIFY REPORT)

.NDX
database index pointers (INDEX)

.PRG
program files, sequence of commands (ASCII file; any editor)

.QRY
query file (CREATE/MODIFY QUERY)

.SCR
entry screen format (CREATE/MODIFY SCREEN)

.TXT
normal DOS ASCII text file used for output (COPY TO)

.VUE
view file (CREATE/MODIFY VIEW)

Field Types

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:

  1. character
  2. numeric
  3. date
  4. logical
  5. memo

Command Structure

Most dBASE III Plus commands which are related to record processing have the following format:

command [[FIELDS] fields] [scope] [FOR clause] [WHILE clause]
The command always begins with the command name. Commands that can operate on a subset of fields in a record have a required or optional field list. The keyword FIELDS is required for some commands. The scope, FOR, and WHILE clauses restrict the records that the command will affect.

Scope

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

FOR/WHILE clause

FOR condition
choose all records that match the condition

WHILE condition
choose all records that match the condition until the first not matching record. Other records with this value may exist, but if they do not immediately follow the current record, they will not be processed. A WHILE is much faster than a FOR when using ordered data.

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
Examples:
gross_prof < 1000
success = 'y'
date_trans > = str(02/10/79)
You may join two or more simple expression together to form a compound expression:
         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
Examples:
.not. approved (Note: approved must be a logical value)
dtoc(work_date) >= "1/1/89" .and. dtoc(work_date) < "7/1/89"


CREATING AND HANDLING A DATABASE

Establishing Requirements

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.

Creating the Database Structure

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.

Using An Existing Database

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

Entering and Editing Data

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]

Edit Screen

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]

Browse Screen

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.

Displaying Data

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.

Sorting and Indexing the Database

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.

Query Files

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.

Simple Calculations and Statistics

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'.

Joining Two Databases

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).

Moving Files Across Packages

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.

Storing and Running a Sequence of Commands

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.


Command Mode COMMANDS

The many commands available in Command Mode are explained alphebetically in this section.

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.

Format:
? expression or
?? expression

Examples:
? 22*34

?? DATE( )


APPEND

Add records to the active database file.

Format:
APPEND [BLANK]

Examples:
append

append blank

APPEND FROM

Add records from another file to the active database file.

Format:
APPEND FROM database filename [FOR condition]
APPEND FROM foreign filename [TYPE] type [FOR condition]

where type is one of:

Examples:
append from jobsmore

append from jobsmore for name = 'White, J.'

append from source.dat type sdf

ASSIST

Change to Control Center Mode from Command Mode.

Format:
ASSIST

AVERAGE

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.

Format:
AVERAGE [field list] [scope] [FOR condition] [WHILE condition] [TO variable]

Examples:
average

average gross_prof

average gross_prof to profmean

average next 10

BROWSE

Presents a spreadsheet-like, full-screen view of records in the database for editing and appending records. Exit browse by pressing Control and End.

Format:
BROWSE [FIELDS field list]

Examples:
browse

browse fields prog_name, hrs_worked

CLOSE

Close files that are currently in use. (Similar to the USE command with no operands.)

Format:
CLOSE {ALL | DATABASES | INDEX}

Examples:
close all

close index

CONTINUE

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.

Format:
CONTINUE

Example:
locate for author = "Margaret Atwood"
display title
continue
display title

COPY

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.

Format:
COPY STRUCTURE TO filename [FIELDS field list]
COPY TO filename [scope] [[TYPE] extension] [FIELDS field list] [FOR condition] [WHILE condition]

where extensions for exported files are:

DELIMITED
DELIMITED WITH BLANK
SDF
WKS

Examples:
copy structure to duplist

copy to templist

copy to good for success = 'y'

copy to jobs type wks

copy to wpfile type sdf

copy to bad for .not. approved fields name, sex, age

COPY FILE

Duplicates any file.

Format:
COPY FILE filename1 TO filename2

Examples:
copy file clients.dbf to newlist. dbf

COUNT

Determines the number of records that meet a specific condition. If no condition is specified, COUNT determines the number of records in the database.

Format:
COUNT [scope] [FOR condition] [WHILE condition] [TO variable]

Examples:
count

count next 100 for gross_prof < 1000

CREATE

Creates new files of various types.

Format:
CREATE database filename
CREATE { QUERY | REPORT | SCREEN | VIEW | CATALOG} filename

Examples:
create jobs

create query names

create report jobdata


DELETE

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.

Format:
DELETE [scope] [FOR condition] [WHILE condition]

Examples:
delete record 10

delete for recno() > 100

delete for prog_name = 'White, J.'

DELETE FILE

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.

Format:
DELETE FILE filename

Examples:
delete file a:extra. dbf

DIRECTORY

By default, lists database filenames with the extension .DBF on the current directory.

Format:
DIR [drive:] [path] [filename]

Examples:
dir *.*

dir a:

DISPLAY

Displays the contents of a database file in an unformatted list.

Format:
DISPLAY [field list] [scope] [FOR condition] [WHILE condition] [TO PRINTER | TO FILE filename] [OFF]

Examples:
display

display prog_name, job_id, client_id off

display hrs_worked for prog_name = 'White, J.'

DISPLAY STRUCTURE

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.

Format:
DISPLAY STRUCTURE [TO PRINT]

Examples:
display structure to print

DO

Executes the specified command file. Any editor can be used to create the ASCII .PRG file containing the commands.

Format:
DO filename

Example:
do payanal

EDIT

Allows you to alter the contents of one or more records in the active database.

Format:
EDIT [scope] [FIELDS field list] [FOR condition] [WHILE condition]

Examples:
edit

edit record 12

edit for prog_name = 'White, J.'

edit while prog_name = 'White, J.'

ERASE

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.

Format:
ERASE {filename | ?}

Examples:
erase ?

erase tempfile. dbf

FIND

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.

Format:
FIND key

Example:
find 'White, J.'

INDEX

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.

Format:
INDEX ON key expression TO index filename

Examples:
index on last+first to alphlist

index on city+str(zip,5) to citylist

INSERT

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.

Format:
INSERT [BLANK] [BEFORE]

JOIN

Creates a new database file by merging the specified records and fields from two open database files.

Format:
JOIN WITH filename TO newfilename FOR condition [FIELDS field list]
where filename is the name of the other database in another work area

Example:
use client
select 2
use transact
select 1
join with transact to clientnew
for client_id=B->client_id
fields client_id, client, B->Date_trans, B->Total_bill, B->Order_id

LIST

Displays the contents of a database file in an unformatted list.

Format:
LIST [FIELDS field list] [scope] [FOR condition ] [WHILE condition] [TO PRINTER | TO FILE filename] [OFF]
LIST STRUCTURE [TO PRINTER | TO FILE filename]

Examples:
list off

list for .not. approved

list fields prog_name, job_id, client_id

list to printer

list structure to file dbstruct.lis

LOCATE

Searches the active database file for a record that matches the specified condition. LOCATE works on unindexed databases: sorted and unsorted.

Format:
LOCATE [FOR] condition [scope ] [WHILE condition ]

Examples:
locate for prog_name = 'White, J.'


MODIFY

The command can be used to modify database file structures, query, report, screen, and view files.

Format:
MODIFY { COMMAND | QUERY | REPORT | VIEW} filename MODIFY STRUCTURE

Examples:
modify structure

modify command labprog

MODIFY FILE

The command can be used to modify any ASCII file.

Format:
MODIFY FILE filename

Examples:
modify file sample.doc

PACK

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.

Format:
PACK

QUIT

Quit closes all databases, index and other files properly and returns to DOS.

Format:
QUIT

RECALL

Removes the deletion marker from the specified records which are marked for deletion in the active database.

Format:
RECALL [scope] [FOR condition] [WHILE condition]

Examples:
recall record 10

recall for recno() > 100

REINDEX

Rebuilds all active index (.NDX) files in the current work area based on the command used to construct the original index files.

Format:
REINDEX

RENAME

Gives a new name to a file.

Format:
RENAME filename1 TO filename2

REPLACE

REPLACE changes the contents of the specified fields.

Format:
REPLACE field WITH expression [, field WITH expression] [scope] [FOR condition] [WHILE condition]

Examples:
replace job_title with upper(job_title)

replace all job_title with lower(job_title)

REPORT

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.

Format:
REPORT FORM filename [PLAIN] [HEADING character expression] [NOEJECT] [scope] [FOR condition] [WHILE condition] [TO PRINTER | TO FILE filename]
where filename is name of report form file
PLAIN no headers or footers printed except on first page
HEADING defines extra heading to be printed on the first line of each page
NOEJECT suppresses the initial page eject for the first page

Examples:
report form client to file clientrep

RUN

Executes a DOS command or any program which can be executed by DOS, from within dBASE III Plus.

Format:
RUN command

Example:
RUN dir a:*.*


SELECT

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.

Format:
SELECT [n | alias]
where n number of the work area, 1 to 10 alias alphabetic work area designator, A to J

Example:
select B

SET

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.

Format:
SET

SET BELL ON/OFF

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.

Format:
SET BELL {OFF | ON}

SET DEFAULT TO

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.

Format:
SET DEFAULT TO drive

Example:
set default to b

SET DELETED

Determines whether records that are marked for deletion are included (OFF) or ignored (ON) by other dBASE III Plus commands. Default is off.

Format:
SET DELETED [ON | OFF]

SET EXACT

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.

Format:
SET EXACT {ON | OFF}

SET FIELDS

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.

Format:
SET FIELDS {ON | OFF | TO field list | TO ALL}

Examples:
set fields to name, balance

set fields on

set fields off

SET FILTER TO

Limits the display of records to those that meet a specified condition.

Format:
SET FILTER TO [FILE filename] [condition]

Examples:
set filter to date_trans >= str(02/10/79)
list

set filter to file dateqry

SET FUNCTION

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.

Format:
SET FUNCTION key# TO expression

Example:
set function 10 to "set bell off"

SET ORDER TO

Sets up any open index file as the master index, or removes control from all open indexes, without closing any . NDX files.

Format:
SET ORDER TO [n]

Examples:
set order to

use sales index datedx, orderdx, clientdx
set order to 3

SORT TO

Creates a new database file sorted on the specified keys. By default, sorts are in ascending order and are case sensitive.

Format:
SORT TO filename ON field1 [/A] [/C] [/D] [, field2 [/A] [/C] [/D] . . . ] [scope] [FOR condition] [WHILE condition]
where /A ascending order (default) /C ascending order; no difference between upper and lower case /D descending order

Examples:
sort to newlist on prog_name

sort to templist on hrs_worked /d

sort to himarks on marks for marks > 75

STORE

Stores an expression into one or more memory variables.

Format:
STORE expression TO variable list

SUM

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.

Format:
SUM [field list] [scope] [FOR condition] [WHILE condition] [TO variable]

Examples:
sum

sum gross_prof

sum gross_prof to proftot

sum next 10


TYPE

Display the contents of an ASCII file.

Format:
TYPE filename

USE

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.

Format:
USE [database filename] [INDEX index filename list ]

Examples:
use jobs

use jobs index namefile, billfile

use

ZAP

Removes all records from the active database file. BE CAREFUL. ZAP is equivalent to, but faster than, a DELETE ALL followed by a PACK.

Format:
ZAP


DATE AND TIME FUNCTIONS

Character to Date

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.

STORE CTOD('12/25/88') TO CHRISTMAS
Stores the date 12/25/88 to the variable CHRISTMAS. The date now has a numeric meaning to allow for comparison between dates

LIST FOR WORK_DATE > CTOD('6/29/89')
List all records with a date greater than June 29, 1989 in the field WORK_DATE (type date).

SUM(HRS_WORKED) FOR WORK_DATE >= CTOD('1/1/89') . AND. WORK_DATE < CTOD('7/1/89')
Total the hours worked on dates in field WORK_DATE between Jan. 1, 1989 and June 30, 1989, inclusive

Date to Character

Changes a date expression to a character expression. Allows comparison to dates given in character format.

COPY TO JANJNE FOR DTOC(WORK_DATE) >= "1/1/89" .AND. DTOC(WORK_DATE) < "7/1/89"
Converts the contents of the WORK_DATE field to a character expression. If the expression is between Jan. 1, 1989 and June 30, 1989, the record is copied to the file JANJNE.DBF

Date

Gets the system date from DOS. If the system date was not entered correctly, date() will report the incorrect date.

LIST FOR WORK_DATE < DATE( )
List the records for which the WORK_DATE is lower that the system date.

Numeric Day of the Week

Returns the numeric code for the day of the week.

LIST FOR DOW(WORK_DATE) = 2
List the records for which the WORK_DATE is 2 or Monday. WORK_DATE is type date

Numeric Month of the Year

Returns the numeric code for the month of the year.

LIST FOR MONTH(WORK_DATE) <= 6
List the records for which the WORK_DATE is the first 6 months. WORK_DATE is type date


CHARACTER FUNCTIONS

Character Name of the Day of the Week

Returns the name of the day of the week.

LIST FOR CDOW(WORK_DATE) = "SUNDAY"
List the records for which the WORK_DATE is SUNDAY. WORK_DATE is type date

Character Name of the Month

Returns the name of the month.

? CMONTH(CHRISTMAS)
Returns the value "December" if the memory variable CHRISTMAS contains 12/25/88

Convert to Uppercase

Changes all lowercase characters to upper case, while leaving the uppercase characters and nonalphabetic characters unchanged.

REPLACE JOB_TITLE WITH UPPER(JOB_TITLE)
Converts the contents of the JOB_TITLE field to upper case for the current record

Convert to Lowercase

Changes all uppercase characters to lower case, while leaving the lowercase characters and nonalphabetic characters unchanged.

REPLACE JOB_TITLE WITH LOWER(JOB_TITLE)
Converts the contents of the JOB_TITLE field to lower case for the current record

Trim Leading Blanks

Remove blanks that occur before the first visible alphanumeric character. Useful for removing blanks that may have been entered by error.

REPLACE JOB_TITLE WITH LTRIM(JOB_TITTLE)
Converts the contents of the JOB_TITLE field by eliminating the leading blanks for all records.

Trim Trailing Blanks

Remove blanks that occur after the last visible alphanumeric character.

REPLACE FULL_NAME WITH RTRIM(FIRST_NAME)+' '+RTRIM(LAST_NAME)
If the FIRST_NAME field of the current record is of width 9 and contained 'Suzanne ', this command would concatenate "Suzanne" with a blank and the trimmed contents of the LAST_NAME field. If LAST_NAME contained 'Smith ', "Suzanne Smith" will be stored in the FULL_NAME field


MATHEMATICAL FUNCTIONS

Absolute Value

Takes the absolute value of the numeric expression.

LIST FOR ABS(CURRENT_CNT - OLD_CNT) < 100
List all records where the difference between the CURRENT_CNT and the OLD_CNT is less than 100.

Maximum Value

Finds the maximum of two numeric expressions.

REPLACE SALARY WITH MAX(SALARY*1. 05, NEW_FLOOR)
Replace the SALARY with the maximum of the current SALARY*1. 05 or the NEW_FLOOR.

Minimum Value

Finds the minimum of two numeric expressions.

LIST FOR MIN(SALARY,MEAN_SALARY) >= 35000
List all records where the minimum of the SALARY field and the MEAN_SALARY field is greater than or equal to $35,000


OTHER TYPE FUNCTIONS

Value to String

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.

LIST FOR STR(ZIP,4) = '1575'
List records with 1575 in the ZIP field
INDEX ON STATE+STR(ZIP,6) TO ADDRFILE
Converts the ZIP number to a character expression and concatenates this to the STATE character expression to create a single character expression to be used to build the index JOBFILE.NDX

String to Value

Converts character expressions to numeric expressions.

LIST FOR VAL('1575') = ZIP
List records with 1575 in the ZIP field, a numeric field


RECORD POINTER FUNCTIONS

Check for Deleted Records

Returns the logical value .T. if the record pointed to by the record pointer has been marked for deletion.

LIST FOR DELETED( )
Lists the records marked for deletion

Record Number

Determines the number of the current record.

LIST FOR RECNO( ) < 50
Lists all records that have a record number less than 50.

Assist Mode COMMANDS

Set Up

Database File
open an existing database file and index files

Format for Screen
use a custom screen for data entry and edit

Query
use a query file

Catalog
add file opened to catalog file

View
open a view file

Quit dBASE III Plus
end the session; save all information

Create

Database File
create a database file, define its structure, and allow entry of records

Format
create a custom screen for data entry and edit

View
create a relation between files as a working environment and save it to a file

Query
create a filter condition and store it in a query file

Report
create a report form that defines the report layout

Label
create a label form that defines the label layout

Update

Append
add records to the database

Edit
edit database records; viewed one at a time

Display
list specified records and fields one screen at a time

Browse
display the database in a multiple record format

Replace
change contents of the specified database fields

Delete
mark specified records for deletion from the database

Recall
unmark specified records previously marked for deletion

Pack
permanently remove all records marked for deletion

Position

Seek
locate record using field on which the database is indexed

Locate
locate record meeting a specified condition; database file not indexed

Continue
search for next record using condition specified by LOCATE command

Skip
move current record pointer backward or forward

Goto Record
explicitly position the current record pointer

Retrieve

List
display specified fields; no pauses

Display
display specified fields; pause at each screen

Report
use a report form to prepare a report of selected records

Label
use a label form to prepare labels of selected records

Sum
sum specified numeric fields

Average
average specified numeric fields

Count
count the number of records meeting a specified condition

Organize

Index
create an index (.NDX) file

Sort
create a copy of the database sorted on one or more fields

Copy
duplicate any type of file

Modify

Database file
change the structure of an existing database

Format
change an existing custom screen format

View
change an existing view file

Query
change an existing query file

Report
change an existing report form (.FRM)

Label
change an existing label form (.LBL)

Tools

Set drive
set the default drive for the data disk

Copy file
copy files

Directory
display the names of files

Rename
rename a disk file

Erase
delete a disk file

List structure
display the structure of the active database file

Import
create dBASE files from .PFS files

Export
create .PFS files from dBASE III Plus files


COMMANDS BY FUNCTION

File Creation Commands

COPY
duplicate database file

COPY FILE
duplicate any file

COPY STRUCTURE
duplicate structure of database

CREATE filename
create a database (filename.DBF)

CREATE QUERY filename
create a custom filter or query file (filename.QRY)

CREATE REPORT filename
create a report definition file (filename.FRM)

CREATE SCREEN filename
create a screen format file (filename.FMT) and a custom screen format (filename.SCR)

CREATE VIEW filename
create a relation between files as a working environment and save it to a file (filename.VUE)

INDEX ON
define an order for the active database (.NDX)

JOIN
combine data from two different sources (.DBF)

MODIFY COMMAND filename
create/edit program files using internal editor (.PRG)

MODIFY FILE filename
create/edit any ASCII text file

SORT TO filename ON
reorder active database creating a new file (.DBF)

File Modifying Commands

DELETE FILE
erase named file

ERASE
erase named file

MODIFY COMMAND filename
create/edit program files (filename.PRG)

MODIFY FILE filename
create/edit any ASCII text file

MODIFY QUERY filename
create/edit a custom filter or query (filename.QRY)

MODIFY REPORT filename
create/edit a report form file (filename.FRM)

MODIFY SCREEN filename
create/edit specified screen file (filename.SCR)

MODIFY STRUCTURE
edit the structure of a database; no loss of data

MODIFY VIEW filename
create/edit a view file (filename.VUE)

RENAME
change the name of a file

File Manipulation Commands

CLOSE ALL|filetype
close all files or files of a specific type currently in use

REINDEX
rebuild all active index (.NDX) files

SELECT
switch between active work areas

SET ORDER TO
change the priority of the active index (.NDX) files

SORT TO filename ON
reorder active database creating a new file (.DBF)

USE
open and close individual databases

Data Addition Commands

APPEND
add records to a database file

APPEND BLANK
add one record to a database file

APPEND FROM file
add records to a database file from another file

BROWSE
menu-driven full-screen database editing/appending

INSERT
add one record to a database file

Data Editing Commands

BROWSE
menu-driven full-screen database edit

DELETE
mark records for removal

EDIT
edit the contents of records

PACK
permanently delete all records marked for removal

RECALL
unmark a record which was marked for removal

REPLACE
change the contents of a database field

ZAP
permanently deletes all records from database

Data Display Commands

?
display an expression list on the next line

??
display an expression list on the current line

AVERAGE
compute arithmetic mean

BROWSE
display active database for editing/appending

COUNT
count the number of records that meet some criteria

DISPLAY
display contents of database

LIST
display contents of database, screen pause

REPORT FORM filename
display information using a report form file

SUM
compute the sum for specified fields

TYPE filename
type any ASCII file to the screen

Data Location Commands

FIND
locates specified record, indexed file

LOCATE
locates specified record, unindexed file

Debugging Commands

ASSIST
change from Command Mode to menu mode

DIR
list files

DISPLAY
display contents of database

DISPLAY STRUCTURE
display structure of database

LIST
display contents of database

LIST STRUCTURE
display structure of database

Program Interfacing Commands

DO filename
execute specified command file (filename.PRG)

RUN
execute a DOS command

QUIT
close all files and return to DOS from Command Mode

Function Key Template

Function keys F5 through F10 require a database (.DBF) file to be selected before issuing the command.

F1=Help, F2=Assist, F3=List, 
F4=Directory (Dir), F5=Display Structure, F6=Display 
Status, F7=Display Memory, F8=Display, F9=Append, F10=Edit


[Return to the top of this document]