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

The goal of this system is to make changes to an inventory database. Specifically, the database contains the number of items in stock (or on hand) for each part. At the end of the day, we want to make adjustments to the number of items in stock based upon the day's sales.

The important fields of the database file are partno, a character field that contains the part number, dept, a character field that contains the department, and onhand, a numeric field that contains the number of items in stock for each part. The application use two index files: dept, based on the dept (department) field, and partno, based on the partno (part number) field.

This program is quite simple for ease of understanding. It is by no means comprehensive, and couldn't be used in real life. Later, we'll modify this program to make it more comprehensive and usable.

Pseudo-code is the steps of the program in English language. It's good to write (or at least think through) your programs in pseudo-code first before actually writing the dBASE program. In this way, you can often spot problems or deficiencies in the program before you actually start writing program code.

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 If you've worked with any programming language, you'll recognize that dBASE's memory variables are like the variables in most languages..

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,

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

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:

The dBASE accept command allows you to query the operator at the keyboard for a character value. The syntax of the command is

The command produces the screen display:

The operator then types in a name and presses the Enter key, 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.

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,

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. If the operator mistakenly types an invalid numeric character, dBASE produces erratic results.

This program adds a few features to the sell1.prg program. First, it uses the set talk off command so that dBASE's reports of what it has done doesn't interfere with out reports. Second, it uses the upper() function so that the operator doesn't have to type the part numbers in capital letters. Finally, it displays a few lines of data that report on the replacement that the program made.

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

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

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

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

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

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

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

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

then you get

If you say

then you get

The best way is to say

which gives

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

to get

Note the space after the comma.

The Rtrim function is identical to the Trim function.

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

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

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

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

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

Note that you can't say

because the + operator accepts only character type data.

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.

This program adds a check to see if the part number that the operator typed in exists in the database. This is an important step, as in the previous programs, if the operator typed an incorrect part number, the seek command left the record pointer at the end of the database file, and the subsequent replace command operates on the last record.

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:

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:

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

but a more efficient statement is

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:

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:

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.

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,

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.

This program adds a do while loop so that the program keeps executing over and over. Prior to this program, if the operators had a number of adjustments to make, they would need to run the program once for each adjustment. Now the sell5 program will run again and again, until the operator enters zero for the part number.

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:

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

For example,

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

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:

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

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:

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:

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

or equivalently,

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:

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:

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:

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.