Document #760
==========================================================================
Enter R:BASE Data Through a WordPerfect Form
==========================================================================
PRODUCT: R:BASE VERSION: 3.1 or Higher
==========================================================================
CATALOG: Programming AREA : Programming
==========================================================================
Patti Dauphin & Nelson Harrison, Systems Officers, Ministry of Community
and Social Services, Southwestern Regional Centre, R.R. #1, Blenheim,
Ontario Canada N0P1A0 You can reach them at (519) 676-5431 extension
2507/2513.
The Southwestern Regional Centre is a part of the Ministry of
Community and Social Services of the Ontario government. It is a
facility dealing with approximately 600 physically disabled clients.
We use R:BASE 4.5 to store data about routine/preventative maintenance
of our physical plant operations. Currently, there are paper forms
which are used for data input. To reduce data entry steps, we
explored the possibility of using WordPerfect as a data entry tool.
Using WordPerfect for data entry eliminates the need to hand-write a
requisition form and the data entry assigned to one clerk is reduced
significantly. It also reduced software costs as we did not need to
buy additional units of R:BASE.
A user enters all the information needed for a routine maintenance
request into a table in WordPerfect. This information is then
converted into an ASCII file and stored in a special directory on the
network. A menu option in our R:BASE application transfers the ASCII
file information into the database.
Some programming is needed on both sides. There are two WordPerfect
macros used and two R:BASE command files.
The WordPerfect side
Create a table in WordPerfect to allow data entry of all necessary
information. In this table, put a lock on the fields which have field
name identifiers. For data fields, place underscores to indicate the
maximum length of a field. The underscores will be stripped off in
R:BASE.
At the top of the WordPerfect document, type in a computer identifier
so that it is known exactly from what computer the data originated.
This identifier must not be longer than three characters. This will
differentiate file names created at the same time.
**** screen shot of the WordPerfect form/table
The first WordPerfect macro, named repair.wpm, is designed to:
SYMBOL 183 \f "Symbol" \s 10 \h retrieve the document
SYMBOL 183 \f "Symbol" \s 10 \h give the document a filename
SYMBOL 183 \f "Symbol" \s 10 \h call the second macro
SYMBOL 183 \f "Symbol" \s 10 \h save the document as ASCII.
Ensure there is a Pause Key in the macro that when pressed, will nest
into the next macro.
Since the information collected from the WordPerfect table is being
saved on a network drive, file names had to be unique. Include the
following steps in the macro to generate unique file names. If you
save the generated ASCII file to diskette you won't need to do these
steps.
1. Assign Document 2 a file name by inserting the time-stamp
immediately following the computer identifier in document 1, e.g.
6113:15, where 61 is the computer number and 13:15 is the time.
2. The ":" within the file name cannot be saved as part of a filename.
In the macro, backspace to the ":" and replace it with another
character, e.g. "_".
3. Block the filename and assign the block as a variable.
4. Save the file (document 2) as DOS text with the variable filename
and add an extension. We use QQQ as the extension.
Here's what our first macro, repair.wpm, looks like:
File C:\WP51\REPAIR.WPM
Description repair requisition
{DISPLAY OFF}{Typeover}
{Setup}42%2/%1/5{Enter}{Exit}
{Retrieve}repair
{Down}{Down}{Down}{Down}{Down}{Down}{Down}
{Date/Outline}1
{Setup}423ú1,ú4{Enter}{Exit}
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Enter}~
{Tab}{PAUSE KEY}{Exit}~
{Home}{Home}{Up}
{End}{Date/Outline}37:9{Enter}1
{Tab}{PAUSE KEY}{Exit}~
{Home}{Home}{Up}
{End}{Date/Outline}37:9{Enter}1
{End}{Left}{Left}{Left}_{End}
{Block}{Word Left}
{Macro Commands}31{Enter}
{NEST}copydata~
{Typeover}{Switch}
{Text In/Out}11C:\WP51\{VAR 1}.QQQ{Enter}
{Exit}ny
{NEST}printdoc~
{;}{Print}2~
{Exit}nn
{Shell}2senddata.bat{Enter}~
{Exit}nn
The second macro, copydata.wpm, is designed to copy the data from the
original document, document 1, into document 2. It is called from the
first macro.
When copying the data to document 2, ensure the proper delimiters and
commas are included. In order to transfer ASCII data into R:BASE, all
the data must fit on one line in Document 2. No wrap around can occur.
Within the macro, format document 2 so that a smaller pitch can be
used. If the data still will not fit, use a different paper size i.e.
14 x 8.5". This allows more data to fit on one line. Remember to use
a pitch common to all printers. This is what our second macro,
copydata.wpm, looks like:
File C:\WP51\COPYDATA.WPM
Description copies to doc2
{DISPLAY OFF}
{Switch}{Format}170{Enter}0{Enter}{Cancel}
27nREPAIR{Enter}1{Exit}{Font}4Nprestige 20cpi{Enter}1
"{Switch}{Home}{Right}{Block}{Word
Left}{Move}12{Switch}{Enter}{End}", {Switch}
{Home}{Home}{Up}
{Down}{Down}{Down}{Down}{Down}{Down}{Block}{End}{Move}12{Switch}
{Enter}{End},{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},"{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}","{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}",{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},"{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}","{Switch}{Tab}{Block}{Home}{Home}{Down}{Move}12{Switch}{Enter}
27nREPAIR{Enter}1{Exit}{Font}4Nprestige 20cpi{Enter}1
"{Switch}{Home}{Right}{Block}{Word
Left}{Move}12{Switch}{Enter}{End}", {Switch}
{Home}{Home}{Up}
{Down}{Down}{Down}{Down}{Down}{Down}{Block}{End}{Move}12{Switch}
{Enter}{End},{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},"{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}","{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}",{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End},"{Switch}{Tab}{Block}{End}{Move}12{Switch}{Enter}
{End}","{Switch}{Tab}{Block}{Home}{Home}{Down}{Move}12{Switch}{Enter}
{Home}{Home}{Down}"{Left}{Left}{Del}{Switch}{Typeover} {Home}{Home}
{Up}
Finally, create a batch file to send the saved DOS text to the network
directory and then shell out to DOS from the macro repair.wpm to
execute the batch file. The process of sending the ASCII file to the
network can only be done once a minute because of the way that the
file name of the document is assigned. However, for us this has not
been a problem since by the time the data is entered and stored on
the network it takes over a minute anyway. This is what our batch
file, senddata.bat, looks like:
@echo off
use P: \\fsrc1b\pccommon
copy c:\wp51\*.qqq p:\requests
use p: /d
erase c:\wp51\*.qqq
The R:BASE side
Create a table to store the list of file names to load. We named the
table Treeload and it contains these columns:
FILENAME TEXT 8 file name of WordPerfect document.
FILEEXT TEXT 3 file extension of WordPerfect document.
FILESIZE INTEGER the size of the file
FILEDATE TEXT 8 date the WordPerfect document was created.
FILETIME TEXT 6 time the WordPerfect document was created.
Create a table to store the loaded WordPerfect information. We use an
intermediate table, Reqinfo; the data is manipulated and transferred
from this table to the final data table, Routine. The final data
table, Routine, has columns of data that are not entered through the
WordPerfect form. The Reqinfo table in our case has the following
columns:
FILENAME TEXT 8 file name of WordPerfect document.
REQDATE DATE requisition date.
BC INTEGER budget code of department
LOCATION TEXT 20 location within the department.
CONTACT TEXT 20 contact name.
EXTENSN# INTEGER phone extension number.
PRIORCDE INTEGER priority code (how important is the requisition.
ROOM# TEXT 4 room number.
WORKREQ TEXT 160 work required.
The R:BASE command file, ldnwreq.cmd, gets a list (directory) of all
files in the REQUEST directory on the network and stores the list in
the Trreload table. Moving through the Trreload table row by row with
DECLARE CURSOR, each data file is loaded into the Reqinfo table.
*(* Command File Name: LDNWREQ.CMD *
* *
* This command file loads the WordPerfect 5.1 trades requisition *
* information from the Network (i.e. drive P:\REQUESTS directory).*
*)
SET V vdbname TEXT, vfileext TEXT, vfile TEXT
SET ERROR MESSAGES OFF
CLS
WRITE "Loading In Repair Requisition Files .... please wait" +
AT 11,13 BLINK
OUTPUT allfiles.dat
DIR P:\REQUESTS\*.QQQ
OUTPUT SCREEN
LOAD trreload FROM allfiles.dat
CLS
WRITE "Loading In Repair Requisition Files .... please wait" +
AT 11,13 BLINK
ERASE allfiles.dat
DECLARE C1 CURSOR FOR SELECT filename, fileext FROM trreload
OPEN C1
FETCH C1 INTO vdbname ind1, vfileext ind2
WHILE SQLCODE<>100 THEN
SET VAR vfile = ("P:\REQUESTS\" + .vdbname + "." + .vfileext)
LOAD reqinfo FROM .vfile AS ASCII
FETCH C1 INTO vdbname ind1, vfileext ind2
ENDWH
DROP CURSOR C1
DELETE ROWS FROM trreload
ERASE P:\REQUESTS\*.QQQ
CLEAR VAR vdbname vfileext vfile
RETURN
The file 2routine.cmd strips away underscores from the location and
contact fields and transfers the information from the Reqinfo table
to the Routine table.
*(* Command File Name: 2ROUTINE.CMD *
* *
* transfers the information from the REQINFO table to *
* the ROUTINE table and removes the underscores. *
*)
CLS
WRITE "Transferring data .... please wait" AT 11,23 BLINK
SET VAR vmonth# INT
APPEND reqinfo TO routine
SELECT month# INTO vmonth# FROM months WHERE currmth = "Y"
UPDATE routine SET +
month# = .vmonth#, +
location = (SGET(location,(SLOC(location,"_")-1),1)), +
contact = (SGET(contact,(SLOC(contact,"_")-1),1)), +
hrlyrate = 0, jobtype = "W", status = "O", jobtime = 0, emp# = 99, +
rtaskcde = "OTH" WHERE jobtype IS NULL
DELETE ROWS FROM reqinfo
CLEAR VAR vmonth#
RETURN
Note that additional columns in the Routine table are filled with
default values in the UPDATE command; not all the data is entered in
the WordPerfect form. In versions of R:BASE prior to 4.5 you would
need to write a DECLARE CURSOR routine to remove the underscores;
you could not nest the SLOC function inside the SGET function.
Nesting the TEXT expressions lets you reduce many lines of code into
one.
WordPerfect is a trademark of and available from WordPerfect Corp.,
1555 N. Technology Way, MS K1369, Orem, UT 84057-2399, 801-451-5151