1 Read Me First

These articles are reprinted from the June 1991 edition of
TechNotes/dBASE IV. Due to the limitations of this media, certain
graphic elements such as screen shots, illustrations and some tables
have been omitted. Where possible, reference to such items has been
deleted. As a result, continuity may be compromised.

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center. For subscription information, call 800-545-9364.



2 UDF Library

What's In a Name?

Often times dBASE users create a database file with a character field
that contains a person's full name only to find that it would have
been better to store the first, middle, and last names into three
separated character fields. (I won't quote the "Hindsight is 20/20"
cliche. Oops! Too late.) Since the length of the names within the
full name are not consistent, we can't simply use the REPLACE ALL
command to separate the full name field into the three character
fields. With the help of a UDF called PickName you can pick out the
first, middle, or last names. PickName also perform upper/lower case
conversion. PickName works with a full name that is empty, with or
without a middle name. This function only works with names composed
of no more than three words.

Syntax
PickName(,,)

Usage

The first argument is the full name. The second argument tells the
function whether on not to make the first letters of each name upper
case.

"U[ppercase]" or "" - Change to uppercase
"L[owercase]" or " " - Change to lowercase

The third argument tells the function which word to return.

"left" - Pick the name on the left
"middle" - Pick the name in the middle
"right" - Pick the name on the right

Examples

.mName = " KeVin a. mann"

.? PickName(mName,"u","m")
A.
? PickName(mName,"","Left")
Kevin
.? PickName(mName,"","right")
Mann
? PickName(mName,"low","r")
mann
Consider this structure:

FullName C 45
FirstName C 15
MidName C 10
LastName C 20

In the FullName field, assume that the full name was entered in the
following fashion: first, middle, last. To separate the names stored
in Fullname into the respective fields FirstName, MidName, and
LastName for all records in your database you would type the following
at the dot prompt:

.REPLACE ALL Fname WITH PickName(Names, "", "left"), Mname WITH
PickName(Names, "", "mid"), Lname WITH PickName(Names, "", "right")

If It's Tuesday, This Must Be dBASE
How do you calculate the number of specific days of the week between
any two given dates? How can you calculate the number of business
days between two given dates? NumOfDay() will return the number of a
particular day of the week between two given dates. When entering the
day of the week, you should enter at least the first two letters of
that day, since some days of the week share the same first letter.

Syntax
NumOfDay(, , <"option">)

Usage
The first and second arguments contain the starting and ending dates,
respectively. The third option is the day that you want to count.
Use at least the first two letters of the day, otherwise, the result
might not be correct.

Examples

.mStartDate = {01/01/91}
.mEndDate = {12/31/92}

.? NumOfDay(mStartDate, mEndDate, "sunday")
104
.? NumOfDay({01/01/91}, {12/31/92}, "su")
104
.? NumOfDay({01/01/91}, {09/15/92}, "sun")
91
.?NumOfDay({02/01/91}, {07/15/92}, "wed")
76

To find out the number of week days between two given dates type the
following command lines:

.mStartDate = {05/03/91}
.mEndDate = {06/31/92}
.mWeekDays = (mEndDate - mStartDate) -
NumOfDay(mStartDate, mEndDate, "Saturday") -
NumOfDay(mStartDate, mEndDate, "Sunday")
? mWeekDays
303

Note:
Multi-lined statements as shown in the third line of the above example
and on the opposite page should be entered on one contiguous line when
working from the dot prompt.

Get to Work!
While we are on the subject of dates, here are a couple of functions
that return the first or last date of the month that does not fall on
a weekend day, (which is Saturday or Sunday for most of us.) One date
parameter is required as shown in the example below.

Examples

. ? DATE()
06/10/91

. ? FWDoM(DATE())
06/03/91

. ? CDOW(FWDoM(DATE()))
Monday

. ? DATE()
06/10/91

. ? LWDoM(DATE())
06/29/91

. ? CDOW(LWDoM(DATE()))
Friday

First Day Of Date
This function returns the first occurrence of a given day in a month.
FDoD() requires a date parameter and a character string representing
one of the seven days in a week.

Examples

. ? DATE()
06/10/91

. ? FDoD(DATE(),"Tuesday")
06/04/91

. ? FDoD(DATE(),"Thursday")
06/06/91

The DAY parameter for FDoD() is not case sensitive.
Last Day Of Date\

This function returns the last occurrence of a given day in a month.
As with FDoD(), LDoD() requires a date parameter and a character
string representing one of the seven days in a week.

Examples

. ? DATE()
06/10/91

. ? LDoD(DATE(), "Thursday")
06/27/91

. ? LDoD(DATE(), "Sunday")
06/30/91

The DAY parameter for LDoD() is not case sensitive.


Function: PickName()

FUNCTION PickName
* Author: Brian Nguyen
PARAMETERS FullName, CaseOfChar, LeftMidRight
SET ECHO OFF
SET TALK OFF
SET EXACT OFF
FullName = LTRIM(RTRIM(FullName))
mToken = ""
DO CASE
CASE "RIGHT" = UPPER(LeftMidRight) .AND. ;
LEN(FullName) > 0
mIndex = LEN(FullName)
DO WHILE mIndex >= 1 .AND. ;
SUBSTR(FullName, mIndex, 1) <> " "
mToken = SUBSTR(FullName, mIndex, 1) + mToken
mIndex = mIndex - 1
ENDDO
CASE "MID" = UPPER(LeftMidRight) .AND. LEN(FullName) > 0
mToken = FullName
DO WHILE LEN(mToken) >= 1 .AND. ;
SUBSTR(mToken, 1, 1) <> " "
mToken = SUBSTR(mToken, 2)
ENDDO
mToken = LTRIM(mToken)
mToken = SUBSTR(mToken, 1, AT(" ", mToken) - 1)
CASE "LEFT" = UPPER(LeftMidRight) .AND. LEN(FullName) > 0
mIndex = 1
DO WHILE mIndex <= LEN(FullName) .AND. ;
SUBSTR(FullName, mIndex, 1) <> " "
mToken = mToken + SUBSTR(FullName, mIndex, 1)
mIndex = mIndex + 1
ENDDO
ENDCASE
DO CASE
CASE LEN(mToken) = 0
mToken = ""
CASE LEN(CaseOfChar) = 0
mToken = UPPER(SUBSTR(mToken, 1, 1)) + ;
LOWER(SUBSTR(mToken, 2))
CASE "UPPERCASE" = UPPER(CaseOfChar)
mToken = UPPER(mToken)
CASE "LOWERCASE" = UPPER(CaseOfChar)
mToken = LOWER(mToken)
CASE " " = UPPER(CaseOfChar)
mToken = UPPER(SUBSTR(mToken, 1, 1)) + ;
LOWER(SUBSTR(mToken, 2))
ENDCASE
SET TALK ON
RETURN mToken

Function: NumOfDay()
FUNCTION NumOfDay
* Author: Brian Nguyen

PARAMETERS StartDate, EndDate, WhatDay
SET TALK OFF
SET ECHO OFF
mDayCount = 0
SET EXACT OFF
mdays = ;
"Sunday Monday Tuesday WednesdayThursday FridaySaturday"
* Space above exactly!
IF AT(UPPER(whatday),mdays) = 0
RETURN 0
ENDIF
mday = INT(AT(UPPER(whatday),mdays) / 9) + 1
mCurrDate = StartDate
DO WHILE mCurrDate <= EndDate
IF DOW(mCurrDate) = mDay
mDayCount = mDayCount + 1
ENDIF
mCurrDate = mCurrDate + 1
ENDDO
SET TALK ON
RETURN mDayCount

Function: FWDoM()
FUNCTION FWDoM
* Author: Dan Madoni
* First Work Date Of Month

PARAMETERS f_date

f_cent = SET("CENTURY")
SET CENTURY OFF

f_fd = CTOD(RIGHT("00" + LTRIM(STR(MONTH(f_date))), 2) + "/01/" +;
RIGHT("00" + LTRIM(STR(YEAR(f_date))), 2))

DO WHILE DOW(f_fd) = 1 .OR. DOW(f_fd) = 7
f_fd = f_fd + 1
ENDDO

SET CENTURY &f_cent
RETURN f_fd

Function: LWDoM()
FUNCTION LWDoM
* Author: Dan Madoni
* Last Work Day Of Month; needs LDOM()

PARAMETERS L_date

l_cent = SET("CENTURY")
SET CENTURY OFF

l_fd = ldom(l_date, "D")

DO WHILE DOW(l_fd) = 1 .OR. DOW(l_fd) = 7
l_fd = l_fd - 1
ENDDO

SET CENTURY &l_cent
RETURN l_fd

Function: FDoD()
FUNCTION FDoD
* Author: Dan Madoni
* First Date of Day

PARAMETERS f_date, f_day

f_day = UPPER(f_day)
IF .NOT. f_day $ ;
"SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY"
RETURN {}
ENDIF

f_cent = SET("CENTURY")
SET CENTURY Off

f_fd = CTOD(RIGHT("00" + LTRIM(STR(MONTH(f_date))), 2) + "/01/" +;
RIGHT("00" + LTRIM(STR(YEAR(f_date))), 2))

DO WHILE UPPER(CDOW(f_fd)) <> f_day
f_fd = f_fd + 1
ENDDO

SET CENTURY &f_cent
RETURN f_fd

Function:LDod()
FUNCTION LDoD
* Author: Dan Madoni
* Last Date of Day; needs LDOM()

PARAMETERS l_date,l_day

l_day = UPPER(l_day)
IF .NOT. l_day $ "SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,;
FRIDAY,SATURDAY"
RETURN {}
ENDIF

l_cent = SET("CENTURY")
SET CENTURY OFF

l_fd = LDOM(l_date, "D")
DO WHILE UPPER(CDOW(l_fd)) <> l_day
l_fd = l_fd - 1
ENDDO

SET CENTURY &l_cent
RETURN l_fd





3 Q & A

GETting Nowhere

I have a program in which I perform an @.SAY/GET command within a DO
WHILE .T. loop. In most cases, the program works just fine. In some
instances, however, I will obtain an error that says I've exceeded the
maximum number of GET commands. How can I extend this number beyond
the 128 allowed before I get an error?

The section on configuring the CONFIG.DB file in Getting Started
refers to a number of settings that you can alter to improve dBASE IV
performance. Two of those settings, BUCKET and GETS increase the
amount of kilobytes for and the number of GETS respectively.

However, this may not be your most viable solution. Even if you
express a very high number, you're still setting a ceiling that
potentially will still render the same error. Instead, simply place a
CLEAR GETS statement before your ENDDO in your DO WHILE .T. loop.
This will allow you to continue to loop and perform @.GETs infinitely.

Floating Point Accuracy

Can you explain the differences between type N and type F numeric
fields. Language Reference states that type F data types are more
useful in scientific applications. Why can't you use type N if you
carry the decimal precision out many decimal places? Wouldn't this
give the same results if we set precision out to 15 and we were only
interested in the first 10 digits after the decimal point? Is there
any usefulness to type F besides the possible rounding error?

The main difference between N and F is that type N uses BCD (binary
coded decimal) which represents numbers with more numeric accuracy.
In the classic sense, a floating type is a preferred data type for
scientific usage since they often deal with very large numbers and can
factor in a very small degree of inaccuracy. Versions of dBASE before
dBASE IV used a float type method to determine numbers. The new type
N in dBASE IV uses the BCD method. N is the preferred data type for
numbers which is why it was decided to change the N data type from
float to BCD. The Float type was kept to retain a compatibility with
previous versions of dBASE.

The Lotus Position

I am attempting to import a .WKS file from Lotus 1-2-3 using the
APPEND FROM command with the WKS parameter. What happens is that the
first record does not get appended. Am I doing something incorrectly
or is this a problem?

You will encounter this problem in dBASE IV when you have not used
column headings in your Lotus spreadsheet. If you have no headings,
insert a blank row at the top of the spreadsheet. Once in dBASE IV,
the APPEND FROM will work correctly.

Weekly Reporter

I want to group my database by weeks but I only have a date field in
the database. How can I calculate with this field to group by week?

While in report design, add a group band (position your cursor in the
Page Header band and choose this option from the Bands menu). When
prompted for an "Expression value", enter the following:

INT((date_field - {01/01/84}) / 7)

Substitute the name of the date field in your file for the italicized
place marker in the above expression. Since January 1, 1984 was a
Sunday, this expression will return a week number starting on a
Sunday. Voil! Your report is grouped by week. All you need do now
is place the necessary summary information in the Group Summary band.

Overriding Popup Pick-list

I am using the popup picklist option for screen forms outlined in the
README.DOC file. As it works now, if I enter something that is
already there, the popup does not show up and the entry is accepted.
If I make an entry that is not in the popup database, the popup shows
up as it should. There are times when I would like to make an entry
into the database file that is not already in the popup database but
if I press Esc, I get an error saying I've made an invalid entry.
What can I do?

When using the popup picklist option, a program called U_POPS is
created. If you edit this code, you will see toward the end of the
listing a line that says

return(ll_return)

If you change the return parameter ll_return to .T., it will eliminate
the validation of the entry against the popup file, thus always
allowing you to continue whether you entered the entry from the
keyboard or chose it from the picklist. While you are editing your
file, if you want to make your own entry into a field that has the
popup-picklist option selected, make your entry, press Enter, and
press Escape when the popup appears. The value you entered will be
retained in that field.

Just Say the Secret Word

I want to use an @.SAY.GET command to input a password but don't want
the password displayed on screen. To do this, I set the COLOR OF
FIELDS and COLOR OF NORMAL the same so they blended together. I
thought this would work great except for one problem. The cursor also
disappears and I need that to verify that keys are being pressed. Is
there a way to accomplish this?

There are several password entry routines that don't show any cursor
movement. But, if you have a need to show keyboard activity (without
showing what keys were pressed) here is a UDF that will display an
asterisk (*) for every character typed, and will erase an asterisk
when the backspace key is pressed.

All you do is pass what the password should be to the function and do
something like that shown below.

Camoflaged cursor example

@ 5,20 SAY "Enter Password: "
IF .NOT. DispKey("Mypass")
?? "Password invalid"
ENDIF

FUNCTION DispKey
* Author: Michael P. Dean
PARAMETERS lc passwrd
lc guess = ""
ln start = COL()
ln col = COL()
DO WHILE .T.
lc key = INKEY(0)
IF lc key = 13
EXIT
ENDIF
IF lc key = 127
lc guess = ;
IIF(LEN(lc guess) > 0, SUBSTR(lc guess, 1, (LEN(lcguess) - 1)),;
lc guess)
ln col = IIF(ln col - 1 < ln start, ln col, ln col - 1)
@ ROW(),ln col SAY " "
ELSE
@ ROW(), ln col SAY "*"
ln col = ln col + 1
lc guess = lc guess + CHR(lc key)
ENDIF
ENDDO
RETURN UPPER(lc passwrd) = UPPER(lc guess)
* EOP: Dispkey.PRG




4 Manual Overwrite - Replacing From Arrays - Joe Stolz

Replacing from Arrays
Joe Stolz
Arrays and array processing have previously been discussed in this
column. Still, there seems to be a never ending stream of additional
information to add to our base of knowledge about any particular
subject. In that vein, though it may seem esoteric at first, let's
discuss the command REPLACE FROM ARRAY.

This command, which made its appearance in version 1.1, allows the
contents of records in a database to be modified or updated from a
series of values stored in an array. This is contrasted with the
usual REPLACE WITH command where the specified fields in the database
are modified using the values or variables specified in the WITH part
of the REPLACE command. The easiest way to view this is with a simple
example.

A database contains a record which has a misspelling. The name
"Joens" should be spelled "Jones". Using REPLACE with a variable we
would accomplish this with the two commands below to find and update
the record by correcting the misspelled word.

new_name = "Jones"
REPLACE Lastname WITH new_name FOR Lastname = "Joens"

Using the array method leads to this series of commands:

DECLARE new_name[1]
new_name[1] = "Jones"
REPLACE FROM ARRAY new_name FIELDS Lastname FOR Lastname = "Joens"

Although the syntax differs dramatically, the concept and the result is
the same. An additional factor to take into consideration with REPLACE
FROM ARRAY is that, while a REPLACE WITH command can perform
calculations during the REPLACE operation, as in:

REPLACE ALL Salary WITH Salary * 1.05

there is no single command which will process all records in the file
using REPLACE FROM ARRAY. This is because the contents and the
structure of the array are used as a source for the update data in
contrast to the specification of this information following the WITH
clause. The closest you could come to the REPLACE ALL example above
is to copy the first 100 records to a 100 row array, modify each array
element within a DO WHILE loop performing the salary calculation, then
finally REPLACE FROM the array into the first 100 records. Then you
would have to proceed with the next 100 records, and so on. This is
not the most direct way to proceed and its performance would not be
equivalent to that of the more direct REPLACE ALL command.

Which brings up another interesting point about arrays: there are one
and two-dimensional arrays and each behaves differently in a REPLACE
FROM ARRAY command. A one-dimensional array is one in which there is
only one array index and the index represents the number of "fields"
in the array. That is, Name_array[10] is equivalent to a database of
10 fields within a single record. A two-dimensional array is more
analogous to a database in that the first array index refers to the
number or "records" in the array and the second index refers to the
number of "fields" present. Thus, Employee[50,15] represents a series
of 50 employee records, each containing 15 fields of data. In a
theoretical sense then, Name_array[10] is equivalent to
Name_array[1,10] since the first dimension represents the number of
records (or rows) in the array which is equal to one in this case. In
fact, dBASE IV treats these two similar representations differently.

If the referenced array is single-dimensioned (as in Name_array[10]),
the database records chosen to be REPLACEd will all be REPLACEd from
the single record that the array represents. However, if the array is
two-dimensional, then the first record that matches the designated
scope will be replaced by the first row (or record) of the array, the
second by the second row and so on. The previous discussion
describing the salary increase along with the need to update each row
in the array makes sense now. It's not at all obvious that a REPLACE
FROM ARRAY operation would keep track of both the array index and
record numbers in the file.

One more tidbit about REPLACE FROM ARRAY: What if there are more
array elements than fields or vice versa? Essentially, the fields and
array elements are matched first to first, next to next until either
one or the other is depleted. At that point the REPLACE of the record
ends whether all the fields in the database are updated or not. You
have no opportunity to name which specific array element matches to
which field in the file structure, unless you use the REPLACE WITH
syntax, so you must construct your array so that it carefully matches
your database. The same concept is true when there are more records
in the file than rows in the array. After the array rows are
processed and none are left, the REPLACE operation stops. Now, with
the example of the array Name_array[1,10], this is a two-dimensional
array which usually contains several records and modifies several
records in a file. Since there is only one row in this array, even if
we specify ALL as a scope in the REPLACE statement, only a single
record will be updated since the second record that matches the scope
has no matching second row in the array to be used in the
modification. If we desire to modify numerous records in the file we
must make Name_array a single-dimensioned array. This underscores the
very different behavior of REPLACE FROM ARRAY in relation to single
and double dimensioned arrays.


5 Gone Fishing! - Joel Saltzman

Gone Fishing!
Joel Saltzman
I woke up in the wee hours of a crisp spring morning. What a glorious
day, I thought. A day perfectly suited for hours of uninterrupted
solitude with just myself, a boat and a quiet inlet. So, I packed up
my rod, reel and tackle box. Thinking that this would be a good time
to catch up on some light reading, I selected the perfect reading
companion for the day: the dBASE IV Template Language Reference by
Aspenwall and Carter. Sure, to most of you, this wouldn't seem like
light reading but remember, I'm a support technician: a being driven
by forces unknown to learn, understand and convey mass quantities of
technical information to those in need. Go figure.

Off I went, driving for miles through beautiful design surfaces. It
was there I made a catch in the vast waters of dBASE IV knowledge.
After casting for hours, that life-affirming tug on my line brought a
rush through my soul. As I reeled in with the adroitness of the most
adept angler, it broke through the surface, flapping in feverish
fury. A beauty it was, the likes of which I'd never seen. For my
eyes beheld my first glimpse of the mysterious and rarely seen
Report.COD.

Murky metaphors aside, the template language gives the dBASE IV
developer an opportunity to enhance the functionality of the screen
form, label, and report form generators. The "Popup - Pick List"
option in the screen form generator of dBASE IV version 1.1 is an
example of a useful improvement made possible by the template
language. In this article, we present two enhancements which are
appended to the report generator. First of all, conditions for
summary fields can be selected easily. In addition, the user can
determine certain properties about the record or records that contain
the minimum or maximum within a report.

.COD File Modifications

In order to put any .COD file changes into effect, one 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,
skip the next paragraph. You can also obtain the REPVERT.GEN file
from our bulletin board service in the Template Language SIG, but you
cannot make the .COD file changes yourself.

Owners of the dBASE IV Developers Edition can make the changes to the
REPORT.COD file, compile it, and use the new REPORT.GEN they've
created. It is a good idea to copy it to another filename with a .COD
extension so that the original REPORT.COD is still intact. You can
use MODIFY COMMAND, or any other text editor to change the .COD file.
It is important to 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

where the reference to Filename.COD in angle brackets is replaced with
the file you've just modified. DTC will attempt to compile the
template into a .GEN file which is usable by the dBASE IV report form
generator. If compilation was successful, copy the new .GEN file into
your dBASE system files directory.

Method of Implementation

Whenever you want to use the modified template instead of the standard
REPORT.GEN, type in the following line from DOS:

DTL_REPORT=

For example, if the .GEN file name is REPVERT.GEN

DTL_REPORT=REPVERT.GEN

The setting of DTL_REPORT can be put into a batch file so that dBASE
IV always uses your modified .GEN file. Keep in mind that these
enhancements apply only to summary fields in the report generator.

TOTAL FOR

To enter a condition that you would like the field to summarize on,
type

TOTAL FOR

in the Description area of the summary field. The words "TOTAL FOR"
must be capitalized. If they are not, they are treated as regular
descriptive text and the condition will not be applied. The parameter
should be replaced with any valid dBASE expression that
produces a logical result.

When trying to print the report, you may experience a compilation
error or an error after the report has started to print or display. A
compilation error will occur if you entered no condition at all and
typed only the words TOTAL FOR in the Description area. If you
misspelled a field or memory variable name, or typed in any other
expression that is incorrect in its syntax, you will get "Variable not
found" or another compilation error message.

If the condition has correct syntax, but produces an invalid result
when computed, you may get an error after the report has started to
display or print. For instance, if the data file has a character type
field called "CODE", and TOTAL FOR Code = 3 is in the description of
the summary field, compilation will succeed, but a "Data type
mismatch" will occur when you try to print the report since the number
3 is being represented without quotes as a numeric. To recover from
either of these errors, type in the correct conditional expression
after TOTAL FOR.

Samples of TOTAL FOR Usage

There can be many needs for a conditional summary. Keep in mind that
a condition can be applied to any of the seven summary operators
(Average, Count, Maximum, Minimum, Standard Deviation, Sum, and
Variance). These are explained on Page 10-27 of Using the Menu
System. Here are some examples.

Surveys (using Count)

Field Name Type Width Index

RESPONS_NO C 5 Y
QUES_1 L 1 N
QUES_2 N 1 N

Here is a database file that contains the responses to a survey. Each
record in the database file contains one person's set of responses.
It would be useful to find out how many people responded "Yes" to
question 1. To do this, create a summary type field in the report
Summary band. (This is done by positioning the cursor where you would
like the field to appear in the Report Summary band, pressing F5, and
choosing Count from the Summary column.) In the Description area, type
in

TOTAL FOR Ques 1

then save the field by pressing Ctrl-End. Alternatively, to find all
people that responded "No" to question 1, you would type

TOTAL FOR .NOT. Ques 1

in the Description area of the summary field.

Surveys (using Average)

Using the same database file as in the previous example, we would like
to determine the average answer to Ques_2, which requires a numerical
response. The valid responses to that question are 1, 2, 3, 4, and
5. If zero is entered, that would mean that the responder chose not
to answer that question. If the zero answers would be included, it
would skew the average downward. So a conditional average would be
ideal. In a summary field with the AVG operator, this is the line you
would type in the Description area:

TOTAL FOR Ques 2 > 0

If you were to figure out the conditional average in the report
without "TOTAL FOR", you would need to create a calculated field in
the Detail band, two summary fields in the Summary band, and a
calculated field in the Summary band. This is why I believe "TOTAL
FOR" is more straight forward.

Total of Contributions from Major Contributors (Using SUM)

Field Name Type Width Dec Index

NAME C 25 Y
AMOUNT N 10 2 N

This database file contains the names of contributors and the amount
they pledged for a fund drive. We need to know how much the people
who contributed at least $10,000 pledged in total. This time, create
a summary field with the Sum operator. In the Description area, type

TOTAL FOR Amount >= 10000

When you run the report, you will see the total pledges for all major
contributors. If you wanted to see the total pledges for all records
with the last name of "Smith" that contributed at least $1000, you
could use this condition in the Description area:

TOTAL FOR Name = "Smith" .AND. Amount >= 1000

Highest Number of Hits in each League (Using MAX)

Field Name Type Width Index

NAME C 25 Y
LEAGUE C 1 N
AT BATS N 3 N
HITS N 3 N

Our baseball statistics database file contains players from the major
leagues. The league field contains an "A" for the American League,
and an "N" for the National League. To find out how many hits the
batter with the most hits had, you could create a summary field with
the MAX operator, using a numeric field called Hits as the field to
summarize on. If you want to see who was hottest with the stick in
the American League, you would type

TOTAL FOR League = "A"

in the Description area.

To find the most hits for frequent National League batters (those with
at least 500 at bats)

TOTAL FOR League= "N" .AND. At_Bats >= 500

Try out some of your own possibilities. There are many good uses of
TOTAL FOR.
MINMAX
When finding out what the minimum or maximum of a data set, one
usually wants to know two things. The first is the number that is the
maximum or minimum. Also, one may want to see the "owner" of the
minimum or maximum. For instance, in the last example of TOTAL FOR
above, it would be helpful to see which player hit the most. This
player would be the owner of the maximum.

Here is the format of the function entered into the Description area
of an un-named summary field that will force the report generator to
print the owner of the minimum or maximum instead of the actual
figure.

MINMAX [TOTAL FOR ]

As with the TOTAL FOR function, the words MINMAX (and TOTAL FOR) must
be capitalized to be recognized as calling the MINMAX function as
opposed to an actual description. The shown
above will be replaced by an expression that produces a character
result. So if the field is a date or numeric type field, you would
need to use one of the dBASE IV data type conversion functions such as
DTOC() or STR() to create a character expression.

There is an additional complication with the owner calculationthere
may be ties. In the baseball example, there may be more than one
player that has the most hits. It is desirable to print as many of
the owners as space will allow, either horizontally or vertically.
The picture functions table cannot be used for this field, as dBASE IV
evaluates this field as a numeric, but the MINMAX function produces a
character result. Therefore, the template area of the field is
utilized as a place to put some basic picture functions.

To print multiple owners, with one owner on each line, put a
semi-colon (;) in the template expression area. To print multiple
owners on the same line, omit the semi-colon. If you want to limit
the number of characters printed horizontally, put a number at the
beginning of the template expression area. So to see only thirty
characters of owners, put 30 in the template. If you don't put any
number in the template area, 254 characters (maximum size of a
character memory variable) will be assumed. If the owners list
exceeds the chosen maximum length, ** Many ** will be displayed in
place of the owners list.

In addition, the TOTAL FOR enhancement described above may be used in
conjunction with MINMAX if you want to know the owner of a minimum or
maximum based on a condition in the database. If you use the TOTAL
FOR option, first put MINMAX and its expression, and then type in
TOTAL FOR with its condition. Use of TOTAL FOR is optional.

Compilation errors or unexpected results can occur if the syntax is
not followed exactly. A "Data type mismatch" will occur upon
compilation if the expression entered after MINMAX is not a character
expression. As MINMAX and TOTAL FOR are entered in the Description
area of a summary field in the report, there is no way for dBASE IV to
validate the entry at the time the description is entered.

Samples of MINMAX Usage

Owner of Most Hits (Using MAX)

For this example, utilize the database structure contained in the
final TOTAL FOR example. To find out which batter or batters had the
most hits, type

MINMAX Name

in the Description area of the Max type summary field. The field to
summarize on would be Hits. If you wanted to see who had the most
hits in the American League, the description would be

MINMAX Name TOTAL FOR League = "A"

Salesperson of the Month (Using MAX)

Field Name Type Width Dec Index

SALESMAN C 25 Y
SALES AMT N 10 2 N
MONTH N 2 0 N

Now we'll return to an every day business application. Let's say we
wanted to reward the salesperson with the largest gross sales amount
for a particular month. To find how much the top salesperson sold is
easy. Simply create a summary type field in the report Summary band
using the MAX operator. Select Sales Amt as the field to summarize on
and enter

TOTAL FOR Month = 3

in the Description area of that field so that the top salesperson for
March will appear. To find out which salesperson had the top sales
amount, create another summary field as above. However, this time,
enter

MINMAX Salesman TOTAL FOR Month = 3

in the Description area of this second summary field. Type 50 in the
template area to see salespersons who share top honors separated by
commas, but not to exceed 50 characters. When you save this summary
field, all you will see on the screen is 50 in its place. Don't
panicthe report layout screen always shows the template to represent
the field on the screen.

Date of Lowest Temperature (Using MIN)

Field Name Type Width Dec Index

DATE D 8 Y
LO_TEMP N 3 0 N

The local meteorologist wants to know what the coldest days of the
year 1990 were. She would create a summary field in the report
Summary band. As she wants the lowest temperature, she would use the
MIN operator. Lo_Temp would be the field to summarize on. If the
database only contained dates from 1990, she would put the following
in the description:

MINMAX DTOC(Date)

The DTOC function will show the date in MM/DD/YY format. To see the
month and day, you could use

MINMAX TRIM(CMONTH(Date)) + " " + STR(DAY(Date))

in the description of the field. If the database file contained dates
from a variety of years, you would use the following description to
see the coldest month and day or days within 1990:

MINMAX TRIM(CMONTH(Date)) + " " + STR(DAY(Date)) TOTAL FOR YEAR(Date)
= 1990

As you can see, you can get pretty fancy with your expressions. The
only limitations are the 80 characters that the description will hold
and your creativity. (To see all 80 characters of the description at
once, press Enter to open the Description area, and then press F9
(Zoom) to see the entire description at the bottom of the screen.)

Theory of REPVERT.COD File Changes

The REPORT.COD file contains all of the dBASE Template Language code
that is compiled by the Template Language compiler to produce the
REPORT.GEN file. The REPORT.GEN file is combined with the particular
report form file (.FRM file) to generate dBASE code that will print
the report (.FRG file). In order to change the functionality of the
report form generator for TOTAL FOR and MINMAX, the REPORT.COD file
must be modified.

When a dBASE IV report is run, summaries are computed on an as you go
basis. For instance, if you choose to use the SUM operator to add up
a field for a group or over the entire report, the sum will be
accumulated as dBASE IV processes each record. Commands like SUM and
CALCULATE are not utilized by the report generator to provide for
greater efficiency overall. The procedure that does the accumulation
is called UPD_VARS, short for "update variables".

For the TOTAL FOR change, an IF conditional statement needs to be
inserted before the update of the accumulating variable, as well as an
ENDIF afterwards. So if you typed

TOTAL FOR At_Bats > 500

in the Description area of a summary field named COUNT_REG with the
operation "Count", the lines

IF At_Bats > 500
COUNT_REG = COUNT_REG + 1
ENDIF

will be included in the .FRG file instead of

COUNT_REG = COUNT_REG + 1

This is the only change required for all summary types except for
minimums and maximums.
In the real world, when one figures out a minimum or maximum, the
first value is used as a standard of comparison that all other entries
have to be compared to. The report generator works the same way.
This is fine if you are examining the entire data set. However, if
only records that fulfill a condition are included for consideration,
it is possible that the first record may be among the records excluded
by the condition. The numeric memory variable that holds the minimum
or maximum needs to be filled with a non-numeric value when the first
record does not meet the condition.

As we know from the world of mathematics, 1/0 produces an undefined
result. Within dBASE IV, 1/0 does not result in a division by zero
error message. Instead, the value is a bunch of asterisks! So
including the line

DivZero = 1/0

in a program or at the dot prompt is legal and acceptable (although
hard to fathom) dBASE syntax. After further investigation, one will
discover that any numerical comparison with a variable defined with a
value of 1/0 will always be false. Therefore, each of the following
expressions will produce a false result:

5 > DivZero
5 < DivZero
5 = DivZero
1/0 = DivZero
1/0 = 1/0

A maximum is normally checked by statements like

IF Field > MaxVar
MaxVar = Field
ENDIF

A set of mathematically equivalent statements which are used in this
modified template is

IF .NOT. Field <= MaxVar
MaxVar = Field
ENDIF

MaxVar is always initialized to 1/0. When a record that meets the
condition is reached, Field <= MaxVar will be false. Therefore, the
.NOT. of that expression will be true, and MaxVar will be set to the
appropriate value.

The MINMAX calculation requires some fancy handling. The program
needs to maintain two memory variables. The first maintains the
current minimum or maximum, whereas the second holds the owner of the
minimum or maximum. So template code is inserted to create and
maintain both fields, although only the owner is printed.

There could be multiple owners of the minimum or maximumthere may be
a tie. The picture functions will not help us in this case, as the
owner field is a character type field, but the picture functions
relate to a numeric field since the report generator thinks that a
minimum or maximum is being printed.

Therefore, the .COD file is modified to look at the field template for
picture formatting information. For printing one line of owners (or
one owner) horizontally, the program checks for a number in the field
template to see how wide the field can be. Alternatively, the report
will print one owner per line if it sees a semicolon (;) anywhere in
the field template. The .COD file limits the width of the field as it
keeps track of the owner or owners. When the semicolon is present,
FUNCTION ";" is added to the ?? command that prints the owner.

Actual REPVERT.COD Changes

The modifications that you will need to make to the REPORT.COD
included in the developers edition of dBASE IV Version 1.1 are shown
on the next few pages. The lines to insert are in italics. Copy the
REPORT.COD to REPVERT.COD or any other name you would like to assign
it. The line number references are from the REPORT.COD file included
in the original release of version 1.1. To modify a later REPORT.GEN,
refer to the names of the procedures in the .COD file. The listing
will start from the bottom of the .COD file so that the line number
references remain valid thoughout the modification procedure. t



6 RepVert.COD Changes


This paragraph, define assign summary vars(), begins on
line 2470 in REPORT.COD. The actual changes replace lines
2490-2492 in REPORT.COD, prior to the second last endif
before the enddef.

define assign summary vars()
//
// This is synonymous with assign calculated vars.
//
x = 0
foreach FLD ELEMENT k
if FLD FIELDTYPE == Summ data then
if !FLD FIELDNAME then
++xsum
endif
if FLD OPERATION == MAX OP || FLD OPERATION == MIN OP
then
minmax tab =at("MINMAX", FLD DESCRIPT)
if minmax tab > 0 then
++xsum
endif
endif
endif
next k;
xsum = 0
if x then }

{ endif
return
enddef

This procedure, define sub init summary vars(), begins
on line 2363 in REPORT.COD. The actual define line
changes, as there is an additional parameter necessary. The
other changes replace line 2377

(beginning with { otherwise: // Min, Max or Sum)

through 2382 (the line prior to the endcase.)

define sub init summary vars(cursor, xmsum array)
//
// Called by init summary vars() and reinit group/page
variables()
// Set summary variables to starting values for next
grouping.
//
if cursor.FLD OPERATION > SUM OP then }
STORE 0 TO r sum{++xxsum}, r sum{++xxsum},\
r sum{++xxsum}, r sum{++xxsum}, {priv vars}
{ else
case cursor.FLD OPERATION of
AVERAGE OP: }
STORE 0 TO r sum{++xxsum}, r sum{++xxsum}, {priv vars}
{ COUNT OP: }
{priv vars}=0
{ SUM OP: }
{priv vars} = 0
{ otherwise: // Min or Max
}
{priv vars} = 1/0
{ minmax tab = at("MINMAX", cursor.FLD DESCRIPT)
if minmax tab > 0 then
++xmsum array
priv vars = "r msum"+STR(xmsum array);}
{priv vars} = SPACE(1)
{ endif
endcase
endif
return
enddef

The procedure init summary vars begins on line 2334 in
REPORT.COD. A parameter is added to the line that says sub
init summary vars(k). This is line 2352 in REPORT.COD.

define init summary vars()
//
// Initialize summary fields, similar to calculated fields,
it is
// necessary to assign "unrelated" starting values to these
fields.
//
...
sub init summary vars(k, xsum)
...
enddef

This is a new procedure for REPORT.COD. It should be
inserted before the define upd find string(string) line,
line 2055 in the REPORT.COD.

define minmax owner(cursor, xsum array)
//
// Update the variable to maintain the "owner" of the
MIN/MAX
//
var priv vars1, minmax exp, minmax maxlen, minmax wrap;
minmax tab = at("MINMAX", cursor. FLD DESCRIPT)
minmax wrap = at(";", cursor.FLD TEMPLATE)
if minmax tab > 0 then
priv vars1 = "r msum"+STR(xsum array + 1);
if total for tab > 0 then
minmax exp = substr(cursor. FLD DESCRIPT, minmax
tab + 6, total for tab - 7);
else
minmax exp=substr(cursor. FLD DESCRIPT, minmax tab +
6);
endif
if val(cursor. FLD TEMPLATE) > 0 && val(cursor. FLD
TEMPLATE) < 255 then
minmax maxlen = val(cursor. FLD TEMPLATE);
else
minmax maxlen = 254;
endif
if minmax maxlen < 10 then
minmax maxlen = 10;
endif
}
IF {priv vars} = {cursor.FLD SUMFIELD}
IF LEFT({priv vars1}, 10) <> "** Many **"
{ if minmax wrap > 0 then}
IF LEN({priv vars1}) + 1 + LEN(TRIM({minmax exp}))
> {minmax maxlen}
{ else}
IF LEN({priv vars1}) + 2 + LEN(TRIM({minmax exp}))
> {minmax maxlen}
{ endif}
{priv vars1} = "** Many **"
ELSE
{ if minmax wrap > 0 then}
{priv vars1} = {priv vars1} + ";" + TRIM({minmax
exp})
{ else}
{priv vars1} = {priv vars1} + ", " + TRIM({minmax
exp})
{ endif}
ENDIF
ENDIF
ELSE
{priv vars1} = TRIM({minmax exp})
ENDIF
{ endif
return
enddef

This procedure, update summary and calc vars(), occurs
on line 1929 of REPORT.COD. The last set of changes fall
between the "endcase;" on line 2015 and the "otherwise:"
line on line 2016. The second set of shading replaces the
lines starting from the line after "*? Max" (line 1982)
through the "ENDIF" before "{SUP OP: }" (line 1989.)
Finally, replace the lines "case FLD OPERATION of" and
"AVERAGE OP: }" with the first set of changes. These lines
are on line 1971 and 1972 of REPORT.COD.

define update summary and calc vars()
//
// This function is currently based on a calc all scenario.
// Which means all calculations happen in a left to right
and
// top to bottom based on position in the report design
surface.
//
...
//
// Summary fields
//
Summ data:
if !FLD FIELDNAME then
++xsum
priv vars = "r msum" + STR(xsum)
else
priv vars = lower(FLD FIELDNAME);
endif
if external define then
textspos(0);
if upd find string(priv vars + "=") then
loop
endif
endif
total for tab = at("TOTAL FOR", FLD DESCRIPT)
if total for tab > 0
then }
IF {substr(FLD DESCRIPT, total for tab + 9)}
{ lmarg(3)
endif
case FLD OPERATION of }
{ AVERAGE OP: }
*? Average
r sum{++xxsum} = r sum{xxsum} +1 {tabto(40)}&& count
r sum{++xxsum} = r sum{xxsum} + {FLD SUMFIELD}{tabto(40)}&&
sum
{priv vars} = r sum{xxsum} / r sum{xxsum - 1}{tabto(40)}&&
average
{ COUNT OP: }
*? Count
{priv vars} = {priv vars}+1
{ MAX OP: }
*? Max
IF .NOT. {FLD SUMFIELD} < {priv vars}
{ minmax owner(k,xsum);
if at("MINMAX", FLD DESCRIPT) > 0 then
++xsum
endif}
{priv vars} = {FLD SUMFIELD}
ENDIF
{ MIN OP: }
*? Min
IF .NOT. {FLD SUMFIELD} > {priv vars}
{ minmax owner(k, xsum);
if at("MINMAX", FLD DESCRIPT) > 0 then
++xsum
endif}
{priv vars} = {FLD SUMFIELD}
ENDIF
{ SUM OP: }
*? Sum
{priv vars} = {priv vars} + {FLD SUMFIELD}
{ otherwise: // STD or VAR
if FLD OPERATION == STD DEV OP then }
*? Std deviation
{ else }
*? Variance
{ endif }
r sum{++xxsum}=r sum{xxsum}+{FLD SUMFIELD}^2\
{tabto(40)}&& sum of squares
r sum{++xxsum}=r sum{xxsum}+{FLD SUMFIELD}\
{tabto(40)}&& sum
r sum{++xxsum}=r sum{xxsum}+1\
{tabto(40)}&& count
r sum{++xxsum}=r sum{xxsum-2}/r sum{xxsum-1}\
{tabto(40)}&& average
*? variance
{priv vars}=\
(r sum{xxsum-3}+r sum{xxsum-1}*(r sum{xxsum}^2);
-(2*r sum{xxsum}*r sum{xxsum-2}))/r sum{xxsum-1}
{ if FLD OPERATION == STD DEV OP then }
{priv vars}=SQRT({priv vars})\
{tabto(40)}&& std deviation

{ endif
endcase;
if total for tab > 0
then lmarg(0)}
ENDIF
{ endif
otherwise:
//
// footer fields
//
...
enddef

The procedure, reinit group variable(), begins its
definition on line 1864 of REPORT.COD. The changed lines
replace line 1894, which reads sub init summary vars(k).

define reinit group variables()
//
// Group break and summary fields are setup for next
grouping.
//
i=0
x=0
foreach FLD ELEMENT k
//
// Summary field and reset on group?
//
...
if !FLD FIELDNAME then
priv vars="r msum"+STR(x)
else
priv vars=lower(FLD FIELDNAME)
endif
sub init summary vars(k,x)
if at("MINMAX", FLD DESCRIPT) > 0 then
++x
endif
else
if FLD OPERATION > SUM OP then
xxsum = xxsum + 4
else
if FLD OPERATION == AVERAGE OP then
xxsum = xxsum + 2
endif
endif
endif
...

The procedure, reinit page variable(), begins its
definition on line 1828 of REPORT.COD. The changed lines
replace line 1848, which reads sub init summary vars(k).

define reinit page variables()
//
// Summary fields set to Reset every page are assigned
starting values.
//
...
if FLD RESET == Each Page then
if !FLD FIELDNAME then
priv vars="r msum"+STR(i)
else
priv vars=lower(FLD FIELDNAME)
endif
sub init summary vars(k, i)
if at("MINMAX", FLD DESCRIPT) > 0 then
++i
endif
else
if FLD OPERATION > SUM OP then
xxsum = xxsum + 4
else
if FLD OPERATION == AVERAGE OP then
xxsum = xxsum + 2
endif
endif
endif
endif
next k;
xxsum=0
return
enddef

The procedure output band procs() begins its definition
on line 489 of REPORT.COD. The actual change comes over
350 lines later, a few lines after the template says "//
test for invalid picture templates". The changes replace
lines 867 through 869, which read "else}", "PICTURE "{FLD
TEMPLATE}" \", and "{ endif".

...
//
// test for invalid picture templates
//
if temp && (FLD FIELDTYPE != Pred data || FLD PREDEFINE
!= 1) &&
FLD VALUE TYPE != DATE TYPE then
if i > 70 then}
PICTURE ;
{ separate(temp);
else
minmax tab = at("MINMAX", FLD DESCRIPT)
if minmax tab == 0 then}
PICTURE "{FLD TEMPLATE}" \
{ else
if at(";", FLD TEMPLATE) > 0 then}
FUNCTION ";" \
{ endif
endif
endif
endif
endif
endcase
...

Within the same procedure, output band procs, there is a
paragraph called "Summ data:" on line 796. After the line
"if !FLD FIELDNAME then"
insert the new code.

...
Summ data:
if !FLD FIELDNAME then
if at("MINMAX", FLD DESCRIPT)>0 then
++xsum;
endif
priv vars="r msum"+STR(xsum);
else
priv vars=lower(FLD FIELDNAME);
endif
endcase
endif
if x then
priv vars2=priv vars2+")";
endif
if !suppress line &&
(bandtype == Page Header || bandtype == Page Footer) then
plainopt(ecursor);
endif
//
// For output of suppress repeated value memo fields
//
...

At the end of the global variable definitions at the top
of REPORT.COD before line 114, add the following two
variables.

total for tab, // location of TOTAL FOR in FLD DESCRIPT
minmax tab // location of MINMAX in FLD DESCRIPT

On the line preceeding the total for tab definition,
place a comma (,) after the variable name.




7 Etc.

Report Basics

The report form generator in dBASE IV is designed to take whatever
data it receives and print it. If you need to print sorted files,
selected records, or multiple databases, make sure that the query or
index tag needed is activated prior to developing and printing the
report.

If you are grouping in the report, make sure that the database or
query is indexed or sorted in at least the same order as the report
groupings. For example, if you would like to see the street names in
alphabetical order while grouping by state and city, the index tag
should be State+City+Street, with State as the key of the first group
and City as the key of the second group.

If you want to print information particular to a record, this
information should be printed in the Detail band. Information
pertaining to a group of records should be printed in the Group Intro
or Group Summary bands. For information that is based on all of the
data in a report, use the Report Intro or Report Summary band.

Information in the Group Intro bands should be information directly
derived from the data in the database referring to the group. Summary
totals will not compute correctly if they are placed in the Group
Intro band, as dBASE IV computes the totals as it prints or processes
the records in the detail band. Summaries of group information should
always go in the Group Summary band.

When displaying summaries of numbers, make sure that the template is
large enough to accomodate the sum of all records involved. Adding up
100 records of a numeric field that is three characters wide could
produce a five digit sum.

Make sure that you have the desired printer model selected in the
destination submenu of the Print menu while in report design.

Print Screen

Some TSRs, particularly print spooling programs, may report conflicts
during or after running dBASE IV. This could manifest itself on
computers with EGA or VGA video systems. This is because these
systems allow more than 25 lines of text to be visible. As a result,
the BIOS on EGA and VGA video systems provide a means of changing the
default print screen program to one that recognizes the additional
lines on the screen. Interrupt vector 5 is the interrupt dBASE IV
changes to allow the print screen to work when there are more than 25
lines on the screen.

This can be worked around by changing the vector to point to the
alternate print screen routine BEFORE loading your TSR. Listed below
is a dBASE IV program that will create a .COM file, which when run,
will set interrupt vector five to use the alternate print screen
routine. Also listed below is a script for use in the DOS DEBUG
program. You can use either one to create PSFIX.COM. REMEMBER! Run
PSFIX.COM before running your TSR program!

dBASE IV Program

SET PRINTER TO FILE PSfix.COM
??? "{180}{18}{179}{16}{205}{16}{128}{251}{16}{116}{4}{179}{32}{205}{16}{205}{32}"
SET PRINTER TO

DEBUG Script

The DEBUG script below will produce the same results as the dBASE IV
program listed above. At the DOS prompt, enter the DEBUG utility and
enter the assembler commands shown at right. The information that you
enter is italics.

-A
XXXX:0100 MOV AH,12
XXXX:0102 MOV BL,10
XXXX:0104 INT 10
XXXX:0106 CMP BL,10
XXXX:0109 JZ 010F
XXXX:010B MOV BL,20
XXXX:010D INT 10
XXXX:010F INT 20
XXXX:0111
-RCX
CX 0000
:11
-Npsfix.com
-Q

Memory Variables to DOS Files

There are cases when it is preferable to write the contents ONLY of
all of my memory variables to a DOS file. For instance, transfering
these variables in a Lotus spreadsheet is an instance where this would
be useful particularly if there are a great number of variables.

The process involves listing the memory variable information to a
file, then reading that file back into a database and parsing through
the "records" which are actually the lines from the LIST MEMORY
command. With the use of SET ALTERNATE, you can send only the content
information of each variable, regardless of how many or what they are
named, out to a text file. MemWrite is a program that will accomplish
the task.

* by Rick Knight
SET TALK OFF
SET SAFETY OFF
LIST MEMO TO FILE MEMOS
USE CATALOG.CAT
COPY STRUCTURE EXTENDED TO Temp
USE TEMP
DELETE ALL
PACK
APPEND BLANK
REPLACE Field name WITH "LINE", REPLACE Field type WITH "C",; Field
len WITH 79
CREATE Mvars FROM Temp
APPEND FROM Memos.TXT SDF
LOCATE FOR "User Memory Variables" $ Line
SKIP 2
SET ALTERNATE TO MVARS.TXT
SET ALTERNATE ON
DO WHILE LINE <> " " && while line is not blank
IF SUBSTR(Line, 19, 1) $ "NF"
? SUBSTR(Line, 22, AT('(', SUBSTR(Line, 22)) -1)
ELSE
? SUBSTR(Line, 22)
ENDIF
SKIP
ENDDO
SET ALTERNATE OFF
CLOSE ALTERNATE
* EOP




8 Error Trapping by Number - Dan Madoni

Error Trapping by Number
Dan Madoni

When I create an application in dBASE IV, I try to stay as far as
possible away from a "dBASE-looking" program and give it more of a
custom appearance. One of many areas in which I apply this philosophy
is error trapping. My error trapping routines generally check the
number returned by the ERROR() function which I then use to display a
re-worded message that better fits my application. Sometimes, the
result is to inform, possibly directing a more experienced user to
troubleshoot their own solution. For example, I would rather have a
user of a checkbook application see a message that reads

The Checkbook database has been damaged

versus

Not a dBASE IV database

In other cases where I don't trap for a specific error or something
unexpected occurs, I display a message that informs the user that
something appears to have gone wrong internally. In these cases, the
program may not be able to provide helpful details to the user but may
be useful to me when they report the trouble. For example, instead of
allowing the user to see the message

Cannot re-define popup in use

my ON ERROR routine, using ON ERROR DO ErrMsg, would display...

Checkbook program Internal Error: 174

Here, the number 174 represents the number returned by the ERROR()
function which is about re-defining popups in use. I found that a lot
of programmers use this method for trapping errors for a few reasons.
One reason is for saving face. It's not a direct reflection on the
program (and therefore the programmer) when something goes wrong with
the program. An "internal" error doesn't cast blame on the program or
the computer. Secondly, it gives you more control over the program
because your users are compelled to report errors that are
unfamiliar. Consequently, you can document unexpected bugs in your
program for later revision.

One drawback to this method of error reporting by number is that the
dBASE IV manual doesn't include a list of errors by order of number.
When you are supporting the user, you have to frantically search your
manual, error for error, to find the problem. Well, although it was a
tedious task, I have actually gone to the trouble of compiling the
list of version 1.0 and 1.1 errors into a database so that we can all
have it when we need it to support a customer or to debug programs.
The database (called Errors.DBF) is available on our BBS and it is
printed out on pages 15-17. I also threw in a generic error trapping
routine that uses this method for those of you who would like to try
this method.

NOTE: Not all dBASE error messages can be trapped. Any message
without an error number cannot be trapped. Most error messages that
are a result of a DOS error (Drive not ready, Too many files open, and
so on) are also not trappable.

PROCEDURE ErrMsg

badmsg = MESSAGE()

IF LEN(WINDOW()) <> 0
*-- Check to see if window is open.
nowwind = WINDOW()
DEACTIVATE WINDOW &nowwind
ENDIF

SAVE SCREEN TO ERR
@ 24,0 SAY SPACE(80) COLOR W+/R
@ 0,0 SAY "Checkbook program Internal" +; "Error: " +
LTRIM(STR(ERROR())) COLOR W+/R

*-- The IF...ENDIF below allows you to press
* CTRL-Y to suspend your program and
* display the error message for debugging

IF LASTKEY() = 25
SET CONSOLE ON
SET CURSOR ON
SET COLOR TO W+/B,GR+/BG
SET TALK ON
? badmsg
?
? "Checkbook program has been SUSPENDed."
SUSPEND
ENDIF
RETURN


9 Running dBASE IV 1.1 in Windows 3.0 - Dave McLaughlin

Running dBASE IV 1.1
in Windows 3.0
Dave McCloughlin

This short guide provides information on how to run dBASE IV 1.1 under
Windows 3.0. It contains the basic steps needed to setup and run
dBASE IV in the Windows environment, general information about
Windows, and tips for running dBASE IV efficiently in Windows.

Here are the three basic steps recommended for running dBASE IV in
Windows:

l. Check your memory. As long as you meet the minimum memory
requirements for Windows in each of its basic configurations, you will
find that you have enough memory to start dBASE IV. If you plan to
run multiple applications or multiple sessions of dBASE IV
simultaneously you will need more RAM. Windows RAM requirements:

Conventional Extended
Mode Memory Memory Processors

Real 64OK 80286 or greater
Standard 640K +256K 80286 or greater
386 Enhanced 640K +1024K 80386 or greater

Although minimum memory requirements are enough to start dBASE IV in
Windows, we recommend that you have at least 2 megabytes of RAM to run
it efficiently. This will allow you to take advantage of special
features that substantially improve performance and flexibility for
running large dBASE IV applications.

See Chapter 13 in Windows User's Guide for more information on
Windows memory requirements.

2. Disable dBCACHE. Windows uses its own disk cache program (called
SMARTDrive) which cannot be used simultaneously with dBCACHE, the
dBASE IV disk cache program. You must make sure dBCACHE has been
disabled to run dBASE IV in Windows. Ashton-Tate supplies a batch
file (CACHEDB.BAT) for turning dBCACHE on and off. A copy is
transferred to your dBASE IV subdirectory automatically when you first
install dBASE IV.

If you installed dBCACHE when you first set up dBASE IV you can turn
it off by going to your dBASE IV subdirectory in DOS and typing

CACHEDB OFF

You only need to type this once to uninstall the cache. If you
would like to re-install dBCACHE for use without Windows, again go to
your dBASE IV subdirectory and enter CACHCEDB with the parameter LIM
if you use expanded memory or EXT if you use extended memory.

3. Install dBASE IV into the Program Manager. To install dBASE IV
into the Windows Program Manager, start up Windows and double click on
the SETUP icon in the Main Group. Choose the 'Set Up Applications'
option from the 'Options' menu. Follow the following instructions for
the dialog boxes to have Windows search your computer's hard drive and
install dBASE IV into the Program Manager. Windows will create a
dBASE IV PIF (Program Information File) and install it into a special
non-Windows program group. To start dBASE IV just double click on the
newly installed dBASE IV icon.

See chapters 3 and 12 in your Windows User's Guide for more
information on the different options for starting dBASE IV from the
Program Manager.

Creating a Special PIF File for dBASE IV

You specify how to start non-Windows applications with a PIF (Program
Information File). A PIF is a special file that contains information
specific to your program running in Windows.

If you used Windows Setup and installed dBASE IV using the Setup
Applications option, Windows will have already generated a dBASE IV
PIF called DBASE.PIF. You can edit this PIF or create a new one using
the Windows PIF editor which can be found in the Accessories Group.
At the top of the page you will see the PIF editor in both the 386
enhanced mode and the standard mode.

Explanation of Settings

Program Filename Type the path name of the file that starts dBASE
IV. For example: C:\dBIV\dBASE.EXE.

Window Title Enter the name you want to appear in the dBASE IV window
title bar. This only applies when running dBASE IV in a window.

Optional Parameters Type /T. This will bypass the dBASE IV sign-on
screen and save you the trouble of pressing Return each time you start
dBASE IV.

Startup Directory

You can specify the directory you want current when dBASE IV starts by
typing it in the Startup Directory text box. For example, if you have
a special data directory you would like active when dBASE IV starts,
type it in this text box.

Video Mode (real and standard modes only) This specifies how dBASE IV
uses the display. dBASE IV is a text mode application, so select the
Text option.

Memory Requirements Because Windows will automatically assign as much
memory as is available for dBASE IV, you don't need to change the
default Memory Requirements options.

XMS Memory (real and standard modes only) This option tells Windows
how much memory to assign for extended memory. Leave these options at
0.

Directly Modifies (real and standard modes only) These options are
used to tell Windows if your application takes control of devices, to
the exclusion of other applications. Leave these options off.

No Screen Exchange (real and standard modes only) Leave this option
off to allow cutting of the dBASE IV screen to the clipboard. See
details on using the clipboard later in this guide.

Prevent Program Switch (real and standard modes only) You can conserve
memory by selecting the Prevent Program Switch option. However, if
you plan to use Windows as a task switcher leave this option off.

Display Usage (enhanced mode only) Display Usage is really up to
you. The Full Screen option will give you optimum performance while
Window will let you use the clipboard and see other applications
running simultaneously.

Execution (enhanced mode only) Select the Background option to allow
dBASE IV to continue executing while running other applications. If
you enable Exclusive, other applications will halt while using dBASE
IV.

Close Window on Exit Turn on Close Window on Exit. This closes the
dBASE IV window automatically whenever you exit dBASE IV. If this is
off you will need to confirm closing the window each time you exit.

Reserve Shortcut Keys (real and standard modes only) Leave this option
off. You would set these keys if they were needed by dBASE IV.
Selecting them reserves them specifically for your application.

Advanced Options (enhanced mode only) There are no special
requirements needed under the Advanced Options to run dBASE IV. If
you are interested in further optimizing dBASE IV using PIF settings,
refer to your Windows User's Guide.

"General Information", Chapter 12 of the Windows User's Guide is
dedicated to more information about running applications in Windows.
It contains a special section on non-Windows applications (DOS
applications). It is a good general reference to help you get started
and is highly recommended.

A Note about Modes

dBASE IV can be run in each of Windows three modes: real, standard and
enhanced. In real and standard modes dBASE IV has to be run full
screen. You can still switch between multiple programs; however,
while you are using dBASE IV in these modes, Windows suspends other
applications.

In enhanced mode, only available on 80386/486 computers, dBASE IV can
be run in either a window or full screen. Because Windows handles all
screen functions when running programs in a window, dBASE IV will run
much slower.

Using the Windows Clipboard

You can utilize the Windows clipboard to capture portions of a dBASE
IV screen to paste into other applications. In a window you can
capture a portion of the screen by highlighting it with your mouse.
To cut the highlighted section to the clipboard press the right mouse
button or select the Copy Enter option from the Edit submenu, which is
located on the dBASE IV window menu. When you are in full-screen
mode, you can capture the whole screen by pressing Alt-PrtScrn.

Only Windows applications designed to work with the clipboard can take
advantage of all of its features. In dBASE IV you can use the
clipboard to paste textual data into a memo field or the dBASE IV
editor.

Printing in Windows

Like all non-Windows applications, dBASE IV does not take advantage of
the Windows special print drivers or Print Manager. Windows does
attempt to prevent port contentions between multiple applications
running simultaneously. But care must be taken when printing from
dBASE IV in Windows. If you begin a print job in dBASE IV then switch
to another application and attempt to print using the same printer you
may possibly interrupt or ruin your dBASE IV report. Also, because
tasks running in the background (or running from an inactive or closed
window in enhanced mode) usually are given a lower priority,
attempting to print in the background with dBASE IV will slow the
print job down. If you plan to print from dBASE IV running in Windows
it is best to keep the dBASE IV window active until the job is
completed.

Tips and Precautions

Windows provides the benefits of a common graphical interface,
multitasking, and improved data sharing. But, it can also present
some new hazards for unsuspecting users. Here are a few tips and
precautions you should keep in mind when running dBASE IV in Windows:

Running multiple sessions of dBASE IV.

While you can run multiple sessions of dBASE IV concurrently in
Windows, you must be careful not to edit the same file from different
sessions. Here are a couple ways to avoid this. The first method is
to run the DOS SHARE utility. In addition to preventing two sessions
of dBASE IV from editing the same file, it will also prevent any other
program from editing the file. Secondly, you can use the multi-user
version of dBASE IV and enable file-locking. This is probably the
best method for ensuring your data won't accidentally get updated from
two different sources simultaneously.

Running CHKDSR or disk optimizers

Running CHKDSK with the /F parameter or disk optimizers in Windows
may corrupt your hard disk's FAT table. Never run these types of
programs when running dBASE IV in Windows. If you have created an
application in dBASE IV that rolls out to DOS and runs these programs,
do not attempt to run your application in Windows.

Turning off your computer

You probably have already trained yourself to exit dBASE IV before you
turn off your computer. In Windows it may not be obvious that dBASE
IV is running, especially if it is minimized. Apply the same rule
that you use with dBASE IV to Windows: Exit Windows before you turn
your computer off.

Allow Close When Active

This option, available in 386 enhanced mode allows Windows to
terminate dBASE IV when you exit Windows. If you have files open in
your dBASE IV session when you exit Windows the data can become
corrupted. As a rule, do not enable this option when creating a PIF
for dBASE IV. By default this option is off.

Printing in Windows

Although Windows does a good job of preventing conflicts between
multiple applications using the same printer, it is a good idea to let
dBASE IV complete its printing before attempting to print from another
session.

Modifying a dBASE IV database with a different application

Remember if you haven't taken precautions to lock your dBASE IV files,
editing them within another application may cause corruption if the
file is open in a concurrent session of dBASE IV.

Leaving files open in dBASE IV when you switch to other sessions

Task switching has been greatly simplified by Windows. So simple that
it's easy to forget that you have dBASE IV running when you switch to
other applications. For this reason, it is a good idea to close files
before switching to other programs. This way if another program locks
up your computer you don't have to worry about losing dBASE IV data.

Assigning Shortcut Keys in Windows

You can assign shortcut keys to perform Windows functions from within
your application. If a key is assigned to one non-Windows
application, another application's use of that key is disabled.
Pull-down menus which use the same keys are disabled as well. Be
aware of how you set up your applications to prevent key conflicts
within dBASE IV.

Running dBASE IV in the Background

If you plan to run dBASE IV in the background in 386 enhanced mode you
need to enable the Execution:Background option in your PIF. (This
option is not available in real or standard mode.) For example, you
could sort a database in the background in one session while creating
a form in another. Depending on the task you want to run you can
optimize its performance by setting its priority. To change
background and foreground priorities use the Advanced Options in your
PIF editor. To run faster in the background increase the Background
parameter.