FOXPRO (Aj)

PROGRAMMING IN FOXPRO
INTRODUCTION TO DATABASE
What is a database ?
A database is an organised collection of related information. Any unorganised
information is just a pile or dump, and it cannot be called a database. A database serves as an
information base and can use it for:
? Retrieving desired information
? Taking meaningful decision
? Re-organising information
? Processing information
Data Structure
It is a term used to refer the logical structuring of data. So, in a database, information is
stored in records (rows) and fields (column).
RECORD
Collection of data or related facts is a Record. Each data makes up fields of the
database. Each field has different field name, type and size. The relevant information is stored
in the fields.
Database Management System (DBMS)

A Database Management System is a tool for managing information stored in the
database. Managing a database includes the following functions.
1.
2.
3.
4.
5.
6.
1.
File Creation
Modifying a file structure
Adding records
Deleting records
Selecting records
Printing reports
File Creation :
Creating a database file means defining the data to be maintained and organising the
database to accommodate the data keeping requirements to be met by the system.
Once the file is designed, data can be added to it.
2.
Modifying a File
Modifying a file means changing its structure. To add or delete fields to the records in a
file or change the width of one or more fields.
Ordnance Factories Institute of Learning Ambajhari
2
3.
Adding Records
To add records in a file, you enter data in the fields, record by record.
4.
Deleting Records
Unnecessary records may be deleted or removed from the data file.
5.
Selecting Records
The ability to select just those records which meet certain criteria in an essential
capability of any DBMS.
6.
Printing Reports
Most data management operation require the production of printed reports that show the
status of data in the file in both detailed and summary form.
Introduction to FoxPro
FoxPro is one of the leading Data Base Management System (DBMS) software for PC.
This is an enhanced and updated version of the FoxBASE+ software.
One of the first DBMS for the PC was developed by ASHTON-TATE in 1979-80 that was
called dBASE II. Then that company introduced the updated version of dBASE II in 1984 that
was called dBASE III. Soon, dBASE III became the industry standard. Later, ASHTON-TATE
released the enhanced and updated version of dBASE III, i.e. dBASE III PLUS.
To get a share of the expanding DBMS market for the PC segment, Fox Software Inc.
came out with FoxBASE+ that was almost compatible with dBASE III PLUS. Then it was
updated and the new software was called FoxPro. Gradually, new versions of FoxPro were
released to make it more powerful and user friendly. However, this has also widened the gap
between dBASE and FoxPro, and now they are only partly compatible.
FoxPro is also called the Relational Database Management System (RDBMS). It helps
you to design database files as per the requirements and as per the specified format. It also
helps you enter and manage data in database files. FoxPro also helps you to edit, view, change
data in the database through simple built-in commands. Once the database is ready, you can
use it to retrieve selected information from it. The retrieved information can be displayed or
printed as per the desired report format. The best part is that the data stored in the database is
flexible. i.e. you can change / modify the contenets as well as the structure of a database any
number of times. FoxPro has many other powerful commands for managing multiple database
files, protection and documentation of files.
Several versions of FoxPro are available for use. Earlier versions of FoxPro were DOS
based, but since version 2.5, FoxPro can run under four different operating platforms DOS,
Windows, UNIX and Macintonsh. Because of differences in these operating environments,
there are a few differences among these four versions of FoxPro 2.5.
In order to reduce the gap between dBASE IV and FoxPro, the new release of FoxPro
FoxPro 2.6 is a definite step in that direction. With FoxPro 2.6 you can directly use all dBASE
IV database, screen, report and label files. FoxPro 2.6 will automatically convert dBASE IV files
to FoxPro format, whenever necessary.
Designing the Database Structures

The first step in setting up the file structures is to identify all the items of information that
your FoxPro application must contain. The list of necessary data items includes
?
?
?
?
Items explicitly requested by the users

Items included on any report or used in any calculation
Items that determine the record sequence for a report or processing operation
Items that contribute to record selection criteria
Having identified all the data items, you must decide how many database files you need
and which items belong in each database. When you actually set up the file structures, you
must assign each field a name and specify the data type, field length, and for numeric fields, the
number of decimal places. If you wish, you can also select the fields that will serve as the basis
for indexes; several of the necessary index keys are usually evident from the outset, but you will
undoubtedly add more indexes as the system evolves.
The first-and, for a beginner, the most difficult-step is deciding how to group the data
items into database files. The file structures should be setup to support the overall goals for any
database application :
?
?
?
?
?
?
?
?
To accommodate the required items of information

To carry out the required calculations and processing operations
To produce the required output
To achieve the best performance possible with the users hardware and operating
system
To optimize use of hard-disk space
To maximize the accuracy and integrity of the data
To allow for future expansion and enhancements
To facilitate modification by other users or programmers
Defining Database Structure
?
?
?
While defining the structure, you have to specify the following details:
A unique field name for each field, i.e. each field must have a name that is not being used
by any other field.
Field type, i.e. the type of data, such as numeric or character to be stored in each field.
Width of each field. For numeric field, also specify the number of decimal digits.
The fields in each database file should store items of information that belong together
logically. A FoxPro 2.5 or 2.6 database can contain up to 255 fields totaling up to 65,500
characters. In the Extended version, you can open 255 database simultaneously, and in the
Standard version, you can open 25. Because you can open multiple databases and link them
based on common fields, you can avoid a great deal of redundancy by eliminating from each
database most items of information that you can look up in other files. For example, there is no
need to store a persons name in both the Names File and the Supplementary Information File,
and there is certainly no need to store the name in Contacts File or Transaction File.
Naming Files and Fields :

Before you create the database files for your application, you should begin to develop a
consistent set of naming conventions for database files, fields, and indexes. These guidelines
should be simple enough that you, and other programmers who may modify your system, can
readily remember the rules. They also must be general and flexible enough to extend to all the
other types of files and objects that make up a complete application.
Within the limitation of an eight-character file name, two or three characters of which are
devoted to the application prefix, try to assign names that clearly indicate the purpose or use of
the files.
FoxPro field names can be up to ten characters long and can contain any combination of
letters, digits, and underscores, except that the first character must be a letter. Spaces and
punctuation marks are not allowed in field names. As with file names, you should strive to
invent field names that clearly indicate the purposes of the fields.
Choosing Data types :

FoxPro supports six data types for database fields.
Character
-This data type accepts all the standard ASCII characters, that is, all the
characters that you can generate by pressing keys on the keyboard--including letters, digits,
punctuation marks, graphics, foreign language, and line-and box-drawing characters.
Character fields can be up to 254 characters long.
Numeric, float These fields can hold only numeric data. Thus you can only store digits 0 to 9,
decimal point (.) and the optional leading plus (+) or minus (-) sign. The maximum length is
20 digits, including up to 18 decimal places. FoxPro stores more significant digits for the
float type data as compared to the numeric data. Therefore, the float type data (field) is
better for scientific calculations. However, since FoxPro is generally used for business
applications, the float fields are not commonly used.
Date - This type accepts only valid calendar dates. FoxPro stores the month, day, and fourdigit year (although most date display formats show the year as a two-digit number).
Logical This type accepts only the logical values true(yes), entered as T, t, Y, or y, and
false (no), expressed as F, f, N, or n. Logical fields are particularly useful for storing
information that has only two states.
Memo This type accepts an unlimited amount of data of any kind (subject to memory
and disk space limitations), usually free-form text. The data to be stored in the memo field is
not stored in the database file but in an auxiliary database file with the same name but with
the extension name .fpt. FoxPro allots a ten byte space in the main database file to store
the location of the memo data in the auxiliary database file. Memo fields are useful in
reducing the size of a database file. For entering data into the Memo filed, press CTRL +
Home (or) CTRL + PgDn.
Setting field widths :

All fields stored in the .DBF file are fixed in width-that is, the amount of space they
occupy is always exactly the same as the field width defined in the database structure, whether
or not the user has entered any data. When you define a FoxPro database, you need only
specify the widths for character, numeric, and float fields; indeed, you cannot enter a width for
any other field type.
Assigning Index Key fields :

Indexes play a central role in any FoxPro application. Indexes serve the following
purposes:
? They enable you to view records in sequences other than the order in which they were
entered, without physically rearranging the records.
? They support the linkages between files that enable you to view matching records from
multiple related databases.
? They enable you to conduct fast searches to find individual records based on unique
values in one or more fields.
? Even in a larger database, they enable you to quickly process a small group of records
with identical or similar values.
?
They support the fast, efficient record selection operations carried out by FoxPros
Rushmore query optimizer.
Re-evaluating the field list :

The final critical look at your file design worksheets to verify that you have not omitted
any essential fields and that each of the fields listed is required. One common pitfall, especially
when you are guided by the wish lists of enthusiastic users, is to include many fields that really
do not belong in the database.
THE FOXPRO USER INTERFACE

FoxPro does not force you to choose between the menu and command modes- both are
usually available, and you can use any combination of commands and menu selections to
accomplish a given task. The System menu bar is always visible at the top of the screen, and
the Commands window appears by default in the lower-right corner. You can operate the
FoxPro menus with a mouse, by using function keys and cursor movement keys, or by pressing
Alt - key combinations. In some cases, you can execute menu commands directly by pressing
Ctrl - key combinations without first activating the menu system.
The FoxPro Windowing Environment :

The FoxPro window components and controls are labelled in the Command
window pictured as below :
Selected bar
Menu popup
Title bar
Menu bar
Window
Scroll bars
When you start up FoxPro, only one window, the Command window, is active. All other
editing activities also take place in windows, and you may have as many windows open at once
as you wish. Whenever you start up the FoxPro text editor, invoke any of the form design tools (
such as the Screen Builder, Menu Builder, or Report Writer), or type a command that requires a
window, such as BROWSE, FoxPro automatically opens a new window for this activity, leaving
all other active windows open and placing the new window on top.
CONSTRUCTING EXPRESSIONS AND CONDITIONS

An expression is a command component made up of database fields, memory variables,
constants, function, and operators, combined in accordance with the rules of FoxPro syntax.
An expression always evaluates to a single quantity of a particular data type- character,
numeric, date, or logical - although it contain components of several different data types,
including memo fields, you can use an expression almost anywhere that a quantity of the same
data type is permitted. Conditions will play an important role in work with FoxPro, beginning in
the earlier stages of application development, where you will use them in FOR and WHILE
clauses, filters, RQBE , and conditional index expressions to describe record selection criteria.
Displaying data with the ? command
When you work in the Command window, the ? command provides a handy method for
displaying the result of evaluating any expression. This command, which you will use
extensively in programs, displaying or prints the results of evaluating any list of expressions on
the next available screen or printer line. For example, you could display the result of adding 2
and 3 with the following command
? 2 + 3
In most cases, your expressions will include references to database fields and other
FoxPro objects. For example, with the ACE Names File open, you could display the contents of
the NAME field from the current record with the following :
? NAME
You can display multiple expressions by listing them in the ? command, separated by
commas, as follows:
? IDCODE, NAME, CITY, STATE
The following command displays a persons ID code, and the length of time (in days)
between ACEsfirst and last (most recent) contacts with the person:
? IDCODE, NAME, LASTCONT - FIRSTCONT
The following command displays current date at a specified column (say 20).
? DATE( ) AT 20
The ?? Command :
The ?? command is a minor variation of the ? command. Whereas the ? command
displays information in the next row, ?? displays it in the same row. In other word, FoxPro
issues a line-feed before displaying data with ?, whereas it does not issue line-feed with ??.
??
NAME
??
LACALITY
??
CITY
Controlling printer with ??? :
Regardless of the status of SET PRINTER, the ??? command sends the codes to the
printer without affecting the print head position. The following command sends the codes to the
printer without affecting the print head position. The following command sends the code for
selecting the condensed printing mode on Epson and compatible printers.
???
CHR(015)
The ASCII code for selecting the condensed printing mode is 015. The CHR function
returns (passes) the ASCII code 015 to the ??? command, that sends it to the printer.
Using Operators :
Operators are symbol that represent specialized operations that FoxPro can carry out.
All the FoxPro operators as they are used with data of various types you are familiar with the
arithmetic operators and the symbols used to represent them: addition (+), subtraction (-),
multiplication (*), and division (/). FoxPro can also raise a number to a power, using either ^ or **
as the exponentiation operator. For example, you could express 2 cubed (2 raised to the third
power) as
2 ** 3
or
2 ^3
You can use the modulus operator to compute the remainder that results from dividing
one number by another. This operator is handy for carrying out unit-of-measure conversions.
For example, to convert 87 inches to feet and inches , you would divide the number of
inches(87) by 12; the number of whole feet is the integer portion of the result, and the number of
inches is the remainder.
You can use the INT function, which is described later to obtain the integer portion of the
result of the division, and use the modulus operator to compute the remainder, in an expression
like the following:
87 % 12
The notation of carrying out calculations on character, date, or logical data may at first
seem foreign, but the fact that FoxPro uses some of the familiar arithmetic operator symbols to
represent these calculations should help to alleviate your initial confusion. In most cases these
symbols represent operations that are roughly equivalent to their mathematical counterparts.
For example, adding two character strings together means combining them end-to-end, with no
intervening spaces. For a particular record in the Names File, the expression
CITY + STATE + ZIP
might evaluate to
SAN FRANCISCO
CA94101
The spaces between San Francisco and CA result from the fact that the CITY field is
longer (20 Characters) than its contents (13 characters). If you use the - operator instead of +,
FoxPro removes the trailing blanks from all but the last character string, and combines all the
blank spaces at the end of the resulting string. You can use this operator when you must
produce character strings of a given length but with no embedded blank spaces. For the same
record used in the previous example, the expression
CITY - STATE - ZIP
Would evaluate to
SAN FRANCISCOCA94101
Note that the result of evaluating this last expression includes seven trailing spaces, derived
from the CITY field
The FoxPro Operators
Operator
Data Types
^ or **
*
/
%
+
=
==
N, F
N, F
N, F
N, F
N, F
C
D+N
N, F
C
D-N
C, M, N, F, D
C, M
Meaning
Exponentiation
Multiplication
Division
modulus
Addition
Concatenation
Adds days to a date
Subtraction
Concatenation, embedded blanks combined
at end
Subtracts day from a date
Equal to
Equal to and equal in length
10
Operator
Data Types
Meaning
<>, #, or !=
>
>=
<
<=
$
NOT or .NOT.
AND or .AND.
OR or .OR.
C, M, N, F, D
C, M, N, F, D
C, M, N, F, D
C, M, N, F, D
C, M, N, F, D
C, M
L
L
L
Not equal to
Greater than
Greater than or equal to
Less than
Less than or equal to
Substring (contained within)
.T. if following expressions .F.
.T. if both expressions are .T.
.T. if either expression is .T.
When you combine two conditions with AND, the resulting condition is .T. only if both the
component conditions are true. For example, the following display records in the Names Field in
which the state is California and the last contact date was January 1,1992:
LIST FOR STATE = CAOR LASTCONT > {01/01/92}
When you combine two conditions with OR, the resulting condition is .T. if either of the
component conditions is true. For example, the following selections all people in California,
regardless of their last contact date, together with all those whose last contact date was after
January 1, 1992, regardless of where they live:
LIST FOR STATE = CAOR LASTCOUNT >{01/01/92}
The NOT operator is used to negate a condition - that is, to select all records in which
the condition is not true. For example, the following command selects records in which the state
is either California nor Oregon.
LIST FOR STATE <> CAANDF STATE <> OR
You can also use the NOT operator to select records in which a logical field has the
value .F. for example, you can display all the records from the Names File in which the MAIL
field is .F. with the following :
LIST FOR NOT MAIL
*****
11
FoxPro Functions
A function is a named operator that adds to the FoxPro language the ability to perform
more specialized calculations or operations than you can with explicit expressions constructed
using only the operators. Each function requires a specific type of input performs a particular
transformation on this input, and yields a result a specific type of output.
Every FoxPro function returns as output a single quantity of a particular data type, and
you can use a reference to the function anywhere a quantity of that data type is permitted. A
function reference consists of the function name followed by a pair of parentheses that enclose
the input. A function may require one input, more than one, or none, but even when you do not
provide input, you always include the parentheses to differentiate the function reference from a
field or memory variable with the same name.
Mathematical Functions
SQRT( )
SQRT( ) returns the square root of a positive numeric expression. Enclose the numeric
expression within parenthesis.
Examples:
? SQRT(49)
7.00
? SQRT(100)
10.00
LOG( ) and LOG10( )
FoxPro has two functions to calculate logarithm of a positive numeric expression. LOG()
returns the natural logarithm (to base e) and LOG 10( ) returns the common logarithm (to base
10)
Examples:
? LOG(100)
4.61
? LOG10(100)
2.00
12
INT( ), FLOOR( ) and CEILING( )

The INT( ) function returns the integer part of a numeric expression. It leaves out any
decimal part in the expression.
Examples:
NUM = 1234.5621
? INT(NUM)
1234
The FLOOR( ) function is similar to INT( ). It returns the nearest integer that is less than
or equal to the specified numeric expression. For instance, ? FLOOR(NUM) too displays 1234.
The CEILING( ) function, on the other hand returns the nearest integer that is greater
than or equal to the specified numeric expression. For instance, ? CEILING(NUM) displays
1235.
ABS( )
The ABS( ) function returns the absolute value of a numeric expression. That is, it
converts a negative expression to the corresponding positive number.
Examples:
? ABS(-124.45)
124.45
? ABS(NUM)
1234.5621
ROUND( )
ROUND( ) rounds off the numeric expression to the specified number of decimal places.
For example, the following command rounds off the NUM variable to whole number (i.e., 0
decimal places) and stores the result in the NUM2 variable.
? ROUND(NUM, 0)
1235
? ROUND(NUM, 1)
1234.6
You can round off a numeric expression to 10s, 100s, 1000s, etc. by specifying the
negative decimal places with ROUND( ). For instance,
? ROUND(NUM, -2)
1200
? ROUND(NUM, -3)
1000
13
MIN( )
The MIN( ) function returns the expression with the lowest value. You can use two or
more expressions with MIN( ). The expressions can be of numeric, character or date type.
However, all expressions used with MIN( ) must be of the same type.
Examples:
? MIN(12, 15, 9)
9
? MIN(CA, AZ, BA, ZD)
AZ
MAX( )
The MAX( ) function is similar to MIN( ), except that it returns the expression with the
highest (maximum) value. You can use two or more expressions of numeric, character or date
type with MAX( ).
Examples:
? MAX(12, 15, 9)
15
? MAX(CA, AZ, BA, ZD)
ZD
BETWEEN( )
BETWEEN( ) determines if the value of an expression lies between the values of two
other expressions of the same data type. The expressions may be of numeric, character or
date type.
Example:
X = DATE( )
Y = DATE( ) + 1
Z = DATE( ) - 1
? BETWEEN(X, Z, Y)
.T.
BETWEEN( ) checks if the date variable X is between Y and Z. Since, X (current date)
lies between Y (tomorrows date) and Z (yesterdays date), the command displays .T.
MOD( ) and %
The MOD( ) function returns the remainder from a division of two numbers.
Example:
? MOD( 1965, 100 )
65
You can also use %, the modulus operator to get the remainder from a division of two
numeric expressions.
14
Example:
? 1965 % 100
65
EXP( )
The EXP( ) function returns the value of en.
Example:
? EXP(1)
2.72
SIGN( )
The SIGN( ) function returns the numeric value of 1, -1 or 0 depending on the sign of a
numeric expression. It returns 1 if the numeric expression is positive; -1 if it is negative; and 0 if
it is equal to 0.
Examples:
STORE 0 to MARKS
? SIGN(MARKS)
0
? SIGN(20.3)
1
? SIGN(-12.34)
-1
LEN( )
The LEN( ) function returns the length of a specified character expression. Example:
CH = RAJAN
? LEN(CH)
5
String Functions
TRIM( )
TRIM( ) returns the specified character expression after removing all trailing blanks. You
can use this function in expressions that combine several character string on a report or
screen display if you wish to eliminate unsightly embedded spaces.
Example:
? TRIM( RTI
)
RTI
15
UPPER( )
UPPER( ) returns the specified character expression in upper-case.
Example:
? UPPER(Regional)
REGIONAL
LOWER( )
LOWER( ) returns the specified character expression in lower-case.
Example:
? LOWER(Regional)
regional
PROPER( )
PROPER( ) returns the specified character expression with the first letter of each word in
uppercase and the remaining characters in lowercase.
Example:
? PROPER(ORDNANCE FACTORY)
Ordnance Factory
SUBSTR( )
SUBSTR( ) returns a specified number of characters from the specified character
expression or memo field.
Example:
? SUBSTR(ORDNANCE FACTORY, 10, 4)
FACT
STR( )
STR function is used to convert a number to a character string, and VAL to make the
opposite conversion. The command accepts three inputs- the number to be converted, the total
length of the resulting character string, and the number of decimal places.
Example:
? STR(1234.12, 6, 1)
1234.1
Time & Date Functions:

TIME( )
The TIME( ) function returns the current system time.
Example:
? TIME( )
15:48:32
16
DATE( )
The DATE( ) function returns the current system date.
Example:
? DATE( )
21 / 04 / 03
DAY( )
The DAY( ) function returns the numeric day value from a date expression. Example:
STORE DATE( ) TO CURRENT
21/04/03
? DAY(CURRENT)
21
MONTH( )
The MONTH( ) function returns the numeric value of the month from any date
expression. Example:
? MONTH(CURRENT)
4
CMONTH( )
The CMONTH( ) function returns the name of month (as character expression) from any
date expression.
Example:
? CMONTH(CURRENT)
April
YEAR( )
The YEAR( ) function returns the numeric year from any date expression. It always
returns the year with the century, regardless of the setting (ON or OFF) of SET CENTURY.
You can use a date expression (field, variable etc.) with YEAR( ). You can also use a literal
date string with YEAR( ).
Example:
? YEAR(CURRENT)
2003
? YEAR({12/06/95})
1995
17
DTOC( )
The DTOC( ) (Date to Character) function is an important function that converts a date
type data to characters.
Example:
STORE DTOC(CURRENT) TO TODAY
21/04/03
CTOD( )
The CTOD( ) (Character to Date) is the reverse of DTOC( ). That is, it converts a date
stored as character data to a date-type data. An interesting feature is that it does not allow you
to store invalid dates. The following commands first create a character variable CH_VAR with
09/08/95and then convert it to a date variable DATE_VAR.
Example:
CH_VAR = 09/08/95
STORE CTOD(CH_VAR) TO DATE_VAR
DOW( )
The DOW( ) (Day Of Week) function returns a number corresponding to the day of week
from the specified date-type expression. The day 1 of the week is Sunday.
Example:
? DOW(CURRENT)
2
CDOW( )
The CDOW( ) (Character Day Of Week) function returns the name of a day from the
specified date-type expression.
Example:
? CDOW(CURRENT)
Monday
Precedence of Evaluation
When you construct a complex expression, you must take into account the order in
which FoxPro evaluates the components; it does not, as you might guess, simply proceed
from left to right. The rules that govern the order in which an expression is evaluated depend
on the precedence of the various operators the expression contains. FoxPro evaluates
expressions in the following order:
18
1. Expressions enclosed in parentheses, starting with the innermost set of parentheses, and
adhering to the rules of precedence within each set
2. Functions
3. Mathematical operations, in the following order:
a. Exponentiation
b. Multiplication and division, from left to right
c. Modulus calculations
d. Addition and subtraction, from left to right
4. Character string operations
5. Relational operators, from left to right
6. Logical operators, in the following order:
a. NOT
b. AND
c. OR
To adhere to this sequence of evaluation, FoxPro may make two or more passes
through a complex expression in order to produce the final output.
The FoxPro Record Pointer

When a database is open, FoxPro keeps tracks of the current record in the file - the
record that most recently displayed, edited, or operated on with any FoxPro command- with an
indicator called the record pointer. When you update a database in a Change or Browse
window, you can easily identify the current record by the display colours and the location of the
editing cursor. In these editing windows, the actions affect only the current record.
When you first open a database, the record pointer is positioned at the top of the file-on
the first record entered if no index is active, or on the first record in the index if an index is
controlling the processing sequence. Subsequent commands may move the pointer anywhere
in the file, or to the end-of-file.
Use GOTO command to move the record pointer directly to the top of the file, the bottom
of the file, or to any record, as identified by number. The command are, as follows:
GOTO TOP
GOTO BOTTOM
GOTO RECORD <numeric expression>
Use SKIP command to move the record pointer forward or backward any number of
records. For example, for move forwarding four records with
SKIP 4
Or backward three records with SKIP -3
19
CREATING COMMAND FILES

A command file is a disk file that contains a series of commands. Command file is
actually a computer program, which presents a series of commands to the computer in a
specific order. You record commands in files because it is more convenient having FoxPro
execute a batch of commands than typing each command at the dot prompt. (In FoxPro all
commands are executed in the dot prompt) the basic procedure for working with command files
goes as follows. First, create the command file using MODIFY COMMAND. Then run the
command file by asking FoxPro to DO the command file. Correcting the errors in the command
file is termed as debugging.
Creating Command files with MODIFY

Lets write a mailing labels command file now. Call this command file LABELS and
create a database named as MAIL.DBF as below :
Field Name
Field Type
Width
FNAME
LNAME
ADDRESS
CITY
STATE
ZIP
Char
Char
Char
Char
Char
Numeric
15
15
20
15
15
6
There are two ways to create a program file. You can use the FoxPro menu system (through
File
New
Program) or by using MODIFY COMMAND. The command to create a
new command file, or edit an existing one, is MODIFY COMMAND plus the name of the
command file. The command will be as follows:
MODIFY COMMAND LABELS ?
FoxPro displays the prompt
EDIT: LABELS.PRG
and gives a blank screen on which to write the command file. Make sure you are typed it as per
syntax below.
? TRIM(FNAME),LNAME ?
? ADDRESS ?
? TRIM(CITY)+ , + STATE + STR(ZIP) ?
If you make errors go through the program and make changes. To save the file press ^W or
^End.
Now you have written a command file and stored it on the disk as LABELS.PRG.
20
Running command files with - DO

First lets tell FoxPro to use MAIL.DBF as the database:
USE MAIL ?
Now run the command file, tell:
DO LABELS ?
And youll see one of the records from MAIL.DBF in label format:
Betsy Smit
222 Lemon Dr.
New York, NY 01234
Using DO WHILE and ENDDO
DO WHILE and ENDDO is a very powerful and flexible set of commands. This is used to
execute a set of commands repeatedly. All you have to do is enclose the commands to be
repeated between a DO WHILE and an ENDDO command. Every DO WHILE begins a loop,
which will be ended by an ENDDO command. Lets try in the LABELS program.
MODIFY COMMAND LABELS ?
this will display the contents of LABEL.PRG
Press ^N to make room for a new line, add DO WHILE command at the top. Then add ENDDO
at the end of the loop. Here, add a SKIP command to have FoxPro skip down to the next name
in the database as it performs the commands in the loop. Finally a RETURN command tells the
FoxPro to return to dot prompt after the program has been executed.
DO WHILE .NOT. EOF()
? TRIM(FNAME),LNAME
? ADDRESS
? TRIM(CITY)+ , + STATE + STR(ZIP)
SKIP
ENDDO
RETURN
Save by ^W or ^End. On the prompt give:
USE MAIL ?
DO LABELS ?
Then the program output comes as below :
Besty Smit
222 Lemon Dr.
New York, Ny 01234
Record no.1
21
Ruth Doe
1142 J.St.
Los Angeles, Ca 91234
Record no.2
You can get rid of the record numbers by asking FoxPro to SET TALK OFF.
USE MAIL ?
SET TALK OFF ?
DO LABELS ?
Here it will display all mailing labels without record no.
To add a blank line between the mailing labels use a ? command in the program.
GO TOP ?
the command returns FoxPro to the top of the database.
To put comments in FoxPro programs, start the line with an asterisk (*).
To add a comment to any command, type && at the end and then enter the desired
comment.
Keys
Function
Modify structure
CTRL + W
CTRL + PgUp
CTRL + PgDn
CTRL + I
Save changes
To move a field up
To move a field down
Insert field
Browse window
CTRL + N
CTRL + T
Append Record
Delete Record
Modify command file
CTRL + N
CTRL + V
CTRL + Y
CTRL + W
Inserts one blank line

Insert ON
Deletes one line
Save command file
Reviewing Command Files :

A Command file is a computer program written in the FoxPro language. It contains stepby-step instructions for FoxPro to perform a specific job.
To create or edit a command file (program), enter MODIFY COMMAND, followed by the
name of your program, at the dot prompt.
To run a program, enter the DO command followed by the name of the program. FoxPro
will execute each command in the file without you retyping it.
To tell FoxPro to repeat portions of a program, use the DO WHILE and ENDDO
commands to create a loop.
22
Making Decisions
Using FoxPro in an Interactive Manner
If the command file is going to print certain labels, it needs to know which labels you
want. That is, it needs to ask you which labels you want to print. You can make your command
files ask questions with the ACCEPT and INPUT commands. When you use either of these
commands, you enclose the question to be asked in apostrophes. You also need to provide a
memory variable in which to store the answer to the question. To make the memory variable
accessible outside of the command file, you need to define it as PUBLIC. Lets try out the
ACCEPT command with a command file called TEST.
MODIFY COMMAND TEST ?
When the blank screen appears, type these lines :
PUBLIC NAME
ACCEPT What is your names?TO NAME
and save it with CTRL+W. Then DO TEST. Youll see the prompt on-screen:
What is your name? _
FoxPro will leave the prompt on the screen until you type an answer. You can type any answer.
FRED ASTAIRE ?
After you press Return, the dot prompt appears because the program is over. If you type the
following in dot prompt,
? NAME ?
youll see
FRED ASTAIRE
If you were to DISPLAY MEMORY now, youd see that you have a memory variable called
NAME, of the Character type, with the contents FRED ASTAIRE. So youve been able to have
the command file ask a question, wait for an answer, and remember the answer by storing it to
a memory variable.
The INPUT command is very similar to the ACCEPT command, except that it is used
when the answer to the question is a number. For example, try making a command file called
TEST2 that looks like this:
PUBLIC ANSWER
INPUT Enter a numberto ANSWER
Save it. Then DO TEST2, and the request appears:
Enter a number _
Type any number, say 888, and press Return. The dot prompt reappears. Next type
? ANSWER ?
Youll see
888
23
When you DISPLAY MEMORY, you see that you have a memory variable called ANSWER, and
it is Numeric.
The @ Command and Designing Custom Screens

The @ Command
1.
2.
3.
4.
The @ command is one of the most powerful and flexible commands that can be used
for several purposes, such as
To display the desired data in a required format
To input data in a field or variable
To draw or clear box and lines
To create custom screen format files, and so on.
Displaying Data with the @ Command

The @ command can be used to display any kind of data, such as a field, memory
variable or array, result of functions, etc. on the specified position on the screen. The screen of
your display up to 80 columns in each row, where each column displays one character. The
screen can display up to 25 rows.
To display any data with the @ command, you need to specify the coordinates for the
starting position. For example, @ 10, 5 SAY DATE( ) displays the current date at screen
coordinates 10,5 (refers to the physical row 11, column 6).
Inputting Data with @ GET and @ EDIT

The @ GET (or @ SAY GET) and @ EDIT are similar. These commands in
combination with the READ command are used to input data in a field, memory variable or array
element.
A field or variable name is specified with @ SAY GET or @ GET. The command
displays the current value of the GET variable. Then, the READ command activates GET
variable(s) and you can enter the new data.
Example:
CLEAR
STORE Sudha SharmaTO STAFF
@5,5 SAY Enter new name: GET STAFF
FoxPro displays the following message on your screen in row 5.
Enter new name: Sudha Sharma
24
Notice that Sudha Sharma is displayed in reverse video (or different colour) and you cannot
enter the new name yet. To enter data in GET variable(s), you have to use the READ
command.
? Enter READ in the command window.
Restricting Data Input with RANGE

You can use the RANGE clause with a GET variable to specify a range of acceptable
values. Enter the following commands:
CLEAR
BASIC = 1000
@ 5,0 SAY Enter Basic (1000-10000) GET BASIC RANGE 1000,20000
READ
FoxPro prompts you to enter the value. Try to enter a value outside this range, say 50,
and FoxPro displays a message:
RANGE: 1000 to 20000
FoxPro does not accept any number outside the specified range and the cursor stays in
the BASIC variable. It will exit from the @ SAY GET command only when you enter a
number within the range.
Advanced Features of @ SAY command

Several features are available with @ SAY to format the displayed text in different
ways. For instance, you can display a date in the British format regardless of the date setting,
enclose a negative number within parentheses, or display numeric data in currency format. To
format displayed text in the desired way, use the PICTURE or FUNCTION clause with @
SAY, and specify the suitable code. For instance, consider the following commands:
NUMBER = 450
@ 4,10 SAY NUMBER FUNCTION $
$ 450
Common FUNCTION codes for @ SAY
Code
Purpose
B
Left-justifies numeric data within the display region.
E
Displays date type data as a BRITISH date
T
Trims leading and trailing blanks
Z
Displays as all blanks if its numeric value is 0
(
Enclose negative numbers in parentheses
!
Converts alphabetic characters to upper-case
^
Displays numeric data using scientific notation
$
Displays data in currency format
Code
X
Y
!
$
*
.
,
25
Common PICTURE codes for @ SAY

Purpose
Allows any character
Allows logical Y, y, N and n only
Converts lower-case letters to upper-case letters
Displays the current currency symbol
Asterisks are displayed in front of the numeric value
Specifies the decimal point position
Specifies the position for comma (,) in numeric data
Using PICTURE and FUNCTION with @ GET command

Like, @ SAY, @ GET can also use PICTURE and/or FUNCTION. The special
codes used with @ GET have dual functions first, they can format the displayed information
(GET variable), and second, they can restrict the data input in the GET variable.
e.g.
STORE SPACE(10) TO NAME1, NAME2
@ 5,0 SAY Enter NameGET NAME1 FUNCTION !
@ 7,0 SAY Enter NameGET NAME2 PICTURE @!
READ
The ! function code and @! picture code automatically convert entered letters to upper-case.
Whereas the function code (!) is automatically applied to the entire variable, the picture code (!)
is applied to the entire variable due to @ prefixed to !. Now, consider the following commands:
PROJ_CODE = SPACE(10)
@ 9,0 SAY Enter Project CodeGET PROJ_CODE PICTURE !!999!!!!!
READ
Each picture code (character) specifies the format for the corresponding character position of
PROJ_CODE. Therefore, FoxPro will automatically convert the first two characters to uppercase; will allow only numeric data in the next three characters and will convert the last five
characters to upper-case.
26
Code
A
B
E
T
!
^
$
Code
A
L
N
X
9
#
!
$
*
.
,
Common FUNCTION codes for @ GET

Purpose
Allows alphabetic characters only (no spaces or symbols)
Left-justifies numeric data within the output field
Edits date type data as a BRITISH date
Trims leading and trailing blanks
Converts alphabetic characters to upper-case
Displays numeric data using scientific notation
Displays data in a currency format
Common PICTURE codes for @ GET
Purpose
Allows alphabetic characters only
Allows logical data only
Allows letters and digits only
Allows any character
Allows only digits in character data. Allows digits and
signs in numeric data
Allows digits, blanks and signs
Converts lower-case letters to upper-case letters
Prefixes the current currency symbol to numeric data
Asterisks are displayed in front of a numeric value
Specifies the decimal point position
Used to separate digits to the left of the decimal point
The WAIT command presents a message and waits for the user to press any key. The
variations of the WAIT command are explained below.
------------------------------------------------------------------------------------------------------------------------------Variation
Effects
------------------------------------------------------------------------------------------------------------------------------WAIT
When used as a single command, WAIT presents the message,
Press any key to continue,and waits for a keystroke. It does not
store the resulting keystroke.
WAIT TO <MemVar>
When used with TO and a memory variable, WAIT will store the
resulting keystroke to a memory variable.
WAIT message
You can use your own messages with the WAIT command,
enclosed in quotation marks, to replace the default Press any key
to continuemessage.
-------------------------------------------------------------------------------------------------------------------------------
27
Lets try a small command file to test the @ and WAIT commands. Type
MODIFY COMMAND TEST@ ? at the dot prompt. Type the following program.
***** Program to test @ and WAIT commands
***** First set up variables.
ANYNUMBER = 0
ANYWORD = SPACE(254)
ANYDATE = DATE()
***** Next, use @ SAY, GET to read in new data
CLEAR
@ 2,5 SAY Enter a Number GET ANYNUMBER
@ 5,5 SAY Enter a sentence or two GET ANYWORD
@ 10,5 SAY Enter a dateGET ANYDATE
READ
***** use WAIT to pause before memory display
@ 15,1 WAIT Press any key to see memoryTO KEYPRESS
DISPLAY MEMORY
Save the command file and then enter DO TEST@ ? to execute the program.
Making Decisions with IF and ENDIF

You can embed some decision making into your command files by using the IF and
ENDIF commands. The syntax is
IF < Condition >
< Statement/s >
ENDIF
FoxPro checks the condition or conditions specified with IF. If it is true, it executes the
command listed within IF ENDIF.
Start by modifying the LABELS program. Change it so that before it prints labels, it asks
which zip code area you want labels for, and only prints labels for individuals who live in the
specified zip code area.
MODIFY COMAND LABELS ?
Change the program contents as below :
28
***** Mailing Labels Program

SET TALK OFF
CLEAR
INPUT What zip code area ?TO AREA
GO TOP
DO WHILE .NOT. EOF( )
IF ZIP = AREA
? TRIM(FNAME), LNAME
? ADDRESS
? TRIM(CITY) + , + STATE + STR(ZIP)
?
ENDIF
SKIP
ENDDO
RETURN
Then run the above program after opening the MAIL database. The first thing that should
happen is that the screen clears and the following question appears.
What zip code area ?
Type zip code what you want and press RETURN. Then labels appear on the screen only
those records having the same zip code. You modify the above program as below for printing
labels.
SET TALK OFF
CLEAR
INPUT What zip code area ?TO AREA
ACCEPT Shall I send labels to the printer? (Y/N)TO YN
IF UPPER(YN) = Y
SET PRINT ON
ENDIF
GO TOP
IF ZIP = AREA
? ADDRESS
?
ENDIF
SKIP
ENDDO
29
SET PRINT OFF

RETURN
IIF( ) The Compact IF Function

The IIF( ) function, that is called immediate IF, is a one-line version of IF-ELSE-ENDIF.
A condition is specified with IIF(). This function returns one expression if the condition is
evaluated as true and returns another expression if the condition is false. Both these
expressions are also specified with IIF().
e.g.
? IIF (SALARY > 3000, Salary is more than 3000, Salary is not more than 3000)
Nested IF ENDIF structures

Like nested DO WHILE ENDDO loops, you can also use nested IF-ENDIF structures.
However, for each IF, there must be a corresponding ENDIF.
e.g.
USE DEPOSIT
IF RATE < 10
*
* < statements >
IF RATE < 8
*
* < statements >
ELSE
* < statements >
ENDIF
ENDIF
Making Decisions with DO CASE

The IF ENDIF clause is useful for allowing a program to make a simple either/or
decision. Some programs may have to decide from several possibilities what to do next. The
DO CASE ENDCASE clause ensures that the program can do this. The syntax is :
DO CASE
CASE <Variable> = <Value>
Statement/s
OTHERWISE
Statement/s
ENDCASE
30
Please notice the following program.
CLEAR
? 1. Add new names
? 2. Print Labels
? 3. Edit a record
? 4. Exit
INPUT Enter choiceto CHOICE
DO CASE
CASE CHOICE = 1
APPEND
CASE CHOICE = 2
DO LABELS
CASE CHOICE = 3
BROWSE
OTHERWISE
QUIT
ENDCASE
Inside the DO CASE clause, the program decides what to do based upon the value of
CHOICE. If CHOICE = 1, the program appends. If CHOICE = 2, the program executes LABEL
program. If CHOICE = 3, the program goes into BROWSE mode. If none of these situations
occurs (OTHERWISE), the program quits.
Using FOR - ENDFOR

The FOR ENDFOR executes a set of statements within a loop a specified number of
times. A memory variable or an array element is used as a counter to specify how man times
the statements inside the loop are executed.
e.g.
** PROGRAM TO DISPLAY LOGARITHM OF NUMBERS 100 to 110
FOR NUMBER = 100 TO 110
? LOG(NUMBER)
ENDFOR
By default, FOR ENDFOR increments the counter (memory variable) by 1. However, if
required, you can specify the increment rate through the STEP option with FOR.
e.g.
** PROGRAM TO DISPLAY SQUARE ROOT OF SERIES 10,12,14 30
FOR NUMBER = 10 TO 30 STEP 2
? SQRT(NUMBER)
ENDFOR
31
Macro Substitution
The Macro substitution is a useful feature that makes the programs flexible and
interactive. The macro allows you to use a variable name for a file name, field name, etc. in a
program file. A macro is similar to a normal character variable, except that it has the
ampersand character (&) in front of it, e.g. &NAME, &XYZ, &INVOICE etc.
e.g.
NEWFILE = DEPOSIT
USE &NEWFILE
When these commands are used in a program file, the first command creates a character
variable NEWFILE and stores DEPOSIT in it. When it executes the USE command, it
substitutes &NEWFILE with its contents. That is, USE &NEWFILE becomes USE DEPOSIT.
Accordingly, FoxPro opens the DEPOSIT database file.
e.g.
To allow macro feature this flexibility, you must modify the LABELS command file as
below :
SET TALK OFF
CLEAR
ACCEPT Search on what field ? TO FLD
ACCEPT Look for what &FLD ? TO COND
ACCEPT Shall I send labels to the printer ? (Y/N)TO YN
IF UPPER(YN) = Y
SET PRINT ON
ENDIF
GO TOP
IF &FLD = &COND
? ADDRESS
?
?
ENDIF
SKIP
ENDDO
SET PRINT OFF
RETURN
When you run the above program, screen clears and displays
Search on what field ?
32
Lets answer by entering CITY. The next question to appear is
Look for what CITY ?
And you can answer by typing San Diego. Then it asks
Shall I send labels to the printer ? (Y/N)
Answer N for now. Youll see the mailing labels for the San Diego residents. You can try
running the program again and observe the use of macro substitution.
Reviewing Decision Making

The ACCEPT command displays a prompt and waits for the user to enter any character
data followed by a press on the Return key.
The INPUT command displays a prompt and waits for the user to enter a number,
followed by a press on the Return key.
The @, SAY, GET and READ commands will place a prompt anywhere on the screen
and wait for the user to fill in or modify an existing memory variable or field.
The IF ELSE ENDIF commands set up decision clauses in programs.
The DO CASE ENDCASE clause sets up decisions where only one alternative of
several is possible.
Macro substitution allows you to build portions of command lines as a program is
running, and substitute those portions into the actual command, using the & symbol and a
variable name.
CREATING A NEW DATABASE

For creating a new database, Please select File ?
Then the following screen will appear.
New from menu bar.
33
Select Database option and click OK option. Then the following screen will appear.
Please enter field name, field type, field width and Decimal places (if field type is float) for your
database. Use Field <Insert> or <Delete> for inserting a new field or deleting an existing field.
After completing your field entries, Select < OK >. Then the following screen will appear.
34
Enter the name for your database. If you want to change default drive/directory, you can
change by clicking and selecting on it. Select < Save > option for saving your database. Then
the following screen will appear.
Select < Yes > option for inputing data records now. Then enter data records. After
completing, press <Esc> Key to close the menu.
FoxPro Commands related to Database :

?
LIST
Lists record contents of current database
LIST TO PRINTER
Prints record contents of current database
LIST FIELDS NAME, CITY

Lists fields of Name, City only
LIST FOR PIN > 110025

Lists records whose PIN field values are more than 110025
SET HEADING OFF

Removes field heading display
CLOSE DATABASES
Close all opened databases
?
SET STATUS ON
Sets status bar on
SET TALK OFF

Sets interaction off
SET CONFIRM ON
Sets confirmation on
APPEND
To select append mode
APPEND BLANK
To add a blank record at the end of the database file
LIST FOR CITY = MADRAS

Lists records where City field = Madras
CREATE SAMPLE
Creates a SAMPLE database
SET DEFAULT TO D:\MYFILES

Sets default directory
QUIT
Quits from FoxPro
DISPLAY
Displays current record only
DISPLAY NEXT 4
Displays next 4 records from current record
DISPLAY FIELDS NAME, PIN

Displays fields Name, Pin only
LOCATE FOR NAME = RAJ

Locates record where Name field = RAJ
CONTINUE
Continues Locating/Searching
EDIT or CHANGE
Edit/Change record contents
EDIT FIELDS NAME, CITY

Edits fields Name, City only
35
36
BROWSE
Shows several records on the screen in the form of a horizontal table
BROWSE FIELDS CITY, NAME

Browses fields City, Name only
BROWSE WIDTH 10
Displays fields of current database file in columns that are 10 characters wide
GOTO 7
Goto 7th record
GOTO TOP
Goto Top of the record
REPLACE RATE WITH RATE-1 ALL

Decreases value of Rate field by 1 for all records
Deleting Records :
For deleting records, Please select Record menu ?
appear.
Delete. The following screen will
You can choose Scope / For / While for deleting records under condition.
SCOPE :
?
?
?
?
?
?
?
OPTION
ALL
NEXT
RECORD
REST
DELETE RECORD 2
DELETE REST
DELETE FOR RATE < 10
RECALL
RECALL NEXT 3
PACK
MODIFY STRUCTURE
DELETES
All records in the database
Specified no.of records starting from current record
Specified Record
Current record and all subsequent record
-
Deletes record no.2

Deletes rest records
Deletes if rate field is less than 10
Recalling deleted records
Recalls next 3 records
Removes records permanently
Allows modifying structure of opened file
Sorting a Database :
To sort a database, Please select Database ? Sort. Then the following screen will appear.
1.
2.
3.
4.
5.
6.
7.
8.
Select the database you want to sort

Select the fields and move to Sort order fields window
You can change sorting option to Ascending / Descending / Ignore case
You can change sorting fields hierarchy order
You can sort part of database using input conditions ie., Scope, For, While loops
You can restrict sorted output database fields
Save As option asks the name for the sorted database
Press OK to complete
37
38
Commands related to Sorting :

?
USE ADDRESS
Open Address database
SORT ON NAME TO NAMELIST

Sorts on Name field and stores database as NAMELIST
SORT ON NAME/D TO NAMELIST

Sorts on Name field in descending order
SORT ON NAME/C TO NAMELIST

Sorts on Name field by ignoring case
SORT ON NAME TO LESS_FLD FIELDS NAME, CITY

Sorts on Name field. The sorted database LESS_FLD contains only NAME,CITY fields
SORT ON NAME TO ADDRESS1 FOR CITY = New Delhi

Sorts on Name field for records containing City field = New Delhi
SORT ON CITY, NAME TO ADDRESS3

Sorts on City, Name fields and stores as ADDRESS3 database.
primary field and Name field is secondary field in sorting.
Here City field is
Indexing a database :
Indexing a database creates an index file and not database file. Indexing a database is
better than sorting. Indexing is better for frequent changes in records. Sorting is better for
fixed, rarely changes in records. For indexing a database, Select File ? New ? Index
1.
Select the database you want to index
2.
Select the fields and move to Index order fields window
3.
You can change index option to Ascending / Descending / Unique
4.
You can change index fields hierarchy order
5.
You can index part of database using Index Expression
6.
Save As option asks the name for the indexed database
7.
Press OK to complete
39
Commands related to Indexing :

?
USE ADDRESS
Opens Address database
INDEX ON NAME TO NAME_IND

Indexes on Name field and creates NAME_IND.IDX index file
SET INDEX TO NAME_IND

Sets index file Name_ind open
USE ADDRESS INDEX NAME_IND.IDX

Opens Address database file with Name_ind index
REINDEX
FoxPro rebuilds the index files
The Compact index files reduce the size of the index files and make them efficient.
INDEX ON CITY TO CITY COMPACT

Indexes on City field and creates Compact index file named as CITY
INDEX ON CITY + NAME TO CITYNAME

Indexes on City, Name fields (multiple field indexing) and file is named as CITYNAME
FIND RAJ
To find the record that contains Raj in the NAME field (indexed field).
40
FOXPRO COMMAND AND FUNCTION SUMMARY

!
&
&&
*
@ SAY
@ GET
@ CLEAR
ACCEPT
ALLTRIM( )
APPEND
APPEND FROM
AVERAGE
BROWSE
CHANGE
CHR( )
CLEAR
CLEAR ALL
CLEAR GETS
CLOSE ALL
CMONTH( )
Synonymous with RUN.

Runs the specified DOS
command or program within FoxPro. Also used with
@..SAY..GET as PICTURE symbol.
Modulus operator Returns the remainder obtained by
dividing a numeric expression by another numeric
expression.
Macro substitution
Comment indicator. Use this symbol to add comments to
command lines.
Indicates the beginning of a non-executing comment line
in a program file.
Displays output at the specified row and column position.
Creates an editing region.
Clears a portion of the screen or active window.
Accepts character string data from the screen.
Optionally, also displays a specific prompt.
Returns the specified character expression with leading
and trailing blanks removed.
Enables you to add records to the end of the current
database file.
Adds records to the end of the current database file from
another file.
Computes the arithmetic mean of numeric expressions or
fields.
Opens the Browse window and allows editing of screenfull of records.
Displays record(s) of the current database file for editing.
Same as EDIT.
Returns the character associated with the specified
numeric ASCII code.
Clears the screen.
Releases all memory variables and arrays and the
definitions of all user-defined menu bars, popups and
windows from the memory. Also closes any open
database and associated files.
Releases all pending @ GET statements.
Closes all files in all work areas and selects work area 1.
Returns the name of the month from a specified data
expression.
COL( )
CONTINUE
COPY FILE
COPY
STRUCTURE
COUNT
CTOD( )
DATE( )
DELETE
DISPLAY
DO
DO CASE
DO WHILE
DTOC( )
EDIT
EOF( )
FIND
FOR
FOUND( )
GO / GOTO
41
Returns the current column position of the cursor.

Continues the previous LOCATE. Tries to locate the next
record that meets the specified condition.
Makes a duplicate copy of a specified file. Any type of file
can be copied. (Similar to DOS COPY.)
Copies the structure of the current database file to a new
database file.
Counts database file records that meet the specified
condition(s).
Converts a character expression to a date expression.
Returns the current system date.
Marks the records for deletion in the active database file.
Displays the contents of record(s) in the current database
file and the results of expressions.
Executes a command or procedure file
Marks the beginning of a DO CASE ENDCASE
structure. FoxPro executes the first statement block
(after DO CASE and before ENDCASE) whose
associated conditional CASE statement evaluates to true
(.T.).
Marks the beginning of a DO WHILE ENDDO loop.
FoxPro executes a block of statements within this
conditional loop as long as condition(s) specified with DO
WHILE is/are true, unless it encounters an EXIT
command.
Returns a character type date from a date expression.
Displays the specified / all fields of the active database
file for editing.
Determines whether or not the record pointer is
positioned past the last record in the current or specified
database file.
Searches for the first record with a specified string in an
indexed database file.
Marks the beginning of a FOR ENDFOR loop. FoxPro
executes the commands listed between FOR and
ENDFOR a specified number of times.
Returns true (.T.) if the previous CONTINUE, FIND,
LOCATE or SEEK was successful.
Positions the record pointer on the specified record
number in the active (current) database file or a database
file open in specified work area.
42
GOTO
TOP
BOTTOM
IF
IIF( )
INPUT
INT( )
LEN( )
LIST
LOCATE
LOWER( )
LTRIM( )
MAX( ) / MIN ( )
MODIFY
COMMAND
MONTH( )
PROPER( )
READ
RECNO ( )
RENAME
REPLACE
Moves the record pointer on the first / last record in the

active (current) database file or a database file open in
specified work area.
Marks beginning of the IF ENDIF structure. FoxPro
executes a set of commands listed between IF and
ENDIF if logical condition(s) specified with IF is/are true.
This structure may also include ELSE.
Returns one of two values depending on the result of a
logical expression specified with the function.
Displays a prompt on the screen and inputs data from the
keyboard and stores into a specified memory variable.
Returns the integer portion of a numeric expression.
Returns the number of characters (including blank
spaces) in a character expression.
Continuously
displays
all/specified
fields
(and
expressions) of records of the current database file.
Sequentially searches the database file for the first record
that matches a specified expression.
Converts a specified character expression in lower-case
letters.
Removes leading blanks from a specified character
expression.
Returns the expression with the highest / lowest ASCII or
numeric value or the latest date from a list of character,
numeric or date expressions. Two or more expressions
can be used with the function.
Opens a text editing window and allows you to modify or
create a program (text) file. It assumes a default
extension name .PRG.
Returns the numeric month for a specified date
expression.
Returns the specified character expression with the first
letter of each word in uppercase and the remaining
characters in lowercase.
Activates objects, such as fields, check boxes, lists,
popups, buttons, etc. created with @ GET and @
EDIT commands.
Returns the current record number.
Renames a file.
Repalces the contents of specified field(s) with new
expression(s).
ROUND( )
ROW( )
RTRIM( )
RUN
SEEK
SELECT
SET
SET BELL ON /
OFF
SET CARRY ON /
OFF
SET
CENTURY
ON / OFF
SET
CONFIRM
ON / OFF
SET DATE
SET DECIMALS
SET DEFAULT TO
SET DEVICE TO
SCREEN
/
PRINTER / FILE
SET EXACT ON /
OFF
SET FILTER
43
Return a numeric expression rounded to a specific

number of decimal places.
Returns the current row position of the cursor.
Returns the specified character expression with all trailing
blanks removed.
Executes an external operating system command or
program.
Searches the current indexed database file for the first
occurrence of a specified expression in the controlling
index key.
Activates the specified work area. Any file open in that
work area becomes the active (current) database file.
Opens the View window and provides an easy way to
open database files, establish relations and to change
several other settings.
Enables or disables sounding of bell during editing.
Determines whether or not FoxPro carries data forward
from the current record to the new record created with
APPEND or INSERT.
Determines whether or not the century portion of data
expressions is displayed.
Specifies whether or not Enter or Tab must be pressed to
exit an input field and move to the next object.
Sets the format used to display date expressions.
Specifies the minimum number of decimal places
displayed in the result of mathematical calculations.
Specifies the default drive and directory for various file
read and write operations.
Directs output of @ SAY commands to the specified
device (i.e. screen, printer, or file).
Specifies the rules used when comparing strings of
different lengths. When set to ON, the expressions must
match character for character, including blank spaces, for
them to be equal.
Specifies a condition for the current database file. This
condition is automatically applied to all commands that
use the database file.
44
SET ORDER
SKIP
SORT
SPACE( )
SQRT( )
STORE
STR( )
SUBSTR( )
SUM
TRIM( )
UPPER( )
USE
WAIT
YEAR( )
ZAP
Selects a specified index file or tag as the controlling

(master) index file/tag for the current or specified
database file.
Moves the record pointer forward or backward in the
current or specified database file.
Sorts records in the current database file and stores the
stored data in a specified new database file.
Returns a character string made of a specified number of
spaces.
Returns the square root of the specified numeric
expression.
Stores the specified data in memory variables or array.
Automatically creates the memory variables, if they do
not exist.
Returns the character string equivalent to a specified
numeric expression.
Returns a specified number of characters from the
specified character expression or memo field.
Totals all or specified numeric field values in the current
database file. The results can be stored in memory
variable(s) or arrays.
Returns the specified character expression after
removing all trailing blanks.
Returns the specified character expression in uppercase.
Opens a database file and associated index files. Can
also open the database file in a specified work area.
Pauses the execution of the FoxPro command or
program and displays a message. FoxPro resumes
operation on pressing a key or clicking the mouse.
Returns the year from the specified date expression.
Removes all records from the current database file. This
is equivalent to DELETE ALL followed by PACK.
However, ZAP is much faster. Once ZAPped, records
can not be recovered.
" Take me from Untruth to Truth,

Take me from Darkness to Light,
Take me from Death to Immortality "
Gita Verse.

FOXPRO (Aj)

Uploaded by

Copyright:

Available Formats

FOXPRO (Aj)

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

FOXPRO (Aj)

Uploaded by

Copyright:

Available Formats

PROGRAMMING IN FOXPRO

Database Management System (DBMS)

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Designing the Database Structures

Items explicitly requested by the users

To accommodate the required items of information

Ordnance Factories Institute of Learning Ambajhari

Defining Database Structure

Naming Files and Fields :

Choosing Data types :

Ordnance Factories Institute of Learning Ambajhari

Setting field widths :

Assigning Index Key fields :

Ordnance Factories Institute of Learning Ambajhari

Re-evaluating the field list :

THE FOXPRO USER INTERFACE

The FoxPro Windowing Environment :

Ordnance Factories Institute of Learning Ambajhari

CONSTRUCTING EXPRESSIONS AND CONDITIONS

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

INT( ), FLOOR( ) and CEILING( )

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Time & Date Functions:

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

The FoxPro Record Pointer

Ordnance Factories Institute of Learning Ambajhari

CREATING COMMAND FILES

Creating Command files with MODIFY

Ordnance Factories Institute of Learning Ambajhari

Running command files with - DO

Ordnance Factories Institute of Learning Ambajhari

Inserts one blank line

Reviewing Command Files :

Ordnance Factories Institute of Learning Ambajhari

Ordnance Factories Institute of Learning Ambajhari

The @ Command and Designing Custom Screens

Displaying Data with the @ Command

Inputting Data with @ GET and @ EDIT

Ordnance Factories Institute of Learning Ambajhari

Restricting Data Input with RANGE

Advanced Features of @ SAY command

Ordnance Factories Institute of Learning Ambajhari

Common PICTURE codes for @ SAY

Using PICTURE and FUNCTION with @ GET command

Ordnance Factories Institute of Learning Ambajhari

Common FUNCTION codes for @ GET

Ordnance Factories Institute of Learning Ambajhari

Making Decisions with IF and ENDIF

Ordnance Factories Institute of Learning Ambajhari