Robert W. Weeks bob.weeks@cox.net, http://members.cox.net/bob.weeks

Indexing

Indexing overcomes many of the disadvantages of sorting. With indexing, dBASE creates an index file that contains the values of the indexed field and the record numbers. dBASE then uses the index file to give order to the database, and also to locate data quickly. The Index command looks like this:

. Index on <expression> to <index file name>

Suppose in a mailing list you want to view the database by both name and zip code. You could issue these two commands:

. index on name to name
. index on zip to zip

These two commands will create two index files, name.ndx and zip.ndx (dBASE automatically adds the extension ndx), which contain ordering information for the name and zip fields.

Now, suppose you want to start dBASE and use the names database file ordered by name. You should issue the command

. use names index names

This command opens up the names database file and activates the names index file, so the database appears sorted by name. You could have said

. use names index zip

in which case the database would appear in zip code order.

If the database names is already in use through the Use command, you can change the active index file with the command

. set index to <index file name>

For example, if you had issued the command

. use names index zip

and now want to view the records in name order, issue the command

. set index to name

This command changes the active index file to name, and the apparent order of the records.

Indexing has several advantages. The first is that it is usually quicker to build an index file than to perform a sort. Second, you can have many index files, so you can change the ordering of the database as quickly as you can type the set index to command. Third, active index files are automatically updated as data is added, deleted, or changed, so your index files can always be ready. About the only disadvantage of indexing is that the index files occupy space on the disk.

When you have an indexed database, you usually want to make all index files active while adding, editing, or deleting data. If some index files are not active, then those indexes are not updated to reflect the changes to the database, and therefore the indexes are not valid. For example, to add new data to the database, you should say

. set index to zip, name
. append

Now, as you enter new data, dBASE updates both the zip and name index files. You can have up to seven index files active at once. If you find that you forgot to make an index file active, you can reindex the database by making the index files active with the Set Index To command and then issuing the Reindex command. This action will rebuild the active index files.

Find and Seek

The Find command demonstrates another advantage of indexing database files: with indexed files, locating data is very fast.

The dBASE Locate command described earlier performs a sequential search through the database, looking for a record that satisfies the condition in the Locate command. If the matching record is near the end of a large database, the search could take a long time to complete. In fact, if no record satisfies the condition, Locate still searches through the entire database. Thus, to simply know that there exists no record that meets the condition requires looking at every record in the database.

The Find command, used on indexed fields, can locate any record in an extremely large database in just a few seconds. Most of the time the record is located immediately, without any apparent delay. The Find command looks like this:

. find <alphanumeric string>

Find locates the first occurrence of the string in the database. For example, in the mailing list file which is indexed by name, the command

. Find Reagan, Ronald

moves the record pointer to the record for Ronald Reagan. You can then enter other commands such as Edit or Delete.

To use Find, the database must be indexed on the field you want to use the Find command on, and the index to that field must be active through the Use...Index command or the Set Index To command.

Note that Find moves to the first record that matches the character string. If there are several records with identical index field contents, and the one the Find command points to is not the one you need, how do you move through the database to find the record you need? If you issue the Find command again, dBASE will simply find the same record again. The key is to remember that since the index file for the field you're searching for is active, the database appears to be sorted in that order, so once you've found the first record, then simply use the Skip command to move to the next matching record. If this is not the one you need, then Skip again.

The Seek command is identical to the Find command, except that Seek can accept variables as input. Note that when using Find, we said

. Find Reagan, Ronald

Sometimes, you may have the name to find stored in a memory variable. For example:

. a = "Reagan, Ronald"
. seek a

This Seek command is identical to the above Find command. If you said

. find a

then dBASE would look for a person named "a". You can also use Seek like this:

. seek "Reagan, Ronald"

dBASE will then rebuild all active index files.

Mathematical Operators

dBASE uses standard algabraic operators for arithmetic. The operators are:

+ Addition
- Subtraction
* Multiplication
/ Division
? Exponentiation

The order of precedence is exponentiation first, then multiplication and division, then addition and subtraction. Use parenthesis to control the order of evaluation. For example, 5+2*3+4 is equal to 15, but (5+2)*(3+4) is equal to 49.

Relational Operators

Relational operators are used to compare one value to another. The dBASE relational operators are

< Less than
> Greater than
= Equal to
<> Not equal to
<= Less than or equal to
>= Greater than or equal to

Relational operators, of course, work with numbers, but also work with dates. The key to comparing dates is to remember that as time goes on, dates get "larger." Thus, 12-15-85 is less than 03-01-86.

Logical Operators

Logical operators are most often used to combine several relational comparisons. The dBASE logical operators are

.not. Negation
.and. logical and
.or. Logical or

String Operators

String operators combine strings to form larger strings. The dBASE string operators are

+ String concatenation, which "adds" two strings
- String concatenation with intervening blanks moved to the end of the result
$ Substring search

Concatenation

String concatenation simply takes one string and tacks on the second string immediately following. For example,

. a = "William"
. b = "Jones"
. c = a + b
. ? c
WilliamJones

Note there is no space between the original two variables. Contrast with this:

. c = a + " " + b
. ? c
William Jones

In this case, we add the literal space (denoted by " ") between the variables, and the result is what we want. Also,

. c = b + ", " + a
. ?c
Jones, William

Here we add a command and a space between the variables.

Search

The substring search operator looks through character variables or fields looking for a character pattern. For example, suppose you have names stored in a character field called Name. then

. display all for "Steve"$name

displays all records where the name field contains the characters "Steve" which may or may not be what you're looking for. If your goal was to find all people with the first name Steve, then this doesn't really work, as this display all command will also display people with the names (first or last) of Steven, Stevenson, Stevens, and so forth. The point is that substring search doesn't care at what position in the field the string is found, just that is is found or not. If it's important for you to search for people based on their first name or last name alone, then your database should contain separate fields for the first name and last name.

Numeric Functions

Exp

The Exp function returns the value of the constant e (the base of the system of natural logarithms) raised to the power of the argument of the function. The format is

exp(<numeric expression>)

Log

The Log function returns the natural logarithm of a number. The format is

log(<numeric expression>)

Round

The Round functions rounds off a number to a certain number of decimal places. The format is

round(<numeric expression>, <number of decimal places>)

For example,

. ? round(3.75, 0)
4.00

. ? round(3.7544, 2)
3.7500

Note that Round returns the same number of decimal places as in the input.

Sqrt

The Sqrt function returns the square root of a number. The format is

sqrt(<numeric expression>)

String Functions

Trim

The Trim() function removes trailing blanks from a string. It is most useful for output purposes. For example, suppose that the field fname is 20 characters long. If you say

. ? fname + lname

then you get

Ronald Reagan

If you say

. ? trim(fname) + lname

then you get

RonaldReagan

The best way is to say

. ? trim(fname) + " " + lname

which gives

Ronald Reagan

By using the + operator, which concatenates strings, you could say

. ? trim(fname) + ", " + TRIM(fname)

to get

Reagan, Ronald

Note the space after the comma.

Rtrim

The Rtrim function is identical to the Trim function.

Ltrim

The Ltrim function trims blank characters from the left end of a charcter expression.

Upper

The Upper() function converts all characters to upper case. From the example above,

. ? upper(fname)
RONALD

Lower

The Lower() function reverses the action of Upper().

. ? lower(fname)
ronald

At

The At() function can be used to locate characters in a string. For example.

. ? at("def","abcdefgh")
4

This means that the string "def" starts at position 4 in the string "abcdefgh".

At() is useful for determining whether something exists in a string, because if the string is not found, the at() returns zero. For example, suppose you have a single field name that contains both first and last names. To display all records for people with the first name Steve, you could use

. display all for at("Steve", name) <> 0

If name does not contain the string "Steve", the at() returns 0, and the record will not display. But if "Steve" is found, the at() returns a number other than zero, and the record will display.

Substr

Substr() returns a substring of a character field. For example,

. ? substr("abcdefgh", 2, 3)
bcd

The first parameter is the string to take the substring of, the second parameter is the character position to start the substring at, and the third parameter is the number of characters to extract.

Examples:

Suppose you have a character field called "Zip" and you want to display a list of all zip codes beginning with "606" Here's how, using the Display command as an example:

. display all for substr(zip,1,3) = "606"

Since we want to extract the first three characters, the second parameter is 1, meaning we start taking characters at position one, and the third parameter is 3, meaning that we take three characters.

Now suppose our inventory part numbers, stored in the field "Partno" are 10 characters long and we want a list of all part numbers ending with "A5" Here's how:

. display all for substr(partno,9,2) = "A5"

We start in position 9, the next to last position, and take it and the following character for a total of two characters.

Date Functions

Date

Date() returns the current system date. This date is correct only if you entered the correct date when you started your computer, or if you have an accessory device to enter the date, it is working properly.

. ? date()
08-01-85

Day

Day() returns the numeric value of the day of the month from a data variable.

. ? day(lastcall)
14

Month

Month() returns the numeric value of the month from a date variable.

. ? month(lastcall)
7

Year

Year() returns the numeric year from a date variable.

. ? year(lastcall)
1985

Dow

Dow() returns the numeric code for the day of the week from a date variable. Monday is day number one.

. ? dow(lastcall)
3

Cdow

Cdow() is like DOW(), but returns a character day of the week instead of the numeric day of the week.

. ? cdow(lastcall)
Tuesday

Cmonth

Cmonth() returns the name of the month for a date. For example, if today is May 15, 1986, then

. ? cmonth(date())
May

If you have a date stored in a date field or date memory variable called "duedate", this command will store a stylized representation of the date in the memory variable x:

. x = cdow(duedate) + ", " + cmonth(duedate) + ", " + str(day(duedate)) + str(year(duedate))
? x
Wednesday, January 1, 1986

Ctod

Ctod() stores a date to a date variable. Since dBASE stores dates in a special way, this function is the best way to place a value in a date variable.

. replace lastcall with ctod("07-01-85")

This function is extremely important, as it is used all the time. Unfortunately, with dBASE it's not possible to write a statement like this:

. display all for duedate >= 06-06-86

Instead, you must use

. display all for duedate >= ctod("06/01/86")

Dtoc

Dtoc takes a date as an input and returns a character string for output. The format is

dtoc(<date variable>)

For example, suppose you have a field in the database called paydate, which is a date type field. You can't say

. ? "The date paid is " + paydate

because the + operator concatenates only character variables, and paydate is a date type variable. You can say

. ? "The date paid is " + dtoc(paydate)
The date paid is 07-15-85

which works because Dtoc converts a date field or memory variable and converts it to characters, which will concatenate properly.

Val

The Val function converts a string of characters and returns a numeric value. The format is

val(<character string>)

For example,

a = "123.45" (a is a character type variable)
b = val(a) (b is a numeric type variable)
? b
123

Note that Val returns only the integer part of its input.

Str

The Str function takes a numeric variable and returns a character variable. The format is

str(<numeric expression>, <result length>, <result decimals>)

Str is useful for those places where you can have only character type data. For example,

a = 123.45
. ? "The value is $" + STR(a, 6, 3)
The value is $123.45

Note that you can't say

. ? "The value is " + a

because the + operator accepts only character type data.

Set Commands

dBASE contains a large number of Set commands which modify the working environment of dBASE. Some of the common Set statements are (default values are capitalized):

Bell

Set bell ON/off - Turns the bell that rings as you fill a data file on and off. The default is on. Most people turn this off soon after starting dBASE or initiating a command file.

Confirm

Set confirm on/OFF - Forces you to press the return key at the end of a field instead of moving automatically. Most people set confirm on soon after starting dBASE or initiating a command file.

Help

Set help ON/off - Determines whether dBASE asks you if you want help after attempting to execute a command with an error. Most of the time, the error is a mistyped word which you spot instantly, so replying to the help question is an annoyance. The command set help off causes dBASE to not ask this question.

Decimals

Set decimals to <number> - sets the minimum number of decimals that display.

Default

Set default to <drive> - sets the default drive for storing databases and other files. Often one of the first commands you'll issue each time when you start dBASE, especially when using floppy drive systems.

Deleted

Set deleted on/OFF - With set deleted off, records that have been deleted still appear in the database and participate in commands like report, display all, and so forth. With set deleted on, they don't. But even with set deleted on, deleted records will respond to commands like display without a scope when the database record pointer is pointing to a deleted record.

Filter

Set filter to <condition> - causes the database to appear that it contains only records meeting the condition.

Margin

Set margin to <number> - Sets the left margin of the printer.

Print

Set print on/OFF - turns the printer off and on for output. When you say set print on, all output is directed to both the screen and printer. Set print off turns the printer off and sends output to the screen only.

Talk

Set talk ON/off - controls whether the results of command execution are displayed on the screen. In command files, you'll generally set talk off, but when using dBASE from the dot prompt, you'll want talk on.

Console

Set console ON/off - This command determines whether output (other than that generated by the @ Say and @ Get statements and error messages) is displayed on the screen. This command is useful when directing output to the printer, saying set console off will not display the data on the screen, and the report may print faster. Error messages are not affected by this command, use set talk off to suppress them.

Device

Set device to print/SCREEN - This command determines whether the output of the @ Say and @ Get commands will display on the screen or printer. Use this command when directing program output generated with these statements to the printer. The default is set device to screen.

Escape

Set escape ON/off - With set escape on, (the default state of dBASE) pressing the escape key while dBASE is processing a command like list or replace or while it's running a program will cause the operation to cease. (Actually, dBASE displays the Cancel, Ignore, or Suspend message.) With set escape off, dBASE will ignore a press of the escape key and run the command or program to completion.

Use this command within your programs to prevent users from accidentally interrupting crucial processes, or circumventing your menus or security procedures. Use it with care--develop your programs with set escape on so that you can stop your programs if you need to. When they're debugged and finished, add the set escape on statement.

Exact

Set exact on/OFF - This command determines how rigorously dBASE compares character fields, character memory variables, or character strings. The default setting is set exact off.

With set exact off, dBASE takes the two character items and compares them by starting with the first character of the left-hand side item and comparing it with the first character of the right-hand side item. If these are different, of course, the comparison fails. But if the two characters are identical, dBASE advances to the second character of the left-hand side item and compares it to the second character of the right-hand side item. This process continues until two differing characters are found, or until the end of the right-hand item is encountered. If all characters match to the end of the right-hand item, dBASE declares declares the two items as equal.

For example, suppose lname contains "Washington". Then, with set exact off, "Wash" = lname is false. But lname = "Wash" is true. In the first case, the right-hand side item lname, even though it matches the left-hand side item through the first four characters is longer that the left-side item, so the comparison fails. In the second case, the left-hand side item matches the right-hand item through the entire length of the right-hand side item, so the comparison passes.

With set exact on, lname = "Wash" is false instead of true. With set exact on, dBASE requires both items to be exact matches through their entire lengths.

Function

Set function - This command lets you program your own function keys, useful when you find yourself entering the same commands over and over again at the dot prompt. With this command, you assign up to 30 characters to a function key. When you press the key, dBASE enters the command for you.

The form of the command is

set function <function key number> to <command>

For example, to have function key 5 issue to command display all for state = "KS", use the command

set function 5 to "display all for state = "KS";"

The semicolon represents a press of the return key, which is necessary at the end of most commands. Otherwise, dBASE displays the command at the dot prompt, and you'll have to press enter yourself. The function keys in dBASE are predefined with these meanings (of course, you can change the meaning of any you like):

F1 help;
F2 assist;
F3 list;
F4 dir;
F5 display structure;
F6 display status;
F7 display memory;
F8 display;
F9 append;
F10 edit;

Safety

Set safety ON/off - This command determines whether dBASE checks to see if a file exists before proceeding with commands like copy, copy file, create, index, join, save, sort, total, update, and zap. With set safety on, (the default state), dBASE asks for confirmation before overwriting an existing index file or database. With set safety off, dbASE does not ask for confirmation.

As a general rule, leave set safety on when using dBASE from the dot prompt. It may prevent you from accidentally deleting a file you hadn't thought about for a while. But in program files, where the situation is more controlled, go ahead and use set safety off. Otherwise, some of your operations, such as sorting to a temporary database file that you use over and over, will cause dBASE to display overwrite warning message. It will disrupt your displays, and the operator may not know how to respond.

Alternate

set alternate - The two forms of this command let you direct most types of dBASE screen output to a disk file. This is useful when you'd like to send the results of a report over a modem, or perhaps spruce up the output of the report with your word processing program. To use set alternate, first issue the command

set alternate to filename

Supply the filename of your choice. This command simply tells dBASE what file to direct output to. It's not until you issue the command

set alternate on

that dBASE starts sending output to the file. Use set alternate off to stop sending output to the file. Sometime later, you can say set alternate on again to resume capture of the output (the new captured output is added to the end of the file), with set alternate off pausing capturing. Finally, when you're through capturing all the data you want, issue the command close alternate to close the file. Then you may exit dBASE and use the file in any manner you wish.

Century

Set century on/OFF - This command determines whether dBASE displays dates as 11/01/86 (example with set century off) or as 11/01/1986 (example with set century on). The default is set century off, so centuries don't normally display.

Append from

Append from is useful for transferring data from one database file to another. For example, if you have a database called names2, and you want to add certain records from the database file names1, you can say:

. use names
. append from names1 for state = "IL"

The first command first makes names2 the current database file, and the second command adds those records from the names1 database that have the state field equal to "IL".

Copy To

Copy to creates a new database from an existing one. For example:

. use names1
. copy to names2 fields name, state

This command creates a new database called names2 that contains just the name and state fields from the names1 database. You can use a for condition to copy only certain records.

Total

The Total command creates a summary database. For example, you may wish to know the total of the amount fields for each state. This command will do that:

. use names
. total to summary on state

This command creates a database called Summary that contains as many records as there were individual states in the Names database. For each record, the field amount contains the sum of all the amount fields for each state. The effect is much like creating a report with a subtotal on the field state.

You must sort or index the database on the key field (state in this example). Total can accept a condition with the For statement, as in

. total to summary on state for company = "IBM"

Using More than One Database

A key feature of dBASE III Plus is its relational capability, meaning it can work with more than one database file at a time. This feature is important for advanced business applications for several reasons. Say we want to track student enrollment records for a high school or university. We might design a database called students with fields like these: sn, name, address, and other biographical information about the student. Then, we need to keep track of all the classes the students have enrolled in. How can we keep track of this? One way is to add fields to the students database called class, year, and grade.

With this database structure, every time a student enrolls in a class we'd create a new record in the students database, filling in the biographical information about the student, and then the class, year, and later on, the grade information. Since each student may take many classes, this structure duplicates biographical information like name and address many times, wasting storage space, requiring extra effort for data entry, and leading to uncertainty about the information. If an error is made entering the address, so that different addresses appear for the student on different class records, which address is the correct one?

To avoid entering the biographical information needlessly, we might create a database with a structure like this: sn, name, and address, and then fields for the classes: class1, year1, and grade1 for the first class, class2, year2, and grade2 for the second class, and so forth. This database structure is inefficient, too. We'd have to declare as many class, year, and grade fields as we anticipate a student ever enrolling in. Students who take just a few classes and then leave the school have just as many fields in their records as do students who take 50 classes, but their records are mostly empty-a waste of space.

Furthermore, this database structure makes it difficult to answer questions like "Did John Doe ever take Chemistry 101?" Since any class could occur within any of the class fields, you'd have to search for a record with these conditions: the name is "John Doe" and class1 is "Chemistry 101" or class2 is "Chemistry 101" or class3 is "Chemistry 101" and so forth, all the way through class50 is "Chemistry 101." Searching for a particular class taken in a certain year takes even more work.

Suppose, instead, that we restrict the students database to just the sn, name, address, and other biographical information, and created a second database called classes. This second database contains the fields sn, class, year, and grade.

The students database contains just one record for each student in school, without duplication of information. The classes database contains one record for each class a student takes. Whenever a student enrolls in a class, we fill out a record for just the classes database, entering the student number, the class title, year, and grade. The key to making the system work is that each database shares a common piece of information, the student number.

This database design cures the duplication of information problem with the first design. If a student enrolls in just one class and drops out, only one record in classes is created. If a student takes 200 classes, there are 200 records for that student in classes. It also cures the query problems associated with the second design. For example, to see whether a student has ever taken a class, simply look for a record in the classes database where sn equals the appropriate student number, and class equals the class you're looking for.

This type of relationship is called a many-to-one relationship, because we have many classes for one student. Similar situation occur frequently. For example, an inventory may contain several thousand parts, but each vendor may supply dozens of parts. You'd have a database called inventory that has a field called supplier#, and a second database called suppliers that contains supplier# and information about the supplier. For invoicing, where you send many invoices to the same customers, you'd link two database files with the customer name or customer number.

dBASE III Plus has three methods of coordinating multiple data files. One method is the join command, which combines two databases into one based upon some condition. For example, with the students and classes example, you could join the two databases into a third database. The appropriate condition is that the student number from students equals the student number from classes. The resulting database from the join is like the inefficient method of tracking the enrollment--a single file with all the student information for each class. That's not necessarily bad, as the purpose of the join is to get a database to print. You'll have to erase it soon, however, because as soon as you add a new class or student the database becomes obsolete-its information is no longer current.

The second method of coordinating the databases uses the set relation command, which instructs dBASE III Plus to automatically move in a second database when you move in a first. For example, if you wanted to browse through the classes database and have dBASE III Plus keep the students database in synchronization with classes, you'd use set relation. It's good for many-to-one relationships like this, where there are many classes being related to one student, but not too helpful in the opposite situation, the one-to-many relationship, where there's one student related to many classes. For situations like this, which arise when printing transcripts or report cards, the programming language or the join command provide a solution.

Select

dBASE can work with up to 10 databases at one time. Each open database occupies a separate work area called a select area. For example, to use two databases at once, you could say:

. select 1
. use names index name
. select 2
. use accounts index number

In this case, we have two select areas active. Select area 1 contains the names database, and select area 2 contains the accounts database. To use the names database, you need to change the select area with the command

. select 1

and to go back to the accounts database, use

. select 2

When in select area 1, you can access data in the database in select area 2 by using the "->" pointer. For example, if the database in select area 1 contains a field called name, then you can do this:

. select 1
. use data1
. select 2
. use data2 (Note that select area 2 is now active)
. ? data1->name
Jones, Jack

Note that we said ? data1->name. The "data1" is called the alias of the database in select area 1, and it is normally the same as the database's name. Then we follow the alias name with "->", which is the select area pointer, meaning that dBASE is to look to select area 1 in this case to find the following value.

Set Relation

The Set Relation command provides a way of linking two database files together. For example:

. select 1
. use names index cnum
. select 2
. use invoices
. set relation to cnum into names

Now, whenever the database record pointer moves in the accounts database in select area 2, it also moves the record pointer in the names database in select area 1. dBASE moves the record pointer for select area 1 so that the field named Cnum always contains the same value in both the Names and Invoices databases. Therefore, if the record pointer in the Invoices database moves so that the value of Cnum for a record is 1002, dBASE immediately moves the record pointer in the Names database to the first record that has 1002 in its Cnum field. In this way you can keep two databases synchronized on a common field.

You must satisfy these two conditions for the Set Relation command to work: There must be a common field in the two databases, and the database you are relating "into" must be indexed on the related field. This "related into" field need not have the same name as the common field in the "relating" database. dBASE attaches the relation to the field that the "related into" database is indexed on.

When in select area 1, you can access data in the database in select area 2 by using the "->" pointer. For example:

. select 1
. ? invoices->amount

This displays the value of the field amount in the invoices database, even though we are in select area 1 for the names database. The word to the left of the -> (Invoices in this example) is called the "alias" for the database. It is simply a name for the select area. You can define aliases like this:

. select 4
. use names index lastname alias x

To refer to a field name in this database when in a select area other than 4, you say

. ? x->state

The "x" is the alias for the database names in select area 4. dBASE makes the alias the same as the database name if you do not supply one in the Use statement. You can give any alias name to any database, but it's generally most convenient to use the same alias name as the database name.

Command Files and Programming in dBASE III Plus

Command files are a way of automating many dBASE tasks. You can group dBASE commands together and execute them all at once. dBASE also provides a series of control statements that control the flow of command execution. dBASE also provides memory variables, which are locations to store calculation results and other temporary data.

Memory Variables

Memory variables are temporary storage areas that can hold the same values that fields can hold. The difference is that while the value of a field changes as dBASE moves from record to record, the value of a memory variable is constant until you change it.

To create a memory variable, you give it a name (the naming rules are the same as for field names) and use the equals sign to give it a value. For example,

. name = "John Smith"
. balance = 34,565.45
. paid = .T.

dBASE senses the type of data you assign to the memory variable and assigns the data type accordingly.

You can examine the values for memory variables with the question mark, as in

. ? name
John Smith

The command Display Memory will display all the memory variables in use at the time, along with their data types and current values.

dBASE has a limit on the number of memory variables active at any one time. Therefore, you should cancel memory variables when they're no longer useful. The Release command does this:

. release name, balance

Saving Memory Variables

At times you may want to save memory variables to a disk file so you can use them again in another session. The Save command performs this task:

. save to mems

This command creates a disk file called Mems that contains all the memory variables in use and their values.

Retrieving Memory Variables

To retrieve the memory variables from a disk file, use the Restore command:

. restore from mems

This command retrieves all the memory variables and their values from the disk file mems. It restores all the memory variables to the exact same state as when you issued the Save command.

dBASE includes several useful options to the Save and Restore commands. One option allows you to save and restore a subset of the memory variables. For example, the command

. save all like net* to mems

saves memory variables that have names that start with the characters "net", like Netbalance, netdue, netamount, etc. The Restore command works the same. The command

. save all like net? to mems

saves memory variables that have names that start with the characters "net", followed by any single character, like "Net1", "Net2", "Netf". etc. The wildcard characters "*" and "?" work just like the wildcard characters in MS DOS.

The Restore command has one final option. Normally when you restore memory variables from a disk file, dBASE erases all current memory variables. If you have memory variables that you want to remain active after you issue the restore command, use the Additive option like this:

. restore from mems additive

This command will leave current memory variables undisturbed, and simply adds the new memory variables to the active memory variables.

Accept

The dBASE Accept commands allows you to query the keyboard for a character value. The syntax of the command is

. accept "Enter the name to search for:" to lookname

The command produces the screen display:

Enter the name to search for:

The operator then types in a name, and the name is stored in the memory variable Lookname.

The Accept command is a good way to input character data to memory variables. Use Accept only for character values. If you use Accept for numeric or date values, you will have to convert them to numeric data with the Val() function, or to dates with the Ctod() function.

Input

The Input command is much like the Accept command, but Input can accept numeric, date and logical values in addition to character values. For example,

. input "Enter amount due:" to amount

prompts the operator to enter a number, which is then stored in the memory variable Amount.

Use Input to accept numeric data. If you use Input for character data, you must enclose it in quotes.

Wait

The Wait command is useful for entering a single character to a memory variable. Wait is usually used to allow the operator to review the screen and continue. For example,

. wait "Press any key to continue..." to ready

In the Accept and Input commands, the operator must press the return key after entering the data. The Wait command, however, acts immediately upon pressing the key.

Say and Get

The "?" command displays the value of a variable or expression on the screen. This command, however, gives us little control over how the information looks or where on the screen it is displayed. The @Say command gives us the flexibility we need. The @Say command looks like this:

@ row, column say expression

For example,

@ 5,10 say amount

This command displays the variable amount at row 5 and column 10 of the screen.

The rows and columns are numbered starting with the top left corner of the screen being row 1, column 1. There are 80 columns and 24 rows on the screen.

@Say can display only one item at a time. Therefore, you can't say

@ 5,10 say lastname, firstname

but you can say

@ 5,10 say lastname + ", " + firstname

The row and column numbers can be expressions, as in

. row = 5
. column = 18
. @ row, column say amount*1.5

You can retrieve the current row and column from the functions Row() and Column().

The Set Print command directs all output except that output produced by the @Say statement to the printer. For example, to get a listing of your database, you could say

. set print on
. display all
. set print off

The Set Device To command works like the Set Print statement, but the output from @Say commands are sent to the printer. To start printed output, say

. set device to print

and to cease printed output say

. set device to screen

Say and Get

The @Say command displays formatted information on the screen or printer. The @Get statement retrieves formatted information from the screen. It looks like this:

@ row, column say message get variable

For example

@ 5, 10 say "Enter First Name:" get fname

This example prints the message "Enter First Name:" and provides a space to fill in the data. You can have many @Get statements in a row to create a form for data entry:

@ 5, 10 say "Enter First Name:" get fname
@ 7, 10 say "Enter Last Name:" get lname
@ 9, 10 say "Enter Hours Worked:" get hours

and so forth.

Get

When dBASE encounters a @Get statement, it displays the message and entry space on the screen and continues with the next statement. It is not until dBASE encounters a Read statement that dBASE stops and allows entry to the fields. Therefore, the example above needs to look like this:

@ 5, 10 say "Enter First Name:" get fname
@ 7, 10 say "Enter Last Name:" get lname
@ 9, 10 say "Enter Hours Worked:" get hours

Read

After dBASE encounters the Read statement, it moves the cursor to the first field on the screen. You can edit or type the data in that field, and then press the return or down arrow key to move to the next field. You can also use the up arrow to move up the screen to previously displayed fields. This is called "full-screen" displaying. After you move the cursor off the screen by pressing the return key in the last field, dBASE actually "reads" the screen and moves on to the next statement in the program. It's similar to the dBASE EDIT command, but you create your own prompts and allow access to only those data items you want. You can also enter data to memory variables with @Get.

Format Files

Many times you may want to use the same sequence of @Say and @Get statements several times in a program. Instead of repeating the same statements over and over, dBASE allows you to create a format file containing the @Say and @Get statements. You use the Modify Command command and supply a filename with the extension ".fmt", and type in the @Say and @Get commands. Don't use any commands other than @Say and @Get. Then you use the Set Format To command in the program file to use the format statements in the format file.

For example, you might want to take the last format statement example and type them in a file called Editscrn.fmt. You could then say

. set format to editscrn
. read

and this would be exactly equivalent to

. @ 5, 10 say "Enter First Name:" get fname
. @ 7, 10 say "Enter Last Name:" get lname
. @ 9, 10 say "Enter Hours Worked:" get hours
. read

The statement Set Format to closes the current format file.

The Clear command clears the screen display and resets the current row and current column values to one and one, which is the top left corner of the screen.

If

Many times you want to perform different actions based upon the value of a field or memory variable. The If command gives us this capability. The structure is like this:

If condition
statement group 1
else
statement group 2
endif

For example, suppose you want to replace the value in the field overtime with "Y" if a person worked over 40 hours. This statement will work:

if hours > 40
replace overtime with "Y"
endif

The condition in this example is "hours > 40", and the statement to execute if this is true is replace overtime with "Y". If the condition is false, that is if the value in the field hours is 40 or less, then dBASE jumps to the statement after the Endif statement and goes from there. This If statement does not have an Else part.

The Else part is useful in this case: Suppose you want to make the overtime field be "Y" if the hours field is over 40, but "N" if it is 40 or less. You could say

if hours > 40
replace overtime with "Y"
endif
if hours <= 40
replace overtime with "N"
endif

but a more efficient statement is

if hours > 40
replace overtime with "Y"
else
replace overtime with "N"
endif

In this case, dBASE evaluates the condition hours > 40. If it is true, dBASE performs the statement replace overtime with "Y" and goes to the statement after the endif statement. If hours > 40 is false, then dBASE performs the statement in the else part, replace overtime with "N", and goes to the statement after the endif statement.

The statement groups can be longer than one line:

if state = "IL"
replace amount with amount * 1.1
count = count + 1
tamount = tamount + amount
endif

Remember to include the endif statement at the appropriate point. For example, in the above construction, the three statements between the If and Endif statements are executed only if the condition is true, that is, if state = "IL". This example is different:

if state = "IL"
replace amount with amount * 1.1
count = count + 1
endif
tamount = tamount + amount

In this example, the statement tamount = tamount + amount is executed all the time, because it lies outside the scope of the If statement, that is, after the Endif statement.

It's customary to indent statements contained within an If--Endif block or other programming control structures. The indenting is entirely optional and does not affect the meaning of the program, but makes the program easier to read and understand by visually showing the program structure.

Do While

Many times you want to set up a group of statements to execute over and over again. The Do While statement gives this capability. The structure is like this:

do while condition
statements
enddo

dBASE executes the statements between the do while and enddo statements as long as the condition is true.

For example,

counter = 1
do while counter <= 10
? counter
counter = counter + 1
enddo

This program prints the integers from one to ten. We start by initializing the memory variable Counter. Then we come to the Do While statement. dBASE evaluates the condition, which is "Counter <= 10". Since it is true, dBASE enters the body of the Do While statement and executes the two statements

? counter
counter = counter + 1

Then dBASE comes to the Enddo statement. The ENddo statement causes dBASE to branch back to the Do While statement and evaluate the condition again. If the condition is true, dBASE enters the body of the loop again. If the condition is false, then dBASE goes to the next statement after the Enddo statement and continues there. In this example, dBASE will execute the loop ten times, after which the value of Counter becomes 11, and the condition is no longer true, so dBASE exits the Do While loop and continues execution with the first statement after the Enddo statement.

You can see that Do While loops must contain some element in the condition that changes and will eventually cause the condition to become false, otherwise the loop would execute forever. Also, use care in placing the Enddo statement, as in placing the Endif statement.

Do While loops are useful for processing an entire database with more complicated actions than the Replace command will allow. You can use the Eof() function to control the Do While loop in this case. The Eof() function becomes true when you try to access a record after the last record in the file. Also, remember that the Skip command moves the database record pointer to the next record in the file.

For example, suppose you have a database of weekly hours worked, and you want to compute regular and overtime pay. First, compute Regpay by multiplying Hours and Rate. Then, you want to make the character field Overtime contain "Y" if the employee worked over 40 hours, and "N" otherwise. Also, the numeric field Otpay should contain 50% of the employee's pay rate times the number of hours worked over 40, but only if the employee worked overtime, of course. This program will do this:

goto top
do while .not. eof()
replace regpay with hours * rate
if hours > 40
replace overtime with "Y"
replace otpay with .50 * rate * (hours - 40)
else
replace overtime with "N"
endif
skip
enddo
? "Pay calculation finished."

The controlling factor in this Do While loop is the condition ".not. Eof()". While the database record pointer is pointing to any valid record number, Eof() is false, so .not. Eof() is true, and the loop executes. But as soon as dBASE tries to access a record past the last record in the database, Eof() becomes true, so .not. Eof() is false, and the loop stops.

Within the loop, the statement "replace regpay with hours * rate" is always executed, as it is not within any If statement. This is because we want to calculate the regular pay for every employee. The If statement is similar to the previous example. Then we use the Skip command to position the database record pointer to the next employee's record. Then the Enddo statement causes dBASE to jump back to the Do While statement and evaluate the looping condition again.

The looping continues until the Skip statement positions the database record pointer past the last record in the database. Then .not. Eof() becomes false, and dBASE jumps to the statement past the Enddo statement, and prints the message "Pay calculation finished."

Loop

Two statements that modify the Do WHile looping are the Loop and Exit statements. The Loop statement causes dBASE to jump back to the Do While statement and evaluate the condition again. For example, to process only those employee's records who worked overtime, you could do this:

do while .not. eof()
if overtime = "N"
skip
loop
else
(Processing statements go here)
skip
endif
enddo

Exit

The Exit statement exits the Do While loop altogether and goes to the next statement following the Enddo statement. A simple example is like this:

do while .T.
accept "Do you want to continue (Y-N)?" to x
IF x = "N"
exit
endif
enddo

This program asks the question "Do you want to continue (Y-N)" and accepts the answer to the memory variable x. It then tests to see if x is "N", in which case the program executes the Exit statement and stops. Otherwise, the program loops again.

Note that when testing input from the keyboard, you must consider both upper and lower case input. For example, in the above program, the user could respond either "N" or "n", but this program will correctly interpret only the upper case input. You could say

IF x = "N" .OR. x = "n"

or equivalently,

IF upper(x) = "N"

The Do Case Statement

The Do Case statement provides an elegant method of performing different actions according to the value of a variable or field. You can always simulate the action of the Do Case statement with the If statement, but the If statement will often be quite complex and difficult to read. The case statement looks like this:

do case
case condition 1
Statement group 1
case condition 2
Statement group 2
otherwise
Statement group for otherwise
endcase

The Do Case statement works this way: dBASE evaluates condition 1. If it is true, it executes statement group 1 and goes to the statement following the Endcase statement. If condition 1 is false, then dBASE evaluates condition 2 to see if it is true, and so forth. If no condition is true, the dBASE executes the statements for the Otherwise condition, if it is included.

Suppose you want to process a database and perform different actions based on the employee's sex. You could do this:

goto top
do while .not. eof()
do case
case gender = "M"
? "Employee is male"
case gender = "F"
? "Employee is female"
otherwise
? "Error - Gender not recorded or is"
? "erroneous for this employee"
endcase
skip
enddo

Of course, instead of simply displaying a message as above, you could perform more complicated, multi-statement operations. The Otherwise statement is always optional and is not necessary in all cases. But in this case, if nothing was ever entered for the gender field, or if it was something other than "M" or "F", then we would not know that if we had no Otherwise statement, because dBASE would simply display no message and go on to the next record.

Set Commands for Debugging

Set Talk determines whether dBASE displays the results of command lines. When Set Talk is on, dBASE displays the results, when off, it doesn't. The command looks like this:

. set talk off

Usually, when running a dBASE program, you want Set Talk Off.

The Set Echo command displays command lines as they are executed. This is useful for debugging purposes, as you can see the command line after all the substitutions have been made. It looks like this:

. set echo on

Echo is normally off.

Set Debug is useful when used with Set Echo. Set Echo sends the command lines to the screen, and having both the program output and the echoed lines on the same screen is sometimes confusing. Set Debug sends the output of the Set Echo command to the printer instead of the screen. The command looks like this:

. set debug on

When Debug is on, the output goes to the printer; when off, it goes to the screen.

Set Step allows to you execute a dBASE command program one line at a time. This is useful when debugging, so that the lines don't fly past the top of the screen. When Set Step on, dBASE gives the message "Type any key to step - Esc to cancel" before each line of the program. You can type a key such as the spacebar to continue, or press the escape key to stop the program.