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

Introduction

dBASE III Plus is database software, which means it manipulates data stored in a database. While that seems like a trivial concept, it's actually quite important. Database programs like dBASE III Plus require that your data fit into the rigid structure that a database imposes, and if your data won't fit into that structure, you can't use dBASE III Plus to work with it.

dBASE III Plus is also general purpose software. That means that dBASE III Plus is "blank" when you first start it. It knows nothing about a address list, accounting system, or balancing a checkbook. dBASE III Plus, instead, contains a large number of commands, operators, and functions that can perform the actions necessary to perform the work you want to do. It's your job to build your system with these facilities that dBASE III Plus provides. This means that you must know the precise procedures you need to follow to do your work, and then know how to use dBASE III Plus well enough to get it to do just that.

Using dBASE III Plus can be easy or difficult, depending on the nature of your project and your level of experience. The important thing is to start small and master the fundamentals of dBASE. Once your feet are wet and you've successfully created a number of small projects, try your hand at larger projects.

General Principles

Using dBASE or any other computer program is not difficult. The computer does, however, demand a high degree of precision in your work. For example, you'll find out that many dBASE commands use English words. This does not mean, however, that dBASE understands English in the same way that humans do. As humans, we can mumble a sentence, say words in incorrect order, and speak in approximations, and others will be able to understand our meaning. But the dBASE commands each have a highly specific meaning, and you must type the correct command to produce what you want. Approximations won't do.

Second, be aware of the current situation. If you press a key or issue a command by mistake, dBASE will probably do something you didn't intend. If you proceed as though you entered the correct command, you'll probably make additional errors that will require correction. Be aware of what the current situation is, and respond appropriately.

Third, be aware of the capabilities and limitations of dBASE. To familiarize yourself with what it can do, read the entire documentation book. You don't have to read it and understand how to do everything right now, but read it to be aware of features that exist, and in what situations you might use them. For example, if you never read far enough to learn that dBASE has a variety of built-in functions to work with dates, you might find yourself spending hours trying to write programs to work with dates. But having read the documentation from front to back, you'll be aware that dBASE has these date functions, and when it's time to work with dates, you can read about the date functions to learn how to use them.

As for limitations of dBASE and problems with dBASE, the best advice is to start small and then do the big project. For example, dBASE can sort data on up to five fields, but a field is the smallest unit of data that dBASE can sort on. Suppose that you create a database for a mailing list where you store the person's name in one field, typing the names like John Doe and Mary Baker. You have someone enter 20,000 names to this database. Now it's time to print the mailing labels, sorted by last name. Sadly, you'll find that dBASE won't be able to sort this database by last name, just by first name (and not even accurately by first name). If you entered a dozen or so names and tried to do the sort, you'd realize your mistake (you need two fields, one for the first name and another for the last name) when the cost of the mistake is still small. dBASE is flexible to work its way out of this situation without having to re-enter the 20,000 names, but it will take some programming work and more than a casual acquaintance with dBASE III Plus.

The moral: Start small, learn the ins and outs of dBASE along with the techniques used to tackle specific solutions, and then proceed to the large, complex projects.

Remember that you, and not dBASE III Plus, bear the responsibility for the correctness of your results. dBASE is perfectly happy to perform any type of procedure that you call for, as long as it's allowed and dBASE knows how to do it. But merely being able to do something does not mean that it's right to do it, or that the results make sense. Unfortunately, many of your incorrect procedures or commands will not produce error messages from dBASE. And verification of complex procedures, such as posting journal entries from one account file to another, is not often easy.

Finally, use your imagination. It's impossible for anyone to teach you all there is to know about dBASE III Plus (or any other software) and how to apply its features to your situation. Your imagination and experimentation will lead you towards getting the most out of your software.

What is a Database

A database is simply a collection of data.

It's not quite as simple as that; database programs like dBASE require that your data fit into a certain structure. The telephone book is an example of a highly structured database, containing three pieces of information repeated over and over--name, address, and telephone number. A mailing list is a more elaborate example of a database, containing the information in the telephone book, plus company names, zip codes, and other information. The telephone company's database is even more elaborate, containing all the above information plus a record of calls, charges, and payments.

Accounting systems are examples of complex database systems. A database system is a term describing all the individual database files plus all the programs and procedures used to manipulate the data. For example, an accounting system has many different databases--the chart of accounts, the collection of journal entries, the list of customers and creditors, and so forth. The accounting system also has programs and procedures to input data, perform calculations, post data from one database to another, and prepare printed reports.

Database Structure

Fields

The fundamental piece of data in a database is the field. A field is the smallest piece of data you want to manipulate apart from the rest of the data. What constitutes a field will vary according to the needs of the application.

For example, if you keep track of customers, you probably have their names and address stored in a Rolodex. Each card looks something like this:

Washington, Harold
Mayor
City of Chicago
100 N. Lasalle Street
Chicago IL 60602

and the next one might look like this:

Baker, John
President
ABC Company
1 N. State Street
Chicago IL 60602

We might replace the actual data on these cards with a template card that looks like this:

Name
Title
Company
Address
City State Zip

Each piece of data, like name, title, and company, is called a field. In this case, our database has seven fields: name, title, company, address, city, state, and zip. Each field has its name, and a value at a particular time. For example, for the first card, the field title has the value "Mayor". For the second card, it has the value "President".

Remember, each field is the smallest piece of information you can deal with conveniently. For example, with this design, it is not easy to extract a customer's first name from the name field (dBASE does provide this capability, but it is cumbersome). Thus, if you need access to the first and last names independently of each other, it would be better to create two fields, called fname and lname, and perhaps even a third one called mi for the middle initial.

Records

In a database, fields are grouped into records. In our example above, each record is the collection of the name, title, company, address, city, state, and zip fields for each customer. Thus, records are analogous to each card in the Rolodex file. Your database will have as many records as customers you want to keep track of.

Records are grouped together in files, also called the database file. A database file, then, represents the entire Rolodex card file. dBASE stores database files as regular DOS files with the extension .dbf.

With dBASE, you can have as many database files as you like. Each file will probably have a different field structure to suit the needs for the data. Files are stored on disks, either floppy or hard disks. With dBASE, the limit of the size of any database is the size of the disk drive, so those with hard drives can make larger databases than those with floppy diskette drives.

Field Types

dBASE fields are strongly typed, meaning that a field is designed to hold a particular type of data, such as characters, numbers, or dates. By forcing you to declare a field as numeric or date, for example, dBASE gains knowledge about your data that gives it capabilities beyond simpler database programs that don't use typed fields.

dBASE also requires a length for its fields. The length, measured in number of characters, defines the amount of storage each field occupies in the database. Field lengths are sometimes easy to choose; a zip code is five characters (maybe not if you're using the nine character zip codes), but for names and addresses, the field length is not so easy to determine.

You define the type of data a field is to hold, along with its length, when you create the database.

Character Fields

Character data consists of alphabetic characters, numerals, and punctuation characters. Use character data type for names, addresses, descriptions, part numbers, telephone numbers, and so forth. Character fields can be from one to 254 characters in length. You specify the length for character fields when you define the database.

Numeric Fields

Numeric data types are used to store numbers for calculation. In dBASE, numbers can be up to 19 digits long, including the minus sign and decimal point. Accuracy, however, is limited to 15.9 digits. This means that the largest accurate integer is 999,999,999,999,999 and the largest accurate money amount is $9,999,999,999,999.99. You specify the maximum length and number of decimal places for a numeric field when you define the database.

What type of field should you use for data like zip codes, social security numbers, part numbers, and the like? Although a zip code could be stored in a numeric field, it's usually best to store it in a character field. Reserve numeric fields for "true" numbers, such as money, weights and measures, and items that you intend to do arithmetic with. "Pseudo-numbers" like social security numbers, telephone numbers, and zip codes are best stored as character fields.

Logical Fields

Logical data types are used for "yes-no" data that is either true or false. The characters Y, y, T, and t mean true, and N, n, F, and f mean false. You do not specify a length for logical data; dBASE fills in the default length of one for you.

Date Fields

Date type fields store dates in the form mm/dd/yy. You do not specify a length for date fields, as dBASE fills in the default value of eight characters for you. dBASE includes many date functions that let you extract the day, month, or year from a date field.

It's important to use date fields for dates. You could store a date in a character field, but you'll lose the ability to sort, compare, and manipulate the dates without going through a lot of effort and frustration.

Memo Fields

Memo fields are a special type of field that let you store free-form text in an efficient manner. When entering or editing data in a memo field, you use the dBASE III Plus word processor, which provides simple word processing-like functions. Memo fields can be up to 4,000 characters long. dBASE stores memo field data in a special file database text file with the same file name as the database, but with the extension .dbt. You do not specify a length for a memo field, as it grows as you add more text. Memo fields do, however, use 10 bytes (or characters) of storage for each record in the database, no matter how little text is actually stored in the memo field.

While it appears that memo fields provide a solution for storing textual material, memo fields are quite limited as to what you can do with the data in them. You can't, for example, search through memo fields looking for a particular word or phrase. You can do so with data in character fields.

Using dBASE III Plus

dBASE III Plus provides three modes of operation--the dot prompt, the Assistant, and the programming language. Dot prompt mode was a only method of using dBASE II, and it's still used with dBASE III Plus. In this mode, you type a command, dBASE III Plus interprets it and performs the action the command calls for. The dot prompt reappears, and you can enter another command. It's not unlike entering DOS commands to the A> or C> prompts.

The Assistant is a mode of using dBASE III Plus that was first introduced in dBASE III and enhanced in Plus. The Assistant presents a series of menus that allow you to issue dBASE III Plus commands without memorizing the command syntax. The Assistant is useful for those new to dBASE III Plus, as they won't have to memorize the syntax of the dot prompt commands. It's also a useful learning tool, because as the Assistant generates the command, it displays the command at the bottom of the screen. Thus, you can learn dBASE III Plus commands as you execute them.

The Assistant provides menus for selecting and executing many commands. The method dBASE III Plus uses for selecting menu items is not as smooth as in programs like Lotus 1-2-3 or even Ashton-Tate's own Framework II. To the Assistant's credit, the bottom of the screen shows the equivalent dot prompt command as you build it through the menu choices. That's important, because most people will eventually gravitate towards the dot prompt method of working with dBASE III Plus, and when you're at the dot prompt, you must type the complete command yourself.

The Assistant is a useful adjunct to dot prompt mode, but that's what it is--an adjunct. You'll eventually want to learn to enter commands at the dot prompt, as learning to use the commands at the dot prompt is the first step in learning to write programs in dBASE III Plus.

The programming language, present throughout all versions of dBASE, is a way of grouping dBASE III Plus commands into a command file and executing them all at once, without intervention by the operator. This mode is useful for repetitive tasks, and for allowing those not familiar with dBASE III Plus commands to use the program. It is also the mode used for creating menu-driven applications and other complex information management systems.

Writing simple, macro-like programs is easy--just type the commands into the program editor as you would at the dot prompt. When you run the program, dBASE III Plus executes the commands in turn, just as though you are typing them yourself. But real programs, the types of programs needed to perform the complex tasks that many people want to do, requires considerable skill and practice in programming a computer.

Starting dBASE III Plus

First, start your computer with the procedure you normally follow. You'll eventually get to either the A> or C> prompt, depending on whether your computer has a hard disk drive. Don't bypass the date and time prompts--dBASE can make effective use of today's date and time in queries and calculations, but only if you input it correctly.

If your computer has two floppy diskette drives and no hard disk drive, insert the dBASE III Plus System Disk #1 in disk drive A, insert your data diskette in drive B, type dbase and press Enter. In a moment, dBASE will ask for System Disk #2. When it does, remove System Disk #1 from drive A and replace it with System Disk #2.

For a system with dBASE installed on a hard disk drive, you must first change to the directory where dBASE is installed. The name of that directory could be anything you desire, but if it is named dbase, the appropriate commands are

cd \dbase
dbase

Exit From the Assistant

In any case, you'll usually see the Assistant screen. The Assistant is a way of using dBASE through menus. We won't use the Assistant, because it's important to gain an understanding of dBASE through typing the commands yourself.

To quit the Assistant and move to the dot prompt, press Esc (the Escape key).

Set the Default Disk Drive

Hard disk drive users won't normally do this--they store their data on the hard disk drive (drive C), which for them is already the default. But floppy diskette users need to tell dBASE that their data diskette is in drive B. To do so, type

set default to b:

Open a Database

Before you can do anything with a database, you must open it with the use command. Our database is called invtory, so type

use invtory

To get a list of database files, use the command dir. To get a list of all files, use the dir *.* command.

What's The Structure?

Once you've opened a database, you may want to examine the database's structure--the names, data types, and lengths of the fields. To examine the structure, type

display structure

The output looks something like this:

Structure for database: C:invtory.dbf
Number of data records: 43
Date of last update : 08/09/88
Field Field Name Type Width Dec
1 PARTNO Character 8
2 DEPT Character 12
3 ONHAND Numeric 4
4 PRICE Numeric 10 2
5 COST Numeric 10 2
6 ROP Numeric 4
7 DESC Character 40
8 LS Date 8
9 TAXABLE Logical 1
** Total ** 98

You can follow this command (and many other dBASE commands) with the words to print to send the output to the printer if you have a printer attached.

A similar command is

list structure

The difference is the display structure command will pause the screen display when it fills. You can then press a key to show the next screen. List structure doesn't pause. Use display structure to view the structure on the screen, and list structure when your goal is to print the structure on the printer.

For this database, the partno field is the items part number, the dept field is the department (Toys, Food, etc.) that this part belongs to, the onhand field is how many of these we have in stock, the price field is the selling price, the cost field is our cost, the rop field is the reorder point for this item, desc is the description, ls is the date on which we last received a shipment of these items, and taxable is whether the item is taxable.

What's in the Database?

To view the data in the database, we'll use the display command at first. The simplest form is

display all

which displays, one screen at a time, all fields of all records. Since the fields of our database won't fit on one line of the screen, dBASE wraps the lines. The result is confusing. To display a more useful result, we can choose the fields we want to see with the fields option to the display command. To view the department, part number, on hand, and price fields, type

display all fields dept, partno, onhand, price

You can display just the fields you want in this manner. Actually, it's not necessary to use the fields command word. This command will work just as well:

display all dept, partno, onhand, price

A similar command is

list

which shows data just as does display all, but doesn't pause as each screen fills. The list command takes all the options that display all does.

The display all and list commands can also calculate data for display. Our database contains the price field, which is our selling price, and the cost field, which is our cost for the part. List the per-unit profit for each part:

display all partno, price - cost, price / cost

For each record, dBASE III Plus shows the partno field, and then subtracts cost from price and shows the result, then divides price by cost and shows the result.

Displaying Selected Records--Making Queries

Queries Against Character Fields

dBASE lets you work with a subset of the database records by making a query. Suppose you want a list of the items in the Toys department. Our job is to formulate a logical expression that's true for the records we're interested in. In this case, we can identify the records we want by using the for option to test the value in the field dept. If the value is Toys, we want to view the data, otherwise, we don't want to see it. This command will do the trick:

display all for dept = "Toys"

In dBASE, when you use character values in commands like this, you enclose the value in single or double quotation marks. dBASE searches are case-sensitive, so you must type the comparison value Toys as it appears in the database. Typing toys won't find the data we want. Use of the for option is important in dBASE, as many commands use it.

If you wish, you can select the fields to display in the result with a command like

display all fields partno, onhand, ls for dept = "Toys"

Add to print at the end of the command to send the result to the printer.

dBASE also searches for character strings contained within fields. Suppose we want to search for all the items that contain the word Gold. We'll use the substring search operator, which looks like this

display all for "Gold"$desc

Interpret the dollar sign as meaning is contained within.

A useful function to use when searching for characters is upper(). This function takes an input value and produces its capitalized equivalent (it has no effect on already capitalized letters). If we compare the capitalized version of the dept field against the all capitals word "TOYS", we won't have to worry about how the data is capitalized:

display all for upper(dept) = "TOYS"

This command doesn't modify any data in the database. The conversion to capitals is for comparison only.

Queries Against Numeric Fields

Now, find all records where the quantity on hand is greater than or equal to 7. In this case, we'll test the field onhand against the numeric value 5. The command is

display all for onhand >= 7

Numeric test values don't appear in quotes. dBASE lets you use these comparison operators with numeric values: = (equals), > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), and <> (is not equal to).

We can also compare one field against another. Our database contains the field onhand, which is the number in stock for each part, and the rop field, which is the reorder point. List all the parts that we need to reorder:

display all for onhand <= rop

Queries Against Dates

You must know two things to make queries against dates. First, dBASE manipulates dates using a serial number scheme. Each day has its own serial number, and the serial number gets larger as time passes. Thus, dates in the past have smaller serial numbers and are considered less than today.

Second, you can't write a statement like for ls = 03/17/88. You must use the ctod (character to date) function as in for ls = ctod("03/17/88"), which does what the first for statement looks like it should do. Example: find all items received before March 1, 1988.

display all for ls < ctod("03/01/88")

Compound Queries

Now let's find the items in the Toys department with five or more on hand. In this case, the records must pass two tests at the same time to be of interest. We combine the conditions with the logical operators .and., .or., or .not. as required. We use .and. because we require the condition dept = "Toys" and the condition onhand >= 5 to be true at the same time. The command is

display all for dept = "Toys" .and. onhand >= 5

Note that you must write and as .and. with the periods. Now, generate a list of items in either the Toys or Food departments. In this case, as long as the record passes either the dept = "Toys" or dept = "Food" tests, we want to view it. Combine the conditions with .or. like this:

display all for dept = "Toys" .or. dept = "Food"

Now find all items received during the month of June, 1988. In this case, the date must be greater than or equal to June1, 1988, and at the same time, less than July 1, 1988. Here's the command:

display all for ls >= ctod("06/01/88") .and. ls < ctod("07/01/88")

When typing a lengthy command like this, just keep typing the command, and dBASE will wrap the command line and move to the next line automatically. Remember, you can use the fields option to restrict the fields that display in the result. Add to print at the end of the command to print the result.

List

The display command is nice enough to pause when the screen fills, letting us view the data conveniently. But if you're using display with to print in order to print the data, you may not want dBASE to pause at each screen. In this case, use list, which looks like this:

list fields partno, onhand for dept = "Toys" to print

You can see that list uses the same command options as does display.

Correcting Typing Errors

If you're typing a command and reach the end of the line, just keep on typing. If you make a mistake and dBASE rejects the command, dBASE will ask whether you want help. Generally, answer N to the offer of help. Then, you'll get the dot prompt back,and you can press Up arrow to view the command you typed. You probably made a typing error, so use Left arrow and Right arrow to move around the command and make corrections. Then press Enter to execute the corrected command.

The Up arrow key will retrieve the last 20 commands you entered. Use this feature--it will save typing effort, and you can see that using dBASE involves a lot of typing.

Counting, Summing, and Averaging

dBASE III Plus can count records with the count command. In its simplest form, type

count

dBASE counts all the records in the database and displays the number. The count command can accept a for clause, so the command

count for dept = "Toys"

displays the number of records in the Toys department. Count can place its result in a memory variable with the command

count for dept = "Toys" to t

dBASE creates a memory variable called t which contains the number of records in the Toys department.

The sum command works similarly to count, but sums numeric fields for all the records in the database. Its simplest form, the command

sum

displays the sum for all numeric fields in the database. Sum accepts a for clause, so the command

sum for dept = "Toys"

sums all numeric fields for just the records in the Toys department. Additionally, you can specify a field list which restricts the operation of the sum command to just the numeric fields of interest. Also, you can type an expression in the sum command. When you do this, dBASE calculates the expression for each record and them sums the result. It doesn't make sense to sum the price and onhand fields, so we can calculate the expression price * onhand for each record and sum it (the * means multiplication). This will give us the value of our inventory.

sum price * onhand for dept = "Toys"

Sum can place the output in a memory variable with a command like

sum value for dept = "Toys" to s

This command places the sum in the memory variable s.

The average command works like the sum command, but returns the average. The command

average price for dept = "Toys"

Adding Data

The most common method of adding data to dBASE is through the append command. At any time after you issue the use command to open a database file, you can type

append

and dBASE presents a form for typing data. When entering data, type the data, and (usually) press Enter to move to the next field. You can use Up arrow, Down arrow, Left arrow, Right arrow, Backspace, and Delete (Del) to move around the form and correct errors. Add this data to the database (observe capitalization--dBASE won't do it for you):

PARTNO A-1150
DEPT Toys
ONHAND 8
PRICE 10.00
COST 5.00
ROP 5
DESC Backgammon Game
LS 08/03/88
TAXABLE T

After you press Enter on the Taxable field, dBASE presents another entry form. Since we're through entering data for now, press Esc to abort the entry process.

An Annoyance--Set Bell and Set Confirm

When you added data with the append command, you usually pressed Enter to move to the next field. Usually, but not always. When you typed the final digit of the price and added fields, dBASE beeped and moved to the next field automatically. This is due to two environmental settings called set bell and set confirm.

When set bell is on (the default state), dBASE beeps when you type and fill a field to capacity. That's annoying, so you may want to type set bell off. When set confirm is off (the default state), dBASE automatically moves to the next field when you type and fill a field to capacity. That's annoying, because you continually must remember whether to press Enter after typing each field. To force you to press Enter after typing each field, even when the field is filled to overflowing, type set confirm on.

Adding a Field to the Database

Woops. We forgot to include a field to hold the value of the inventory for each item. Fortunately, dBASE lets us correct this error of omission with the modify structure command. First, make sure the database invtory is in use. Then, type

modify structure

dBASE presents a form containing one line for each field. Using Down arrow, move until dBASE presents a blank area for a new field (it will appear as you move past the last field). Type value for the name, n for numeric data type, 12 characters long, with 2 decimal places. After typing the decimal places, dBASE presents yet another new field area. Press Enter to indicate we're through typing new fields. Type y when dBASE asks for confirmation.

Field Name Type Width Dec Field Name Type Width Dec
ëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë ëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
1 PARTNO Character 8 9 TAXABLE Logical 1
2 DEPT Character 12 10 VALUE Numeric 12 2
3 ONHAND Numeric 4 0
4 PRICE Numeric 10 2
5 COST Numeric 10 2
6 ROP Numeric 4 0
7 DESC Character 40
8 LS Date 8

The restructuring takes a moment to complete. It will take many moments if your database is large.

Calculating a Field

When we added the value field to the database with modify structure, dBASE left its contents blank. We can use the replace command to fill it with the appropriate values. In this case, the field value should be equal to onhand times price. Since dBASE like almost all computer programs uses the asterisk for multiplication, here's the command:

replace all value with onhand * price

Replace can accept a for clause, so to increase the price of just the items in the Food department by two dollars, we can say

replace all price with price + 2.00 for dept = "Food"

We could increase the price of all the Toys by 10 percent with this command:

replace all price with price * 1.1 for dept = "Toys"

Note that after we perform a command like this, the numbers in the value field will not be current. In other words, dBASE III Plus doesn't record a relationship between the fields and doesn't realize that the value field should always contain price * onhand. Each time you change a price or quantity on hand, then, you'll need to update the value field.

A useful way to use the replace command is to help make the database more consistent. For example, in a database with a field called state, you might want to make sure that the states are stored as all capitals. This command:

replace all state with upper(state)

takes each state, converts it to uppercase, and replaces the old state with the new version.

The Record Pointer and Scopes

At all times, dBASE maintains a pointer, called the database record pointer, that points to one record. The record pointed to is called the current record. dBASE notifies us of the current record on the status line. A display like 14/25 means that the current record is record 14 out of 25 total records.

You can move the record pointer with the goto command. You can say goto 16, which moves to record 16 (note the change on the status line), or goto top and goto bottom. Once you've moved the record pointer, you can do something to that record.

Now we can understand the meaning of the word all in commands like display all and replace all. All is a scope, which tells dBASE what range of the database to apply the command to. The default scope for the display command (and most other dBASE commands that accept scopes) is just the current record. Test it with these commands:

goto 18
display

dBASE displays just record 18, because the default scope, which takes over unless we explicitly type a scope, is the current record. The meaning of the scope all is to move the to the top record, then advance the record pointer record by record, applying the command to each record until the end of the database is reached, a condition called end of file or eof.

Why doesn't a command like display all for dept = "Toys" display all the database records? The scope all means to consider all records for display. The for clause determines which records will actually display, but because of the scope, all will be considered.

Editing Data

Edit

One way to edit data is through the edit command. The most common way to use it is to position the record pointer to the record you want to edit, and then type edit. As in append, you can use Up arrow, Down arrow, Left arrow, and Right arrow to move around the editing form. Use Del (delete) to delete characters. Use Page up and Page down to move from one record to the next. When finished with the edit, use the Exit key (Control-End) to quit.

Browse

The browse command is similar to edit, but it presents the data in a tabular form. Once in browse, you can move about the screen as in edit. Moving past the top or bottom borders of the screen brings additional records into view. Since most database's records will be too wide to view completely in one width of the screen, use the Pan keys (Control-Left arrow and Control-Right arrow) to shift the screen to the left and right.

Change

Change is just like edit, but accepts a scope and the fields and for clauses just like display does. For example, type

change all fields partno, dept, desc for dept = "Sports"

This often provides a more focused edit than the edit command does.

Locating Data

If you want to edit a record, you must first find it. For small databases, that's not much of a problem--just use edit or browse and look around until you find the data. That's not feasible with larger databases.

The locate command provides the answer for some applications. The purpose of locate is to position the record pointer to records that meet your search specification. It's a lot like using display all in that it makes use of the same type of for clause.

We'd like to edit records for items in the Toys department. The command to use is

locate for dept = "Toys"

dBASE moves the record pointer to the top of the database, then examines each record in turn until it comes to one meeting the dept = "Toys" condition. Then dBASE stops and lets you enter additional commands. Type edit and you're editing the record. Press the Exit key (Control-End) when finished.

To look at additional records meeting the for condition, type continue. This command repeats the same locate command, starting with the record after the last record found. You can edit or display it, or use continue (use up arrow for this) again to look for additional matching records. Eventually, dBASE will not find any more records meeting the condition, and says so by reporting end of locate scope.

Reports

dBASE III Plus includes a report generator that overcomes many of the limitations of the display command. First, the report generator lets you use a title at the top of the page, and numbers and dates each page. You can include the columns of data you want, along with custom column headings. The report generator will total numeric columns and make subtotals and sub-subtotals. All this information, the design of the report, is stored in a format file so that the next time you want to print the report, you can easily reuse the same design.

Creating a Report

To create a report, first use the use invtory command if necessary to open the invtory database. We'll create a report format called dept, so the command to type is

create report dept

dBASE presents the menus for creating a report. When using these menus, use Left arrow and Right arrow to move from one menu to the next. Then use Up arrow and Down arrow to highlight the command you want to execute. Then, and this is important, press Enter to actually execute the command.

First, let's enter the page title. The highlight should already be positioned on that command, so press Enter. In the box that appears, enter this information:

Options Groups Columns Locate Exit 10:23:19 am
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Page title _
_ Page width (positions) 80 _ èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Left margin 8 _ _ ACME CONSOLIDATED INDUSTRIES _
_ Right margin 0 _ _ _
_ Lines per page 58 _ _ Inventory Report _
_ Double space report No _ _ _
_ Page eject before printing No _ àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
_ Page eject after printing Yes _
_ Plain page No _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

Press Control-End to complete the entry. We don't need to change other items on the options menu, but the meaning of most options is clear.

To tell the report generator what columns of data to present in the report, use Right arrow to move to the columns menu. The important items on this menu are contents, which is usually the name of the field that should display in this column, heading, which is up to four lines of text to appear at the top of the column, and width, the width, in characters, of the column.

For the first column, we want the dept field. The highlight should be positioned on contents, so press Enter. Type dept and press Enter. Now use Down arrow to move the highlight to heading and press Enter. Type Department and press Enter. dBASE proposes a number as the width of the column. That number is either the width of the field as defined in the database, or the width of the heading, whichever is greater. We'll take dBASE's suggestion for the width, so we're through with this column. The screen should look like this:

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Contents dept _
_ Heading Department _
_ Width 12 _
_ Decimal places _
_ Total this column _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Department _
_ _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

When you're ready, press Page down to move to the form for the next column.

Note that dBASE is creating a prototype of the report near the bottom of the screen.

For the next column, the contents is the field partno and the heading is Part Number. Note the use of two lines in this heading. We'll leave the width at dBASE's suggestion. Press Page down to move to the next column.

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Contents partno _
_ Heading Part;Number _
_ Width 8 _
_ Decimal places _
_ Total this column _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Part _
_ Number _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

For the next column, the contents is the field onhand and the heading is Quantity On Hand. Since this is a numeric field, dBASE proposes to total this column by showing Yes after Total this column. Since it doesn't make sense to calculate this total, move the highlight to that menu item and press Enter. That changes the Yes to No. Press Page down to move to the next column.

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Contents onhand _
_ Heading Quantity;On Hand _
_ Width 8 _
_ Decimal places 0 _
_ Total this column No _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Quantity _
_ On Hand _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

The next column should have its contents as price, and the heading as Selling Price. Don't total this column.

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Contents Price _
_ Heading Selling;Price _
_ Width 10 _
_ Decimal places 2 _
_ Total this column No _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Selling _
_ Price _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

The fifth column's contents is price * onhand, and the heading is Total Value. This illustrates that the contents of a field can be a calculated expression as well as a regular field. This column should be totaled, so leave Total this column as Yes.

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Contents price * onhand _
_ Heading Total Value _
_ Width 12 _
_ Decimal places 2 _
_ Total this column Yes _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ
èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Total Value _
_ _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

Finally, we're finished defining our report, so use Right arrow to move to the Exit menu and select Save. This action saves the report formatting information to disk and returns to the dot prompt.

Printing the Report

To print our report, use this command:

report form dept

The report displays on the screen. Use Control-s to stop the screen; press any key to resume display. Follow this command with the words to print to print the report.

Report form also accepts a for clause, so it's possible to say

report form dept for onhand >= 5

Report form does not accept the fields option as does display.

A Report with Subtotals

We'd like to enhance our dept report so that it shows the subtotal of the field value for each department. The key concept to remember is that the field dept controls when dBASE should start a new department group.

We'll modify the dept report design to include the subtotals. Use the command

modify report dept

Use Right arrow to move to the Groups menu. Press Enter on Group on expression, type dept, and press Enter. Use Down arrow to move the highlight to Group heading, type Subtotal for Department:, and press Enter. Now, since we're finished, use Right arrow to move to the Exit menu and select Save.

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ Group on expression dept _
_ Group heading Total for Department: _
_ Summary report only No _
_ Page eject after group No _
_ Sub-group on expression _
_ Sub-group heading _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

To print the report, use report form dept as usual. This report, unfortunately, doesn't work. The inventory items aren't grouped properly.

Sorting

The dept report subtotals don't work because the database must be sorted on the dept field. Sorting in dBASE is easy. The key concept to remember is that sorting creates a new database. The sort command looks like this:

sort on dept to temp

This command creates a new database called temp that contains the same structure and data as invtory, but with the data ordered by dept. The source database, invtory, ins't changed. The actual command we'll use is

sort on dept, partno to temp

This command sorts on two fields at once. If there's a tie in dept, dBASE sorts by partno. The effect is that the inventory items will be sorted by partno within each dept. You can sort on up to five fields in this way.

Options to the sort command include the /d option, which sorts in descending order (ascending order is the default). For example,

sort on price/d to temp

sort the database in descending order by the price field, with the largest price at the top and the smallest at the bottom.

The /c option sorts without regard to capitalization. If your data is not capitalized consistently, you'll want to use this option.

Once the sort completes, we'll need to switch over to the temp database to run the report. Use these commands:

use temp
report form dept

Use to print if you have a printer. Now this report has the proper subtotal groups.

Problems with Sorting

Sorting works, but not as well as we might like. First of all, sorting takes a lot of disk space. You must have room enough on the disk for a second, sorted copy of the database (the database file temp in the above example) plus room for temporary work files dBASE may require during the sort. Second, the file that results from the sort is often useful for a short time only. If you add, delete, or edit data in the original database file (invtory in our example), the sorted file is not updated--there is no dynamic linkage between the two files. You'll have to resort the database. Third, many databases need to be sorted several different ways for different purposes. One person needs the subtotal by department, so the database must be sorted on dept. Another person needs the inventory sorted by part number; still another by description. It will take a lot of sorting to satisfy these needs. Indexing is a method of ordering a dBASE database that overcomes some of the disadvantages of sorting.

Indexing as Sorting

Indexing is a process that dBASE uses to provide order to a database, and also to locate data quickly.

When using indexing, dBASE creates an index file that it uses later on. The index file, which you name anything you wish, is separate from the database and is not automatically linked to the database.

Let's create an index to the invtory database that will order the database by dept. First, make sure you're using the right database with the command

use invtory

Then issue the command

index on dept to dept

This command creates an index file called dept which contains information about the dept field. We say the dept index file is keyed on dept. We could have said

index on dept to d

in which case the index file is called d. It doesn't make any difference what you call the index files, but it's common to name them with the same name as the field they're keyed on.

Let's create another index file with the command

index on partno to partno

Now we have a second index file called partno, keyed on partno.

To make the database appear to be sorted by the dept field, use the command

set index to dept

This command makes the dept index file the active index file. Now use any command like display all to display data. The data appears in department order. Now type

set index to partno

Use display all or some other command to display data. Because the partno index is now the active index file, the database appears to be sorted by part number.

One advantage of indexes is the dBASE automatically updates them as you add, edit, or delete data. As soon as you add data to a database, you can display the data, and it will be in the correct order as determined by the index. The key is that you must activate all the index files so that dBASE will update them. If you don't have index files active when you modify the database, the index files may not be valid. Here's the command to activate both index files:

set index to dept, partno

Now, type append to start adding data and add the following data:

PARTNO A-0570
DEPT Food
ONHAND 8
PRICE 2.00
COST .95
ROP 5
DESC Coca-Cola
LS 08/10/88
TAXABLE F

After you press Enter on the notes field, dBASE presents another entry form. Since we're through entering data for now, press Esc to abort the entry process.

Now, to present the new data appearing in the proper order by department, type

set index to dept, partno

and then

display all fields partno, dept, desc

Note that the data appears with the other items in the Food department. To make the data appear in part number order, type

set index to partno, dept

and then repeat the display all command (use the Up arrow key a few times to retrieve this recently issued command.) Now the new data appears in order by the part number.

Note that the last two set index to commands specified both index files, even though we really needed to say just set index to partno to make the data print in part number order. But remember, if you make any editing changes to the database, you need to make sure that any index files that are keyed on fields you're changing are active. By using all the indexes all the time, you don't have to worry about this problem. This problem is made worse by the fact that dBASE won't notify you that an index file is invalid; it just may not work correctly.

dBASE pays attention to only the first index file listed when ordering the data. This means that saying set index to dept, partno won't necessarily produce the database in the same order as sort on dept, partno to temp, as dBASE doesn't pay attention to the second (or subsequent) index files. You may use up to seven index files with the set index to command.

In summary: Use the command index on fieldname to indexfilename to create an index file. Then use set index to indexfilename to activate the index or indexes you want to use.

Indexing as Locating

The locate command described earlier works fine, but suffers from being slow. Locate starts at the top of the database and examines each record in turn to see if it satisfies the for condition. If the data you're looking for is near the end of a large database, locate can take a long time to find the data. Locate also must search through the entire database to notify you that the data you're looking for doesn't exist. Using indexed files can help with the speed problems of locate.

If you have data that you locate through a key field, such as name, account number, part number, social security number, or so forth, first create an index on that field. For example, with our invtory database, we might want to look up data based on the part number. We need an index based on the field partno. We already have one from the previous section, but if you didn't follow the above exercise, issue the command

index on partno to partno

Now make the partno index file the active index file with the command

set index to partno

Now, to locate data, use the seek command like this:

seek "A-1500"

(Note we didn't surround the data with quotes as we did with locate.) dBASE positions the database record pointer at the first record where the field partno contains A-1500 and present the dot prompt. You're free to use edit or any other command to display or manipulate the data.

If you search for something that isn't in the database, such as

seek "ABCD"

dBASE reports No find.

Although it isn't apparent because our database is small, seek works much quicker than locate. Seek will never take more than a few seconds (most of the time it is nearly instantaneous), while locate can take minutes to search a large database.

In summary: Use the seek command to search for data rapidly. You must have an index file for the field you want to search on, and the issue the set index to command with the key field index file listed first. Then type seek followed by the data you want to look for.

One last note about indexes: When you want to open a database file and its associated index file, you can do it with one command, as in

use invtory index partno, dept

This is equivalent to the two commands

use invtory
set index to partno, dept

Totaling a Database

The total command creates categorized summaries of the database. For example, you might be interested in knowing the total value of the inventory for each department. What you need to do is sum all of the value fields for each value in the dept field. The total command does this, creating a new database file that contains one record for each value in the dept field. Each record in the new database will contain, in the dept field, the name of the department, and the value field will contain the sum of all the value fields for this department. The new database will contain as many records as their are distinct values in the dept field. If you have 14 departments, the new database contains 14 records, one for each department.

The total command requires that the database be sorted or indexed on the key field. That's the field that determines how the records are grouped, similar to the subtotal on field in a report design that uses subtotals. In this case, we already have the index file dept created for the invtory database, so issue this command:

use invtory index dept

If you haven't followed along and don't have the dept index file, issue these two commands:

use invtory
index on dept to dept

Now we're ready to issue the total command:

total on dept to depttot

dBASE then performs the totaling. This may take a few minutes (or longer) with large databases.

To examine the results of the total command, let's open the depttot database with the command

use depttot

Now, let's use this browse command to view the results:

browse fields dept, onhand, price, value

For each record, the onhand, price, and value fields contain the sum of all the records for each dept field value. For example, the record for the Toys department shows the total toys on hand in the onhand field (this is probably meaningless), the total of all the individual toy prices in the price field (this is surely meaningless), and the total of all the values in the value field (this is the field we're really interested in).

You can narrow the action of the total command with a scope (the default scope is all records) or a for clause, and you can restrict the totaling action with a fields clause. In this case, we might have issued this command:

total on dept to depttot fields value

Now, the depttot database would contain just the totals for the value field, the only field that makes sense to sum. All the character, date, and logical fields still appear in the depttot database, although the dept field is the only necessary one.

If you make changes to the invtory database, you'll have to run the total command again to update the depttot database. Also, make sure the source database (invtory in this example) is indexed or sorted on the key field (dept in this case). If it is not, the results of the total command aren't accurate. You can use the result of the total command (the depttot database in this example) as you would any other database, perhaps presenting the output with a report form created through the create report command.

A dBASE III Program

dBASE III Plus includes a comprehensive programming language that you can use for many tasks. dBASE III Plus programs may include the same statements that you type at the dot prompt, plus special programming statements. To create the program, you type the statements into a disk file using the dBASE text editor. When you run the program, dBASE executes the statements from the file just as though you typed them at the dot prompt.

Let's take an example. When you start dBASE III, many people type the two commands set bell off and set confirm on. Also, you may want to start using a database. We'll create a dBASE program to do this for use.

The first step in creating a dBASE program is to decide on the name for the program. The name, like a database file name, can be up to eight characters long, use the alphabet and digit characters, but no spaces or special punctuation. Let's call our program prog1. Now, issue the command that summons the dBASE text editor so we can type our program. Here's the command:

modify command prog1

Into the dBASE text editor, type these commands, pressing the Enter key after each line:

set bell off
set confirm on
use invtory index dept, partno

When finished (if you need to, you can use the arrow keys to move the cursor and make any corrections), press Control-w to resave the program to disk and return to the dot prompt.

Now, to run the program, type

do prog1

The do command instructs dBASE to run the commands contained in the file prog1. No action will take place on the screen (unless you have a misspelled word in your program), but you'll see the status line change to indicate the database now in use.

If your program contained a mistake, dBASE will report so at the bottom of the screen. dBASE Also will ask you to cancel, suspend, or ignore the message. The safest response, and the one to take in this case, is to press c to cancel the program. Then use the command

modify command prog1

to summon the text editor and make changes to the program. Your error was probably a mistyped word, so locate it and make the correction. Then press Control-w to save the program again. Now you can type do prog1 to run the program again.

Let's create a more complicated program that uses a device called a memory variable. Memory variables are temporary storage areas to hold calculation results, input from the keyboard, and other data.

Our program will ask the person at the keyboard to type a department name (Toys, Food, or whatever), and will produce a report showing all the parts in that department. The steps we need to follow are to first open the database file and the necessary index files, ask for the department to print the report for, and then issue the appropriate report form command. We'll use a memory variable to hold the name of the department that the person inputs.

We'll call our program dept, so to enter the text editor and begin typing the program, use the command

modify command dept

Type these lines exactly as shown:

use invtory index dept, partno
clear
accept "Enter department name: " to d
report form dept for dept = d

When finished, press Control-w to save the program to disk.

The first line of the program, use invtory index dept, partno, opens the database file and the indexes. We've seen this before.

The second line of the program, the command clear, clears the screen. You can use this command anytime you like to clear the screen.

The third line, accept "Enter department name: " to d, does several things: First, it displays the message between the quote marks. The message can be anything you want. Then it waits for the person at the keyboard to type something. After the person types something and presses Enter, what the person typed is stored in the memory variable called d. Thus, d contains the department to display data for.

The fourth line, report form dept for dept = d, prints the dept report form as we've done before. The for dept = d part restricts the display to just the data that meets the for condition, just as with the display all command. The difference is that the memory variable d will be evaluated and replaced with whatever d contains. If the person at the keyboard typed Toys when asked by the accept command, then the command report form dept for dept = d becomes report form dept for dept = "Toys", and that command produces the report we want.

Try it. The name of the program is dept, so to run the program, type

do dept

When the program asks for input, be sure to enter the department name with the first letter capitalized and the rest of the name in small letters, because that's the way the data was entered into the database.

Function Keys

dBASE III Plus lets you use function keys as shortcuts for entering common commands from the dot prompt. You can change the function key definitions to suit your own needs. These are the standard dBASE III Plus function key definitions:

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

First Sample Report:

Page No. 1
08/08/88
ACME CONSOLIDATED INDUSTRIES

Inventory Report

Department Part Selling Quantity Total Value
Number Price On Hand


Sports A-3300 250.00 2 500.00
Music A-4400 250.00 2 500.00
Food A-1300 1.50 3 4.50
Sports A-2900 500.00 3 1500.00
Food A-0260 0.59 4 2.36
Stereo A-4000 495.00 2 990.00
Sports A-2800 350.00 5 1750.00
Cosmetics A-1500 32.95 1 32.95
Clothes A-1400 300.00 2 600.00
Food A-1155 0.60 8 4.80
Clothes A-2000 500.00 2 1000.00
Toys A-1600 2000.00 0 0.00
Sports A-2400 100.00 2 200.00
Stereo A-3800 145.00 3 435.00
Cosmetics A-1200 26.95 4 107.80
Nature A-2600 50.00 2 100.00
Nature A-2500 95.00 1 95.00
Stereo A-3600 295.00 2 590.00
Stereo A-1700 299.00 2 598.00
Nature A-2700 75.00 2 150.00
Stereo A-4100 59.00 4 236.00
Cosmetics A-1900 100.00 7 700.00
Clothes A-2100 500.00 1 500.00
Sports A-0300 250.00 3 750.00
Sports A-3200 100.00 0 0.00
Sports A-0700 2000.00 9 18000.00
Toys A-3400 1.95 5 9.75
Toys A-0100 300.00 5 1500.00
Sports A-3100 28.95 7 202.65
Stereo A-3700 195.00 8 1560.00
Clothes A-0200 30.00 4 120.00
Food A-0250 1.75 6 10.50
Music A-1100 19.95 5 99.75
Toys A-3500 9.95 7 69.65
Clothes A-0900 10.00 7 70.00
Stereo A-4200 89.00 9 801.00
Food A-1255 5.59 9 50.31
Toys A-0150 10.00 4 40.00
Music A-4300 400.00 3 1200.00
Stereo A-3900 299.00 4 1196.00
Sports A-3000 45.00 8 360.00
Literary A-2300 69.50 3 208.50
Music A-4500 700.00 5 3500.00
*** Total ***
40344.52

Second Sample Report:

Page No. 1
08/08/88
ACME CONSOLIDATED INDUSTRIES

Inventory Report

Department Part Selling Quantity Total Value
Number Price On Hand


** Total for Department: Clothes
Clothes A-0200 30.00 4 120.00
Clothes A-0900 10.00 7 70.00
Clothes A-1400 300.00 2 600.00
Clothes A-2000 500.00 2 1000.00
Clothes A-2100 500.00 1 500.00
** Subtotal **
2290.00

** Total for Department: Cosmetics
Cosmetics A-1200 26.95 4 107.80
Cosmetics A-1500 32.95 1 32.95
Cosmetics A-1900 100.00 7 700.00
** Subtotal **
840.75

** Total for Department: Food
Food A-0250 1.75 6 10.50
Food A-0260 0.59 4 2.36
Food A-1155 0.60 8 4.80
Food A-1255 5.59 9 50.31
Food A-1300 1.50 3 4.50
** Subtotal **
72.47

** Total for Department: Literary
Literary A-2300 69.50 3 208.50
** Subtotal **
208.50

** Total for Department: Music
Music A-1100 19.95 5 99.75
Music A-4300 400.00 3 1200.00
Music A-4400 250.00 2 500.00
Music A-4500 700.00 5 3500.00
** Subtotal **
5299.75

** Total for Department: Nature
Nature A-2500 95.00 1 95.00
Nature A-2600 50.00 2 100.00
Nature A-2700 75.00 2 150.00
** Subtotal **
345.00

** Total for Department: Sports
Sports A-0300 250.00 3 750.00
Sports A-0700 2000.00 9 18000.00
Sports A-2400 100.00 2 200.00
Sports A-2800 350.00 5 1750.00
Sports A-2900 500.00 3 1500.00


Page No. 2
08/08/88
ACME CONSOLIDATED INDUSTRIES

Inventory Report

Department Part Selling Quantity Total Value
Number Price On Hand


Sports A-3000 45.00 8 360.00
Sports A-3100 28.95 7 202.65
Sports A-3200 100.00 0 0.00
Sports A-3300 250.00 2 500.00
** Subtotal **
23262.65

** Total for Department: Stereo
Stereo A-1700 299.00 2 598.00
Stereo A-3600 295.00 2 590.00
Stereo A-3700 195.00 8 1560.00
Stereo A-3800 145.00 3 435.00
Stereo A-3900 299.00 4 1196.00
Stereo A-4000 495.00 2 990.00
Stereo A-4100 59.00 4 236.00
Stereo A-4200 89.00 9 801.00
** Subtotal **
6406.00

** Total for Department: Toys
Toys A-0100 300.00 5 1500.00
Toys A-0150 10.00 4 40.00
Toys A-1600 2000.00 0 0.00
Toys A-3400 1.95 5 9.75
Toys A-3500 9.95 7 69.65
** Subtotal **
1619.40
*** Total ***
40344.52

Sample Screen Format

èëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëë
_ _
_ ACME CONSOLIDATED INDUSTRIES _
_ Inventory Records _
_ _
_ _
_ Part Number A-1600 Department Toys _
_ Description Projection TV _
_ _
_ Quantity On Hand 0 Reorder Point 3 _
_ Last Shipment Received 05/28/88 _
_ _
_ Our Cost 1,250.00 _
_ Selling Price 2,000.00 _
_ Taxable Y _
_ _
_ _
_ _
_ _
_ _
àëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëëÎ

Quiz

1. Display a list of the items in the Food department.

list for dept = "Food"
display all for dept = "Food"

2. Count the items in the Food department.

count for dept = "Food"

3. How many Food items are there? (Not the number of records that belong to the Food department, but the number of Food items in stock.)

sum onhand for dept = "Food"

4. Determine the total value of our inventory.

sum price * onhand

5. Increase the prices of the Toys by 25 percent.

replace all price with price * 1.25 for dept = "Toys"

6. list the new on hand quantity, price, and value for the Toys.

list onhand, price, value, price * onhand for dept = "Toys"

7. Restore the original prices of the Toys.

replace all price with price / 1.25 for dept = "Toys"

8. How many A-1500 are in stock?

locate for partno = "A-1500"
display (or, if you wish)
edit

9. Change the quantity on hand for part number A-1800 to 3.

locate for partno = "A-1800"
edit

or

replace all onhand with 3 for partno = "A-1800"