Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8911.ZIP
Filename : GATHER.TXT

Output of file : GATHER.TXT contained in archive : TN8911.ZIP
Gathering Information in a Chaotic World
Mina Nichols
Your typical database applications, such as accounting, billing, and inventory
control, require little more than tracking lists of fairly homogeneous data
and are usually rather straight forward to implement. Some of us, however,
are faced with the challenge of developing database applications in a far
less orderly environment. Often the data can only be gathered in a jumbled
mass and it is not until we have assembled a sizable body of disorganized
information that we can begin to extract that which is meaningful to our

This article describes two techniques for designing database systems that can
adjust to change. The first is based on the CREATE FROM
command that has been with us since dBASE II. The second technique uses a
feature introduced with dBASE IV, the ability to search for data in a memo

Imagine that you are an associate at a counseling clinic and have recently
begun to take an interest in alcohol-related problems. Manually searching
through each patient's records to locate patients who might be candidates for
a drinking program would be a very time consuming job. But, if your clinic
already stores the information in a dBASE memo field, you can program dBASE
to search the records for you. This article provides a code example that
searches for keywords in a memo field and stores the result in a new field in
the main database.

This article also shows how the structure of a database may be changed within
a program. While manually changing the structure of a database is as easy as
falling off a curb, the functionality exists in the dBASE language to do it
programmatically. Since your colleagues at the clinic are too busy with
other things to read the user manual, as the selfless defender of mental
health that you are, you will include in your program a mechanism to add the
new field to your database automatically.

But first, we need to talk about the system your office already has in place.
Let's assume that your current system has a setup program that opens the
proper databases and displays a main menu. For this example we'll be using a
two file database. The first file, called Patient.DBF, lists all the
patients, their case number, name, address, and any other information that
occurs once per patient. The other file, called Session.DBF, has a record
for every session each patient has attended and contains the session date and
notes and also contains the patient's case number (as a way of linking it to
the Patient file. By the way, there is no relation set between these files;
there could be, it just isn't necessary for this example). The new field
will be added to the Patient file because it only needs to be noted once per
patient. The Session file is the one that has the session notes memo field
that we will be searching for evidence of alcoholism.

After checking with your supervisor or otherwise covering yourself, modify the
setup program to include a call to the AddField program. I won't go into
detail on how this is done since it will be different on every system, but in
general, you will have to find the portion of code responsible for the main
menu. Then, make sure you choose a keystroke for this new option that does
not conflict with any of the others in the menu. Add "add a new field to the
database", or something like it, to the menu list. Next, find the CASE
structure that executes the user's choice and add a

DO AddField
to it. Also, make sure you know which databases are open and in which work
areas so that you can make the appropriate adjustments to the code provided.
Neither file needs to be indexed for this procedure but any filters on either
file will be unset by the program.

How AddField.PRG adds a field.
Okay, let's look at the program that modifies the structure of the database:
AddField.PRG (seen in Listing 1). The program begins with some fluff that
lets the user know what's happening. Basically, it tells the user what it's
going to do, then it checks to make sure the user wants to continue, then
prompts the user for a name to give the new field.

The active ingredients in this program are the COPY TO Pstru STRUCTURE

COPY TO Pstru STRUCTURE EXTENDED creates an extended structure file called
Pstru that describes the structure of the current database. An extended
structure file is just an ordinary database containing these five fields
Field_name, Field_type, Field_len, Field_dec and Field_idx.

If the names seem familiar it's because they correspond to the five columns of
the CREATE/MODIFY STRUCTURE screen. The fields of the current database, in
this case Patients, are read into the extended structure as records. Or,
more precisely, a record is automatically added to the extended structure for
each of the fields in the current database.

For example, the extended structure file for the Patients database is shown at

Just as an extended structure can be created from a database, a database can
be created from an extended structure. The CREATE Patient FROM Pstru does
just that, but not until we've slipped in our new field.

Now, if the extended structure file is just another database, and each field
of the structure it describes is just another record, how do you suppose we
might add a new field to the structure? If you said "append a new record to
the extended structure" give yourself 5 points. That's what the APPEND BLANK
in our program is for. The REPLACE fills the new record with the proper
information for the field we want to create, and the CREATE Patient FROM
Pstru creates a new empty database from the slightly modified extended

All that's left to do is to append the records from our backup into our newly
remodeled database, and voila, we have a new Patient.DBF with our new field
and all our old data in it.

You can probably think of some other useful applications for this procedure.
For example, you could create a procedure that creates a new annual total
field at the end of each year and then fills it with Q1Total + Q2Total +
Q3Total + Q4Total. Be careful not to get carried away with this one though,
you're only allowed 254 fields to a .DBF.

How KeyWord.PRG Finds Keywords in Memo Fields
Now that we've added the new field to our database, let's see how we check
each memo field to see if it contains our keyword.

Our second program, KeyWord (shown in Listing 2), is called from AddField
which can easily be adapted to keep calling KeyWord for as many keywords as
you like (by encasing it in a loop).

KeyWord begins by asking the user to enter a keyword. For example, if you're
looking for drinkers, you might use "drink" or "bar" or "blotto" as keywords
whose presence in the notes field might indicate a patient's proclivity for
substance abuse.

The keyword search is accomplished through the mechanism of two concentric
loops, one that reads the next patient record and one that reads all his
session records. Don't jump to the notion that SCAN does the lookup. I use
SCAN to create my loops (because it saves me having to add a SKIP; That is,
in dBASE IV, the SCAN/ENDSCAN structure takes the place of the traditional DO
WHILE .NOT. EOF()/SKIP/ENDDO structure).

The actual lookup is done with the AT(m_KeyWord,Notes) function, which returns
the position of the keyword in the Notes field. If the keyword is nowhere in
the notes field, AT() returns a 0. So, if the result of AT(m_KeyWord, Notes)
is greater than zero, then the keyword has been found in the notes field and
we should set the m_KeyWord_Found flag to TRUE and stop looking through the
session notes (EXIT the loop).

Next, the program goes back to the Patient file, puts an "x" in the new field
if the m_KeyWord_Found flag is set to TRUE, then continues on to the next
Patient record.

Fine Tuning the Keyboard Search
The astute reader may notice one or two tiny flaws in this example. For

instance, diagnosing a patient as a drinker is an awesome responsibility for
anyone as stupid as a computer, and let's face it, they are very stupid.
Perhaps we should provide a way for the computer to do the time consuming
search and let a qualified human judge the findings.

Let's make a few adjustments to the inner SCAN loop so that the program will
DISPLAY the memo in which it finds the keyword. That way the user will have
a chance to review the computer's findings. We'll make the program continue
to look through all the patient's records instead of stopping as soon as it
finds the keyword. Once all the records have been checked, the program will
ask the user for a decision and place the result in the new field.

Listing 3 shows the changes to the Keyword program from the beginning of the
first SCAN all the way to the end of the program.

Now you know two techniques for creating database systems that can be made to
change automatically. Let it be an inspiration in these crazy-making

times. t

  3 Responses to “Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8911.ZIP
Filename : GATHER.TXT

  1. Very nice! Thank you for this wonderful archive. I wonder why I found it only now. Long live the BBS file archives!

  2. This is so awesome! 😀 I’d be cool if you could download an entire archive of this at once, though.

  3. But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: