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:
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 ("").