PRODUCT:  R:BASE                  VERSION :  3.1 Or Higher
     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
          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.
     SET V Vwrksta = (ENVVAL('Worksta')), Vwrksta = (SGET(.vwrksta,3,1)), +
       VdateFormat = (CVAL('date format')) 
     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.
     *(assign default date values for searching)
     COMPUTE v1date AS MAX maxdate FROM mergelog
     IF v1date IS NULL THEN 
       SET V v1date TO (.#date)
     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.
     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
     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'
     OUTPUT &vfile
     UNLOAD ALL FOR &vtble
     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!
     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'
     *(restore the table containing the new records into the master 
     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.