.DBO Compiled dBASE IV object code for the
main menu in the application
.DOC Printable text file that graphically
shows the menu structure and the order of
operations of the application (optional)
.FIL Files list pop-up menu definitions
.POP Pop-up menu bars and actions definitions
.PRG dBASE IV program language code for the
main menu in the application

.PRG dBASE IV program language code for
the application itself
.STR File Structure list pop-up menu definitions
.VAL Values list of fields contents pop-up menu
definitions

If you would apply the procedure outlined above for deleting files listed in the
Control Center to an application produced with the application generator, only
the .APP file would be deleted. All of the other files
closely related to the application like .BAR, .POP, .PRG, .DBO, and so on, would
remain on the disk. Deleting *.BAR would get rid of all horizontal bar menu
definitions from the current directory. If there were any other applications
that used horizontal bar menus, they would now be deleted. As you can see from
the chart, there are many different file extensions that may be in an
application. Remembering exactly which .POP or .VAL lists are in a particular
application could make deleting all files related to an application a difficult
task.

This is where AppZap comes in. The application removal is completed in two
steps. First of all, you need to regenerate the application using AppZap.GEN.
Then you would run the AppZap program from the dot prompt. This two step
process insures that you really want to delete the application when you choose
to do so.

Installation of AppZap
To install AppZap, you need to copy the AppZap.GEN and AppZap.PRG files into the
directory where dBASE IV is installed. You may type these files yourself or get
them from the bulletin board. On the Ashton-Tate bulletin board, the file
AppZap.ZIP is located in the DTL Library. Information about typing and
compiling the files can be found at the end of the article.

Operation of AppZap
After installing AppZap, follow these steps to delete an application.
1. Highlight the application name in the application panel of the Control
Center.
2. Press Enter and choose to modify the application.
3. Press Alt-G to go into the Generate Menu.
4. Choose Select Template from the menu.
5. Enter AppZap.GEN and press enter.
6. Choose Begin Generating. Generation will take a matter of seconds.
7. Exit from the Application Generator.
8. Go to the dot prompt.
9. Type DO AppZap

If you have generated more than one application using AppZap, you will see a
menu listing of those applications. Choose one to delete at this time. A menu
will now appear with four choices. These are:

* ALL To delete all files associated with the named application
* NO .DBO To delete all files except for the .DBO and
the

.DBO. This allow the user to run the application as
it stands.
* ONLY APPLICATION FILES To delete all of the application generator
files. The .PRG and .DBO files are retained, allowing the user to run
the application and modify its dBASE IV program. The actual application
objects, like popup and horizontal bar menu files, are not kept.
* EXIT To leave the AppZap program without deleting anything.

What AppZap Does
Applications produced by the dBASE IV application generator are converted to
dBASE IV code by the MENU.GEN template language file. The natural environment
for making an application is the template language. Because of this, the
easiest way to determine which menus and processes are contained within an
application is with a template language program. AppZap.COD is the template
language file that will write all of the file names associated with an
application to the .DEL file.

The main highlight of the AppZap.COD file is that it searches through each menu
looking for other menus. That segment of code begins with the main menu of the
application and works its way down through other menus and popups. For each
menu that is found, a line showing the file name is appended to the name>.DEL file. Other files normally associated with the application are also
included in the .DEL file.

In order to run AppZap.PRG, you will first need to create a database file called
DIRSel.DBF. There should be one field in this file, a character field, named
DIRLINE with a width of 20. The Index should be set to N. Do not add any
records to this database. After you create DIRSel.DBF, you can then run
AppZap.PRG successfully.

The AppZap.PRG dBASE IV program searches for .DEL files. If there are no .DEL
files, a message appears and no processing takes place. If more than one .DEL
file is found, a popup menu showing the names of these .DEL files appears on the
screen. Once a .DEL file is selected, or only one .DEL file was found, a menu
showing different sets of files for deletion is shown. The appropriate option
is then selected by the user. The program verifies to see if the .DEL file is
in the format that AppZap needs to remove the application. The files are then
deleted and the program concludes. .

COD File Modifications
In order to put any .COD file changes into effect, you must use an updated .GEN
file, the file compiled from the dBASE IV template language .COD file. If you
possess the standard edition of dBASE IV version 1.1 and do not have Template
Language capabilities, disregard the next paragraph . You can still get the
compiled AppZap.GEN file from our bulletin board in the DTL Library, but you
cannot make the .COD file changes yourself. The file on the bulletin board is
called AppZap.ZIP.

If you have the dBASE IV Developers Edition, then you can create the AppZap.COD
file, compile it, and use the new AppZap.GEN youve created. The AppZap.COD
file is a text file. You can use MODIFY COMMAND or any other text processor to
change the .COD file. But enter the template code exactly as you see it here,
as certain parts of the code must be in upper case. After making the changes to
the .COD file, type in DTC -i AppZap.COD which will attempt to compile the
template into the AppZap.GEN file which is usable by the dBASE IV application
generator. If compilation was successful, copy the AppZap.GEN file into your
dBASE IV system files directory.

APPZAP.ZIP is an archived version of the file APPZAP.COD and can be found in the
DTL library.


2 Q & A

Questions from our users and answers from our technicians.

Closing Databases Within UDFs
Q. I have a UDF in which I would like to close all databases in every work area
and have tried, without success, to use the CLOSE ALL command. I even tried SET
DBTRAP OFF, but it doesn't make a difference. The databases aren't closed nor
do I get any error messages.

A. The CLOSE ALL command is ignored in a UDF but there is a workaround. The
solution is to go through each work area and issue a USE command without any
parameters to close them all. You have to remember to restore things as they
were before RETURNing from your UDF depending on where it was called from. The
best way to do that is to place the names of all files using SELECT() into an 10
element array which would correspond to each of the available work areas.

Looking Out For Number One
Q. When I LIST RECNO(), PROW() TO PRINTER to test my printer, I noticed
something strange :

Record# RECNO() PROW()
1 1 0
2 2 2
3 3 3

What happened to printer row #1?

A. Use the command SET HEADINGS OFF. Your output will look like

1 1 0
2 2 1
3 3 2

Remember, printer coordinates like screen coordinates, begin at row 0, column 0.

Stubborn Fields
Q. I am creating a format screen and I tried to carry forward two fields. After
I saved it, I decided to remove one of the fields to be carried forward, but
when I save it, it still carries that field forward. Do I have to start a new
format screen?

A. Go to the DOT PROMPT and type
SET CARRY TO
This will cancel out the carry forward. When you save your format screen,
everything will be fine.

Traveling Light
Q. I just PACKed my database and, to my surprise, it runs much faster than
before. Why is this?

A. Any noticeable time differential would be dependent upon the type of machine
and the number of records in your database. In other words, you may have an
XT-class machine (pretty slow by present standards) and a large database with a
number of records that were marked for deletion. In such cases, a performance
increase would be noticeable since a PACK operation removes these deleted
records and re-indexes your file. Before these records had to be "masked"
somewhat like a filter operation.
Filtering is another process that can slow down performance. In dBASE IV
version 1.1, it is much more preferable to INDEX ON...FOR a condition than to
filter.

To Be Continued
Q. I have a multi-page report in which there is a grand total of a numeric field
that must show up on a specific spot on the report-on the bottom of the last
page at the right. If it is not the last page, the word "continued" should
appear. How can I have either the numeric information or the word in the same
place?

A. The following two fields in the page footer band will do the trick.
Create a hidden summary field with the operation SUM. Give the field the name
SumField. The Field to Summarize On will be the field you want to add up. The
Reset Every option should be set to REPORT instead of PAGE. Set the Hidden
option to Yes.

Place the cursor where you want the summary or "continued" to appear. Create a
calculated field using the expression

IIF(EOF(), TRANSFORM(SumField, "9,999,999.99"), "continued")

This expression tests for the end of file condition. If it is true, print the
character representation of the formatted numerical sum. If the file is not yet
at its end, print "continued." The TRANSFORM function is necessary because both
results should produce the same data type. Since "continued" is, by definition,
a character expression, the number needs to be represented by a character

expression.

Blankety-Blank Messages
Q. Why is it that I always get the message:
** WARNING ** Uncompleted transaction found
whenever the I open up a particular database? This only happens to one of my
databases and it doesn't seem to affect any data-but it sure is annoying!

A. We all know that there is a virtual plethora of dBASE IV users who, at one
point or another, concluded that the only way to quit out of dBASE IV was to
turn off the computer. This subsequently causes the aforementioned problem if
you happen to have been in the middle of a BEGIN/END TRANSACTION.

The technical explanation of this is that whenever you perform a BEGIN
TRANSACTION, dBASE IV writes a CHR(1) to the fourteenth byte of the header to
let itself know that this database is in a transaction state. If you leave
dBASE IV without QUITting or ENDing the TRANSACTION, the "transaction marker"
doesn"t get set back to 0. If you subsequently open the database, dBASE IV
notices that header offset 14 has a value of 1 and the warning message is
displayed, (even though the database "works" properly).

But aside from that, an extremely simple fix for this problem is to type RESET
at the dot prompt as illustrated below:

. USE MYFILE
** WARNING ** Uncompleted transaction found
. RESET
. USE MYFILE && No warning message is displayed.

Look, No Hands!
Q. Isn't there a way under program control to put a memo directly into edit mode
without the need to press Ctrl-Home or F9? Ideally, as soon as I cursor onto
the memo field (as a marker), I"d like it to explode out to a editing window.
Is that possible?

A. When you're working with a custom screen, place a UDF in the editing option,
Permit Edit If. The UDF would perform a KEYBOARD command for Ctrl-Home. The
READKEY() checks are for Ctrl-End, Esc, Y or y. If any of these keys are
pressed, a down arrow, CHR(24) is performed.

One last note on this method, the time elapsed from the moment your cursor
enters the memo marker and the time the editing window appears will be longer on
slower machines.

FUNCTION Explode
lkey = LASTKEY()
KEYBOARD IIF(lkey = 23 .OR. lkey = 27 .OR. lkey = 121 .OR. lkey = 89,
CHR(24),CHR(29))
RETURN .T.

The UDF would return a .T. value to screen control. As soon as a cursor was in
the memo field, it would expand to a window as you wish.

DOS Duplicates
Q. I wanted to SET a DOS environment variable from inside of dBASE IV so I tried
the following:

RUN SET WORKS=YES
then I wanted to see if the SETting worked so I tried:

RUN SET
but I didn't see WORKS in the SET list. What went wrong?

A. When a program is run from DOS it is given a copy of the DOS environment
(PATH, COMSPEC, PROMPT,and other settings) which it may query through such
functions as the dBASE IV GETENV() function. The dBASE IV RUN command then
makes a second copy of this DOS environment (actually a copy of the dBASE IV
copy) and sends this copy to the program being executed. Technically dBASE IV
is loading COMMAND.COM in order to run your program and COMMAND.COM is grabbing
a copy of the environment. When the program being RUN finishes and returns
back to dBASE IV this copy (of the copy) of the environment is forever lost and
any changes made to it such as SET WORKS=YES would also be lost.

Arrays and Forms Don't Mix
Q. Why can't I include array elements as memory variables under the forms design
screen? It would seem to me that the ideal way to modify a record would be to
use the command COPY TO ARRAY to put the data into an array, display the data in
the array using a form, then use the command REPLACE TO ARRAY to store the
changed information to the database. But when I try to enter an array name in
the form design, it beeps when I try to type the left bracket symbol.

A. It is true that with the new array capabilities that use of array elements
seems much better suited for use in forms design screens. Unfortunately at this
time, the forms design surface screens for special characters and prevents them
from being entered. There is no workaround to circumvent this.

Menu Camouflage
Q. I have tried to SET TITLES OFF and SET COLOR OF TITLES to B/B so that I don't
see the field titles when I use the BROWSE command (the background color and
menu color blend together). However, this does not produce the desired effect.

A. SET TITLES OFF does not turn off the field titles in BROWSE. SET COLOR OF
TITLES TO B/B has the effect of making the foreground and background color of
the titles the same color so that you don't see the title text and is a good
workaround. However, this method does not work when you BROWSE in a window.
You can produce the same effect in a window by defining the window so that its
title colors make the title invisible (see the DEFINE WINDOW command in Language
Reference). Now if you BROWSE in this window, you won't see any titles. To
reduce the amount of "dead space" with or without a window, use the COMPRESS
option of BROWSE:

SET COLOR OF TITLES TO B/B
BROWSE COMPRESS

or

DEFINE WINDOW BrowWind FROM 5,5 TO 20,74 COLOR ,,B/B
BROWSE WINDOW BrowWind COMPRESS

Extra Wide Reports
Q. I have a report form which is 230 characters wide, and prints fine using
condensed print. It has no heading in the Report Intro band. I want to add a
heading at run time with the HEADING parameter of the REPORT FORM command in
dBASE IV. When I do this with a 30 character HEADING, it seems to wrap my
heading at position 50.

A. You need to set _rmargin to 230 before running your report. The report uses
the margin settings to determine where the center of the report is. Try these
commands from the dot prompt:

_pform = "WHATEVER.PRF"
_rmargin = 230
REPORT FORM Whatever HEADING "This is my special header" TO PRINT

Forced Entry
Q. I would like to enable a lastname field in a screen form to make only the
first letter of the last name in uppercase, all else in lower case. In
addition, I want to prevent lastnames from being entered in uppercase. How do I
go about doing this?

A. While in forms design, modify the template for the field and make the first
character a ! character and all others in the template X's. Then, in the Edit
options, enter the following expression in the Accept value when prompt:

LOWER(SUBSTR(fldname, 2)) = SUBSTR(fldname,2)

Automatic Date Entry
Q. How can I get a keyboard macro to insert the current date while editing in a
screen format?

A. The following ON KEY LABEL command issued from the dot prompt or in a program
prior to a READ or EDIT or BROWSE will set up the F8 function key as a date
stamp key. It is sensitive to whether SET CENTURY is ON or OFF and types in the
year in appropriate format for either case.

ON KEY LABEL F8 KEYBOARD ;
TRANSFORM(MONTH(DATE()), "@L 99") + ;
TRANSFORM(DAY(DATE()), "@L 99") + ;
IIF( SET("CENTURY") = "ON", STR(YEAR(DATE()), 4), ;
RIGHT(STR(YEAR(DATE()), 4), 2) )
When you're editing a date field, you simply press F8 and the current system
date is entered for you. Make sure your TYPEAHEAD is set to at least 10.7


3 Fixing Corrupted Database Files by Joe Stolz

Fixing Corrupted Database Files by Joe Stolz

When all that stands between you and your computer is a careful arrangement
of electronic blips, you can see how easily a disaster can occur.

Corruption of files is an all too common fact. Interruptions in
electricity, whether a result of a thunderstorm or from kicking the plug
out of the wall, can easily do a brutal number on your data. Fortunately
for us, dBASE IV files are laid out quite simply. In fact, all of the data
is written into the file in plain ASCII text form. This simplifies our
ability to recover damaged data files. Below are a series of problems that
are commonly encountered with corrupted database files and a series of
solutions to be applied to correct the problem.

The definitive methodology to be applied in case of .DBF file corruption is
not delved into here. Only specific ailments that are seen in dBASE IV
database files will be discussed, those most commonly experienced and
adapted to simple recovery techniques.

dBASE File Recovery, an Ashton-Tate product, is well suited to the task of
fixing damaged database files, as are utilities found in products such as
PC Tools, Mace Utilities and Norton Utilities. Nonetheless, many of the
techniques discussed can be performed easily, without the need to run out
and purchase any additional products. With that in mind, here is a list of
specific problems seen in data files, a short discussion of possible
corrective measures, and a recommended remedy. Some techniques can be
performed with dBASE IV alone, some require corruption repair utilities
available from our Support Center and some require the extended capability
of commercial data recovery products.

Where to Refer
The following materials that can be obtained from our support department
will help you understand more about recovering damaged data files.

A "Recovery" disk is available which includes files such as DBTCHECK and
DBFCLEAN. Included on this disk are several excellent text files that
describe common corruption problems and solutions. Please note that these
files are also available on the Ashton-Tate BBS.

In an earlier version of TechNotes/dBASE IV dated May 1988, practically the
entire issue was dedicated to corruption and file repair topics. Much of
the text from the "Recovery" disk (whose files are also available on the
BBS) described above comes from this issue.

Other issues of TechNotes of interest are the September '85 issue, page 18
and the December '85 issue, page 1.

Is This Your Scenario?
The following will be a series of symptoms and suggested remedies. Certain
assumptions must be made PRIOR to your consulting this list. These assumed
prerequisite operations are:

1. You verified that NO BACKUP EXISTS so that you can avoid all of the
rigmarole involved in recovering data. Note that recent backups could have
been automatically been created if the file had been modified by the MODIFY
STRUCTURE command. Files created from that command have the extensions
.DBK, .TBK. and .MDK for database, memo and production index files
respectively.
2. You have run CHKDSK /F since this command will correct "lost cluster"
problems on the hard disk. Very often a lost database file is called a
lost cluster by DOS and can be recovered simply by performing a CHKDSK
operation. Files created by CHKDSK generally have names like
FILE0001.CHK. Refer to your DOS manual for more information.
3. You have checked the setup of your CONFIG.SYS file and the BUFFERS=
statement that it contains. BUFFERS are traditionally set to 15 but if
using a cache, will be at 3 or 5. Make sure that no TSR programs
(Terminate and Stay Resident) remain in memory. These environmental
factors may complicate activities in the memory of your computer and can
complicate the task of file recovery.
4. You have made a printout of the file structure of every data file that
you use, or at least a backup copy of the file/structure for use in the
recovery process.
5. You have created one or more backups of the damaged file first! Why
lose what precious little you have now?
6. Use the dBASE IV COPY command to make a copy of the file. In many
cases, minor corruption problems will be eliminated by the filtering
capability of this command.

Common Problems and Suggested Remedies

You attempt to USE a .DBF file and get the error "Not a dBASE database".

This is often caused by a corruption in the first byte of the header. This
byte is typically a Hex 03 (no memo) or a Hex 83 (has a memo field) in
dBASE III PLUS. In dBASE IV it can have several other values.

However, in the very worst case you can still change the byte to a 03 if no
memo exists or 83 if one does exist. This can be performed with a
"byte-level editor" such as from Norton Utilities or that is provided in PC
Tools. The problem with doing this is that it won't recover an SQL table.

A sure-fire method is to use HEADFIX for dBASE IV as long as you have an
accurate and recent backup of the file to be used as a reference by
HEADFIX! Since the first byte also reflects the memo version and SQL
status of the file, if these have been updated or changed since the backup
was made, your fixed file will not reflect these changes.

Recommendation: HEADFIX on the "Recovery" disk. DBFCLEAN does the same
thing and more.

You do not have a copy of the file at all. All you have is this damaged
file. You have no paper copy of the structure either.

You have the option of using HEADXTRK on the "Recovery" disk. If the
header is not totally corrupted, it probably contains enough information
for HEADXTRK to extract and to be used in the creation of a new, clean
structure file. This file can then be used by HEADFIX or DBFCLEAN to fix
the damaged file.

Alternatively dBASE File Recovery can be used to precisely reconstruct any
file structure, visually, on screen. Though other programs (such as
FileFix) exist that allow structure verification and editing, none are as
precise and forgiving as dBASE File Recovery.

Use HEADXTRK if you suspect or know that the header is only slightly
corrupted. Use dBASE File Recovery if there is no header at all or if it
is mostly corrupted. TYPE the file in DOS or use LIST.COM to see the
inside of the file. If the beginning of the file has your field names,
followed by your data, you are in luck. If the data starts at the
beginning of the file, the structure part of the .DBF file is gone and you
must resort to creating a new header for your remaining data. From now on
you will always back up you data files, won't you?

Recommendation: HEADXTRK from the "Recovery" disk in conjunction with
DBFCLEAN.

When you try to open your file the error ".DBT file cannot be opened" pops
up. You have accidentally deleted your memo file. How do you recover all
those memos?

In dBASE IV recovering only the .DBF file when the .DBT has been deleted is
not such a big problem anymore since it's self-correcting. You will get
the message "Memo file missing, create a new empty one? Yes No." If you
don't care about your old memos, choose Yes. Afterwards, your file will
have no memos but will be usable.

If you want to preserve your old memos, you are going to have to find the
memo file somewhere, perhaps with Norton Undelete or a similar utility.
You cannot simply use any old backup copy of your memo file since each
record in the data file points to some place in the .DBT file which
contains your memos. If the memos have been rearranged, changed, or
records deleted from the .DBF file, the memo file will no longer match the
.DBF file.

If you renamed the .DBF and forgot to rename the .DBT you are in luck!
Simply rename the .DBT also. Use FileFind as it might be in a different
directory on your hard disk.

Recommendation: Once your memo file has been found, you may need DBTCHECK
.EXE/.DOC. See MEMOFIX.TXT on the "Recovery" disk from our Software
Support Department for a good dBASE III PLUS style discussion of memo file
layout.

Perhaps your file is corrupted since you can USE the file but any of the
following messages are returned: "Record out of range", "File is not
accessible", "Index damaged, REINDEX", "Record not in index", "End of file
encountered".

First, make a copy of the file and its index and try to do a REINDEX
command to rebuild the index. If that fails, try to USE the file without
the index. With .NDX files this is easy: simply don't open the index.
With a .MDX file, you must delete the .MDX file, USE the .DBF file and
choose "Proceed" when you are warned that the production .MDX file is
missing. If the file without the index can be listed from top to bottom,
that means that only the .MDX/.NDX was damaged, or that there is something
in the data itself that is corrupting the index. If "garbage characters"

are included in the index key expression it can cause errors.

If you have to re-create your .MDX again and you worked with an
"index-less" data file, an easy shortcut is to ZAP the copy of the original
damaged file and then APPEND FROM the new, index-less file. This preserves
the index keys from the original file, but not the data. Another
alternative, if the file could be USEd, is to issue the command

COPY STRUCTURE TO Temp WITH PRODUCTION

Then use this new empty structure file and append all data from the
original file. You may have to write down the tag expressions and create
them from scratch if all else fails.

Simple index corruption is common and easy to repair. Don't forget that if
you lock-up your computer and have to reboot in the process of checking
things out you should run CHKDSK /F again.

Recommendation: Try REINDEX first, then try the file without the index.

It's not the index that's damaged because I checked it already. It's the
file itself that prevents me from LISTing all records from 1 to RECCOUNT()
and tells me "End of file encountered" or "Record out of range".

Most commonly, this is a problem with an embedded EOF (end of file) marker
within the file itself. It could also be caused by a NULL character in the
data.

DBFCLEAN has the ability to eliminate NULL characters and EOF markers. It
is accessed with command line parameters from DOS however, so its DOC file
requires careful review before being used.

For the EOF problem, the only time an EOF character can be a problem is if
the marker (Hex 1A) is in the deletion byte of a record. That is the only
place that dBASE looks for EOF markers. The simplest method to clear this
problem is to GOTO the record just before the problem one and note its
RECNO(). Then GO TOP and issue COPY TO Firstprt NEXT n, where n is the
last record before the problem. Next GOTO n+1 or n+2. Try LISTing the
REST of the file. If it LISTs, then GOTO n+1 and COPY REST TO Nextpart.
You only lose one or two records this way. dBASE File Recovery, and the
Fixfile utilities also clear this problem effortlessly.

Recommendation: DBFCLEAN on the "Recovery" disk. Read the DOC files on
Diagnose, Xlate and Memofix.

The record count is too small. I know that I have more records than are
being displayed or shown in DIR.

This problem is caused by an incorrect record counter located in the .DBF
header. This can be fixed with dBASE File Recovery, with any byte-level
editor, with HEADFIX or with DBFCLEAN.

The record count is kept starting in the fifth byte of the header, going
for four bytes. The number is in "least-significant-byte-first order.
It's easiest to use a calculator (for example, SK or CALC.COM) that
converts decimal to hex, determine the hex representation of the proper
number of records expected in the file and to place the hex pairs in these
bytes in reverse order to that shown on the calculator.

For example, if the number of records is 2345, it is expressed as 929 in
hexadecimal. Since hex numbers come in pairs, this is more accurately
expressed as 09 29. The 20 would be placed in the first byte of the record
count (Byte 5) and the 09 would be placed in the second byte (Byte 6). The
following two bytes should be zero 00 00 (Bytes 7 and 8).

Overestimating the correct number of records is always recommended. Note:
this operation is probably too much for the novice user but it's one of the
easiest problems to fix without requiring expensive utility programs.

Recommendation: DBFCLEAN on the "Recovery" disk, which can make a pretty
reliable estimate as to the correct record count.

Data displays but contains "garbage" ASCII characters ("happy faces" and so
on). In EDIT your data is visible but is shifted and spread across several
fields now.

Garbage data is a tough problem to fix. Only visual inspection will help.
You have to note which records are bad and delete them or skip them.
Shifted data is impossible to fix without inserting blank spaces into the
file to adjust the shift. In practice this is nearly impossible.

DBFCLEAN is a good choice for a case where a small amount of garbage is
found in some records. Shifted data requires dBASE File Recovery or a
similar product.

Recommendation: dBASE File Recovery. In this case it excels over all.

OOPS! I ZAPped my data from the file. I accidentally deleted all records
and issued PACK. My file disappeared and seems to be in a lost cluster
(all or partially).

dSALVAGE and dBASE File Recovery can both recover a ZAPped file. You must
be sure that the hard disk was not used (written to) since the ZAP was
performed.

Recommendation: Use dBASE File Recovery as it is the best way to collect
all your data (as long as you didn't panic and over-write your deleted
data).

WHERE TO GET THINGS

Available Commercially
dBASE File Recovery. Full file recovery. From Ashton-Tate
FF, NU, Diskedit and Filefix, part of the Norton Utilities.
FileFix, part of PC Tools.
dSALVAGE. Full file recovery. From Comtech.
CALC.COM Calculator to view file contents. From PC Magazine.
LIST.COM displays files in hexadecimal. Shareware.

dSalvage
Comtech Publishing
P.O. Box 12340
Reno, NV 89510 (
702) 825-9000

FileFix (PC Tools)
Central Point Software
15220 NW Greenbrier Pkwy #200
Beaverton, OR 97006
(503) 690-8088

FileFix (Norton Utilities)
Symantec Corp.
Peter Norton Group
10201 Torre Ave.
Cupertino, CA 95014
(408) 253-9600

Available from Tech Support and on our BBS:
XLATE Limited to .DBF files with < 128 fields, not for .DBTs
HEADFIX Repairs damaged .DBF file header.
HEADXTRK Copies header to new file.
DBFCLEAN Combines functionality of HEADFIX and XLATE
DBTCHECK Repairs corrupted memo files.
DBTCNVT Converts, rebuilds, and reduces block size of memo files.



4 More IV U 2 C

More IV U II C

Roman numerals and binary numbers are not exactly the most easily
recognized numbers.

When you think of roman numerals, you probably think of of the amount of
time you've spent trying to figure out when all those old movies were
made. Limited as their usage is, they still weave their way in and out of
our everyday life. Thus arises the need (?) for a translating UDF that
turns your boring everyday average number into something that is virtually
non-discernible at first glance. But at least you'll be able to figure out
how old those movies are a little easier.

The syntax is simple. Just pass any number as a parameter. The parameter
passed is numeric but the returned result is (of course) character. See
page 17 for the UDF listing.

Everyday Occurrences
A recent edition of TechNotes/dBASE IV featured several variations of the
AT() function. Here is yet another that is well suited for use with memos
and long strings where the locations of several occurrences of a word or
letter might be needed. Also, see the article entitled "Pass the UDFs" in
the June '89 edition of TechNotes/dBASE IV.

AtArray() (listing on page 18) receives the same parameters as AT(),
except that you additionally specify the name of an array to be created.
AtArray() will determine the number of occurrences of the search string,
create an array with the same number of elements as there are occurrences,
store the location of each occurrence in each array element, and return the
number of matches that were found.

For example:

. string = "my mitt, my cap, my bat, and my baseball"
. ? AT("my",string)
1

. ? ATARRAY("my",string,"occur")
4
. ? occur[1]
1
. ? occur[2]
10
. ? occur[3],occur[4]
18 30

Binary to Decimal and Back Again

Here is a pair of UDFs that convert a decimal value to its corresponding
binary value, and vice versa. For example:

. bval = Binary(317)
. ? bval
100111101

. ? Decimal(bval)
317

The highest number that these UDF's can accurately accommodate is 524,288,
(2 to the 19th power.) The listings for Binary() and Decimal() are on
pages 18 and 19.



5 Roman.PRG

* Roman.PRG
* Author : James Chen
* Note : converts Arabic number (numeric) to Roman numeral (char).
*
* input : arabic (n) 1 .. 3999
* output : roman (c) "I" .."MMMCMXCIX"
*
* error : if number falls outside of range
* returns "Error, number out of range!"

FUNCTION Roman
PARAMETER arabic
SET TALK OFF

* Set lower and upper limit.
IF arabic <= 0 .OR. arabic => 4000
RETURN "Error, number out of range!"
ENDIF
* array of equivalents
DECLARE equiv[10]

equiv[1] = "" &&0
equiv[2] = "I" &&1
equiv[3] = "II" &&2
equiv[4] = "III" &&3
equiv[5] = "IV" &&4
equiv[6] = "V" &&5
equiv[7] = "VI" &&6
equiv[8] = "VII" &&7
equiv[9] = "VIII" &&8
equiv[10] = "IX" &&9

* Roman numeral symbols.
table = "IVXLCDM"

* - Variables.
result = ""&& final result, Roman numeral (char)
tmp_str = ""&& work string for result (char)

table_cnt = 0&& table position count

pos_x = 3&& position of "X" in the table
pos_v = 2&& position of "V" in the table
pos_i = 1 && position of "I" in the table

* Change arabic (numeric) into character.
arabic_char = LTRIM(STR(arabic))

* Get the length of the number.
arabic_len = LEN(arabic_char)

DO WHILE arabic_len # 0

* Find roman numeral, assuming it is 0-9 (rightmost digit).
tmp_str = equiv[VAL(SUBSTR(arabic_char, arabic_len, 1)) + 1]

* If not rightmost digit, translate it using the table.
IF .NOT. table_cnt = 0

* Need to translate "X" before "I"
* otherwise, "I" will be changed into "X"
* and "X" into something else, and result will be wrong.

* Translate all occurrences of "X" to equivalents.
repl_str = SUBSTR(table, pos_x + table_cnt, 1)
DO WHILE AT("X", tmp_str) # 0
pos = AT("X", tmp_str)
tmp_str = STUFF(tmp_str, pos, 1, repl_str)
ENDDO

* translate "V" to equivalent
pos = AT("V", tmp_str)
IF pos # 0
tmp_str = STUFF(tmp_str, pos, 1, SUBSTR(table, pos_v + table_cnt, 1))
ENDIF

* translate all occurrences of "I" to equivalents
repl_str = SUBSTR(table, pos_i + table_cnt, 1)
DO WHILE AT("I", tmp_str) # 0
pos = AT("I", tmp_str)
tmp_str = STUFF(tmp_str, pos, 1, repl_str)
ENDDO
ENDIF

result = tmp_str + result&& add the intermediate result
arabic_len = arabic_len - 1&& decrement arabic length
table_cnt = table_cnt + 2&& increment table position
ENDDO
SET TALK ON
RETURN Result


FUNCTION AtArray
* Author: Dan Madoni

PARAMETERS a_lookfor,a_lookin,a_array

* Store locations in a "Pseudo-array" and determine number of occurrences
STORE 0 TO a_cntr,a_find
DO WHILE a_cntr < LEN(a_lookin)
a_cntr = a_cntr + 1
IF SUBSTR(a_lookin,a_cntr,LEN(a_lookfor)) = a_lookfor
a_find = a_find + 1
a_hold = "a_hold" + LTRIM(STR(a_find))
&a_hold = a_cntr
ENDIF

ENDDO

* Return immediately with no defined array if no ocurrences.
IF a_find = 0
RETURN
ENDIF

*- Define real array as PUBLIC and store elements with pseudo-array.
PUBLIC &a_array
DECLARE &a_array[a_find]
a_cntr = 0
DO WHILE a_cntr < a_find
a_cntr = a_cntr + 1
a_hold = "a_hold" + LTRIM(STR(a_cntr))
&a_array[a_cntr] = &a_hold
ENDDO
RETURN a_find


FUNCTION Binary
* Author: Dan Madoni

PARAMETERS b_num
b_power = 20
b_result = ""
IF b_num <= 1
*- RETURN immediately if less than 1 (or negative) since no conversion
*- would be necessary.
RETURN b_num
ENDI
DO WHILE b_power > 0
*- Start with 2^19 and go down, adding 1's or 0's to the
*- result depending on the outcome of the division.
b_power = b_power - 1
IF b_num / 2^b_power >= 1 .AND. b_num > 0
b_result = b_result + "1"
b_num = b_num - 2^b_power
ELSE
b_result = b_result + "0"
ENDIF
ENDDO
RETURN VAL(b_result)


FUNCTION Decimal
* - Author: Dan Madoni

PARAMETERS d_num
IF d_num <= 1
*- RETURN immediately if less than 1 since no conversion.
*- would be necessary.
RETURN d_num
ENDIF

d_num = LTRIM(STR(d_num))
STORE 0 TO d_cntr,d_new

DO WHILE d_cntr < LEN(d_num)
d_cntr = d_cntr + 1
d_pos = SUBSTR(d_num,LEN(d_num) - d_cntr + 1,1)

*- Add 2 to the nth power or 0 to the result depending on
*- whether or not the current value is a 1 or 0.
d_new = d_new + IIF(d_pos = "0",0,2^(d_cntr - 1))
ENDDO
RETURN d_new



6 Weight and Fluid Conversions by Dan Madoni

Weight and Fluid Conversions by Dan Madoni

You may not be a world famous chef but, whether in the kitchen or in the
chemistry lab, this conversion UDF might come in handy.

Ever opened up a cookbook to make a recipe for more or less people than the
measurements are made for? (Pancakes are a perfect example!) The UDF
Fluid, listed below, will perform any of eight conversions.

American Weight measurement to American Fluid measurement
Metric Weight measurement to Metric Fluid measurement
American Fluid measurement to American Weight measurement
Metric Fluid measurement to Metric Weight measurement
Metric Weight measurement to American Fluid measurement
Metric Fluid measurement to American Weight measurement
American Fluid measurement to Metric Weight measurement
American Weight measurement to Metric Fluid measurement

Field Field Name Type Width Dec Index
1 TABLE Character 10 N
2 GRAM Numeric 11 5 N
3 KILOGRAM Numeric 11 5 N
4 MILLILITER Numeric 11 5 N
5 LITER Numeric 11 5 N
6 OUNCE Numeric 11 5 N
7 POUND Numeric 11 5 N
8 TEASPOON Numeric 11 5 N
9 TABLESPOON Numeric 11 5 N
10 FLUIDOUNCE Numeric 11 5 N
11 CUP Numeric 11 5 N
12 PINT Numeric 11 5 N
13 QUART Numeric 11 5 N
14 GALLON Numeric 11 5 N


To use the UDF, you must first access the conversion database table.

The syntax for this UDF is:

FLUID(, , )

Using this syntax, the following statement computes how many kilograms are
in 12 grams:

. SET DECIMALS TO 4
. ? FLUID(12,"GRAM","KILOGRAM")
. 0.0120



7 Fluid.PRG

FUNCTION Fluid
* - Author: Dan Madoni
PARAMETERS c_mult, c_from, c_to

SET DECIMALS TO 5
IF .NOT. FILE("FLUID.DBF")
RETURN 0
ENDIF

c_from = UPPER(LTRIM(RTRIM(c_from)))
c_to = UPPER(LTRIM(RTRIM(c_to)))

c_alias = IIF(LEN(RTRIM(ALIAS())) > 0,ALIAS(), "A")
SELECT 9
USE Fluid
LOCATE FOR table = c_from

IF EOF()
USE
SELECT &c_alias
RETURN 0
ENDIF

IF TYPE(c_to) <> "N"
USE
SELECT &c_alias
RETURN 0
ENDIF

ret = c_mult * &c_to
USE
SELECT &c_alias

RETURN ret



8 Weight and Fluid Conversions table

GRAMKILOGRAM MILLILITER LITER OUNCE POUND TEASPOON TABLESPOON FLUID OZ. CUP PINT QUART GALLON
--------------------------------------------------------------------------------------------------------------------------------------------------------------
GRAM 1 0.001 1 .00100 .03520 .00220 .04930 .20000 .03200 .00200 .00100 .00050 .00012
KILOGRAM 1000 1 1000 1 35.20000 2.21000 202.83975 67.65899 32 4.42000 2.21000 1.10000 .37000
MILLILITER 1 .00100 1 .00100 .03520 .00220 .04930 .20000 .03200 .00200 .00100 .00050 .00012
LITER 1000 1 1000 1 32.20000 2.21000 202.83975 .20000 32 4.23000 2.12000 1.06000 .26000
OUNCE 28.35000 .28350 28.35000 .28350 1 .06250 6 2 1.03000 .12500 .06250 .03125 .00390
POUND 453.60000 .45360 453.60000 .45360 16 1 96 32 16.48000 2 1 .50000 .06240
TEASPOON 4.93000 .00493 4.93000 .00493 .16666 .01041 1 .33333 .11111 .01388 .00694 .00347 .00086
TABLESPOON 14.78000 .01478 14.78000 14.78000 .50000 .03125 3 1 .33333 .04166 .02083 .01415 .00353
FLUIDOUNCE 29.57400 .29574 29.57400 .29574 1.30000 .08125 9 3 1 .12500 .06250 .03125 .00781
CUP 236.59200 .23659 236.59200 236.59200 8 .50000 72 24 8 1 .50000 .25000 .06250
PINT 473.18400 .47318 473.18500 .47318 16 1 144 48 16 2 1 .50000 .01250
QUART 946.36800 .94636 946.36800 .94636 32 2 288 96 32 4 2 1 .25000
GALLON 3785.47200 3.78547 3785.47200 3.78547 128 8 1152 384 128 16 8 4 1


9 Passing Parameters from DOS by Dan Madoni & Larry Sprott

Passing Parameters from DOS by Dan Madoni & Larry Sprott

Here are a few ways to pass parameters to your program whether you are in dBASE
IV or at the DOS level. Pretend you are a little magnetic signal in a computer
and you live in one of those little electronic chips and played on a baseball
team. You're well known by the home players at dBASE Stadium, because you catch
fly parameters so well and prevent the opposing team, (the PC-Ville Bugs,) from
hitting home runs.

Suddenly, your job is threatened! You're faced with the fear that you might
become just another little magnetic signal. You did such a good job at dBASE
Stadium, but you can't catch squat at DOS Field! The coach is yelling at you.
Your spouse and all the little signals at home are adding to the pressure. The
press is beginning to question your future. Are you WASHED UP??!

I have a feeling that I'm beginning to lose a lot of my readers-particularly the
ones that can't relate to being a magnetic signal. The point of this article is
that dBASE programs have no problem receiving parameters as long you start the
program from dBASE IV. However, you will be unable to receive parameters if you
start the program from DOS using...

C:\DBASE>dbase myprog

This probably isn't too much of an annoyance for dBASE IV users or developers
that can work strictly from within the dBASE IV environment. But those of you
who distribute your applications with RunTime have probably run into this and
felt a little claustrophobic. Here is a way to get around this limitation using
environmental variables in DOS.

The beauty of this concept is that we will also present a way for your program
to know whether or not the parameter is coming in as an environmental variable
from DOS or if it is coming in from a DO...WITH at the dot prompt. As an
example, the following illustrations will accomplish the same thing with regard
to starting a program and passing two character parameters. From the dot
prompt, enter:

. DO MYPROG WITH "Hello","Bye"

From the DOS prompt:

DB4 Myprog Hello Bye

The whole idea is simple. All you have to do is create a batch file that uses
SET to initialize DOS environmental variables with the values that are to be
passed, then check the value of the variables with the dBASE IV GETENV()
function. If you are familiar with the DOS SET command and the GETENV()
function, skip the next section.

What is a DOS Environmental Variable?

In DOS, you can assign a string of characters to a variable name using the SET
command. For example, to initialize a DOS variable called TEST with the string
"Chocolate", you would type the following at the DOS prompt with NO spaces after
the variable name...

SET TEST=Chocolate

Once you have the entered this command, you can enter the command SET at the DOS
prompt. Doing so will list all defined environmental variables. Among the
list, you will see

TEST=Chocolate

To take TEST out of environmental space memory, you would type:

SET TEST=

With this in mind, we come to the GETENV() function in dBASE IV. This gives you
the ability to determine the value of an environmental variable. Let's say we
SET our TEST variable to contain the string "Chocolate" as we did in the example
above. Within dBASE IV we could determine the value of TEST and store it to a
variable called myParam with the following command:

myParam = GETENV("TEST")

By defining a DOS environmental variable at the DOS prompt and then capturing
its value inside dBASE IV, we are able to accomplish our goal.

The Batch File
Shown below is a listing of the batch file:

ECHO OFF
CLS

REM Batch Name : DB4.BAT
REM Authors : Dan Madoni, Larry Sprott
REM Note : Batch file for passing parameters

REM Fill Parameter variables

SET PARAMETER1=%2
SET PARAMETER2=%3
SET PARAMETER3=%4
SET PARAMETER4=%5
SET PARAMETER5=%6
SET PARAMETER6=%7
SET PARAMETER7=%8
SET PARAMETER8=%9

REM Run dBASE IV with /T (to bypass license screen)
dBASE /T %1
REM "Undo" SET variables
SET PARAMETER1=
SET PARAMETER2=
SET PARAMETER3=
SET PARAMETER4=
SET PARAMETER5=
SET PARAMETER6=
SET PARAMETER7=
SET PARAMETER8=

REM End DB4.BAT

NOTE: Using replaceable parameters past %9 requires use of the SHIFT command
in your .BAT file. See your DOS manual for more information.

This program will initialize up to 8 environmental variables with the second
through eighth parameter at the DOS command prompt. This means you can pass up
to 8 parameters with this batch file.

On the dBASE IV End of Things
Now that you have your batch file in order, you are going to need to write some
code to receive the parameters. We will do this by using the dBASE IV GETENV()
function. The two examples below accomplish the same thing-each receives an x
and y parameter. The first example would be run from the Dot Prompt. The
second example would be run from DOS using our batch file. Following the two
examples, we will discuss how to make a program that can do either. From the
dot prompt, enter:

DO Prg1 WITH "Hello","Bye"

The program this calls, as you can see, simply displays the two parameters you
passed to it on screen.

*- Prg1
PARAMETERS x, y
? x
? y
RETURN

Doing the same function from the DOS prompt requires a few more steps from a
code point of view. However, note the absence of quotes around the parameters
passed. Also note that dBASE IV is called via a batch file and not directly.
So, from the DOS prompt you would enter:

DB4 Prg2 Hello Bye

and the dBASE IV code would appear as follows:

*- Prg2
*- Called with DB4.BAT
x = GETENV("PARAMETER1")
y = GETENV("PARAMETER2")
? x
? y
RETURN

Before we show you how to create a program that does both, a small limitation
needs to be brought out. If you write a program like this, you cannot use
logical variables as parameters without using a special workaroud. The example
below will receive a CHARACTER, NUMERIC, DATE, and LOGICAL variable and display
their values. Note that the logical value is passed as a character value and is
converted to logical.

*- Prg3
*- Called from Dot Prompt or DOS Prompt with DB4.BAT
PARAMETERS char, num, date, log

IF TYPE("char") = "L"
*- If the first parameter is logical instead of character,
*_ we assume that there was a DO command without the WITH for passing
*_parameters and that the SET variables were initialized.
char = GETENV("PARAMETER1")
num = GETENV("PARAMETER2")
date = GETENV("PARAMETER3")
log = GETENV("PARAMETER4")

num = VAL(num)
date = CTOD(date)
ENDIF

*- Convert character representation of logical field to a true logical field.
log = (log # "F")

*- Display values
? char
? num
? date
? log

RETURN

The above program can be called with either of the following commands:

From the dot prompt

. DO PRG3 WITH "Hello",7, CTOD("01/01/91"),"T"

From the DOS prompt

C:\DBASE>DB4 PRG3 Hello 7 01/01/91 T

Either would display the following output:

Hello
7
01/01/91
.T.

It seems our little magnetic signal guy isn't washed up after all. Thanks to
the power of dBASE IV, the little guy will be catching fly parameters right and
left and winning over the appreciation of many-a-dBASE user!


10 Introduction to Indexing by Dan Madoni

Introduction to Indexing by Dan Madoni

The inclination of the new user to use SORT over INDEXING is natural but not the
best path for efficient file handling. If you're at that stage where you have
the basics of the Control Center down and you want to move forward into the real
meat of dBASE IV, you have probably come to the conclusion that it's no
midget.

One thing that makes learning to program in dBASE IV even more confusing is the
fact that there are so many different ways to do the same thing. This article
will attempt to spotlight one of those areas, namely, the difference between
SORTing and INDEXing and the difference between the two when it comes to finding
and querying information. Actually, the focus of the article is on INDEXing,
but we'll compare it with SORTing to lay some foundation.

Ever since dBASE II, there have been two approaches to ordering records: SORTing
and INDEXing. When you SORT a database, you are actually creating a new
database with the records in sorted order. If you change the order of the
original database, the SORTed database is not affected since it is a separate
and unconnected entity. It seems like an okay way to place records in a desired
order until you consider the alternative.

Of course, the alternative is to INDEX a database. When you INDEX, you create
an index tag. The index tag does not contain your data-it contains information
on where data is; kind of like the index in the back of a book. When you
activate the index tag, the physical order of your database has not changed; it
is simply being displayed in the order governed by the index tag.

Using an index has many advantages over SORTing a database. An obvious
advantage is that less hard drive space is being used since dBASE IV is not
creating a copy of your database just to put it in order.

Another advantage is that you can instantly find data in your database using a
SEEK or FIND command when the tag is active. This is because dBASE IV can use
the index to find data in the same way you would find something in a book by
checking its index. If you were trying to find data in a database without an
index, using the LOCATE FOR command, dBASE IV looks at each record in sequence
until it finds the one you want just as if you were looking for an area of a
book by paging through it until you found it. The obvious outcome is a much
slower search especially with a large file.

So when would you ever use SORT in place of INDEX? Conceivably, you could
program in dBASE IV for several years and never once use SORT in a program. The
implied conclusion, (in case you didn't catch it,) is that, if you understand
how to use indexes, there is no reason to resort to SORT.

With this in mind, let's shift our focus to some of the confusing things about
indexes...

The difference Between an Index File and an Index Tag
To answer this question, let's go back to previous versions of dBASE. Suppose
you had dBASE II, dBASE III, or dBASE III PLUS and you wanted to create an index
for ordering and searching a field called LASTNAME, and another for a field
called FIRSTNAME. To do this, we would perform the following steps at the dot
prompt:

. USE MyFile
. INDEX ON LastName TO Index1
. INDEX ON FirstName TO Index2

Upon completing these commands, we end up with two files: INDEX1.NDX and
INDEX2.NDX. Remember that these files only contain index information and that
they are not copies of the database-they are two separate files. This kind of
indexing gives you the ability to do many of the things you can do with indexes,
but there were disadvantages.

One such disadvantage is that, in order to keep the indexes updated so that any
new data in the database became a part of the index, you had to make sure that
the index file was open by doing something like this:

. USE MyFile
. SET INDEX TO Index1, Index2

Not only is this a hassle considering the fact that you have to keep track of
all your indexes and make sure all are opened, it is also quite limiting in that
you only have a certain number of files you can have open at a time while you
are in dBASE IV.

dBASE IV introduced a much better approach to indexing with the concept of the
index tag. Index tags not only offer convenience over the old way, they also
offer a couple of new things you can't do with index files.

To begin with, you no longer have to worry about opening up all your index tags
to make sure they are updated. The MDX file-in which the tags are stored-is
automatically opened with the database and subsequently updated whenever data in
the database is modified. We'll get in to some of the things you can do with
index tags that you can't do with index files, but first let's look at syntax.

To create two index tags-one on FIRSTNAME and one on LASTNAME-you would do the
following:

. USE MyFile
. INDEX ON FirstName TAG FirstName
. INDEX ON LastName TAG LastName

To activate the LastName tag:

. SET ORDER TO LastName

To put the database back into natural order:

. SET ORDER TO

One of the new features I would like to bring up at this point involves a brief
discussion on FILTERing. In dBASE IV, we can use the SET FILTER command to view
a subset of data, for example:

. USE MyFile
. SET FILTER TO State = "CA"
. GO TOP

would limit data to only those records in which STATE is equal to "CA". This
was basically the method of choice before dBASE IV came around. The problem
with SET FILTER is that it is extremely slow with large files. Once you set
your filter, dBASE IV has to continuously execute internal LOCATE FOR commands
to find data that matches the FILTER condition. As we discussed earlier, this
is like flipping through a book page by page to find areas of interest instead
of looking in the index to find them instantly.

Previous to dBASE IV, there was no easy way to use the speed advantage of an
index to filter data. These days, we can use INDEX FOR to do the job as long as
we are using index tags and not index files. To obtain the equivalent subset of
data that the SET FILTER command obtains in the above example, we would do the
following:

. USE MyFile
. INDEX ON LastName FOR State = "CA" TAG Calif

And here's the icing on the cake: once the index is created, we never have to
worry about updating the "filter" since index tags are updated automatically!
If we ever want this subset of data again in the future, we don't need to create
a new index-you only need to:

. USE MyFile
. SET ORDER TO Calif

Something else you can do with indexes that you can't do otherwise is to find a
record that almost matches what you want. To illustrate this, suppose we are
trying to find a record with a LASTNAME of "Smith".

. USE MyFile
. SET ORDER TO LastName
. SEEK "Smith"

As a result of this command, we are instantly at the record that has "Smith" in
the LastName field. If there is one or more Smith's, we can BROWSE our data and
see all of them. But what if we aren't quite sure of its spelling? Okay,
okay. The average person doesn't have a hard time spelling "Smith"-but just
suppose. In dBASE IV, we can use SET NEAR to get close enough to the name to
find it. When we SEEK with SET NEAR ON and there isn't a match, we will end up
one record below the record that most closely matches what we are looking for.
For example:

. USE MyFile
. SET ORDER TO LastName
. SET NEAR ON
. SEEK "Smoth"

When we BROWSE the database, we will find ourselves just below "Smith" in the
database, (assuming there isn't a spelling that comes between "Smith" and
"Smoth".)

This is just the beginning of the features offered by indexes in dBASE IV.
Other index commands and functions include:

COPY INDEXES To convert your old index files to dBASE IV index tags.

COPY TAG To do the opposite, (although hopefully, after reading this article,
you won't want to use index files.)

DELETE TAG To remove an index tag you are no longer using. You may want to do
this when your MDX file which contains your index tags is getting too big
because of tags you are no longer using. You may also want to DELETE TAGs if
movement in BROWSE or EDIT is becoming very slow because of all the tags dBASE
has to update every time you update records.

KEY() To determine what expression you used to create your index tag.

LOOKUP() To instantly find a value from a different database with an active
index.

ORDER() To determine which tag you are currently using.

In a nutshell, indexes can make your program appear to run faster and more
efficiently. Practice index tricks. As you create more and more complex
expressions using the different index features as well as other dBASE IV
functions, you'll find how versatile an index can be in solving most of your
search and querying problems.


11 What's Good for the Goose...

What's Good for the Goose...

Changes for the better can be applauded by the majority but you'll still get a
hiss and boo from some users.

In the last update of dBASE IV version 1.1 (x509), some people have discovered
that the RunTime version of BROWSE does not offer an Organize menu. This change
came as a result of many developers who did not want the end users of their
systems accessing indexes that were already set in place by the application.
Many people asked for this "menu block" before the implementation was finally
made in this most recent update. However, like most things, a few developers
were left in the lurch when they found they could no longer access the Organize
menu.

Well, here's a programming alternative. The procedure, ChngOrdr, let's you put
a menu on the screen from which a user can select a controlling index while in
BROWSE. The parameters passed to the procedure can be any row and column
coordinates that will reference the upper left corner of the popup menu. The
sequence of commands below shows how ChngOrdr can be used within your program
(with a file already in use).

ON KEY LABEL Alt-O DO ChngOrdr WITH 2,20
BROWSE NOMENU
ON KEY LABEL Alt-O

PROCEDURE: ChngOrdr

PARAMETERS mRow, mCol
IF LEN(TAG(1)) = 0
SAVE SCREEN TO TmpScrn
@ 5,5 CLEAR TO 7,74
@ 5,5 TO 7,74 DOUBLE
@ 6,12 SAY "No index tags in this database...press any key to continue"
x = INKEY(0)
RESTORE SCREEN FROM TmpScrn
RETURN
ELSE
DEFINE POPUP TagPop FROM mRow, mCol
DEFINE BAR 1 OF TagPop PROMPT " Choose an index to order by " SKIP
DEFINE BAR 2 OF TagPop PROMPT REPLICATE(CHR(205), 29) SKIP
mBarNum = 3
mTagNum = 1
DO WHILE LEN(TAG(mTagNum)) > 0
mPrompt = SPACE(9) + TAG(mTagNum)
DEFINE BAR mBarNum OF TagPop PROMPT mPrompt
mTagNum = mTagNum + 1
mBarNum = mBarNum + 1
ENDDO
ON SELECTION POPUP TagPop DEACTIVATE POPUP
ACTIVATE POPUP TagPop
IF LASTKEY() # 4 .AND. LASTKEY() # 19 .AND. LASTKEY() # 27
SET ORDER TO (LTRIM(RTRIM(PROMPT())))
KEYBOARD CHR(31)
ENDIF
RELEASE POPUP TagPop
ENDIF
RETURN

Is the Pen Mightier?
We are pleased to announce that our most popular software products are
compatible with PenDOS, a pen-based operating environment from Communication
Intelligence Corporation. PenDOS works with standard DOS applications on a 386
notepad computer.

Ashton-Tate's PenDOS-compatible products include: dBASE IV, the most widely used
PC database management system worldwide; RapidFile 1.2, a flat-file PC database
program for fast and easy label, report and mailing list creation; APPLAUSE II,
a comprehensive presentation graphics program with powerful drawing and charting
capabilities; and Framework IV, which combines word processing, spreadsheet,
data management, graphics, outlining, telecommunications and electronic mail
into one integrated program.

dBASE IV version 1.1 and RapidFile 1.2 run under PenDOS using mouse drivers
supplied by Mostly Mice, a supplier of mouse-pointing software. Ashton-Tate
plans to make future versions of dBASE IV fully compatible with PenDOS without
requiring additional mouse drivers.

In addition to providing mouse support, PenDOS takes maximum advantage of the
power of the pen by employing a graphical or character-based user interface,
gesture commands and a writing window which allows handwritten entries on top of
dBASE IV, RapidFile, APPLAUSE II and the Framework products.

Record and File Locking Problems
dBASE IV version 1.1 doesn't support file and record locking on DOS 4.x or DOS
5.x workstations accessing a 3Com 3+Share Network. This is a 3Com problem.
This isn't an issue with the other supported networks (Novell Netware 286/386,
3Com 3+Open, IBM PC Network). Although dBASE IV version 1.1 has not been
officially certified or tested by Ashton-Tate Development, tests within
Technical Support conclude that record and file locking also work with Microsoft
LAN Manager version 2.0.

One symptom to look for in your environment is the ability of many DOS 3.x
workstations to successfully obtain a lock on a record in a .DBF. At which
time, other workstations running DOS 4.x or DOS 5.x can also successfully obtain
a record lock on the same record in the .DBF.

An alternative workaround to those experiencing this problem is to go back to
DOS 3.x on their workstations. Another option, albeit a costly one, is to
upgrade to Microsoft LAN Manager version 2.0.

SQL Server Direct Connect Usually, to directly query the SQL Server, you must
enter at the SQL dot prompt:

SENDSQL "transact-SQL statement" EXECUTE;

Using the program listed below as an interrupt routine, you would press F3 and a
window will appear into which you can enter your Transact-SQL statement or
statements. Statements are sent to the server when you exit the window. The
program retains what was entered until you press Alt-C to clear the window. In
the example program below, you may need to substitute names of arrays, windows
and memory variables so they do not conflict with your own application.

* PopiSQL.PRS
*
* Place the following statement in your Config.DB file:
*
* FUNCTION = 3,"do c:\fullpath\popisql.prs;"
*
SET TALK OFF
IF TYPE("exeArray[1]") = "U"
PUBLIC clrvar
PUBLIC ARRAY exeArray[4]
STORE SPACE(60) TO exeArray[1], exeArray[2], exeArray[3]
clrvar = " * * Press Alt-C to Clear * *"
ENDIF
DEFINE WINDOW temp01 FROM 10,10 TO 15,71 NONE
ACTIVATE WINDOW temp01
ON KEY LABEL Alt-C DO Clearit
@ 3,0 GET clrvar
CLEAR GETS
@ 0,0 GET exeArray[1]
@ 1,0 GET exeArray[2]
@ 2,0 GET exeArray[3]
READ
ON KEY LABEL Alt-C
DEACTIVATE WINDOW temp01
RELEASE WINDOW temp01
SENDSQL exeArray EXECUTE;
RETURN

PROCEDURE Clearit
PUBLIC where
where = VARREAD()
cnt = 0

* Loop to bottom using KEYBOARD to down arrow.
DO WHILE cnt < 3 - VAL(SUBSTR(where, AT("[", where) + 1, 1))
KEYBOARD CHR(24)
cnt = cnt + 1
ENDDO
* Loop back up by repeating the Home, Ctrl-Y and Uparrow key three times.
KEYBOARD CHR(26) + CHR(25) + CHR(5) + CHR(26) + CHR(25) + CHR(5) + CHR(26) + CHR(25)
* EOP: PopiSQL.PRS


12 ChngOrdr.PRG

PROCEDURE: ChngOrdr

PARAMETERS mRow, mCol
IF LEN(TAG(1)) = 0
SAVE SCREEN TO TmpScrn
@ 5,5 CLEAR TO 7,74
@ 5,5 TO 7,74 DOUBLE
@ 6,12 SAY "No index tags in this database...press any key to continue"
x = INKEY(0)
RESTORE SCREEN FROM TmpScrn
RETURN
ELSE
DEFINE POPUP TagPop FROM mRow, mCol
DEFINE BAR 1 OF TagPop PROMPT " Choose an index to order by " SKIP
DEFINE BAR 2 OF TagPop PROMPT REPLICATE(CHR(205), 29) SKIP
mBarNum = 3
mTagNum = 1
DO WHILE LEN(TAG(mTagNum)) > 0
mPrompt = SPACE(9) + TAG(mTagNum)
DEFINE BAR mBarNum OF TagPop PROMPT mPrompt
mTagNum = mTagNum + 1
mBarNum = mBarNum + 1
ENDDO
ON SELECTION POPUP TagPop DEACTIVATE POPUP
ACTIVATE POPUP TagPop
IF LASTKEY() # 4 .AND. LASTKEY() # 19 .AND. LASTKEY() # 27
SET ORDER TO (LTRIM(RTRIM(PROMPT())))
KEYBOARD CHR(31)
ENDIF
RELEASE POPUP TagPop
ENDIF
RETURN