1 UDF Library


Dealing with SET FIELDS

The SET FIELDS command gets highest priority in all operations that have
the FIELDS option. If you SET FIELDS to a particular field or fields and
then attempt to process a field not included in your SET FIELDS list, you
will get an error message.

You can use the TYPE() function to determine the data type of a field or
variable. If a field is not included in the current fields list, it
returns "U", for undefined as it would with a variable that hadn't been
declared only defined as PUBLIC or PRIVATE.

Since the use of SET FIELDS has a potential error-causing effect, it would
be wise to make the issued command sensitive to any specified fields list.
By cycling through all the fields in a structure with a DO WHILE loop and
using TYPE() to test for fields not in the fields list, you can obtain
critical information about a file structure or fields list that no regular
function in dBASE IV provides. Thankfully, a UDF can practically always
fill the bill.

For instance, you could build a fields list to use in macro substitution to
provide field names as is done in the UDF FNames.

The Clock's Ticking

TimeOut() is a UDF that you can use with a PopUp to limit the amount of
time that a user has to make a choice. You can specify the number of
seconds to wait before timing out and you can customize the UDF to take a
specific action when the time out occurs.

When you DEFINE your POPUP, include TimeOut() in a SKIP FOR clause in the
first BAR. Make sure to specify a parameter to indicate the length of a
time out. The program below is an example of how to implement the UDF.
When you run the program, you have 10 seconds to move the highlight or make
a choice. The same is true after each keystoke until a choice is selected
or the popup is aborted.

DEFINE POPUP Test FROM 5,5 TO 10,20
DEFINE BAR 1 OF Test PROMPT " First " SKIP FOR TimeOut(10)
DEFINE BAR 2 OF Test PROMPT " Second "
DEFINE BAR 3 OF Test PROMPT " Third "
DEFINE BAR 4 OF Test PROMPT " Fourth "
ON SELECTION POPUP Test DEACTIVATE POPUP

Special Effects Indexing

For those of you into visual effects, IndxBar can be used in conjunction
with an index to display the progress of your index graphically. It will
draw a bar from a specified coordinate and make it as long as is specified
by the third argument.

The parameters startRow, startCol are the on-screen coordinates where you
want the bar to start, and barLen is how many bar characters you want to
display. To use it, incorporate the UDF into the FOR expression of an
INDEX command such as:

INDEX ON TAG FOR IndxBar( 5,5,25 )

DBTRAP and TALK should be set OFF. It is not recommended that you use such
an index tag with a production .MDX file because every time a record is
modified through BROWSE or EDIT or any other means, your index tag will
need to be updated and you will get a portion of the bar on the screen.
Use it with a non-production MDX file. Since this is a special effect UDF,
it may not be suited for every occasion.

FUNCTION FNames
* by Martin Leon
* Returns a list of fields.

x = 1
mNameList = ""
DO WHILE "" <> FIELD(x)
IF TYPE(FIELD(x)) <> "U"
IF LEN(mNameList) = 0
mNameList = FIELD(x)
ELSE
mNameList = mNameList + "," + FIELD(x)
ENDIF
ENDIF
x = x + 1
ENDDO
RETURN mNameList



FUNCTION Timeout
* Author: Dan Madoni
PARAMETERS t_time

DO CASE
CASE LASTKEY() <> 32
KEYBOARD CHR(32)
RETURN .F.

CASE LASTKEY() = 27
KEYBOARD CHR(27)
RETURN .F.

OTHERWISE
t_key = INKEY(t_time)
ENDCASE

IF t_key <> 0
KEYBOARD CHR(t_key)
ELSE
* Begin your code.
* The code placed in this area will be executed on Time Out
@ 1,0 SAY "PopUp has Timed Out."
* End your code.
KEYBOARD CHR(27)
ENDIF
RETURN .F.


FUNCTION IndxBar
PARAMETERS startRow, startCol, barLen

mComplete = RECNO() / RECCOUNT()
mBars = INT(mComplete * barLen)
@ startRow, startCol SAY REPLICATE(CHR(219), mBars)
RETURN .T.





2 Etc

View Meister

Did you know that you can actually keep a database of graphic images in
your memo file? In your CONFIG.DB file, add a line

WP =

To add a graphic (such as a .GIF file) to a record,

. APPEND MEMO FROM

When you want to view it, simply enter the memo field as you normally would
by pressing Ctrl-Home.

In effect, you have told dBase IV to use a different Word Processor, but in
this case, it is a graphics viewer. This will allow you to display images
without having to RUN an external program.

A simple database might look like :

PART_NO Character 10 Y
PART_NAME Character 25 N
PART_PICT Memo 10 N

Use caution: If you elect to do this, you MAY NOT enter text into the memo
field. Also, if you change your CONFIG.DB file and remove the "WP = " line,
your graphic image will become corrupt if you enter the memo field.

Setting "WP = " can work with various file types.
However, you will want to select one that uses as little memory (RAM) as
possible. Trying to open a large image may cause the error "Insufficient
Memory" or worse, hang the system.

Novell and the TOTAL Command

The following information came from Novell, and has been tested and
verified by them.

Performing a TOTAL command on a Novell Network (totalling to a network
drive) would not work if you are using version 3.02 of the network shells.
If you've switched to an earlier version of the shells, or if you wrote the
file to a local drive, the TOTAL operation would work.

Determining a Drive Status

So, what happens when your custom-made program runs aground when it goes to
look for files on a floppy and the user has no disk inserted. DOS errors
like "Drive not ready" are not trappable by typical ON ERROR routines.
However, there is a way of checking on a drive status and incorporating the
functionality into your programs.

You can obtain a .BIN routine from the download file ISDISK.ARC, available
on our BBS. Using it with the CALL command, an "X" will be returned if a
drive is not ready. The CALL function can be implemented so that you can
use the drive-check utility in expressions. For example, a simple backup
procedure would COPY an open database to drive A only after a drive check.

IF CALL("ISDISK","A")="A"
COPY TO A:ARCHIVE1
ELSE
? "Insert the backup diskette into Drive A:"
ENDIF

Now you Don't, Now you See It

If the CURSOR is set OFF in a program and an error occurs, selecting
"IGNORE" from the "CANCEL IGNORE SUSPEND" error box will turn the cursor
back ON. However, even with the cursor flashing, issuing SET("CURSOR")
returns OFF. No workaround exists for this problem.

Functions within Debug

In order for DEBUG to control program execution, SCOREBOARD, TALK, and STEP
are set to OFF when you are in a DEBUG session. Nevertheless, the user's
program will still reflect the values set by program control. The setting
of SCOREBOARD, TALK and STEP is only an annoyance in those rare occasions
when you may be tracing (via the Display box) the SET() values of these
particular commands. Otherwise they will always show "OFF".

What is more troublesome is the interaction of DEBUG and functions such as
READKEY() because, in some circumstances, the keys pressed in DEBUG are
reflected in the values returned by READKEY(). For example, place the
following code into a program called Testptr.PRG:

SET TALK OFF
CLEAR

x = 7
@ 4,5 SAY "Enter a value:" GET x
READ

IF READKEY()=12 && was an Escape pressed?
RETURN
ENDIF

Activate the Debugger by entering

DEBUG Testptr

Now run the debugger, pressing the Enter key on each line so you can walk
through the code. When the cursor falls on the READKEY() line press D for
Display and type in x as the variable you would like to checkafterwards,
press Escape. That last Escape key will be remembered by dBASE IV and will
be the return value for READKEY()even though you might have pressed
Return for the READ. The line IF READKEY()=12 will evaluate true and the
program will return to the dot prompt, which is not exactly what you might
expect.

There is no workaround for this issue but being aware of the manner in
which the Debugger handles these situations may save you some time
attempting to figure it out.

External Affairs

A memo cannot be fully deleted if an external editor is setup for memo
editing (WP =) in Config.DB. If all the data in the memo is deleted, then
dBASE IV will treat the memo as unchanged and keep the original memo
intact. To workaround this problem, leave at least 1 character (even a
space) in the memo in order to register that a change has been made to the
memo. Then, to delete this 1 character issue:

REPLACE WITH "" && replace with the null string

Label Dej Vu

If a LABEL FORM is called twice from inside a program, an attempt is made
to look for a .LBL file the second time. This is fine if the .LBL file is
actually there but will result in an error if only compiled object files
(.DBO, .LBO, and so on) are present. This may be common in a turnkey
application setting. The error "File does not exist: .lbl"
will result in these cases.

There are two ways around this problem. The obvious first way is to merely
leave the .LBL file in the directory or make sure it accompanies your
applications.

Alternatively, you may dBLINK the .DBO and .LBO together. The .LBO MUST
remain in the directory after linking.

Stragtegically Placed Windows

When SET STATUS is ON, the error "Coordinates are off the screen" will be
triggered when a MENU pad is DEFINEd on line 21 (the line just above the
status bar) and the MENU is ACTIVATEd. Similarly, MENU pads DEFINEd on
lines 22 through 24 return the identical error message when ACTIVATEd.
These areas are considered off limits to menu pads. However, the
presumption is that line 21 should still be addressable since it is above
the status bar.

A solution to this problem is to DEFINE a WINDOW with a border of NONE
where the bottom border is on line 21 of the screen. Windows defined with
a border of NONE have a minimum height of three but all three lines are
addressable. A menu pad can be successfully DEFINEd on the last line of
this window.

SET STATUS ON
DEFINE WINDOW W1 FROM 19,5 TO 21,76 NONE && 3 line tall borderless window
ACTIVATE WINDOW W1
DEFINE MENU M2
DEFINE PAD PAD1 OF M2 PROMPT "YES" AT 2,5
DEFINE PAD PAD2 OF M2 PROMPT "NO " AT 2,35
ACTIVATE MENU M2

This will cause the menu to appear as if it were on line 21 when it really
is on the last line of the window. Note that the window will cover up
anything that is displayed on the screen at the same coordinates, so size
the window accordingly.

Yield to Conditional Indexes on a Network

If, under multi-user dBASE IV, you create a conditional index tag (INDEX
ON...FOR) in which no records qualify and then you APPEND new records, the
conditional index tag is not appropriately updated. For example,

1. Enter dBASE IV in a multi-user environment.

2. At the dot prompt enter:

. USE EXCLUSIVE
. INDEX ON TAG TAG1 FOR validfield =
. APPEND
. Alt-E,E(xit)
. LIST/DISPLAY or COUNT

Note that data is not LISTed or DISPLAYed. 0 is returned for COUNT.
Deactivating the conditional tag and then listing the .DBF reveals the
APPENDed records. Be aware of this effect when considering a conditional
index.




3 Introducing dBASE IV Server Edition

Introducing dBASE IV Server Edition
Jeffrey B. McCrimon

The dBASE IV Server Edition is the first release of a series that brings
the power of dBASE IV to the client/server environment. Server Edition
includes all of the capabilities of dBASE IV version 1.1 and in addition,
allows dBASE IV to operate as a client (front-end) to remote (back-end)
database servers. This first release of the dBASE IV Server Edition will
support the Microsoft SQL Server that was originally developed in
partnership with Sybase, Microsoft, and Ashton-Tate. dBASE IV Server
Edition, in combination with a back-end database server, provides an
attractive solution for database workgroup computing. This solution has
significant advantages over traditional file server architecture.

Client/server architecture divides database management functions into two
componentsa front-end "client" component and a back-end "server"
component. The server acts as a centralized data repository, providing
data storage, data management and control, transaction processing, access
protection, integrity protection, failure recovery, centralized
administration, and reduced network traffic. The client provides the
application engine in which data accessed from the server is manipulated
and presented to the user in the form of displays and hard copy reports.
With dBASE IV Server Edition as the client, you are provided with the dBASE
SQL language as your host language to access server data. The dBASE SQL
language is based on the industry-standard IBM SAA-compliant SQL commands.
The full range of dBASE IV screen management, report and label facilities,
and procedural language allow you to design input screens and format
results while using dBASE SQL to process data requests.

What's New?

In addition to client/server operation, dBASE Server Edition brings several
new features and enhancements.

Dynamic SQL

Current versions of dBASE IV only allow the use of static SQL which means
that the context of the SQL command remain unchanged at the time of program
execution; the entire SQL command is specified in the program file and is
executed as written. Although memory variables and arrays can still be
used and values supplied to them by the application user, static SQL
commands are fixed and precompiled. Therefore, all object names and object
structures must be known when the code is written and cannot change during
execution.

Dynamic SQL, which is new to the dBASE IV Server Edition, allows for the
dynamic construction and execution of SQL statements at application
runtime. This means that you can now create applications that query the
user for input and, based on the input, construct SQL statements at
run-time. Dyanmic SQL gives your applications a high degree of flexibility
now that you no longer have to hard code object names and object structures
into your applications. Dynamic SQL is only valid for programs that access
data against remote database server data in this release of the dBASE IV
Server Edition.

SENDSQL

When dBASE SQL commands are entered against database server data, the
commands are processed by dBASE IV and sent to the database server. If
necessary, the commands are modified to conform to database server's
specific syntax. However, there may be times when you want to send
commands specific to the database server that may not be available with
dBASE SQL. The dBASE IV Server Edition provides a new command, SENDSQL,
used to take advantage of the extended features of the database server
without leaving dBASE IV. With SQL Server for example, users can create
and execute system procedures, stored procedures, and triggers that reside
on the SQL Server, right from your dBASE applications. The SENDSQL command
passes the command string directly to the database server without any local
processing. The results, if any, are then passed back to your
workstation. This allows even tighter integration and better performance
between dBASE applications and the database server.

Extended Memory Support

dBASE IV version 1.1 for DOS was only able to use up to the first 640K of
memory. dBASE IV Server Edition finally brings the increased capacity of
extended and virtual memory to dBASE IV applications. Extended memory is
physical memory that exists above the 1 Megabyte memory range on your
computer. Virtual memory is where your hard disk is used to simulate
memory that isn't available on your computer. This provides a solution for
users who wish to take advantage of extended and/or virtual memory or who
need to run large dBASE applications, but have insufficient memory
available. Insufficient memory may be due to network software or large
memory-resident (TSRs) that occupy DOS memory. The dBASE IV Server Edition
has incorporated Rational Systems DOS/16M Extender Technology, thereby
giving dBASE applications access to all available extended and/or virtual
memoryup to 16 Megabytes. By using DOS/16M, dBASE IV Server Edition only
requires 60K of conventional memory, thereby reserving the available memory
to run your other large programs, such as Framework IV or MultiMate, while
you are still running dBASE IV Server Edition.

The optional disk-cache (Hyperdisk) that was included with dBASE IV version
1.1, is still included with the dBASE IV Server Edition. Hyperdisk is a
disk-cache which can improve your computer's performance by storing
often-accessed data in extended or expanded memory, thus reducing hard disk
access (which is slower). Unlike dBASE IV version 1.1, in which Hyperdisk
took up all available extended or expanded memory when invoked, dBASE IV
Server Edition provides a DOS environment variable DBCACHE, that can be
used to set the amount of extended or expanded memory that Hyperdisk will
use. If Hyperdisk is enabled and DBCACHE is not set, a default of 500K of
extended or expanded memory will be used by Hyperdisk.

Increased Array Size And Memory Variables

To take advantage of the increased amount of memory available with DOS/16M
technology, the dBASE IV Server Edition now allows for arrays containing a
total of 33,554,431 elements. Similarily, the limits for memory variables
have also been increased. The maximum number of memory variables that can
be declared is 33,553,408.

Microsoft Windows 3.0

The dBASE IV Server Edition can be executed as a character-based
application under all modes of Microsoft Windows 3.0, real, standard, and
386 enhanced. However, dBASE IV Server Edition does not support Windows
3.0 multi-tasking (it cannot be run in the background).

Multi-User Control

Current versions of the dBASE product line uses a third-party mechanism
(SuperLok) to control multi-user access. With the dBASE IV Server Edition,
a new internally developed mechanism for controlling multi-user access to
dBASE IV has been added to replace the SuperLok mechanism. This mechanism
is the new CONTROLPATH parameter which can be set within the CONFIG.DB
file. With CONTROLPATH, users can execute dBASE IV Server Edition on a
local workstation, but still be able to run in multi-user mode accessing
shared files on a file server or database server. To users familiar with
the current versions of dBASE IV on a network, this means no more
\DBNETCTl.300 directory to deal with. This new mechanism greatly enhances
performance and makes installation of multi-user dBASE IV Server Edition
easier..

dBASE System Administration Facility (dSAF)

The dBASE System Administration Facility (dSAF) is a dBASE SQL program that
is included which allows you to manage, configure, and monitor database
servers from any dBASE IV Server Edition client on the network. dSAF is
written in the dBASE SQL programming language and the source-code is
provided. It offers easy access to system administration features through
a menu-driven interface consistent with the dBASE IV Control Center.

CHECKSRV Utility

Included with the dBASE IV Server Edition is a utility, CHECKSRV.EXE, which
allows for the testing of a workstation's ability to connect successfully
to a database server. You can run this utility from the DOS prompt or
using the RUN/! command from the dBASE IV or SQL dot prompt.

System Requirements

Here is a list of the following requirements for the dBASE IV Server
Edition:

Availability

The dBASE IV Server Edition will be available in two versions, one being a
three-user version and the second one being a twenty-user version. Unlike
dBASE IV version 1.1, there is no LAN Pack available for dBASE IV Server
Edition. The dBASE IV LAN Pack Access disks do not work with the dBASE IV
Server Edition due to new multi-user access control mechanism. If you
wish to increase the number of users that can access dBASE IV Server
Edition on a LAN, you need to purchase an additional copy, then use the
ADDUSER4 utility to increase the user count. Users of any dBASE product
will be able to upgrade to the dBASE IV Server Edition, please contact the
Ashton-Tate Customer Service department at
(213) 329-9989 for upgrade information.

dBASE IV Server Edition Runtime

Taking a departure from the dBASE IV version 1.1 Developer's Edition, the
dBASE IV Server Edition will not contain a Runtime version of the product.
However, the dBASE IV Server Edition Runtime will be available as a
separate package. The dBASE IV Server Edition Runtime provides a
royalty-free unlimited license that allows for the distribution of
applications that operate in the stand-alone, file-server, and
client/server environmentregardless of the number of users served. Also
unlike the dBASE IV version 1.1 Developer's Edition, the dBASE IV Server
Edition Runtime will not contain the BUILD utility. Ashton-Tate recommends
that you compile your application by either running the entire application
or using the COMPILE command to compile your programs, then using the
DBLINK utility to link your .DBOs into one composite .DBO module. The
dBASE IV Server Edition will contain everything necessary to build
applications including the DBLINK utility, and the Template Language
compiler.

Future Releases

By the time you read this article, Ashton-Tate will have announced the
dBASE IV Server Edition future support for other database servers.
Provided you use dBASE SQL for your application development and not take
advantage of the native database server features (SENDSQL), this means that
your applications will require no modification when moving your
applications over to the new database servers.

Other announcements should include the next major release of the dBASE IV
Server Edition that will include support of the dBASE IV Control Center
access to database server data.

Conclusion

In conclusion, this first release of the dBASE IV Server Edition is
essentially aimed at developers wishing to create applications that will
take advantage of the client/server architecture.




4 Q&A

Q&A
Limited Access

I like having my users use the Control Center but I don't want them
accidentally modifying my Queries, Forms, and Reports. Is there an easy
way to restrict access to these files but still be able to select them from
different catalogs and use them?

Yes. When you select "Add a File to the Catalog", select the object files
(.QBO, .FMO, .FRO) instead of the normal extensions (.QBE, .FMT, .FRM).

Groups for the Aging
I am trying to create an aging report for accounts payable, grouping every
30 days. What is my Group On Expression?

Assuming your date field is called Due_Date :
INT(Date() - Due_Date / 30)
This will take the INTeger portion of Today's Date [Date()] minus the
Due_Date. For example, if today is 07/30/91 and Due_Date is 06/15/91, then
DATE() - Due_Date [07/30/91 - 06/15/91] = 45. 45/30 = 1.5. INT(1.5) = 1.
The INTeger portion of everything from yesterday to 29 days before today
divided by 30 will be 0, 30 to 59 days old / 30 will be 1. Make sure you
have indexed by Due_Date.

Strangers in a Crowd
How do you process totally un-related records as a group? The only
grouping criteria you know is the record numbers. Is there a way to do
this?

Supposing you wanted records 5, 6, 22, 100 to be processed as a group for
your report:

REPORT FORM MyReport FOR STR(RECNO(), 4) $ " 5, 6, 22, 100"

Keep in mind that you must remember to pad all the specified record numbers
with spaces to make them all the same length as specified in the second
argument of the STR() funtion.

Double Vision
In the QBE, how do you display ONLY the records that have multiple
occurences, and not select those that occur once? For example, I have a
database with the following sample records (INDEXed on Salesman) :

Salesman Sale_Date Sale_Amt
Adams 06/01/91 100.00
Carter 06/12/91 50.00
Edwards 06/04/91 75.00
Edwards 06/15/91 50.00
Edwards 06/23/91 45.00
Jones 06/05/91 50.00
Bishop 06/09/91 5.00
Bishop 07/02/91 1.00

Since I only want to see my salespeople who have made multiple sales (not
the ones who got lucky or sold to their relatives), my output should show,
in this example, only the records for "Edwards" and "Bishop".

Add a field to your database, say we call it Counter. For illustration
purposes, let's call your database "Sales".

.Replace all Counter with 1
. *(the number 1)
.TOTAL ON Salesman to Temp
. *(Temporary File)

Create a query with both Sales.DBF and Temp.DBF. Make sure the Include
Indexes option is set to YES. In the figure at right, Temp-> references
the related file. Suppose you wanted only the records for salesman who
made 3 or more sales. Simply change the "> 1" to "> 2" (or ">= 3").

Appending Dates
I am trying to append records from my text file (SDF) format and my dates
are coming out all wrong. My data and structure are as follows:

SMITH JOHN 07/03/91
JONES JIM 08/01/91

LAST NAME C 20 0 N
FIRST NAME C 20 0 N
DATE INPUT D 8 0 N

What I get when I list the file is :

SMITH JOHN 3//91//0
JONES JIM 1//91//0

What is going on?

In dBASE IV, dates are stored internally as YYYYMMDD. Therefore, 08/01/91
is really stored internally as 19910801. Your attempt to read an already
formatted date into a date field is resulting in the incorrect data in your
date field. If the data you have to import cannot be recreated to produce
dates in the YYYYMMDD format, change your database structure so that the
field to accept the date is character. The formatted dates will import as
a string correctly. Then, after the APPEND FROM operation is complete,
MODIFY STRUCTURE and change the data type to date. The conversion will be
done automatically when you save the new structure.


5 Increasing the Performance of dBASE IV version 1.1

Increasing the Performance of dBASE IV version 1.1

dBASE IV version 1.1 is conservative by default, and leans towards ease of
use at the expense of speed optimization. As you become increasingly
comfortable with the software, you will probably want to implement more
sophisticated methods of using dBASE IV which result in greatly improved
performance.

Optomizing your Environment

As you may already know; the 1.1 version of dBASE IV utilizes the extended
or expanded memory above 640K in your computer to retain frequently read
data in memory. When properly installed; the dBCACHE or HYPERDISK greatly
improves the speed of dBASE IV. Please refer to the 1.1 Release Edition of
the TechNotes magazine that came with the dBASE IV package for more
information on dBCACHE. dBASE IV will also utilize other caching software
you may want to use other than dBCACHE.

There is a new DOS environmental variable, called DBHEAP, which controls
the size of the memory allocated to the dBASE IV overlay (.OVL) file
versus the memory allocated to a custom application for its variables. To
set DBHEAP, go to any DOS prompt and type SET DBHEAP=. The default
is 50 and you can experiment with it by setting it first to a low value
like 5, then to a high value like 97. Restart dBASE IV between each test.
In both cases, time the processing speed of your application or typical
operations to verify which is more efficient; then fine-tune the DBHEAP
value. When you encounter an ideal setting; you may want to include DBHEAP
in your AUTOEXEC.BAT or a batch file.

Be sure to read page 16 of the 1.1 Release Edition of TechNotes. It
contains many tips on optimizing performance.

Free up as much of DOS 640K memory as possible. Get rid of the file
ANSI.SYS if you are not using it. If you are using DOS 4.0 or 4.01, remove
the FASTOPEN, APPEND, and SHELL commands in your AUTOEXEC.BAT and
CONFIG.SYS. Also, remove all but the essential drivers in your
CONFIG.SYS. Eliminate the use of TSR or memory resident programs; they can
eat up a great deal of your RAM. Additionally; most TSRs wait for a
hot-key, and waste time evaluating every keystroke in order to determine if
it is the hot-key combination. If you must have TSRs, try to load them
into high-memory.

dBASE IV creates and writes to temporary files during each session. These
files are usually zeroed out and deleted after exiting dBASE IV. By setting
an environmental variable called DBTMP in your AUTOEXEC.BAT these files can
be routed to a specific subdirectory; for example: SET DBTMP=C:\TEMP If
you set DBTMP to a RAM disk; dBASE IV should execute faster.

Keep in mind that dBASE IV is a disk-intensive application; thus the CPU
speed of your computer is often times not as important as the speed of your
harddisk and controller. For example: as you upgrade your computer,
installing a 1:1 controller card and a harddisk with a 23 millisecond
access speed may improve performance much more than installing a math
co-processor or increasing the RAM.

Speed at the Control Center

One of the most popular methods of viewing data in a file is through the
BROWSE command. However, since viewing data in this manner requires
continual screen refreshing of several records, a speed performance
degredation can be detected when using index tags. Therefore, BROWSE with
the database in natural order whenever possible.

When using QBE; instead of doing multiple sorts on various fields, create
one combined index tag; then toggle the USE INDEXES option to YES in the
FIELDS menu of the QBE, and perform a single sort under such index
expression, which should appear at the far right of your file skeleton.

If you use a query to link files prior to printing a report; you do not
need to write the data out to a new database file. Instead, activate the
query and proceed to print the report directly from the linked files.

Speed in a Program

Ordering by index is always preferable to use of the SORT command which
physically rearranges the order of a records. New users will naturally
gravitate toward this command probably because there is more of a
familiarty with the word SORT as opposed to INDEX.

Minimize, or avoid altogether, the use of SET FILTER in your program. Use
a WHILE clause in conjunction with SEEK or FIND in lieu of SET FILTER for
reports or labels. You could also create an index tag and give it a FOR
clause which matches your filter condition. Then SET ORDER to the
conditional index tag instead. For example, instead of using:

SET FILTER TO State = "CA"
use
SET ORDER TO State

where an index was previously created using INDEX ON FOR State
= "CA" TAG State. This will greatly speed up movement from one record to
another in BROWSE or EDIT mode with the appropriate matching criteria set.

Use WHILE instead of a FOR scope as well. For example, instead of

REPORT FORM Invoice FOR Customer = "Smith"
use
SET ORDER TO Customer
SEEK "Smith"
REPORT FORM Invoice WHILE Customer = "Smith"

This will eliminate dead time before and after print jobs by causing the
program to evaluate and print only those records that match the specified
criteria.

Avoid the use of LOCATE in programs. This command provokes a sequential
search through a file. In a large file, this can be quite time consuming.
Instead, activate an index and use the SEEK command.

Analyze the use of QBE queries in your program. Sometimes, queries utilize
filters and sorts which we already have established make more performance
decrease. It is optimum to link files using the SET RELATION command, and
order the data using indexes. If multi-file relational databases are a
regular part of your routine, it is best to become familiar with the logic
of linking files using the commands SET RELATION and SET SKIP and the
FOUND() function for best performance.

The production MDX file is the file which has the same name as the database
file, with an .MDX extension. Production MDX files are designed to
minimize the maintenance of indexes for Control Center users by always
checking the various tag expressions and FOR clauses each and every time a
change is made to any record. As each record is modified; the appropriate
tags are updated. Since production .MDX files are always opened along
with the database, users don't have to worry about reindexing.

A more efficient use of .MDX files in a turnkey application involves
deleting the production .MDX, then proceeding to use the database; thus
avoiding any reference to the production .MDX. Any necessary index tags
can be created in a non-production .MDX file using the following syntax:

INDEX ON TAG OF

Be sure to name your MDX to something other than the database name. Then,
whenever a change is made to the database that involves data in a field
which is included in a tag expression, issue the following command: SET
INDEX TO . Once the index is set, all tags in the .MDX are
verified and updated automatically.

The power of non-production MDXs, however, resides in the ability to turn
them off. As a programmer, you would know whether or not an operation
affects a field which is part of a crucial indexing routine. Anytime you
are performing operations which do not change the data in a key field, you
should close the .MDX file to tell dBASE IV to skip the TAG and FOR clause
verifications. To do this, issue a SET INDEX TO command. By not
specifying an index file, dBASE IV will close all open indexes, resulting
in faster data manipulation in non-key fields, and faster browsing.

Speed on a Network

Technically, it is correct to assume that an application should run at the
same rate of speed on a network compared to single-user. The multi-user
version of dBASE IV runs at nearly the speed of the single-user version.
This is because the extra operations that are performed on non-exclusive
databases, such as record locking, do not take that much extra time. The
slowdown comes from the extraordinary amount of time it takes to transfer
data and system files over the network cables.

Copy the DBASE.OVL and .RES files to the local hard disk. If the
workstation has extended or expanded memory, configure dBCACHE for the
workstation. Refer to pages 6 and 7 of the README.DOC file in your DBASE
directory for more instructions on Network caching.

It is especially important to reduce the operations which write data to
temporary files, and to optimize your programs to take the most advantage
of indexes. Temporary files are created by default in the DBNETCTL.300
subdirectory. Creating temporary files on the network fileserver can
cause a decrease in performance. You should direct temporary files to a
local harddisk (or better yet, to a RAM disk) by setting the DOS
environmental variable, DBTMP in the AUTOEXEC.BAT file of each workstation
with a command such as

SET DBTMP=C:\TEMP

Network shells are TSRs (Terminate Stay Resident Programs) that take up
more of the RAM of the workstation. dBASE IV requires as much as possible,
so try to load the shells into high memory and remove any other TSRs which
may be consuming precious RAM. For Novell users, obtain the new 3.01
shells. These will free up more DOS memory by loading NET3 in extended or
expanded memory. Check the NetWire forum on CompuServe. You should get
around 530K bytes free on a Novell workstation, as reported by CHKDSK.

Run the fileserver dedicated. This is required by dBASE IV when running
under Novell, but also contributes to improve the speed of other networks.
A non-dedicated fileserver running WordPerfect will not be as quick to
satisfy a dBASE IV request as a dedicated fileserver. It really is a small
price to pay for the increased responsiveness of the fileserver to disk
and NETBIOS requests by dBASE IV.

Make sure the disk system on the fileserver is fast. A 65ms access time
will unnecessarily slow a disk-intensive application like dBASE IV. Also,
ensure that the fileserver has an adequate cache in memory. Novell
recommends 800 cache blocks as reported by FCONSOLE. A Netware fileserver
should have a minimum of 4Mb RAM in order to achieve this.

Speed is greatly affected by the network interface card in each
workstation, and in the server. A 16-bit card is faster than an 8-bit
card. Allocating a DMA channel increases speed. Using 16K RAM on the card
is better than 8K. Try to optimize the topology of your network. Ethernet
runs at 10 Megabits/second. Most Token-Ring and Arcnet systems run at 2.5
Mb/sec. Some other types of networks run much slower. Also, try to reduce
the network traffic. If you have other networks connected to your cable
lines, you may want to install a bridge to filter out all but the data
pertaining to your network.



6 Making More Money

Making More Money
Adam Menkes

What is yield? We're talking about money here, not traffic, so if you're a
normal consumer you may not know what the term means. But if you're a
lender, yield is what keeps your buisness alive.

According to the Random House Dictionary of the English Language, yield is
"the income produced by a financial investment, usually shown as a
percentage of cost." From this quote, you might assume that the yield of a
loan and the interest rate of a loan are the same, but that's really only
true if the payments are based on a loan with no points (otherwise known as
discount points). Points are what the lender charges you for a loan (in
addition to interest), and each point is one percent of the loan amount.

Now, let's make you the lender. Given that you're charging points, the
yield will always be higher than the interest rate because you're charging
interest on money that you didn't loan. Think about this: you make a
$100,000 loan with 2 points. What typically happens is that you're the
points out of the money that you're loaning out. In this case, you're
actually lending $98,000, taking $2,000 in points. But you're charging
interest on the whole $100,000.

Is it better to make a fixed rate loan for 30 years at 91/2% with 2 points,
or a 10% loan with no points? The answer is: it depends on when the loan
is paid off.

For example, if the above loan is paid off in 5 years (60 months:

Original loan amount : $100,000

Discount amount : $98,000 [2 discount points ($2,000)]
Monthly payment : $840.85 [PAYMENT(100000, .095/12, 360)]
Original Term : 360
Payoff Term : 60

Yield(100000, 98000, 840.85, 360, 60) = .8349 (monthly yield)
.8349% * 12 = Annual yield = 10.02%

If the loan goes full term:

Yield(100000, 98000, 840.85, 360, 360) = .8112% (9.73% Annual)

The reason the yield is higher than the interest rate is because the lender
is receiving income from money he never loaned to you.

Taking the above example, we can break yield down into two parts. The first
part is the value of an income stream. The second part is the present value
of money to be received at some future time. This is know as the PVIF
(Present Value In Future) and was discussed briefly in the August 1990
edition of Technotes/dBASE IV (along with the other user-defined functions
needed for yield Balance() & Interest()). Basically, if you were to
receive $10,000 in 5 years (60 months) and could invest your money at 10%
interest, you would need PVIF(10000, .10/12, 60) = $6,077.89. In other
words, if you invested $6,077.89 at 10% interest (compounded monthly), then
in 5 years you will have $10,000.

Determining the Yield

There are several steps needed in order to determine the yield on a loan.
First, the interest rate needs to be determined from the original loan
amount, the payment, and the term of the loan. This formula is not accurate
for interest only loans, as there is no term (it is indefinite) but may be
approximated with a term of 780 (65 years). To simplify the example, we
will first show how the yield is determined if the loan goes full term
(when the balloon amount = 0).

Once the interest rate (original) is determined, it is necessary to
determine the loan balance after a specified period (the balloon term).
The difficult part is determining what combination of cash flows at x%
interest and discount amount invested at x% interest will give the same
payoff amount at the balloon period. In other words, when the present value
of the cash flows plus the present value of the future payoff equals the
discount amount. The problem is there is no direct formula, it must be done
by trial and error.

When the loan goes full term, the yield is the same as the interest on the
discounted loan amount with payments calculated on the original loan
amount. In other words, $98,000 invested at what interest rate will give
payments of $840.85/month for 30 years? Interest(98000, 840.85, 360) =
.8112% = 9.73% annual.

If the loan is paid off in 5 years (60 payments) then the formula is a
little more complex. The yield is higher because the $2,000 in discount
points is received over 5 years instead of 30.

Using the above formula, Yield(100000, 98000, 840.85, 360, 60) = .8349
(10.02% annual). Working backwards to verify the formula, we want to check
that the value of the flows (PV()), 840.85 per month, for 5 years at 10.02%
interest plus the present value of the balance in 5 years with no payments
at 10.02% (PVIF()) equals the discount amount. PV(840.85, .8349%, 60) =
$39,557.69.

The PVIF cannot be determined without knowing what the balance on the loan
will be in 5 years. Balance(100000, 9.5%/12, 360, 60) = $96,241.30. Now
that we have the balance in 5 years, we want to know what amount we need to
invest at 10.02% now to give us $96,241.30 in 5 years without payments.
PVIF(96241.30, .8349, 60) = $58,439.86.

Adding the value of the flows ($39,557.69) with the present value of the
loan balance ($58,439.86) we get a discount amount of $97,997.55 (rounded
to $98,000).

Purchasing Loans at a Discount

Now that you have a basic understanding of how yield is different from the
interest rate, you can see how a lot of money can be made in purchasing
loans (mortgages, notes, trust deeds, bonds, and so on) at a discount. In
addition to the Yield() function, Discount() was also created to help you
determine what to pay for a security instrument in order to yield a given
interest rate. For example, if someone is selling a $10,000, 30-year
amortized note with a 115/8% interest rate (payments of $100 / month) and
you wanted to yield 15%, you would not pay more than Discount(10000, 100,
360, 360, 15%/12) = $7,908.61. Notice that this is the yield of $7,908.61
invested at 15% for 30 years with income of $100 / month. Assuming you
could purchase a $7,908.61 note at 15% for 30 years or you could purchase
the $10,000 note at the discount for the same amount. Is one a better
deal?

Yes, the discounted note is better if the loan pays of any earlier than 30
years. Assuming the discounted note pays off in 5 years, Yield(10000,
7908.61, 100, 360, 60) = 1.5058% (18.07% annual). If you needed a 20%
yield, Discount(10000, 100, 360, 360, 20%/12) = 5984.37. This assumes the
loan goes full term. A lot of notes are written to be amortized over a
period (30 years) but due a lot sooner (5 years for example).
Discount(10000, 100, 360, 60, 20%/12) = $7,390.49.

The Better Deal on Both Sides

Using this information, if you are able to purchase such a note, how can
you improve your yield? Once purchased, the only way to do so is to
shorten the term. You cannot change the terms of a note or demand the
borrower pay it off before it is due, unless the borrower agrees to do so
or he has defaulted on the terms. You can, however, entice him to pay off
the loan early. How can you do this and benefit both the borrower and
yourself? If you offer to pay $1,500 bonus if he refinances the loan, you
receive Yield(10000-1500, 7390.49, 100, 360, 1) = 16.344% (196.13%
annual). The borrower only needs a new loan of $8,500 to pay off your
$10,000 loan. $8,500 at $100 per month for 30 years = Interest(8500, 100,
360) = 13.89% annual. His original note was at 115/8%. As long as he can
refinance less than 13.89%, it pays for him to do so. If he can obtain a
new 11 5/8% loan, his payments will reduce to $85 per month. If he
continues to make payments at $100 month, the 30 year loan will be paid off
in about 15 years. Term(8500, .11625, 100) = 180.

Reducing the Term

Term() determines the number of regular payments needed to pay of a loan of
a given amount, at a given interest rate and regular payment. The lower the
interest rate is, the more of the payment is applied towards principal
reduction.

What Term() does is to evaluate each term from 1 to N to determine how
close it approximates the beginning balance (mPV). When they are the same,
the term has been found. Often, there will not be an exact payoff over a
period, as payments on an amortized loan can only be paid in dollars and
cents (hence the payment is always rounded to 2 decimal places) and there
will almost always be some fractional payment due. Currently, the function
Term() is written to return the term at which less than half of the payment
is owed (mPayment / 2.), but can be changed to any figure you wish (such as
< 1/5 payment is due : change to mPayment / 5, < $10 is due : 10, and so
on). If you want to know the term when the loan is paid off completely,
change the line which reads :

DO WHILE (mPV - PV(mPayment, mIntRate, Guess) >= ;
PVIF(mPayment / 2, mIntRate, Guess))

to :

DO WHILE (mPV - PV(mPayment, mIntRate, Guess) >= 0

Using the figures in the shaded box on the following page, we can check
Balance(100000, .01, 360, 360) = Remaining Balance = $9.08.
What's Left on the Loan?
If the payment of $1,028.61 were made on the first of every month for 30
years, then you would owe $9.08 on the loan to pay it off at that time.
That $9.08 is future dollars. That means you would need some small amount
of money invested at 12% interest for 30 years. That small amount of money
can be determined by PVIF(9.08, .01, 360) = .2526. If you invested a
quarter for this period of time and interest rate, you will have $8.99.
This can be checked with FVIF(.25, .01, 360) = 8.99. This function may also
be found in the August 1990 edition of Technotes/dBASE IV for dBase IV.

Applying Money to Principal and Interest

Given a balance of $100,000, an interest rate of 12% (1% per month), a term
of 30 years (360 payments), and a monthly payment of $1,028.61 (determined
by PMT(100000, .01, 360), the way to figure how much of your payment is
applied towards principal and interest is as follows:

1.(REMAINING BALANCE) * (INTEREST RATE) = (APPLIED TO
INTEREST)
2.(PAYMENT) - (APPLIED TO INTEREST) = (APPLIED TO PRINCIPAL)
3.(REMAINING BALANCE) - (APPLIED TO PRINCIPAL) = (REMAINING BALANCE)

Payment # 1 : $1,028.61
Interest Due : 100000 (Balance) * .01 (Int. Rate) = $1,000.00
Applied to Principal : $28.61
Ending Balance (month 1) = New Balance (month 2) = $99971.39

Payment # 2 : $1,028.61
Interest Due : 99971.39 (Balance) * .01 (Int. Rate) = $999.71
Applied to Principal : $28.90
Ending Balance (month 2) = New Balance (month 3) = $99942.49

Make More Money Functions
FUNCTION Yield
PARAMETERS mPV, mDiscount, mPayment, mTerm, mTerm2Chk
* Yield(N1, N2, N3, N4, N5) - Calculates the loan yield.

PUBLIC mIntRate, mBalloon, mTop, mBottom, mGuess
mIntRate = Interest( mPV, mPayment, mTerm)
* [? Interest(100000, 840.85, 360) = .7917% = 9.5%]
* Calculate loan balance at balloon period ( mTerm2Chk)

mBalloon = Balance(mPV, mIntRate, mTerm, mTerm2Chk)
* [? Balance(100000,.095/12,360,360) = 0]
* Calculate yield on discount amount and flows

mBottom = 0
mTop = .50
mGuess = (mTop + mBottom) / 2

DO WHILE ABS(mDiscount - (PVIF(mBalloon, mGuess, mTerm2Chk) + ;
PV(mPayment, mGuess, mTerm2Chk))) >= .01

* [98000 - (PVIF(0, mGuess, 360) + PV(840.85, Guess, 360) =
* 98000 - pv(840.85, Guess, 360) >= .01]

* Loop until the discount amount approximates the present
* value. In other words, when the discount amount less the
* pv(payment, guess, term) <.01. This figure (.01) can be set
* to any number for greater accuracy (i.e. .00001) but slower
* performance.


IF PVIF(mBalloon, mGuess, mTerm2Chk) + ;
PV(mPayment, mGuess, mTerm2Chk) < mDiscount

* If the value returned is less than the discount amount,
* the interest rate is too high. Reset the top range.

mTop = mGuess
mGuess = (mGuess + mBottom) / 2

ELSE && Otherwise, reset the bottom range.
mBottom = mGuess
mGuess = (mGuess + mTop) / 2
ENDIF
ENDDO
RELEASE mIntRate, mBalloon, mTop, mBottom
RETURN (mGuess)


FUNCTION Discount
PARAMETERS mPV, mPayment, mTerm, mTerm2Chk, mYldWanted
* Requires PVIF(), Balance(), Interest()
* mPV = Beginning balance (PV), mPayment = Payments
* mTerm = # of Periods, mTerm2Chk = Period to check
* mYldWanted = Yield expected
mBalloon = Balance(mPV, Interest(mPV, mPayment, mTerm), mTerm, mTerm2Chk)
RETURN(PVIF(mBalloon, mYldWanted, mTerm2Chk) + PV(mPayment, mYldWanted, mTerm2Chk))


FUNCTION TERM
PARAMETERS mPV, mIntRate, mPayment
* Term(Beginning Balance, Periodic Interest Rate, Payment)
* Requires PVIF()
* Loop until the present value of the payments approximates
* the beginning balance within a specified amount.
guess = 1
DO WHILE (mPV - PV(mPayment, mIntRate, Guess) >= ;
PVIF(mPayment / 2, mIntRate, Guess))
*To increase speed and to get the term in which the final
*payment is made, change the while loop to the one below.
*DO WHILE mPV - PV(mPayment, mIntRate, Guess) >= 0
guess = guess + 1
ENDDO
RETURN(Guess)


FUNCTION PVIF
PARAMETERS mpayment, mrate, mperiods
mrate = IIF(mrate > 1.00, mrate / 100, IIF(mrate <= 0, 0, mrate))
RETURN (1 / (1 + mrate) ^ mperiods) * mpayment


FUNCTION Balance
PARAMETERS mpv, mrate, mperiods, mballoon
mpayment = ROUND(PAYMENT(mpv,mrate,mperiods), 2)
RETURN mpv * ((1 + mrate) ^ mballoon) - (mpayment * (((1 + mrate)^mballoon)
- 1) / mrate)





7 The ABCs of UDFs Part II

The ABCs of UDFs
(Part II)
Martin Leon

When you start to use UDFs, it becomes very clear that they are very much
like mini-programs. They are designed the same way as a program and have a
.PRG extension like a regular program. But a UDF, by virtue of its design,
can get into places where a program cannot. This versatility spotlights one
of the primary benefits of UDFs: the capability of incorporating a UDF into
a screen, report, label or application.

UDFs in Format Screens

There are two points at which you can have a format screen make a call to a
UDF for every entry field you define: before you are allowed to edit the
field, and before you are allowed to go on to the next field. If you are a
programmer, these correspond to the WHEN and VALID clauses of an @.GET
statement. If you generate custom screens through the Control Center,
these correspond to the Edit Options: Permit Edit If and Accept Value
When. These are two areas where you could "temporarily re-route" control
to your UDF.

When or Permit Edit If

The intended use of the first option is to allow you to determine whether
entry should be allowed into a particular entry field. The expected result
is a return value of a logical TRUE or FALSE (.T. or .F.). If a UDF
returns TRUE (pronounced "Yes" in English), the field may be edited; if it
returns FALSE, the field will be skipped to the next field on the screen.

How can we use this with a UDF? Let's say that instead of the plain method
of multiple choices currently available in a format screen, you want to see
a popup with a list of the available options for the field. Here's an
example of what could be done.

FUNCTION Popchc
esc_key = 27
enter_key = CHR(13)
DEFINE POPUP Choices FROM 5,35
DEFINE BAR 1 OF Choices PROMPT "First choice "
DEFINE BAR 2 OF Choices PROMPT "Second choice"
DEFINE BAR 3 OF Choices PROMPT "Third choice "
ON SELECTION POPUP Choices DEACTIVATE POPUP
ACTIVATE POPUP Choices
IF LASTKEY() <> esc_key
KEYBOARD PROMPT() + enter_key
ENDIF
RELEASE POPUP Popchc
RETURN .T.

This function defines and activates a popup. The popup is defined in such
a way that it deactivates itself as soon as a selection is made. When you
press Enter on one of the choices in the popup, the return value of the
PROMPT() function will return the prompt for that selected item. The
KEYBOARD command puts that prompt expression and a Return into the keyboard
buffer; "typing" it in for us. If you press Esc on the popup, nothing is
typed in to the keyboard buffer, allowing the user to enter their own
value. Notice that we release the popup we define. IT's a good idea to
leave the dBASE IV environment as close to the way we found it when our UDF
started.

You see that all of this extra work can be done for us before obtaining
permission to edit the field. By returning .T. we allow this field to be
edited. It either receives the characters that the UDF typed in for us
with the KEYBOARD command or allows you to type in your own value.

If you are writing your own format screen, you would call this function as
follows:

@ , GET WHEN Popchc()

From the Control Center screen generator, go to the Edit Options menu for
that field and put Popchc() in the Permit Edit If expression area. After
this is implemented, the popup is displayed and we can make our selection
before the cursor is allowed into that field. We could alternatively press
Escape and type in something different.

VALID or Accept Value When

The intended use of the second option (VALID or Accept Value When) is to
"validate" the entry that was made into the field, or to make sure it is
acceptable data for that field. As with the WHEN option, ther is no way
for the program of knowing what we are doing as part of a validation
UDF, all it expects is a return value of .T. or .F.

As an example, let's say we have three fields in a database: Qty, Price, and
Total. To make data entry easier and less prone to error, we want to
multiply the amount entered in the Qty field by the amount entered in the
Price field, add the appropriate amount of tax and to automatically put the
result in the Total field in the entry screen. Here's the UDF that will do
this.

FUNCTION Totfunc
mAmount = Qty * Price
KEYBOARD STR(mAmount + Tax(mAmount, 7), 6, 2)
RETURN .T.

This function uses the STR() function to convert the resulting total in to
a character string. The 6, 2 tell it to make it a six digit string with
two decimal places. You'd need to modify these numbers to match the
definition for the Total field in your database. Notice we use the Tax()
function created earlier to add the tax. This is assuming that the Tax()
function has been made accessible in one of the manners described earlier.
The resulting character string is stuffed into the keyboard buffer.

In our format screen, we'd need to have the Total field physically
positioned immediately after the Price or Qty field. If Total comes after
the Qty field, have the UDF validate the Qty field. If Total comes after
Price, have it validate the Price field. When you put a value in the
latter of the two and press Enter, it will make a call to this UDF. The
product of the Qty and Price is calculated, tax is added and the result
typed into the keyboard buffer. By returning .T., we allow the value that
we just entered to be accepted and we go to the next field. The keys that
are waiting in the buffer will be typed in to this next field, the Total
field.

The arguments for the STR() function are such that the keys typed in by the
KEYBOARD command fill the entire Total field, causing the cursor to go to
the next field if SET CONFIRM is OFF. If SET CONFIRM is ON, we must add an
Enter key. Change the keyboard command to the following:

KEYBOARD STR(mAmount + Tax(mAmount, 7), 6, 2) + CHR(13)

The idea is to accomplish (through KEYBOARD) what a user would do when they
get to the Total field.

In a program, you validate using the VALID clause of a GET:

@ , GET VALID Popchc()

You can also define a message to be displayed when validation for a field
fails or returns .F. In a program, put the keyword ERROR and a character
string to use as the error message after validating the UDF in an @.GET
statement.

@ , GET VALID Popchc() ERROR "Wrong!!!"

From the screen generator, enter the phrase, Wrong!!!, in Edit Options:
Unaccepted message. Here, there is no need to provide quotes.

Something to remember when you use the Accept Value When option of the
screen generator is that by default, if no entry is made into a field, or
if the data in it is not changed, the validating UDF will not be called and
you will be allowed to proceed to the next field. In other words, entry
into a particular field is not required. A programmer can easily make an
entry be required in a particular field by putting the word REQUIRED after
the word VALID in the @.GET statement.

@ , GET VALID REQUIRED Totfunc()

If you use the Control Center to create screens, you have two choices: make
all validation on every field required, or don't make any of them required.
To make them all required, add the following line to your AUTOEXEC.BAT:

SET DTL_REQ=ON

If you don't have the Maintenance Release of dBASE IV version 1.1, you will
not have the option of making all VALID statements required.

By default, without this environment variable set, none of the Accept Value
When conditions you define will be required. If you do set it, all @.GET
statements using the VALID caluse will be required on every format screen
you create or modify.

Changing Information on the Screen

If your UDF puts information on the screen anywhere along the way, it's
important that you don't inadvertently leave any information on the screen
when the UDF is done. Both of the examples above (Totfunc() and Popchc())
are relatively simple, even though they both add information to the screen.
Popchc() uses a popup, which will clean up after itself. A popup is placed
on top of whatever information is there. When removed from the screen, it
restores any screen information it had previously overwritten. Totfunc()
just types in the resulting total for us. In a more complex UDF that does
screen updates, you must take care to restore the screen if you have
overwritten part of it.

Here's a more complex example of a UDF you might like to add to your format
screens (use it with the WHEN clause or Permit Edit If):

FUNCTION Popmsg
PARAMETER Msg
SAVE SCREEN TO Tmpscreen
mRow = ROW()
mCol = COL()
@ mRow + 1, 5 CLEAR TO mRow + 3, 74
@ mRow + 1, 5 TO mRow + 3, 74 DOUBLE
@ mRow + 2, 40 - ( LEN( Msg ) / 2 ) SAY Msg
@ mRow, mCol + 1 SAY ""
KEYBOARD CHR( INKEY( 0 ) )
RESTORE SCREEN FROM Tmpscreen
RETURN .T.

This UDF will put a box on the screen just underneath the current entry
field with a message centered inside of it. The content of the message is
passed as a character string argument to the function. This allows you to
use the same function with any of the fields on your entry screen. You
just change the argument as needed:

Popmsg( "This field must not be left blank" )

or

Popmsg( "Enter the total amount of compounded interest" )

Notice that since we overwrite a portion of the screen, we must take care
to save the screen image prior to making the change (SAVE SCREEN TO.), and
then restore the original screen image prior to exiting the UDF (RESTORE
SCREEN FROM .).

Moving the Record Pointer

Another consideration when writing format screen UDFs is that, in most
cases, you are sitting on a particular record in your database when you
edit (programmers will often work with the contents of a record indirectly,
through the use of memory variables). If you write a UDF that moves the
record pointer, you must remember to have your function put the record
pointer back before the RETURN command.

Here's a very common example. You want to make a format screen and there
is a particular field in your database for which you want to make sure
there are no duplicate entries. The most efficient way to accomplish this
is by having an index on the field in which you wish to prevent duplicate
entries, and have your UDF perform a SEEK with the value entered by the
user. Whenever you perform a SEEK on an indexed database, the record
pointer is always moved, regardless of whether there is a match or not.
Here's one way to deal with this.

FUNCTION Notdupe
PARAMETER Makevalid, Tagname
* Store record number and currently active index to memory variables.
rec_was = RECNO()
order_was = ORDER()
* Set order to index tag specified by second argument.
SET ORDER TO ( TagName )
* SEEK "proposed" value in the "copy" database.
* Notdupe = .T. if it was not found.
notdupe = .NOT. SEEK(makevalid)
IF .NOT. ntodupe .AND. RECNO() = rec_was
notdupe = .T.
ENDIF
* Put every thing back.
SET ORDER TO (order_was)
GO rec_was
RETURN notdupe

Notice that the current record number and currently active index tag are
stored to memory variables before the record pointer is moved and then are
restored after the SEEK() function does it's work. Since we tell the UDF
what index tag to use for the SEEK(), we are able to use this UDF with any
field for which we wish to prevent duplicates. To call this function:

Notdupe( , "" )

How about a slightly different situation. Suppose you want to make it so
that the value entered can only be one that matches an existing value in a
different database. For example, you have a field in which you are
entering codes and before accepting a code into a record, you need to make
sure it is a code that already exists in a .DBF that has all your valid
codes.

FUNCTION Isfound
PARAMETER makevalid, DBFname, tagname
* Store work area number of highest unused work area to memory variable
workarea = STR(SELECT(), 2)

* Use database to check against in highest unused work area.
* Set to index tag specified by second argument.
USE (DBFname) AGAIN IN SELECT() ORDER (TagName)

* SEEK "proposed" value in the other database.
* Isfound = .T. if it was found
isfound = SEEK(makevalid, VAL(workarea))

* Close other database.
USE IN VAL(workarea)
RETURN isfound

The database and tag to use are passed as parameters. The appropriate
database is opened in an unused work area and set to the specified index
tag. Then we set the logical memory variable isfound to what the SEEK()
function returns. If the value is found in the specified index tag,
isfound is set to .T., and that is what is returned to the VALID clause,
allowing the value to be accepted. Note that before returning, the other
database is closed, restoring the environment to its state before the UDF
was called. The syntax would be:

Isfound(, "", "")

For a more complicated example of the type of validation that can be done
by a UDF in a format screen, look at the README.DOC file that is installed
into your dBASEr directory. On pages four and five it describes how to use
the screen generator to bring up a popup of valid choices when an invalid
entry has been made into a field.

UDFs in Reports

A report is nothing more than a program created by the dBASE IV Report
Generator. Although this program is, in part, determined by what you put
in your report layout, there is a basic sequence that all reports follow.
As such, it is helpful (and in many cases, necessary) to know something
about the flow of the report program before adding your UDFs.

Name that Band

Depending on how it was called, the report will either start at the top of
the current database or at the current record. From there it will proceed
through the database until it gets to the end of the file or to the last
record specified by a NEXT, FOR or WHILE scope.

So when you start the report, it is sitting on the first record that is
going to be printed. If you were to add any of the fields from your
database to the Page Header band or the Report Intro band, they would print
using the values from the very first record. The Bands: Page Heading in
report intro option determines whether the Report Intro or the Page header
is printed first. The contents of the Report Intro band are only printed
once. The contents of the Page Header band are printed at the top of every
new page. Neither of these bands move the record pointer of the affiliated
database or view.

Group bands allow you to do work with groups of records that have something
in common. You may simply want each group to start on a new page, or you
may want to count all the records by group, or sum a certain numeric field
for all the records in a group. When you create a group band, the report
program evaluates the expression or field you are grouping on to see if it
yields the same value as the previous record. If it doesn't, the program
determines it's reached a new group. This is why, in most situations, you
need to create an index on the same expression you are grouping on to make
sure that all related records are "next" to each other logically or grouped
together physically in the database. A Group Intro and a Group Summary
band are added for each group that you create. Neither of these bands
cause the record pointer to be moved.

If you are doing any grouping in your report, the Group Intro bands are
printed at the beginning of the report after the Page Header or Report
Intro. From then on, the contents of a group intro band are only printed
when a new group is reached. As far as progression through the database,
the record pointer is sitting at the first record in the new group and is
not moved after this band is printed.

The contents of the Detail band are printed for every single record in the
current database or view. After this band is printed, the record pointer
is moved to the next record in line. This is the only band where the
record pointer is moved after it is printed. If you've defined a group,
the report program keeps printing the Detail Band until it gets to a record
that doesn't meet the group criteria.

The Group Summary band is printed at the end of a group. Actually, it's
really being printed at the beginning of the next group. The Detail band
advanced the record pointer and the report program knows that we are about
to start processing a new group of records. But before doing so, the
results of any summary operations for the previous bands must be printed.
That's what the Group Summary bands do.

The Report Footer band is printed at the bottom of each page. At a certain
point before reaching the bottom of the page, its contents are printed and
a new page is then started. It's always the last thing to be printed on
the page.

When the last record is reached and all previous bands have been printed
(except the Page Footer), any summary operations that are to be calculated
for the entire report are calculated and printed in the Report Summary
band.

As you can see, there are several places to add UDFs to a report. The way
to incorporate them is by calling the function through a calculated field
in your report. Calculated fields provide a way of displaying information
that isn't actually stored in your database. They are a very useful tool
for extracting more information than what is actually in your database.
They can also be used to add control to the report program. You can put a
calculated field anywhere in your report layout. There are six types of
calculated fields in which you can call your UDFs:

Named calculated fields
Named hidden calculated fields
Un-named calculated fields
Named summary fields
Named hidden summary fields
Un-named summary fields

When you define a calculated field you can leave it's name blank. There is
a big difference in when your function is evaluated, depending on whether
it is named, hidden, or un-named.

A Field By Any Other Name

If you leave the name blank for a calculated field, you have what is
uncannily referred to as an un-named calculated field. (What, you were
expecting some Latin term?) The way these get incorporated into the report
program code by the report generator is through the ? command. The
expression for an unnamed calculated field is evaluated at the exact moment
it is to be printed.

Although you can leave the name of a calculated field blank, there may be
times where one calculated field depends on the result of another. In
order to allow one calculated field to reference another, you must provide
a name for it to be referenced by. Named calculated fields in your report
layout become memory variables in your report program. The value they are
assigned is determined by the expression you use for the calculated field.
One drawback about named calculated fields is that they are only evaluated
or updated at a very specific point in the flow of the report program.
They are all assigned their initial values before anything is printed.
From that point on, they are only updated once before each new detail band
is printed.

The place where having calculated fields, named or un-named, becomes a
problem is in Group intro and Group summary bands when the calculation
depends on information from the database. You see, the memory variable
that contains the result of the calculation is only updated when you get to
the Detail band. The value for any named calculated field in the Group
Intro band is assigned an initial value by the report program that is

dependent on the first record to be printed, since that's where the record
pointer is sitting. The Group Intro is printed and the report moves on to
the detail band. All records that have the same group expression are
printed and the report moves on to the Group Summary band.

At this point, the record pointer is sitting on the first record from the
next group. If you use a named calculated field to display the result of a
calculation that is dependent on the database, it will display the result
based on the value from the last record in the group that was printed,
which is what you would expect. However, if you make it an un-named
calculated field and reference a field in the database as part of the
calculation, it will use the first record from the next group to determine
the result because that's where the record pointer is sitting. In other
words, any un-named calculated fields in a Group Summary band may appear to
"jump ahead" and show you a value from the next group.

Now the report moves on to the Group Intro band again, to process the next
group. But the values for named calculated fields haven't been updated yet
to reflect the fact that we are in the next group because we haven't hit
the Detail Band yet. In other words, any named calculated fields used in
the Group Intro band will appear to "lag behind" and show you the value
from the previous group.

Why is all of this important for UDFs? Because you will not get the
results you expect if you aren't aware this is happening and you will tear
out your hair trying to figure it out. It's good to know this ahead of
time so you don't spend time trying to "debug" a UDF that doesn't really
have a bug. And, if your UDF is getting information from the database while
the report is running, you should know at what points it is getting that
information.

Now for an example of a UDF. The following UDF is for when you have a
database with potentially many duplicate records already in it that you
want to mark in some way so that you can go back later and do something
with them, such as delete or modify them.

FUNCTION Dupemark
PARAMETER dupexprsn
IF EOF()
RETURN " "
ENDIF
x = &dupexprsn
SKIP
IF EOF()
SKIP -1
RETURN " "
ENDIF
IF x = &dupexprsn
SKIP -1
RETURN "Y"
ENDIF
SKIP -1
RETURN " "

The basic idea is to have the UDF advance the record pointer to the next
record, check to see if the parameter passed evaluates the same for the
next record or not, then put the record pointer back where it was when we
started the UDF. In order for this UDF to work, your database must be
ordered by the expression you are using as an argument to Dupemark() (in
other words, create an index tag with the same expression but not bound by
quotes).

Since we use macro substitution on the character string argument passed to
the function, the expression used to determine whether a record is a
duplicate of the next record or not can be more than just a field name.
For example, suppose you want the function to check that both the Lastname
and Firstname fields are identical before reporting that it found a
duplicate, you could put

Dupemark( "Lastname + Firstname" )

as the expression for a calculated field in your detail band. Since it's
in the Detail band, it doesn't really matter whether we give it a name or
not in this case. The function expands the variable dupexprsn into part of
a command and stores the value of Lastname + Firstname from the current
record in the database to the memory variable x. Then it skips to the next
record (if it's not already at the end of file), checks to see if the next
record is at end of file, and if not, checks whether the Lastname +
Firstname equals the value stored in x. If it does, it returns the letter
"Y". It could return any character or value you like; for this example we
will use "Y".

Notice that all other RETURNs out of this function return a space. Every
RETURN in a function must return some value, so we use a space as a return
when no match is found or when an end of file condition is encountered
(again, you could return any value or character you like). In the case of
a report, if your UDF is going to return a character string, it's a good
idea to make sure that every character string returned is of the same
length in order to maintain alignment of the report regardless of the
returned value. Also note that the UDF keeps checking for EOF(). This is
because the report program moves the record pointer and because the UDF
also moves the record pointer. We want to make sure we don't go past the
last record in the database, or an error condition will occur.

To put this in to your report layout, in your Page Header band you might
have heading descriptions that would look something like:

Record number Last Name First Name Duplicate?

and in your the Detail Band have field markers that look something like:

999 XXXXXXXXXXXXXX XXXXXXXXXXXXXXXX X

For record number, you'd use the predefined RECNO field; for the first and
last name, you'd add the fields from the database; for the duplicate field,
you'd have a calculated field with the expression above. Then you'd get a
list of all the records showing the record number, last name, first name,
and a "Y" for all the records where the next record has the exact same last
and first name. In order for this UDF to work properly, your database must
be indexed on last and first name.

As for adding control to the report program, here's an example. Many
people have a problem where they don't want the contents of a record to be
split across a page. Let's say you have a memo with the vertical stretch
option turned on. You want the entire memo to print and not be split
across two pages. The Report generator currently doesn't support this type
of functionality. If you're a developer and are versed in Template
Language, you might be able to whip up a different REPORT.GEN that works
this way. But for the non-programming masses, here's a simpler solution:

FUNCTION MemoWido
PARAMETER maxlines
IF _plineno + maxlines > _plength
EJECT PAGE
ENDIF
RETURN ""

This function requires a parameter indicating the maximum number of lines
that you expect your memo to stretch out to. Then it takes that number and
finds out how many lines are left on the page. If additional lines will
fit on the page, it returns a null, effectively doing nothing. If the
additional lines won't fit, it issues an EJECT PAGE command, causing the
report program to start on the next page. For example, if you expect that
your memo will stretch out to five lines maximum, place the following in
the expression area for a named hidden calculated field positioned in the
detail band, prior to the memo:

MemoWido(5)

This will cause this function to be called, asking it the question "Can I
squeeze an additional five lines on the current page?" Instead of
answering with "Y" or "N" or TRUE or FALSE, the UDF just makes sure that
you can put the additional five lines on the page or causes the report to
start another page.

UDFs In Queries

You may have guessed by now that you add UDFs to queries through the use of
calculated fields. After adding a Calculated Field skeleton to your layout
through the Fields: Create calculated field option, you just add the
expression for the UDF and add that calculated field to your View skeleton
with the F5 key. When you process the query, you will see the result of
the calculation for each record. The values are calculated as the BROWSE
screen is painted and also as you move from field to field and record to
record. So, you can see the result of the calculated field change if your
query isn't ReadOnly and if you modify one of the fields that the UDF in
the calculated field uses.

As an example, Figure 1 shows how we can use the Tax() function mentioned
earlier:

If you were to process this query you would see two columns, one for the
Sale field, and one for the amount of tax due on the amount of Sale. In
this simple example you wouldn't end up with a ReadOnly view and if you
modified the amount of sale for a particular record, the value of taxamt
would reflect the change as soon as you finished making it.

You could even put the Dupemark() function described previously in a
calculated field in a query and work with the data "live", dealing with
duplicates as you see fit. Figure 2 illustrates this.

However, don't try to sort on or filter using the Dupe calculated field
because Dupemark() moves the record pointer. You could see the same
results from the dot prompt or from a program by using the SET FIELDS
command. For the first example above:

SET FIELDS TO Sale, Taxamt=Tax(Sale, 7)
BROWSE

For the second example:

SET FIELDS TO Lastname, Firstname, Dupe = Dupemark("Lastname + Firstname")
BROWSE

The command, SET FIELDS TO, clears the fields list, removing all calculated
fields and bringing all of the fields from the database back in to view.
Also, if you just wanted a quick listing but didn't want to create a
report, you could:

LIST Sale, Tax( Sale, 7 ) TO PRINTER

or

LIST Lastname, Firstname, Dupemark("Lastname + Firstname") TO PRINTER

The LIST command automatically displays the record numbers. If you want to
suppress them, use the OFF clause of the LIST command,

LIST OFF Lastname, Firstname, Dupemark("Lastname + Firstname") TO PRINTER

And of course, you can always look at the result of your UDF by using the ?
command from the dot prompt:

? Tax(10000, 7)

Parameter Checking and Debugging

You can add parameter checking to your UDF by using the TYPE() function on
each of the parameters. The TYPE() function returns a "C" for character
values, "D" for date values, and "L" for logical values. Suppose your
argument expects three character arguments:

FUNCTION Mine
PARAMETERS Param1, Param2, Param3
IF TYPE("Param1") + TYPE("Param2") + TYPE("Param3") <> "CCC"
CLEAR
@ 5,5 SAY "Parameters must all be character values "
?? INKEY(0)
RETURN ""
ENDIF
*rest of your UDF
RETURN mResult

To debug a function without changing it in to a procedure, create a
temporary program that calls the function and then debug the program. The
program (excluding any comments) consists essentially of one line.

*TEMP.PRG
? DBFSize()
*End of TEST.PRG

Then from the dot prompt, enter

DEBUG Temp

When you step through this single command in Test, you will see the
debugger bring up the code for the UDF, allowing you to debug it.

As you can see, there's a lot than can be done through UDFs. You can use
UDFs anywhere that you can use a dBASE IV function (remembering the
limitations and warnings discussed in this article). You can extend the
dBASE language and alter the performance of the various dBASE objects you
work with every day.

Warnings, Suggestions and Advanced Pointers
Below is a list of warnings and suggestions that were strewn throughout the
article and some that may not have come up.

If your UDF moves the record pointer, put it back. Use the RECNO()
function to store the current record number to a memory variable and then
the GO command followed by the same memory variable to put it back:

FUNCTION Mine
PARAMETER Param1
rec_was = RECNO()
* the rest of your UDF
GO rec_was
RETURN mResult

If it alters the information on the screen, don't forget to SAVE SCREEN at
the beginning and RESTORE SCREEN at the end of your UDF:
FUNCTION Mine
PARAMETER Param1
SAVE SCREEN TO Tempscrn
* the rest of your UDF
RESTORE SCREEN FROM Tempscrn
RETURN mResult

If your UDF moves the record pointer, it will not be usable in commands
using the FOR or WHILE clause.

If you move the record pointer in a UDF called by any dBASE command that
moves the record pointer, check for end of file conditions.

Avoid division by 0. If your UDF sends back the result of a division,
don't let it divide by 0. Use an IIF() function to avoid the division is
the divisor is 0:

FUNCTION Mine
PARAMETERS Param1, Param2
RETURN IIF( Param2 = 0, 0, Param1 / Param2 )

If your UDF changes the status of any of the SET options, put them back.
Use the SET() function to check the status of particular setting and store
it to a character variable:

FUNCTION Mine
PARAMETER Param1
talk_was = SET( "TALK" )
SET TALK OFF
* the rest of your UDF
SET TALK &talk_was
RETURN mResult

To suppress the results of all the work your UDF is doing from showing up
on the screen, SET TALK OFF before you call it. Otherwise, you'll see all
the results of each calculation and command as the UDF executes them.

To use a UDF in an index expression, SET DBTRAP OFF. The problem is that
DBTRAP must be set off whenever you want to use that database:

SET DBTRAP OFF
INDEX ON TAX(Sale, 7) TAG Taxtag

Do NOT use UDFs that move the record pointer in index expressions!

You can call your UDFs through ON KEY LABEL commands as follows:

ON KEY LABEL F5 ?? MyFunc()

When you don't want a function to return anything, return a null string
("").