Dec 212017
 
Aston Tate dBase IV Tech Notes for April 91. Useful information.
File TN9104.ZIP from The Programmer’s Corner in
Category Dbase Source Code
Aston Tate dBase IV Tech Notes for April 91. Useful information.
File Name File Size Zip Size Zip Type
TN0491.TXT 80722 28327 deflated

Download File TN9104.ZIP Here

Contents of the TN0491.TXT file


1 Read Me First

These articles are reprinted from the April 91 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 Poetry in Slow Motion

Poetry in Slow Motion
Richard Biegel

Slowing down macros for demonstration and educational purposes.
There are two basic tempos at which we want computers to operate during the
execution of a dBASE program; utterly immobile or working at warp speed.
We want immobility when the program is awaiting data input, as with BROWSE
or EDIT. At all other times we want the system to run like greased
lightning (if not faster). There appears to be no middle ground in our
aspirations. Or is there? Consider the following scenarios:

Scenario One: Rebecca Nightowl, creator of an inventory tracking system,
wants the graveyard shift operator to be able to monitor the contents of
records as they are being updated overnight. She wants each record to be
updated automatically but visibly (and preferably) in the same screen forms
that the data entry operators use for data input during the day. Since her
system has very fast computers, some way must be found to slow the process
down so that the changes in each record can be seen by the naked eye.

Scenario Two: Monica Pedagogue, a highschool teacher, wants her students
to be able to press function keys and see various difficult words written
out slowly. She wants each syllable to be accompanied
by brief explanations. The catch is that she wants all of this done in
BROWSE so that the words and the pupil's responses to them may be recorded
spontaneously in a database.

Scenario Three: Bill Showhow, an office manager, wants his new employees
to be able to learn the operation of the department's data management
system with as little supervision as possible. He wants the ability to run
the system in a demonstration mode that shows how to use BROWSE and EDIT to
input data in exactly the right format for the department's
specifications. Of course, this demonstration sequence must include
on-screen notes and explanations, and must run slowly enough to be read
clearly and at a comfortable pace.

These scenarios require tasks that seem foiled, or made very difficult, by
three problems:

* Full-screen editing tools like BROWSE and EDIT were not designed to be
automatic. They were designed to be interactive, with a human operator
typing away at the keys. Without the operator to massage the data and
terminate the process, the computer just sits there.

* Full-screen editing tools were not designed to have text written over
them, other than the messages included in their design. There is no
immediately obvious way to superimpose text messages at will.

* Even the slowest computers operate at very high speeds by human
standards, and this causes a substantial perceptual problem. Macros are
designed to run very rapidly but a demonstration done at ninety miles an
hour is not particularly useful.

The first problem is solved easily enough with programmed keyboard macros,
which can walk through full-screen editing commands in dBASE IV very
easily. With a judicious use of the PLAY MACRO command, just about
anything in dBASE IV can be made automatic. And as we shall see, the
second problem is solved by reprogramming function keys to call procedures
that deliver messages to the screen, even while in BROWSE and EDIT.

But the last problem remains. If the computer races through the execution
of the macro, the user will not be able to see what is going on. So how
does one go about slowing down a keyboard macro during its execution?

Let's review some common techniques that dBASE IV programmers use to
deliberately slow their programs down, to see which serves our purpose
best.

1 The INKEY() Function. This is by far the most elegant way to effect a
pause in a program, and is recommended in the vast majority of programming
situations. For instance, when the programmer desires a 5 second pause in
the execution of a program, inserting the command

dummy = INKEY(5)

will cause a time-out of 5 seconds.

However, for our purposes this solution has a catch. Keyboard macros
ignore the INKEY() function during their execution, thus eliminating
INKEY() as a solution to our problem.

2 The Furious DO WHILE Loop. This trick involves the creation of a loop in
the program code that counts up uselessly to some well-chosen number; the
higher the number, the longer the delay.

This method is clumsy and is almost never recommended, if only because of
the availability of the INKEY() function. And it won't work for our
purposes anyway, since a keyboard macro is not stopped by the execution of
a DO loop.

3 Deliberate Inefficiency. For obvious reasons, this is not recommended in
most programming situations and should be avoided whenever possible. But
for the purpose of slowing the execution of keyboard macros, it is our best
alternative. For this purpose, a special kind of macro is needed.

The Time-Out Macro

A keyboard macro is not designed to stop in the middle of its execution.
It is merely a series of simulated keystrokes that are executed in rapid
succession, and cannot be given a time out in any conventional sense of the
word. A macro interrupt wouldn't work here.

However, a macro can call another macro, and therein lies our solution. A
series of secondary macros of varying lengths may be used to simulate
pauses in the execution of a primary macro.

There are 12 lines of macro code, each of which shows 6 keystrokes. Each
keystroke specification is enclosed in a set of brackets, as with
rightarrow, and each instructs dBASE IV to press a key. This macro merely
instructs the cursor to go right, then left, then right, then left again
and so on for a total of 72 keystrokes in all. Note that at the end of the
execution of this macro, the cursor will end up where it began; the net
effect is zero.

For the sake of identification, let's call this a time-out macro. This
term is entirely justified; the sole purpose of such a macro is to use up
processor time, without having any other effect. A time-out macro is for
use by other macros whenever they require a pause in their execution.

The more "back-and-forth" keystrokes included in a time-out macro, the
longer it will take for it to run its course, and this is the means by
which its delay time is adjusted. As might be expected, it requires a
certain amount of guesswork and experimentation at first, but once properly
calibrated, a time-out macro is very consistent and dependable.

A potentially annoying aspect of time-out macros is the fact that different
computers run at different speeds. Some computers require more keystrokes
than others to obtain a delay of the same duration. But this should not be
too much of a problem; macros may be edited via the Tools Menu in the
Control Center, just as program files are with the MODIFY COMMAND editor.
Blocks of code may be moved, copied or deleted using the F6, F7 and F8
keys; therefore, adapting a time-out macro to a new computer after
transferring it from another one is only a minor inconvenience.

A Sample Demo Program

Three situations in which time-out macros might be useful are listed at the
beginning of this article. Let's examine a sample application that fits
the third scenario.

Recall that Bill Showhow wanted to create a demonstration mode for his data
entry application. He wanted to have sample data entered automatically,
accompanied by comments and explanations. Above all, it had to happen
slowly enough for a novice to read and understand.

See the end of this article which shows a very simple application named
Demo.PRG, modified to provide such a demonstration mode for one of its menu
options. It runs in the demonstration mode when it is called from the Dot
Prompt with the command

DO DEMO WITH "i"

The parameter "i" tells the program to perform in the demonstration mode;
any other letter will run the program in normal mode. Note that for the
sake of brevity, only one menu choice is given the demonstration option.
For the same reason, the application uses only one database, Member.DBF,
the structure for which is shown below

Field Field Name Type Width Dec Index
1 LASTNAME Character 25 N
2 FIRSTNAME Character 25 N
3 BIRTHDATE Date 8 N
4 ADDRESS Character 30 N
5 CITY Character 30 N
6 STATE Character 2 N
7 ZIP Character 10 N

Notice that PROCEDURE is SET to a file named KeyStrok.PRG (Listing 1).
This file contains a procedure name ADD_1, which is called about three
fourths of the way down.

ADD_1 is comprised of ON KEY LABEL commands that reprogram a series of
function keys and key combinations. Each reprogrammed key now causes a
sub-procedure to be called that prints a message on the screen. These
messages may be printed on the screen even if dBASE IV is executing a full
screen editing command such as BROWSE or EDIT. All it takes is the right
keypressor a macro file to press it automatically!

Let's look at an example. The first command in ADD_1 is

ON KEY LABEL F5 DO P_5

When the F5 function key is pressed manually or by a macro, PROCEDURE P_F5
is executed, causing a series of flashing arrows (produced by CHR(24)) to
appear near the top of the screen. These arrows will point upward to the
last record of a database file in a BROWSE input screen. Beneath the
arrows a message will appear, surrounded by a single-line box .

This message will be followed by a short beep, produced via the ? CHR(7)
command at the end of the procedure.

All other ON KEY LABEL commands in ADD_1 and their associated procedures
work on the same principle. These reprogrammed keypresses provide all of
the messages necessary to narrate the demonstration.

Note that there are three ON KEY LABEL commands in the main program
Demo.PRG. These are more specialized settings, and are used for purposes
other than presenting messages. The statement

ON KEY LABEL F2 SAVE SCREEN TO sKeep

reprograms Function Key F2 to take a snapshot of the screen and store it in
memory. This keypress is used when the present appearance of the screen
must be saved, to be restored at some future time.

ON KEY LABEL F3 RESTORE SCREEN FROM sKeep

reprograms Function Key F3 to restore the snapshot to the screen. This has
the effect of "erasing" what is on the screen now and returning it to a
former appearance.

ON KEY LABEL F4 ? CHR(7)

The statement above reprograms Function Key F4 to emit a beep. This
keypress allows the sound to be made even when a procedure like P_F5 is not
being called.

Now let's turn our attention to the macro that will "press" these function
keys. (All macros are stored in the file referenced as KeySave in the
Demo.PRG program.) All of the keypresses defined in Demo.PRG and PROCEDURE
ADD_1 are invoked from the primary macro. This particular macro is called
by the "A" letter key, and is invoked with the PLAY MACRO A command (See
Demo.PRG). Like the time-out macro we have already introduced, it was
written with the macro editor in the Tools menu in the Control Center.

For the sake of legibility, the code for this macro has been divided up
into discrete units separated by blank lines. Macro code may be input like
this, complete with blank lines; however, dBASE rearranges such a macro
when it is saved.

Examine the first section of macro code in the series shown above. This
code represents the first keystrokes to be executed when BROWSE is
activated. When a BROWSE screen is entered in dBASEIV, the leftmost field
is highlighted and the cursor placed in the leftmost position in this
field. The first keypress of our macro, rightarrow, places the cursor one
space to the right of this position.

This keypress is extremely important. It keeps the events on the screen
from being out of phase; it requires the BROWSE screen to be presented
before any further key presses may be invoked by the macro. Without this
keystroke the first messages will appear on the screen at inappropriate
times. Therefore, it is recommended that all demonstration macros begin
with this keystroke.

The next keypress is F2, which takes a snapshot of the screen. Then the F5
keypress causes the prompt with the flashing arrows to appear. This is
followed by the Alt-F10 Z keypress, which invokes one of four time-out
macros. These four macros are called by Alt-F10 followed by W, X, Y or Z.
Let's discuss these macros for a moment.

As mentioned above, different computers need different numbers of
keystrokes to kill the same amount of time. The macro will have 12 lines
of back-and-forth keystrokes with 6 keystrokes per line, for a total of
72. In reality, almost none of the computers on the market today are slow
enough for this relatively short macro to make a significant delay;
however, we may use it for comparison with others.

Look at the comparison charts shown at the bottom of Page 5. Each shows 4
suggested time-out macro lengths for computers of various speeds. Slower
computers such as the XT series are likely to need macro lengths like the
ones listed in the first table. The AT and its various clones should need
lengths at or above the ones in the middle table. The fastest computers
will need settings at or above the ones in table at far right. Of course,
since each make and model of computer has its own peculiar personality, it
will almost certainly be necessary to do a bit of adjusting to get the
delay time exactly right.

Now let's return to our examination of the primary macro. Here is a
synopsis of what each section of the macro does. Examine all of these
sections, using PROCEDURE KeyStrok, starting on page 8, as a guide to the
various messages presented. Remember that all keypresses consisting of
Alt-F10 followed by W, X, Y or Z are time-outs. F2 takes snapshots of the
screen, F3 restores them, and F4 sounds a beep.

First Section

Presents a series of 3 messages (keypresses F5, F6 and F7) explaining how
to APPEND a new record. Goes ahead and uses the downarrowY key combination
to initiate the APPEND.

Second Section

Slowly spells out the last name Bolton in the leftmost field, sounding the
beep (keypress F4) with each letter and presenting a message (keypress F8)
asking the user to capitalize the first letter.

Third Section

Tabs over 1 field to the right, then slowly spells out the first name
Henry, sounding the beep with each letter and presenting a message
(keypress F9) asking the user to capitalize the first letter.

Fourth Section

Presents a message (keypress F10) showing how to use the Tab key to move
the highlight bar to the right. Presses the Tab key until the bar reaches
the rightmost field.

Fifth Section

Presents a message (keypress Ctrl-F1) showing how to use the Shift-Tab key
to move the highlight bar to the left. Presses the Shift-Tab key until the
bar reaches the leftmost field.

Sixth Section

Tabs over one field to the right and presents a message (keypress Ctrl-F2)
explaining how the Shift-F7 keypress resizes columns in BROWSE. Performs
the Shift-F7 keypress and uses the leftarrow and rightarrow keystrokes to
resize the column. Presents a message (keypress Ctrl-F3) explaining how to
use the Esc key to exit resizing mode.

Seventh Section

Presents a message (keypress Ctrl-F4) explaining how to exit BROWSE. Says
"GOODBYE" (keypress Ctrl-F5) and returns to the main menu.

The Case for Inefficiency

Although dBASE IV macros were never really intended to be executed at
variable speeds, variation may be simulated by deliberate inefficiency.
This inefficiency gives the illusion of control that is very convincing to
the user.

Though the method used to achieve inefficiency in this sample program was a
series of rightarrow and leftarrow keystrokes, it is by no means the only
type of delaying tactic available. Any series of ineffectual keystrokes
will do.

Whatever strategy you choose, time-out macros offer numerous opportunities
for creativity.

* Program..: Demo.PRG
* Versions.: dBASEIV, Format 1.1
* Notes....: This is a simple application that has been modified to
* to provide on-screen instruction if a CHARACTER
* parameter beginning with "i" is passed to it.
PARAMETER pDecide
pDecide = UPPER(SUBSTR(LTRIM(pDecide), 1, 1))

IF pDecide = "I" && Demonstration mode.
RESTORE MACRO FROM KeySave && Primary and Time-Out macros.
SET CURSOR OFF && Get rid of unsightly cursor.
ENDIF

SET BELL OFF
SET BELL TO 100,1
SET ECHO OFF
SET PROCEDURE TO KEYSTROK && Programmed key strokes.
SET SCOREBOARD OFF
SET TALK OFF

DEFINE MENU MDemo

DEFINE PAD pAdder OF MDemo PROMPT " Add a Record " AT 2, 12 && Has
demo.
DEFINE PAD pEdit OF MDemo PROMPT " Edit a Record " AT 2, 33
DEFINE PAD pQuit OF MDemo PROMPT " Exit Program " AT 2, 55

DEFINE POPUP PopAdder FROM 3, 12 MESSAGE "Choose an Appending Mode."
DEFINE POPUP PopEdit FROM 3, 33 MESSAGE "Choose an Editing Mode."
DEFINE POPUP PopQuit FROM 3, 55 MESSAGE "Exit Demo Program."

DEFINE BAR 1 OF PopAdder PROMPT " Browse Format "
DEFINE BAR 2 OF PopAdder PROMPT " Append Format "

DEFINE BAR 1 OF PopEdit PROMPT " Browse Format "
DEFINE BAR 2 OF PopEdit PROMPT " Edit Format "

ON PAD pAdder OF MDemo ACTIVATE POPUP PopAdder
ON PAD pEdit OF MDemo ACTIVATE POPUP PopEdit
ON SELECTION PAD pQuit OF MDemo RETURN

ON SELECTION POPUP PopAdder DO ExitTrap WITH "ADD", BAR()
ON SELECTION POPUP PopEdit DO ExitTrap WITH "DEL", BAR()

ON KEY LABEL F2 SAVE SCREEN TO sKeep && Stores screen condition.
ON KEY LABEL F3 RESTORE SCREEN FROM sKeep && Restores screen
condition.
ON KEY LABEL F4 ? CHR(7) && Bell to grab
attention.

USE MEMBER
DO WHILE (LASTKEY() <> 27) && Main program loop.
CLEAR
ACTIVATE MENU MDemo
ENDDO && End main program
loop.
SET CURSOR OFF
RETURN && End of program.

PROCEDURE ExitTrap
PARAMETER mDecide, mBar

DO CASE
CASE mDecide = "ADD"
DO CASE
CASE mBar = 1
IF pDecide = "I" && This option offers the demonstration
DO Add_1 && mode. Procedure Add_1 contains the
PLAY MACRO A && ON KEY LABEL settings needed. Macro
ENDIF && "A" is the primary macro.
GO BOTTOM
BROWSE
CASE mBar = 2 && NOTE: none of the options at or below
APPEND && this line offer the demonstration mode.
ENDCASE

CASE mDecide = "DEL"
DO CASE
CASE mBar = 1
GO TOP
BROWSE
CASE mBar = 2
EDIT
ENDCASE
ENDCASE

DO WHILE INKEY() <> 0 && Clear out the macro.
ENDDO

DEACTIVATE MENU
RETURN
* EOF: Demo.Prg


Listing 1: KeyStrok.PRG
* Program..: KeyStrok.PRG
* Versions.: dBASEIV, Format 1.1
* Notes....: This is a procedure file that contains PROCEDURE ADD_1.
* ON KEY LABEL is used to program function key and key combinations
* to call other procedures which print messages on the screen.
* These messages will print in response to keystrokes "pressed"
in a macro.
PROCEDURE ADD_1
ON KEY LABEL F5 DO P_F5
ON KEY LABEL F6 DO P_F6
ON KEY LABEL F7 DO P_F7
ON KEY LABEL F8 DO P_F8
ON KEY LABEL F9 DO P_F9
ON KEY LABEL F10 DO P_F10
ON KEY LABEL CTRL-F1 DO P_CtrlF1
ON KEY LABEL CTRL-F2 DO P_CtrlF2
ON KEY LABEL CTRL-F3 DO P_CtrlF3
ON KEY LABEL CTRL-F4 DO P_CtrlF4
ON KEY LABEL CTRL-F5 DO P_CtrlF5
RETURN

PROCEDURE P_F5
@ 5, 10 SAY CHR(24) COLOR W+*/B,B/W
@ 5, 25 SAY CHR(24) COLOR W+*/B,B/W
@ 5, 40 SAY CHR(24) COLOR W+*/B,B/W
@ 5, 55 SAY CHR(24) COLOR W+*/B,B/W
@ 5, 70 SAY CHR(24) COLOR W+*/B,B/W
@ 9, 17 to 13,62
@ 10,19 SAY "The arrows are pointing to the last record"
@ 11,19 SAY "in the database file. We want to APPEND a"
@ 12,19 SAY " new record here. "
? CHR(7)
RETURN

PROCEDURE P_F6
@ 16,10 TO 18,71
@ 17,12 SAY "To APPEND this new record, the {DownArrow} key is pressed."
? CHR(7)
RETURN

PROCEDURE P_F7
@ 19,6 TO 21,73
@ 20,8 SAY 'Press the "Y" key when it asks if you want to "Add new
records?"'
? CHR(7)
RETURN

PROCEDURE P_F8
@ 6, 1 SAY CHR(24) COLOR W+*/B,B/W
@ 6, 3 SAY "Remember to capitalize ONLY the first letter of the last
name!"
? CHR(7)
RETURN

PROCEDURE P_F9
@ 6, 27 SAY CHR(24) COLOR W+*/B,B/W

@ 6, 29 SAY "Capitalize the first name in the same way."+CHR(7)
? CHR(7)
RETURN

PROCEDURE P_F10
@ 9, 20 TO 11,60
@ 10,22 SAY "Use the key to go to the right."
? CHR(7)
RETURN

PROCEDURE P_CtrlF1
@ 10,18 TO 12,63
@ 11,20 SAY "Use the key to go to the left."
? CHR(7)
RETURN

PROCEDURE P_CtrlF2
@ 13,12 TO 17,70
@ 14,14 SAY "Here's how to change the width of a column. It's easy!"
@ 15,14 SAY "First, you press the key comination. Then"
@ 16,14 SAY "use the and keys for resizing."
? CHR(7)
RETURN

PROCEDURE P_CtrlF3
@ 16,9 TO 18,72
@ 17,11 SAY "Then press the key to get out of the resizing mode."
? CHR(7)
RETURN

PROCEDURE P_CtrlF4
@ 12,16 SAY "Then, when you are through entering data, you can:"
@ 13,9 TO 16,72
@ 14,11 SAY "(A) Press or to exit and save data, or"
@ 15,11 SAY "(B) Press or to exit and abandon data."
? CHR(7)
RETURN


PROCEDURE P_CtrlF5
@ 9, 9 TO 11,70
@ 10,11 SAY "That's all for our lesson on adding new records. GOODBYE!"
? CHR(7)+CHR(7)+CHR(7)
RETURN
* EOF: Keystrok.PRG




3 Ad Hoc Queries

Ad Hoc Queries
Don Powells

Giving users the power to build their own query expressions
programmaticaly.

As good application developers we try to anticipate what data our end user
will want to retrieve and their preferred method of accessing that data.
We dutifully create and maintain indexes, complex or otherwise, to make
sure that data retrieval is fast and efficient. There are, however, those
special times when you need to search for the one-in-a-million needle in
the haystack. These are the times when maintaining an index makes little
sense because the index would mostly lie dormant on the hard disk neglected
and unused. These infrequent and often unusual searches are called ad hoc
queries.

dBASE IV provides us with two methods for implementing an ad hoc query:

The LOCATE FOR command

The SET FILTER TO command

With the LOCATE FOR command, you can thumb through each record in the .DBF
until you find one for which your search expression is true. If the first
matching record encountered is not the particular one you were looking for
you can use the CONTINUE command to find the next matching record.
Granted, if your database is large, the LOCATE command performs a brute
force, sequential search through the entire .DBF unless otherwise specified
and is significantly slower than using a SEEK through an index. But, this
is still faster and easier than manually searching through a file cabinet
of paper records when you have large files.

The SET FILTER TO command allows us to view only the records that match our
search criteria; it logically "masks" any records that do not meet the
condition we specify. This, of course, takes extra time and effort. So,
once again, the problem with using this command is that it can be painfully
slow.

Suppose you have 150,000 records and only 20 match your search criteria,
but those 20 records are sprinkled throughout the file with large gaps
between them. Paging through these records in BROWSE or EDIT mode would
appear relatively slow and some computers would definitely test the
patience of your average computer user.

True, both of these ad hoc query methods are slow with large .DBF files,
but when search criteria go beyond the practical scope of an index,
reasonable concessions have to be made. But speed is not the most
important issue here.

The most important issue is finding the desired data. But let's not forget
one very important requirement for these search methods. Both of these
commands require a query string as the search criteria, written using
syntactically proper dBASE IV expressions, composed of field names,
operators, and appropriate data, linked with connectors. Do your users
know how to write expressions like this one?

State = 'CA' .AND. Balance > 0 .OR. Balance > 1000000

Do they know when to use .AND. and when to use .OR. or .NOT.? Most end
users would get frustrated by the fact that if you leave out one period
before or after the connectors, ".AND." and ".OR.", the expression is
invalid or using the wrong one results in finding nothing. It becomes like
a logical puzzle to figure out. So, how do you let a user build a query
expression without getting a support call each time or sending them to QBE
in the non-programmer's interface? The answer is to provide them with a
query builder.

A Sample Query Builder

Bld_qry.PRG is a sample query builder. This program presents the user with
three popup menus from which to select by pointing and shooting. The first
popup is a list of the fields from the dbf. Bld_qry.PRG expects the .DBF
to be open before it is invoked. Rather than trying to remember how to
spell the field name, just pick it from a list. Next, the operators pop
up, letting you select the relationship of the field to the data (such as
=,>,<,>=). After picking the field and operator you are prompted for data
and only allowed to enter the proper data type because of the picture
clause which is used. Now that you have completed that expression, you can
link another one by choosing a connector (such as .AND., .OR. and .NOT.)
from the third popup or indicate that you are done. The query string is
displayed on the screen as it is being built and ultimately, returned to
the calling program by reference using the variable passed as the
parameter.

To use Bld_qry.PRG you must create a variable to contain the expression
that gets built. Assign that variable a null string ("") and pass it as
the parameter when you invoke Bld_qry.PRG. You can then use the resulting
macro expression in a LOCATE command or SET FILTER TO command. The
following code segment demonstrates this process:

USE Dbfname
cexpr = ""
DO Bld_qry WITH cexpr
LOCATE FOR &cexpr && OR SET FILTER TO &cexpr

Four utility UDF routines are used with Bld_qry.PRG:

* Get_Pic() accepts a field name and returns a default picture clause for
character or numeric data. The other data types do not require pictures.

* SayCenter() prints the specified character string in the center of the
specified row of the specified length.

* Tone() sounds an audible tone of the specified frequency and duration.

* Warnbeep() uses the Tone() routine to alert the user of an attempt to
perform an illegal operation (such as logical type field with "<" or ">"
operator) sounding tones more discordantly.

There are a number of useful enhancements that you could make to
Bld_qry.PRG. Displaying the field names allows you to use this program
with any .DBF but sometimes field names can be cryptic. A parallel array
of prompts would make it easier for the user to pick fields. Additional
operators like the contains operator ($) or use of more extensive
parentheses to allow for more complex expressions would give the user more
power and flexibility in building expressions and grouping them. An
editing facility would prevent having to start over from scratch when an
error is made. Some users may want to store certain complex queries for
use at a later date. A storage and retrieval mechanism would be a
practical upgrade.

You're invited to use the ideas in Bld_qry.PRG as a starting point and to
let your creativity extend its capabilities beyond those demonstrated here.

* Program : Bld_qry.PRG
* Desc : Routine to build a query string to be used in
* locate or filter for adhoc queries
* Notes : Expects DBF to be open; returns query string
* using cqrystr passed by reference
* Usage : cqrystr = ""
* : DO Bld_qry WITH cqrystr
* : LOCATE FOR &cqrystr

PARAMETERS cqrystr
SET TALK OFF
* Define the query window with no border.
DEFINE WINDOW wqrywin FROM 0,0 TO 24,79 NONE

* Define field, operator, and connector popups.
SET BORDER TO DOUBLE
DEFINE POPUP pufield FROM 6,1 TO 17,16;
PROMPT STRUCTURE;
MESSAGE "Select a field name or press to abort..."

ON SELECTION POPUP pufield DEACTIVATE POPUP

DEFINE POPUP puops FROM 6,23;
MESSAGE "Select an operator..."
DEFINE BAR 1 OF puops PROMPT "Equal"
DEFINE BAR 2 OF puops PROMPT "Not Equal"
DEFINE BAR 3 OF puops PROMPT "Less Than"
DEFINE BAR 4 OF puops PROMPT "Less Than or Equal"
DEFINE BAR 5 OF puops PROMPT "Greater Than"
DEFINE BAR 6 OF puops PROMPT "Greater Than or Equal"

ON SELECTION POPUP puops DEACTIVATE POPUP

DEFINE POPUP pucon FROM 15,23 TO 19,45;
MESSAGE "Select a connector or choose DONE..."
DEFINE BAR 1 OF pucon PROMPT "DONE"
DEFINE BAR 2 OF pucon PROMPT "AND"
DEFINE BAR 3 OF pucon PROMPT "OR"

ON SELECTION POPUP pucon DEACTIVATE POPUP

* Define data entry window.
DEFINE WINDOW wdata FROM 10,0 TO 12,79 DOUBLE

* Activate the query window.
ACTIVATE WINDOW wqrywin

* Paint screen constants.
DO SayCenter WITH 2,80,"BUILD QUERY EXPRESSION"
@ 5,1 SAY "Field Names"
@ 5,23 SAY "Operators"
@ 14,23 SAY "Connectors"
@ 5,50 SAY "Dbf Name"
@ 6,50 TO 6,59
@ 7,50 SAY UPPER(DBF())
SHOW POPUP pufield
SHOW POPUP puops
SHOW POPUP pucon
@ 21,0 TO 21,79
DO Saycenter WITH 21,80,""

* Initialize query string and variables.
cqrystr = ""
kESC = 27
nfldbar = 0
nopsbar = 0
nconbar = 0
DECLARE opsary[6]
opsary[1] = "="
opsary[2] = "<>"
opsary[3] = "<"
opsary[4] = "<="
opsary[5] = ">"
opsary[6] = ">="

* Do until DONE is selected from connector popup.
DO WHILE .T.
* Activate field popup
ACTIVATE POPUP pufield
nfldbar = BAR()
ccurfld = PROMPT()
ccurtype = TYPE(ccurfld)

IF LASTKEY() = kESC
EXIT
ELSE
* Add selected field to the query string.
cqrystr = cqrystr + IIF("" = cqrystr, "", " ") + ccurfld
ENDIF
@ 22,0 SAY cqrystr

DO WHILE .T.
* Activate operator popup.
ACTIVATE POPUP puops
nopsbar = BAR()

* Add selected operator to query string.
coperator = IIF(nopsbar = 0, "", opsary[nopsbar])

IF LASTKEY() = kESC
EXIT
ELSE
* Logical data has only '=' or '<>' operators

IF ccurtype = "L" .AND. nopsbar <> 1 .AND. nopsbar <> 2
DO Warnbeep
LOOP
ELSE
* Add selected field to the query string.
cqrystr = cqrystr + " " + coperator
EXIT
ENDIF
ENDIF
ENDDO
IF LASTKEY() = kESC
EXIT
ENDIF
@ 22,0 SAY cqrystr

* Enter value according to picture clause.
DO CASE
CASE ccurtype = "C"
getvar = SPACE(LEN(&ccurfld))
CASE ccurtype = "N"
getvar = 0.00
CASE ccurtype = "D"
getvar = DATE()
CASE ccurtype = "L"
getvar = .F.
OTHERWISE
getvar = ""
ENDCASE

ACTIVATE WINDOW wdata
@ 0,1 SAY "Enter " + TRIM(ccurfld) + ":";
GET getvar;
PICTURE get pic(ccurfld)
READ
DEACTIVATE WINDOW wdata
IF LASTKEY() = kESC
EXIT
ELSE
* Add value to query string.
DO CASE
CASE ccurtype = "C"
cqrystr = cqrystr + " '" + TRIM(getvar) + "'"
CASE ccurtype = "N"
cqrystr = cqrystr + " " + LTRIM(STR(getvar))
CASE ccurtype = "L"
cqrystr = cqrystr + " " + IIF(getvar, ".T.", ".F.")
CASE ccurtype = "D"
cqrystr = cqrystr + " CTOD('" + DTOC(getvar) + "')"
ENDCASE
@ 22,0 SAY cqrystr
ENDIF

* Activate connector popup.
ACTIVATE POPUP pucon
nconbar = BAR()
ccurcon = PROMPT()

* Add selected connector to query string or end process.
DO CASE
CASE LASTKEY() = kESC
EXIT
CASE ccurcon = "AND"
ccurcon = ".AND."
CASE ccurcon = "OR"
ccurcon = ".OR."
CASE ccurcon = "DONE"
EXIT
ENDCASE
cqrystr = cqrystr + " " + ccurcon
@ 22,0 SAY cqrystr
ENDDO
DEACTIVATE WINDOW wqrywin
RELEASE WINDOWS wqrywin,wdata
RELEASE POPUPS pufield,puops,pucon
IF LASTKEY() = kESC
cqrystr = ""
ENDIF
RETURN
* EOF: Bld_qry.PRG
FUNCTION get pic
*Get_pic() - return matching picture string for specified field

PARAMETERS cfield
PRIVATE cretval, cstring
cstring = SPACE(20)

DO CASE
CASE TYPE(cfield) = "C"
* character field is bounded by window width.
cretval = "@S" + LTRIM(STR(MIN(LEN(&cfield), 59)))

CASE TYPE(cfield) = "N"
* convert to character to help format picture string.
cstring = STR(&cfield)

IF "." $ cstring
* decimals in numeric...use the form "9999.99"
cretval = REPLICATE("9", AT(".", cstring) - 1) + "."
cretval = cretval + REPLICATE("9",;
LEN(cstring) - LEN(cretval))

ELSE
* no decimals...only need the correct length.
cretval = REPLICATE("9", LEN(cstring))

ENDIF

OTHERWISE
* No picture.
cretval = ""
ENDCASE
RETURN(cretval)
* EOF Get_pic() *

PROCEDURE SayCenter
*Display specified string in the center of given display width.

PARAMETERS nrow, ndispwidth, cstring
ncol = (ndispwidth - LEN(cstring)) / 2
ncol = IIF(ncol<0, 0, ncol)
@ nrow, ncol SAY cstring
RETURN
* EOP: SayCenter *

PROCEDURE Tone
* Tone(nfreq, nduration)
* Sounds an audible tone of a specified frequency and duration.

PARAMETERS freq, dura
SET BELL TO freq, dura
?? CHR(7)
RETURN
* EOP: Tone *****

PROCEDURE WarnBeep
* Sounds alert tone to warn user.
*Requires PUBLIC variable cf_ltog to be set to ON or OFF

mfreq = 300
mdura = 3
mfreq2 = 50
mdura2 = 4.5
DO Tone WITH mfreq, mdura
DO Tone WITH mfreq2, mdura2
RETURN
* EOP: WarnBeep

* EOF Bld_qry.PRG *





4 Resetting Page Numbers

Resetting Page Numbers
Joel Saltzman

Using the Template Language to reset page numbers on reports.

The dBASE IV report generator offers a rich set of functions and tools to
help you print attractive and meaningful reports. An additional
advantage for those who own the Developer's Edition is the ability to
change the way that dBASE IV interprets the report, screen, label, and
application design. This is possible through the use of Template
Language. Thus, for those times when you would like to take the report
generator a little farther than it already goes, the capability is there
for the using.

For example, consider the processing of pagination in reports. The
pre-defined field called PAGENO will supply consecutive page numbers from
the beginning of the report to the end. However, there is no way to reset
the page numbers in the middle of the report, say at a group break.

One of the options in the Bands menu when designing a report is to "Begin
Band on New Page". If you choose to this option in the Group 1 Intro Band,
then each new group will begin at the top of a page. If you use PAGENO,
the new group will begin on the next consecutive page number, but not on
page 1. This is not always the desired effect. However, using the PAGE BY
GROUP option in a modified .GEN file for reports, you may reset this page
counter.

To enable paging by group, create a named calculated field in the band
where you want the page number to appear. Enter any name for the field you
wish. Since it is a dummy field, the name will be disregarded. In the
description area, enter the following, paying attention to upper case where
specified:

PAGE BY GROUP [group number]

The group number represents the group for which you chose to "Begin Band on
New Page". If you leave out the group number out, group 1 will be
assumed. Enter any numeric expression in the expression area of the
calculated field, as it will be disregarded. You may adjust the field
template to your liking.

.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. For owners of the standard edition without Template Language
capability, you can still get the REPVERT.GEN file from our bulletin board
service in the DTL library.

The REPORT.COD file is a text file. Copy it to another filename with the
.COD extension so that the original REPORT.COD is still intact. You can
use MODIFY COMMAND, or any other text processor to change the .COD file.
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 -z

from the dot prompt, which 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

Copy the adjusted .GEN file into your dBASE directory. Whenever you want
to use the adjusted template instead of the standard REPORT.GEN, type in
the following line from DOS:

DTL_REPORT=.GEN

The file, as I have named it, is referred to as REPVERT.GEN. If you were
to use this name, then you would type

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. Alternatively, you may rename the new
.GEN file to REPORT.GEN. The original REPORT.GEN should exist on your
system disks. Even so, you should only replace this file once you are
confident that REPVERT.GEN file works properly.

You don't have to quit dBASE IV to get to DOS. From the dot prompt, type
RUN COMMAND.COM and you'll be at the DOS prompt. If you receive the
message "Bad command or file name", you do not have a path to the directory
that contains your COMMAND.COM. Once you are in a DOS shell, type EXIT to
return to dBASE IV.

Example of PAGE BY GROUP Usage

Suppose you have a database containing population data for cities within
many states. You need to print a report that shows population totals for
all states as well as the populations of all of the cities. Another
requirement is that each state should begin on its own page and should be
numbered as if it were a separate report.

After installing REPVERT.GEN, you open your database ordered by an index
consisting of the expression State + City. After creating a report with a
quick column layout, you add a group band, choose the option to group by
State, a field in the database. You then change the Bands: Begin Band on
New Page option to YES. You decide to place page numbers at the bottom of
each page and create a calculated field in the Page Footer Band of the
report. To reset page numbers to zero at each new group, you give this
calculated field a name (GroupPage, for example), and enter

PAGE BY GROUP 1

in the description area of the field. In the expression area, place the
number 1. This could be any number or numeric expression. Adjust the
template to allow for as many pages as you feel would be the maximum for
each state. Save the settings for the field and the report and you have
now completed.

Theory of PAGE BY GROUP Changes

There are two primary considerations in making the Template Language
changes to accomplish the PAGE BY GROUP effect. First, the report
generator needs to know if the PAGE BY GROUP field is being included in the
report. This is done by checking each calculated field when all calculated
fields are assigned initial values in the "assign calculated vars"
paragraph.

Special care must be taken depending on which band contains the PAGE BY
GROUP field. Under normal circumstances, named calculated fields are
computed when the detail band is printed. This would not be logical or
useful here since the page number must be adjusted only once per group.
The most logical and reliable places to increment the grouped page number
is at the point where report generation determines that the next printing
action would go beyond the end of the page. The best place to reset the
grouped page number to zero is when report generation detects that the
beginning of the next group has been reached. In both of these cases, the
adjustment to the grouped page number will occur prior to the printing of
the summary or footer bands. Therefore, the current page number,
group_page, is used when the page number field is being placed in the Page
Intro or Group Intro band. However, the page number prior to resetting or
incrementing, prev_page, is utilized if the PAGE BY GROUP field is in the
summary or footer band.

As you copy the code on the following pages, keep in mind the need to
capitalize where specified. The lines beginning with -> are the lines of
code you must insert or change. Line numbers provided in the changes are
from the REPORT.COD shipped with the original version of the dBASE IV
version 1.1 Developer's Edition. If you have a later version of
REPORT.COD, use the procedure and paragraph names as guides.

In the definition of "assign calculated vars()", insert and change the
lines where indicated.
This section begins on line 2442 of REPORT.COD.

define assign calculated vars()
//
// Now the starting values for calculated fields are assigned.
// It could be a table, calculated or summary field (or any combination).
//
x=0
foreach FLD ELEMENT k
//
// only if there is a fieldname assigned to the calculated field
//
if FLD FIELDTYPE == Calc data && FLD FIELDNAME then
if !x then }
* Assign initial values to calculated variables.
->{ endif
-> page by group tab=at("PAGE BY GROUP",FLD DESCRIPT)
-> if page by group tab > 0 then
-> group page bandnum = val(substr(FLD DESCRIPT,page by group
tab+13))
-> if group page bandnum == 0 then
-> group page bandnum = 1
-> endif
-> else }

{lower(FLD FIELDNAME)}={
foreach FLD EXPRESSION j in k
j.FLD EXPRESSION}
{ next}

{
-> endif
++x
endif
next k;
if x then }

{ endif
-> if group page bandnum > 0 then }
->roup page=1 && page numbering by group
->prev page=1 && group page before it was zeroed
->{ endif
return
enddef

In the definition of "init calculated vars()", which starts at line 2239,
insert the shaded lines:

define init calculated vars()
...
//
// only if there is a fieldname assigned to the calculated field
//
if FLD FIELDTYPE == Calc data && FLD FIELDNAME then
if !x then }
* Initialize calculated variables.
{ endif
-> if at("PAGE BY GROUP",FLD DESCRIPT) == 0 then }
{lower(FLD FIELDNAME)}={get init val(FLD VALUE TYPE)}
->{ endif
++x
endif
next k;
return
enddef

In the definition of "build case" statement()", which starts at line 2188,
insert the shaded lines.
These lines reset the grouped page number when the group changes.

define build case statement()
...
//
// Group by record count
//
if BAND GROUP REC then }
CASE r mvar{GROUP} > {BAND GROUP REC}
{ endif }
gn level={GROUP}
->{ if group page bandnum > 0 && GROUP <= 3 + group page bandnum then
}
-> prev page=group page
-> group page=0
->{ endif
-> endif
next k;
return
enddef

In the definition of "update summary and calc vars()", change the "if" line
after "Calc data:"
to include the check for the PAGE BY GROUP option. The if line is on line
1948.

define update summary and calc vars()
...
Calc data:
-> if k.FLD FIELDNAME && k.GROUP != 3 && at("PAGE BY GROUP",k.FLD
DESCRIPT) == 0 then}
{lower(k.FLD FIELDNAME)}={
...

In the procedure starting with "define begin new band(cursor)", under the
subheading "Page Footer:", insert the following where indicated. Page
Footer: is on line 1312.

Page Footer: }
Pgfoot
PRIVATE box{if isopen}, pspacing{endif}
->{ if group page bandnum > 0 then }
->prev page=iif(group page=0,prev page,group page)
->group page=group page+1
->{ endif }
gl widow=.F. && disable widow checking
...

About 350 lines after the beginning of the procedure called "output band
procs()",
in the sub-heading "Calc data:", insert the first set of lines right after
"Calc data:".
Insert the last line just before "Pred data:". Calc data: is at line 768.

Calc data:
-> page by group tab=at("PAGE BY GROUP",FLD DESCRIPT)
-> if page by group tab > 0 then
-> if bandtype > 3 then
-> priv vars=priv vars+"prev page"
-> else
-> priv vars=priv vars+"group page"
-> endif
-> else
if FLD FIELDNAME then
...
endif
-> endif
Pred data: ;

In the procedure called Pgplain, insert these four lines afterPRIVATE
box.
PROCEDURE Pgplain is on line 459.

PROCEDURE Pgplain
PRIVATE box
->{ if group page bandnum > 0 then }
->prev page=iif(group page=0,prev page,group page)
->group page=group page+1
->{ endif }
EJECT PAGE
...
RETURN
* EOP: Pgplain

Within the section called "variable initializations" close to the top of
REPORT.COD,
insert the following line after line 166:

-> group page bandnum=0

At the end of the section called "global variables" near the very top of
REPORT.COD,
insert the following two lines. Also put a comma after the last global
variable definition, previous tabs.
Previous tabs is at line 113.

->page by group tab, // location of PAGE BY GROUP in FLD DESCRIPT
->group page bandnum // band number for group paging








5 Marital Bliss

Marital Bliss
Wesley Leung

A simple way to prevent your memos from splitting up.

It can be rather annoying when your memos spread over from one page to the
next or when group summary totals split into two different pages. It's
perhaps more annoying to discover there's no option in the menus to prevent
it. But this doesn't mean you are stuck with no way of preventing this
problem.

Separated summary and memo widow checking is possible by way of a very
small UDF. One so small that it should offer no intimidation to even the
greenest dBASE user. All you have to do is create a file called
Page_Ejt.PRG. This file can be created at the dot prompt by using the
command MODIFY COMMAND PAGE_EJT or in the Control Center by selecting
under the Applications panel and selecting the dBASE Program
option.

The contents of the UDF consists of three lines:

FUNCTION Page_Ejt
EJECT PAGE
RETURN ""

Press Ctrl-End to save the file. If the file was created in the Control
Center be sure to name it PAGE_EJT.

Now modify the layout of the report that's giving you problems, moving the
cursor inside either the detail or the summary band. On the first line of
the band, press F5 and create a calculated field. Give it any name and an
optional description. Then, in the expression area, enter one of the two
following command lines.

To prevent page breaking on memos:

IIF((_plength - _plineno[ -] < MEMLINES(name>));
.AND. _pageno <> 1, PAGE_EJT(), "")

To prevent page breaking on summarybands:

IIF((_plength - _plineno[ -] < band>) ;
.AND. _pageno <> 1, PAGE_EJT(), "")

For example, if the memo field name is Notes and has no page footer, then
the expression would be:

IIF((_plength - _plineno < MEMLINES(Notes)) .AND. _pageno <> 1,
PAGE_EJT(), "")

If the group summary band has a height of 5 lines and the page footer band
has a height of 3 lines, then the hidden calculated field expression would
be:

IIF((_plength - _plineno - 3 < 5) .AND. _pageno <> 1, Page_Ejt(), "")

After entering in the expression, move down to the menu option to make your
field Hidden. Press Enter to turn this option to YES and press Ctrl-End to
save this as a hidden calculated field. You may check for both memo and
summary band widows by adding two hidden calculated fields each with one of
the above expressions.

Page number checking (_pageno <> 1) is done to ensure that an EJECT PAGE is
not executed when the first record contains a memo with a length longer
than one page.

When checking for separated summary bands, there will always be a number of
blank lines equal to the number in your footer band at the end of every
page. This is due to the hidden calculated field. If a Page Footer band
is in use and these blank lines are unacceptable, you can modify the .FRG
code file for the report in question in lieu of this UDF.

Use the Goto menu in MODIFY COMMAND to search for the specific text found
in the Detail or Summary band. By specific text, it is understood to mean
text that you've physically typed into the band during the design of the
report and not the variable text from database files. Then in the first
line of the procedure where this constant text exists, insert three lines:

IF (_plength-_plineno < 5) .AND. _pageno <> 1
EJECT PAGE
ENDIF

An Even Simpler Way
If the idea of creating a UDF is not appealing to you, you can try this
alternate method, which, though not as functional, will do the trick in
many cases.

On the first column and first line of the Detail or Group Summary band,
press F5 to create a calculated field. Name the calculated field and enter
the same expression as in the two examples shown above, except for one
replacement. Instead of Page_Ejt(), enter CHR(12) which is the ASCII
equivalent of a Form Feed. For example:

IIF((_plength-_plineno < MEMLINES()) .AND. _pageno <> 1,
CHR(12), "")

Now enter the Template expression area and delete all but one of the "X"
characters in the template and press Enter. Then press Ctrl-End to save
the calculated field. Here's the catchsince we are forcing the form
feed, the report generator cannot logically determine that a new page is
going to be printed and thus no Page Footer information is printed on that
page. Although no UDF is needed and the change can be done while modifying
the layout of the report, you will lose the Page Footer band. So, if you
don't need a footer and UDFs make you queasy, this way is a viable
alternative.




6 Q&A

Q&A
QThe Case of the Failed LAN Installation

I installed dBASE IV version 1.1 on my PC Net server. However, when I
attempted to run dBASE IV, I received the message "Program too big to fit
in memory" on startup.

My computer boots from the hard disk and no, I haven't write-protected my
system disk. I did a bit of sleuthing and discovered a message in the
INSTALL.ANS that says "Install failed due to error code 9062". I cannot
find any record of that error in my manual. What does it mean?

From our experience, it is a safe bet that you are installing from an
external disk drive attached to your server. We have received reports that
some drives have a timing error that conflicts with disk identifying
process of your install disks. These drives are always 5.25". If you do
not have the correct format of disk for the internal drive, attempt to
install from another workstation that has either a different drive or the
correct internal drive format. If that option is not viable, contact
Customer Service to obtain a different disk format.

Hide and Go Edit

During design of a report, I recently was stymied by the fact that I could
not edit a hidden calculated field I had created. I pressed F5 and
selected the field by name from the picklist in the CALCULATED column.
Although the expression was there, the name was not. When I typed the name
back in (assuming I'd made some oversight), I received an error saying I
had a duplicate field. When I erased it and tried to save, I was told that
the field required a name. I just reverted to the old version till I could
ask your help.

The catch-22 you were in is, believe it or not, a feature. By pressing F5
and selecting a calculated field, other calculated fields can be "cloned",
saving you the time to re-type complex expressions for similar fields.

What you didn't realize in this case is that there are two different
methods for modifying fields. One is by pressing F5 and the other is by
choosing the Modify field option from the Fields menu. The latter option
does allow you normal editing of a calculated field, hidden or otherwise.

It is often the case where we get so focused on one way of doing things,
that we miss alternatives. It's an axiom of human nature: If it doesn't
work, force it! Computers (and their programs) seem to respond the most
apathetically to this belief.

QBE Groupies

I was fooling around with QBE yesterday and it wouldn't let me GROUP BY or
CNT or AVG on my calculated fields. Can this be done?

You are prevented from doing such group-oriented actions on calculated
fields since calculated fields are not part of a physical database even
though it is part of a temporary one. At any rate, we suggest that you
perform your task in two separate queries. First, create the query with
the calculated field and write that query out as a database file. Then
create a second query using the newly created file and do your group or
counting operations on this file.

Lost Memvars

I am using the dBASE IV Applications Generator to build a bar menu system
that allows users to develop an input file for a compiled FORTRAN program.
On one of the items in an attached popup from the main menu I inserted
dBASE code that allows the user to name an input file and the output file
to be used by the FORTRAN program. I store character strings in memory
variables to do this. In another main menu item I inserted dBASE code that
executes a FORTRAN program and passes the contents of the memory variables
as the start-up parameters. My problem is that if this code is run in the
Applications Generator, the contents of the memory variables are erased.
When I execute the code embedded in the menu items outside the Application
Generator, everything goes according to plan so it must be the code
generated by the Applications Generator that wipes out the memory
variables. Do you have any suggestions or experience with a similiar
problem?

The key to your problem is the fact that the popups are attached to your
bar menu and your code is most likely embedded at the popup level. Since
the popups are attached, your code is never reached for there is no
ACTIVATE POPUP statement. Your code ends up in limbo. If the code is
related to the items in the popup, you can embed it at that level, which is
the action level. If the code is more global and would apply to all the
items in your attached popups, you can insert it at the main menu level.

"We Will Control the Vertical"

I have dBASE IV version 1.1 and I am working on a program that will print
on Epson FX 850 or FX 1050 dot matrix printers. I have a couple of reports

with vertical line characters in them and the lines do not line up at all.
How can I compensate for this (if at all)?

The problem does not stem from dBASE IV but from your printer. It is a
bi-directional printer and is not accurately lining up these characters as
it speeds by both ways. The simplest solution may be just to tighten the
paper guides on either side of your form feed paper. But if that doesn't
work, most printers have a means of changing the direction to
uni-directional rather than bi-directional, either by control code or a
switch on the printer. The codes for Epson compatible printers are shown
below.


??? "{Esc}U0" && bi-directional off
??? "{Esc}U1" && bi-directional on




7 UDF Library

Decisions, Decisions

It would be useful to have a means of a pick-list capability in coding
routines where you expect the user to build a dBASE expression, similar to
the way dBASE IV does when you are building an index expression from the
Control Center.

PickList() is a UDF that displays either the field names of the current
database or the files of a certain extension in the current directory.
After selecting from the popup, it then stuffs the selected choice into the
active field or input area using the KEYBOARD command. The syntax of this
UDF is as follows:


PickList("<[STRUCTURE]>/<.")

As you can see, the UDF accepts one of two character string paramters. The
string is either the word "STRUCTURE" (or the first four letters) in upper
or lower case or an extension preceeded by a period (such as .DBF, .PRG, or
.BAK).

If "STRUCTURE" is passed as the parameter, the fields for the currently
active database are displayed in a popup. If no database is open at the
time the UDF is called, a picklist of available .DBF files is first
displayed. The file selected from the picklist is then opened in the
current work area.

If a files list is needed, a file extension is entered. By beginning with
a period, the logic of the UDF will construct a files list with the passed
extension from qualifying files in the current directory. Entering a three
letter extension without a

preceding period returns a logical .F. as does anything other than the word
(or partial) STRUCTURE.

As an example, consider the code segment below wherein the expression
building Shift-F1 key, found in other design surfaces of dBASE IV is
emulated to perform the same task in your own program.

ON KEY LABEL Shift-F1 ?? PickList()

Exp = SPACE(50)
@ 5,5 SAY "Enter dBASE expression:" GET Exp
READ

This UDF is a great foundation for you to become more creative. Perhaps
you could include an array of logical operators such as +, -, *, <, >, #,
.AND., .OR., .NOT. or $.

Do You Know Where Your Index Is?

Although there are a handful of functions that give information about index
files and tags, there are some gaps in functionality. For instance, what
if you wish to obtain the key expression for a currently active index?
What if you had several index files open simultaneously; how would you
determine the name of the .MDX file that contains that currently active
tag. The two functions, ActvKey() and ActvMDX() will do just that.

ActvKey() returns the expression (and not the tag name) of the selected tag
of the active database. ActvMDX()which uses ActvKey() in its
calculationsreturns the name of the current active .MDX file. Each of
these two UDFs is a variation on the KEY() and MDX() functions but deals
specifically with the currently active tags or .MDX files which makes them
more suitable for interrupt routines.

The Value of a Work Area

There are several functions that deal with work areas. SELECT() tells you
the highest available work area. ALIAS() tells you the alias name of the
file in the current work area and DBF() tells you the actual file name
including drive location and extension. There's no function, however,
that tells you the number of the current work area. But there is always a
UDF. The UDF, WorkArea() will tell you the number of the current work
area, provided a file is open in that work area. If no file is open in the
current work area, a value of 0 is returned. No parameter is passed in
this UDF.

A Kinder, Gentler STUFF()

CutPaste() is a cut and paste UDF that enhances and simplifies the STUFF()
function. It will look for a specified string to be replaced and then
replace all occurences of it. The parameters are the field/character
string to search, the value to search for (or cut) and the value to replace
it (or paste) with. No need to figure out at what position to start
replacing or how many characters to replace. CutPaste() does it all for
you. If the specified text is not found, no replacement is made.

Let's say you want to make data entry easier by using an abbreviation for a
long title. With this function, you can go back later and expand all the
abbreviations to their actual values. For instance, suppose you have many
records where you used "L.A." to represent "Los Angeles". To make the
change, you would:

REPLACE ALL City WITH CutPaste(City, "L.A.", "Los Angeles")

This function would also be handy when a company changes names and you
don't want to look for the records that need to be changed as well. In
this instance, you could:

REPLACE ALL Company WITH CutPaste(Company, "Joe's Bar and Grill", "Chez
Joe's")

If you just want to remove a word, you could

REPLACE ALL Company WITH CutPaste(Company, "and Grill", "")

This would make "Joe's Bar and Grill" into "Joe's Bar".

It also has a built-in "safety feature": It will not make the replacement
if doing so would cause any data to be lost. If this doesn't suit your
needs, that segment is noted and can be removed.

FUNCTION PickList
PARAMETER likefile
*-- Preserve screen
SET CONSOLE OFF
b4talk = SET("TALK")
SET TALK OFF
SAVE SCREEN TO B4pop

*-- Assign "files", "fields" or "structure" macro substitution
DO CASE
CASE AT(".", likefile) <> 0 && "." presumes a file extension
likefile = "FILES LIKE *" + likefile
CASE UPPER(SUBSTR(likefile,1,4)) = "STRU"
DO WHILE "" = ALIAS()
USE ?
ENDDO
OTHERWISE
RETURN .F.
ENDCASE

*-- Draw a shadowed box for the popup
tempclr = SET("ATTRIBUTES")
SET COLOR TO G+/B
@ 5,34 FILL TO 20,48 COLOR W/N
@ 4,33 CLEAR TO 19,47
@ 4,33 TO 19,47
SET COLOR TO &tempclr

SET COLOR OF MESSAGES TO W+/B
SET COLOR OF BOX TO G+/B
SET COLOR OF HIGHLIGHT TO W+/R

DEFINE POPUP Flp FROM 4,33 TO 19,47 PROMPT &likefile
ON SELECTION POPUP Flp DEACTIVATE POPUP

ACTIVATE POPUP Flp
Ret = PROMPT()
RELEASE POPUP Flp

*-- Abort if user presses ESCape; otherwise KEYBOARD their choice
IF LASTKEY() <> 27
KEYBOARD Ret
ENDIF

RESTORE SCREEN FROM B4pop
SET TALK &b4talk
SET CONSOLE ON
RETURN .T.

Function: ActvKey
FUNCTION ActvKey
IF ASC(ORDER()) = 0
RETURN "" && Index is not an .MDX type
ENDIF
x = 1
DO WHILE (TAG(x) <> ORDER())
x = x + 1
ENDDO
RETURN KEY(x)

Function: ActvMDX
FUNCTION ActvMDX
Currkey = ActvKey()
x = 1
DO WHILE .T.
MDXName = MDX(x)
IF ASC(MDXName) = 0
EXIT
ENDIF
mTagNum = 1
DO WHILE KEY(MDXName, mTagnum) <> CurrKey .AND. ;
ASC(KEY(MDXName, mTagnum) <> 0
mTagnum = mTagnum + 1
ENDDO
IF KEY(MDXName, mTagnum) = CurrKey
EXIT
ENDIF
x = x + 1
ENDDO
RETURN MDXName

Function: WorkArea
FUNCTION WorkArea

w_cnt = 0
w_found = .F.
DO WHILE w_cnt < 10
w_cnt = w_cnt + 1

IF ALIAS(w_cnt) + "." $ DBF() .AND. LEN(DBF(w_cnt)) <> 0
w_found = .T.
EXIT
ENDIF
ENDDO

IF .NOT. w_found
RETURN 0
ENDIF

RETURN w_cnt

Function: CutPaste
FUNCTION CutPaste
PARAMETERS InField, LookFor, RepWith

IF TYPE("InField") + TYPE("LookFor") + TYPE("RepWith") # "CCC"
RETURN InField
ENDIF

mMatched = .F.
mLookLen = LEN(LookFor)
mLen = LEN(InField)
mRepLen = LEN(RepWith)
mRetVal = InField

DO WHILE AT(LookFor,mRetVal) > 0
mMatched = .T.
mRetVal = TRIM(mRetVal)
mTrimLen = LEN(mRetVal)

* The following IF statement prevents the replacement text from
* "overflowing" the length of the original string.
IF (mTrimLen - mLookLen) + mReplen > mLen
RETURN mRetVal
ENDIF

mCutAt = AT(LookFor,mRetVal)
mRetVal = STUFF( mRetVal, mCutAt, mLookLen, RepWith )
ENDDO
IF .NOT. mMatched
RETURN InField
ENDIF
RETURN mRetVal






8 Manual Overwrite

Calculated Efficiency

A new command allows you to get more results with fewer passes through a
file.

Let's go beyond the one-operation-per-command axiom, shall we? You know,
that old belief that you do one thing at a time as you traverse through a
file. You count the records, then you sum the records and so on. Each
time, a complete pass through the file is necessary. Well, a new command
in dBASE IV makes this redundancy less frequent. It is a command that
allows conjunctive arithmetic operations to be performed during a single
pass through a file or a portion thereof. CALCULATE allows you to count
records, sum or average fields, or determine the minimum or maximum value
within a range. Also on hand is the ability to determine net present
value, standard deviation and variance.

The different operations desired are separated by commas in the command
line. The results of the operation can be sent to memory variables or,
handily enough, an array. In regards to the use of an array, it must be
one-dimensional and is filled from the top to the bottom.

In a CALCULATE command line, the operations are listed first followed by
the memory variable or array element to which the result is to be stored.
It is sometimes the inclination of the new user to stray from this
convention by attempting a command such as:

CALCULATE SUM(Balance) TO mbal, AVG(Payment) TO avgpay

This doesn't get the desired results. As a matter of fact, the most common
result is a call to support to figure out what's wrong. "Hey, you guys!
How come all I get is this message that says 'Illegal value'? Can I get a
refund?" Juggling the subjects and command predicates around a little, we
once again have a happy customer:

CALCULATE SUM(Balance), AVG(Payment) TO mbal, avgpay

Like other similar commands, the TO predicate is optional, only necessary
when the results are to be stored for use in further calculations.
Otherwise, when SET TALK is ON, the requested options are displayed on
screen.

Aside from the obvious functionality enhancement given by one command, some
have asked us if it's better when just doing one operation such as COUNT,
AVERAGE or SUM to use CALCULATE as opposed to pre-existing commands. There
are no speed enhancements to speak of in using CALCULATE over the older
commands except when you count the number of times you may have to pass
through the file to get different desired results.

There are also two schools of thought as to how one might apply this
command: You may decide that CALCULATE should always be used in lieu of
COUNT, AVERAGE or SUM. This may be wise for the purpose of streamlining
code for later maintenance. All of your record counts, averages and sums
(as well as the financial or statistical operations available) are under
the umbrella of one command. But the changes required to a very large
program might deter someone from making this change just for the sake of
keeping up with the new syntax. Also, the older commands are more explicit
in their function and are easily recognized. The word CALCULATE could
appear as more "generic".

What about the additional financial functionality of the CALCULATE
command? One of the most popular methods of discounted cash flow analysis,
NPV or Net Present Value, enables you to determine the nature of monies
paid out or received as they occur at regular intervals. NPV is determined
by adding the initial investment to the present value of available funds.

Standard Deviation (or STD) determines the measure of dispersion around the
midpoint of a group of values. The algorithm to accomplish this, to the
non-math oriented layperson, would be somewhat tedious.

Here's how this plays. First, you'd take the average of a group of values,
subtract that average individually from each value, square the differences
of each, add them together and then take the square root of this result
divided by the initial number of values. Whew! Sounds busy to me! By use
of the CALCULATE command, the result is immediately determined. Isn't
technology wonderful?

The other specialized function of the CALCULATE command is that of
computing the variance, or the mean square deviation. It is used to
determine the standard deviation. The result produced from this function
is a floating point type variable. The variance is a means to an end,
merely an ingredient in the recipe of countless statistics calculations.
It is an exaggerated measure of how disparate your sample data can be.

So, regardless of how you use it, the new CALCULATE command and its ability
to do several tasks while only sweeping through your database one time is a
real time-saver, especially in the case of very large files.

Values in Present and Future

If you're interested in investments, three new functions that simplify some
common financial equations are now found in dBASE IV. They are FV(), PV()
and PAYMENT().

FV(), or future value, will render the value of your nest egg at some point
down the line, provided you faithfully deposited to it. As parameters, it
takes the payment, rate of return and number of periods involved. If you
invest a sum each year, this function will tell you what to expect later
on.

Present value is determined by use of the PV() function. This function is
good for when you need to know how much to save to attain a specific goal.
Essentially, it looks at investment from a today perspective and tells you
what it's going to take for you not to be a burden on your children or
society. For instance, if you wanted to have $1,000,000 in your
retirement account by age 70, then, hey, this is the function for you!

Then, there's the PAYMENT() function which gives you a simple interest
calculation of what you're going to pay for your next debt. You only live
once, right?

IN For the 90's

By including aliases as parameters in several functions and adding the IN
clause to USE, SKIP, GOTO and other commands, you have much more explicit
and efficient control over file handling in dBASE IV..

Since multi-file operations are more the rule than they are the exception,
it was a natural (and much needed) enhancement. By calling the shots on
files in unselected work areas, you can more completely control my
programming environment. (You think police are bad! Programmers are big
on control.)

So now, when you wish to know what record your pointer is positioned on in
work area B, you can issue a command like:

? RECNO(2)
? RECNO(B)
? RECNO("Transact")

The parameter will accept the literal default alias letters or numbers, the
name of the file or an expression that factors out to the same. Macro
Substitution can be used as well to supplant the name of a file that is not
known until run time.

You can also control files and records in unselected areas. Operations
such as moving the record pointer, locking and unlocking a record,
displaying the structure of a file. Additionally, by the use of enhanced
functions, you can determine beginning or end of file, network changes,
index expressions, size of record and file, names of fields in the
unselected file.

All of these abilities represent significant increase in power, especially
considering the enhanced capabilities you have using interrupt routines in
ON KEY commands and UDFs. More and more, the programmer is given the full
schematic, a conductor's score, if you will, to know everything there is to
know about the environment he or she logically controls.

Let's talk about the most useful and most commonly used case where IN
helps. That is the case of USE...IN where a database (and of course its
index) can be set up and used in any work area. The advantage of this is
economy of code. In dBASE III PLUS, you were required to physically switch
to each successive work area to open a file. This meant two or more
commands were required for each file used. Consider the old and new
methods:

dBASE III PLUS dBASE IV

SELECT 2 USE Travel IN 1
USE Client INDEX LASTNAME USE Client IN 2 ORDER LASTNAME
SELECT 1
USE Travel

The new method is much more economical and also clearly shows the work area
next to the file name itself. Furthermore, the fact that .MDX files are
opened automatically relieves the necessity to open specific .NDX index
files making the ability to set the active index out of those available in
a multiple index (.MDX) as a predicate of the USE command even more
economical.

A note on "aliasing" is appropriate here. What follows after the IN as
described here is the alias name. Work areas have default alias names of A
through J or 1 through 10. When a file is opened, the file and work area
can be accessed by the name of the database (minus the .DBF extension), if
no explicit alias name was assigned.

When we discuss USE IN we must realize that since no file has been opened
yet, the work area cannot be accessed by any other means than as the number
assigned to it. Even the letters A-J are insufficient here.

A much better way of aliasing is to use the file name as the alias. In a
program it's always better to refer to a file by its actual name instead of
by the more cryptic letter or number assigned to the work area in which the
file is opened. So you'd prefer to see SELECT CLIENT instead of SELECT B
since it adds a lot more clarity to your program. The same is true if you
are using the IN predicate.

The enchanced functions are:
BOF() CHANGE() DBF() DELETED()
EOF() FIELD() FLOCK() FOUND()
ISMARKED() KEY() LOCK() LOOKUP()
LUPDATE() MDX() NDX() RECCOUNT()
RECNO() RECSIZE() RLOCK() SEEK()
TAG()

The enhanced commands are:
DISPLAY/LIST STRUCTURE GO/GOTO RESET
SKIP UNLOCK USE

If these financial functions intrigue you, make sure you take a look at the
TechNotes/dBASE IV August 1990 article entitled "UDF Hors d'oevres" for a
host of financial and statistical functions.



9 Etc.

The SUN Also Rises

dBASE IV, appearing on new platforms, makes it bright debut on SUN
workstations.

In case you weren't aware, dBASE for UNIX (SPARC/SUN4 version) is now
available. Other platforms such as SUN3, SUN386i, SCO UNIX and SCO XENIX
are still in development. The current models on which dBASE for UNIX will
operate are SparcStation 1, 1+, 2, SLC, IPC, 4/470, 4/490 and
SparcServers. SUN OS 4.0 or higher is required and workstations must be
equipped with at least 4 MB of RAM. Performance increases with additional
memory. dBASE IV for UNIX also supports ethernet local area networks
running TCP/IP and NFS (Network File System).

dBASE for UNIX provides the same functionality, look-and-feel as the DOS
version of dBASE IV. It provides true portability between different
platforms. All DOS dBASE IV version 1.1 source files such as .PRG, .FRG,
and .LBG files as well as binary files such as .DBF, .MDX, .FRO and .DBO
files. All are fully interchangeable between DOS, VMS and UNIX.

The UNIX version of dBASE IV is actually equivalent to the Developer's
Edition of dBASE IV for DOS except for the fact that RunTime is not
included. RunTime is available as a separate package. Multi-user packs
and runtime licenses are available at an additional cost. A single-user
copy of dBASE for UNIX is priced at $995 and is available now.

The Old Disappearing Field Trick

In certain types of queries, an error occurs returning the message "Field
not found" when run. A simple query will not encounter this type of
problem. However, removing even one field from the view skeleton forces
the query generator to produce a SET FIELDS TO list in the resultant .QBE
file. If, in addition to this, you force QBE to physically sort to a
temporary file by using the ASC or DSC operator on a field and not utilize
an index tag, the error can result. For when the "sorted-to" file is
closed the field list remains in effect internally. This causes the
original file to appear as if it has no fields.

The workaround is to go to the dot prompt and type SET FIELDS OFF. This
will stop the action of the field list when in the query. In a program,
issue SET FIELD OFF prior to a SET VIEW TO a query file.

This can also be accomplished by a simple UDF which would contain the
lines:

FUNCTION NOFLD
SET FIELDS OFF
RETURN .T.

By simply referencing the NoFld() UDF in a condition box, it will be
included in a SET FILTER statement in the .QBE file and suppress this
problem. This should have no adverse effect on other conditions within a
condition box since the only possible result of the UDF is a logical true.

Trapping Errors in Boxes

When you are entering commands at the DOT PROMPT, there are times that
something is typed wrong or misspelled. When this happens, an error box
will appear in the middle of your screen, usually with a few choices of
action that you can take as a result of this error. For those of us that
require the extra direction, this feature is wonderful. But for those of
us that pretty much have a handle on the DOT PROMPT and can spot the error,
having to supply this help box with some type of reply can become quite
tiresome. There is a solution if you would like to avoid these prompts.
The way we will be getting rid of this error box is by branching to a
procedure, and to do this you need to use the ON ERROR command.

By using this command you will be able to execute a dBASE program instead
of going to the error box. Type the following text into a dBASE program
file using MODIFY COMMAND.

* Name....: P_Err.PRG
* Version.: dBASE IV 1.1
* Notes...: This program processes error messages. When an error occurs,
* this program will display the error message to the screen
* along with its corresponding error number.

err_len = LEN(MESSAGE()) / 2
??CHR(7)
@ 1, 0 CLEAR TO 4,79
@ 1, 39 - err_len TO 3,42 + err_len DOUBLE
@ 2, 41 - err_len SAY MESSAGE()
@ 3, 38 SAY "[ "
@ 3, 40 + LEN(LTRIM(STR(ERROR()))) SAY " ]"
@ 1, 39 - err_len FILL TO 3, 42 + err_len COLOR W+/R
@ 3, 40 SAY LTRIM(STR(ERROR())) COLOR W+/R

* EOF: P_Err.PRG

After you are through entering the code for P_Err, go to the dot prompt and
enter

ON ERROR DO P_ERR

From this point on in your dBASE IV session, whenever an error occurs that
ON ERROR will trap, the procedure P_Err will execute. To demonstrate the
procedure, type X and press Enter at the dot prompt. You should now be
seeing a box at the top of your screen that contains the error message text
and, in square brackets below it, an error number.

If this sounds like a better way of error notification, make a small
modification to your CONFIG.DB file. By using the MODIFY COMMAND editor or
any text editor, you can enter the command line:

COMMAND = ON ERROR DO P_Err

Press Ctrl-End to save, quit and restart dBASE IV. This will insure that
every time you start dBASE IV, the ON ERROR routine will be set up for you
automatically.
Note that if you already have something in the COMMAND= line that you want
to keep, such as ASSIST, you can create a dBASE program that contains both
the ON ERROR and the ASSIST commands and on the COMMAND= line you would
indicate the name of that program (such as COMMAND = DO STARTUP). This
will enable you, indirectly, to put as many commands as you want on the
COMMAND= line in the CONFIG.DB file.



 December 21, 2017  Add comments

Leave a Reply