DOCUMENT #673
=======================================================================
MANAGING DATA FROM MULTIPLE COMPUTERS OUTSIDE A NETWORK
=======================================================================
PRODUCT: R:BASE VERSION : 3.1 Or Higher
=======================================================================
AREA : PROGRAMMING IN R:BASE CATEGORY: DATA MANIPULATION
=======================================================================
From: Steven Friedman
1661 Pebblewood Dr.
Sacramento, CA 95833
(916) 920-5573
Steven is an R:BASE consultant and co-leader of the Sacramento PC
User Group R:BASE SIG. He has also published R:TUTOR, an R:BASE
training program on diskette.
As laptops and high speed modems proliferate the market, many offices
find themselves dealing with an extended sneaker net where records
from many agents' databases get merged into the central database. It
often becomes difficult then to determine who made recent entries
and when they were made. If an agent's record is changed, there is
often no way of knowing which is the latest entry, since more than
one person may be adding or editing records. To address this, I have
devised a control system to indicate when a record was created, which
ones have been changed, and who made the latest changes.
Control Columns
===============
A method I have found successful is to set up two "control" columns:
one to indicate the date that the record was entered or edited, and
one to indicate who made the change or entry. A third control device
is to also set up a "change flag" to indicate if a record has been
edited since the record was first entered.
I have called the two control columns CNTRLDATE and DBuser.
CNTRLDATE is a DATE column that will hold the date that a value is
entered or edited. DBuser is a TEXT 18 column set up to hold either
the current user's password, or an environmental value that holds the
computer's "name". These could be computed columns, but the values
would then recalculate when the data is merged to the master database.
A better method is to let a form do the work of placing the values
into the table.
For the this example I've created a database that contains two tables
with only 5 columns each. The master table contains all the records,
while the remote tables contain only records for their respective
areas. This is very typical of remote sites where computers are not
physically linked but feed into a central database. Both tables share
identical column names and definitions. Following are the CREATE
TABLE commands that can be used to make these tables.
CREATE TABLE MasterTbl (Custid INTEGER, LastName TEXT 15, +
FirstName TEXT 12, CntrlDate DATE, DBuser TEXT 18)
CREATE TABLE Remote1Tbl (Custid INTEGER, LastName TEXT 15, +
FirstName TEXT 12, CntrlDate DATE, DBuser TEXT 18)
Setting Expressions For Control Values
======================================
On the form used to enter the customer data, create expressions for
the CNTRLDATE and DBuser values. It is possible in R:BASE to set up
an expression for a column without having to actually locate a field
on the form for it. In this case we will not place a field for these
column values on the form, so the user will never be aware of them.
The first expression is for the value we want to automatically enter
into the CNTRLDATE column. This uses the #date global variable which
sets the column value automatically to the system date. This is
defined as:
CNTRLDATE = (.#DATE)
The second control column, DBuser, is used to identify the computer
or the user that entered the record or made the changes. There are two
ways to accomplish this task.
1. Using the CVAL Function. The CVAL function places the set
value of a SET keyword into a variable or in this case a computed
column. On password protected databases, the CVAL function is used
to place the value for the keyword USER (current user password) into
the DBuser column. Define the form expression as:
DBuser = (CVAL('USER'))
An advantage of this method is that if multiple users share a computer
it will identify just the user who made the entry. On the other
hand, it will display passwords of other users unless access to the
table itself is restricted. You can also use the CVAL function to
pick up the network name of the user by using the keyword NAME.
2. Using the ENVVAL Function. Alternatively, instead of using CVAL
and the USER or NAME keywords, use the ENVVAL function and a DOS
environmental variable. First define a DOS environmental variable
called WORKSTAT using the DOS SET command. Place the definition in
the AUTOEXEC.BAT file of each computer. The command syntax for this
is:
SET WORKSTAT=name
Name identifies the computer. In this case we've used the user's
name to identify the computer. This assumes that the database will
be used exclusively by that person, or will at least serve to
identify which computer made the entry when working with a merged
database from a number of remote sites.
In the form, define an expression as follows:
DBuser = (ENVVAL('WORKSTAT'))
Generating Control Values Using Entry And Edit Forms
====================================================
When the user enters a record using the form, the vales for the
system date and user identification are automatically inserted. On
editing the record, the value is changed only if the user actually
makes an edit to a field. Browsed fields are not affected; the
original entry value remains in place.
The net result of this is an audit trail that can tell who entered or
edited what records and when.
Creating a MergeLog table
=========================
Merging this data from the remote site to the central site is done in
two stages. The first step is to create a temporary table based on
all records since the last merge operation. To assist in this I
create an additional audit trail table called MergeLog containing the
number of records included, and the maximum and minimum dates of the
records to be merged, and the name of the table and file. The values
for this are inserted from the command file that creates the table.
CREATE TABLE MergeLog (MaxDate DATE, MinDate DATE, +
NumRecords INTEGER, Filename TEXT 8)
Creating and naming a Temporary Table
=====================================
A temporary table is created using the PROJECT command. You could
name the table TEMP, but this will not serve as any identification,
and can cause problems with later steps when multiple tables called
TEMP are used to merge into the master table. A better method is to
devise a file name that is unique, identifies where the file came
from and when it was created. The command file MAKENAM.CMD uses the
first 3 characters of the workstation name and the month and day of
the system date to create the variable VTBLE which holds a unique
temporary table name.
*(MAKENAM.CMD)
SET V Vwrksta = (ENVVAL('Worksta')), Vwrksta = (SGET(.vwrksta,3,1)), +
VdateFormat = (CVAL('date format'))
SET DATE FORMAT = MMDDYY
SET V Vdate TEXT = (.#date), Vdext = (SGET(.Vdate,4,1)), Vtble = +
(.vwrksta + .vdext)
SET DATE FORMAT .Vdateformat
Getting the Date Ranges for the Temporary Table
===============================================
The next step is to obtain a date range for the records to be
included in the temporary table. This firsts tries to find when the
last merge was done from the MergeLog table. If none exists, it
defaults to the current system date. It then requests the user enter
an ending value for the date range, or accept the default which is
the current system date.
*(GETRANGE.CMD)
*(assign default date values for searching)
COMPUTE v1date AS MAX maxdate FROM mergelog
IF v1date IS NULL THEN
SET V v1date TO (.#date)
ENDIF
SET V v2date = (.#date)
*(prompts to allow user to
change date values)
WRITE "Enter the beginning of the date range for the data you wish +
to send (MM/DD/YY)"
WRITE "or press ENTER to accept the displayed value:"
FILLIN v1date=8 EDIT REVERSE
WRITE "Enter the ending of the date range for the data you wish +
to send (MM/DD/YY)"
WRITE "or press ENTER to accept the displayed value:"
FILLIN v2date=8 EDIT REVERSE
This next command file computes the first and last date range values
and the number of rows in the table and then inserts the selected
rows along with the file/table name into the MergeLog table. Note
that the "&" form of the vtble variable is used here since it is part
of an actual command.
*(TEMPTBL.CMD)
PROJECT &vtble FROM remote1 USING * WHERE cntrldate BETWEEN +
.v1date AND .v2date AND custid IS NOT NULL
COMPUTE vmaxdate AS MAX cntrldate FROM &vtble
COMPUTE vmindate AS MIN cntrldate FROM &vtble
COMPUTE vrows AS COUNT custid FROM &vtble
INSERT INTO mergelog (maxdate, mindate, numrecords, +
filename) VALUES (.mindate, .maxdate, .vrows. .vtble)
Outputting the Data to Diskette
===============================
After the temporary table has been created with all the records
within the assigned date range, the next step is to output it into a
data file that can be sent via modem, or other means and then loaded
into the master database. To do this I have used the UNLOAD command
along with the OUTPUT command directed to a filename on a floppy
diskette. The UNLOAD command can usually be used instead of the
BACKUP command unless the table is huge and might span more than one
floppy.
*(UNLOADAT.CMD)
SET V vdrive = 'A:'
DIALOG 'Enter the drive letter for the floppy disk +
drive you wish to use' vdrive vkey 1
*(create a unique file name using the unique temporary table name)
SET V vfile = ( .vdrive + '\' + .vtble + '.' + 'dat')
WRITE 'Insert a new formatted floppy diskette into drive',.vdrive
WRITE ' Press any key when ready'
PAUSE
OUTPUT &vfile
UNLOAD ALL FOR &vtble
OUTPUT SCREEN
WRITE 'The data has been written to ', .vfile
CLE V vdrive, vfile vtble, vkey, v1date, v2date
Merging the Data into the Master Database
=========================================
Once the datafile is at the central location it can be merged into the
master database. This assumes that the master database uses the same
structure, file names, null symbol, and date formatting conventions
as the remote database files. It is very important that this remain
consistent or the data may not merge correctly!
*(MERGDATA.CMD)
CLS
SET V vdrive 'A:'
DIALOG 'Enter the drive letter of the floppy diskette' +
vdrive vkey 1
*(display files)
DIR &vdrive
FILLIN vfile USING 'Enter the name of the data file +
including the extension as it appears on the diskette'
*(Create input file name)
SET V vinfile = (.vdrive + '\' + .vfile)
WRITE 'Press any key when ready'
PAUSE
*(restore the table containing the new records into the master
database.)
INPUT &vfile
*(find the table name by getting the first 8 characters of the file
name on the diskette)
SET V vtble = (SGET(.vfile,8,1))
*(graft the new table onto the Master table and remove the
temporary table that was created from the data on the floppy diskette)
APPEND .vtble TO MasterTbl
REMOVE &vtble
Once the data is merged into a central database, you can determine
the source of the data and when it was entered or last changed, and
by whom.
Pros and Cons
=============
The advantage of this method is that it automatically tracks when
data was entered and by whom. Because the control columns are
generated automatically by the entry and edit forms, it will not be
successful if ad hoc entering or editing is used. Updates by command
files will work with this method provided the user identification and
system date are defined as global variables and the columns are
updated in the command file. The disadvantage is that is adds
overhead to your database.
Flagging Changed Columns
========================
There are times when you want to automatically flag when an address
or vital piece of information in a table was changed. For example, you
may want to alert the user if an address or phone# has been recently
changed, or a fax# has been added. The method described below works
only if the user uses forms to edit the data.
Create a column in the table defined as CHANGE_FLAG TEXT 4. Then
create a copy of the data entry form and define it as an edit only
form. In the expressions for the form, define a variable as
CHANGE_FLAG = 'C' to indicate changed, do not locate this variable on
the form.
Normally the CHANGE_FLAG column will contain a NULL value. When the
user makes a change to any record using the edit only form, and saves
it, the NULL value in the CHANGE_FLAG column will be changed to "C".
To re-set this value I create another form consisting of a scrolling
region showing the Custid, Lastname, Changed_Flag column, Cntrldate,
and DBuser. I permit only the CHANGE_FLAG column to be edited by the
user. When the database administrator wishes to reset the flags, he
or she need only scroll down the region of changed records and reset
them after the CHANGE_FLAG no longer is needed.