838.TXT
=====================================================================
Global Updates in a Form
=====================================================================
PRODUCT: R:BASE VERSION: 5.5 or Higher
=====================================================================
CATALOG: Forms, Reports & Labels AREA : Forms
=====================================================================
The columns that are used to identify rows of data are not often
edited, and they are often designated as a primary key. Sometimes,
however, you need to update an ID number that is used to uniquely
identify a row. In those situations, you need to make sure that the
ID number is updated across all the tables in the database that
include that column. This preserves data integrity and referential
integrity in your database. To be sure all the tables are updated
correctly generally requires custom code to be written. Users are
often editing data with a form, however, and it would be handy to
have the updates taking place from the form. Using a form requires
little or no custom code.
Table settings in a form can be changed to allow updating of data
across tables. When the data in a linking column is changed, the user
s prompted to change the column in the others tables on the form. The
prompting message can't be turned off; the user is always given the
option of not making the change. A user might decide not to change
the data in some of the tables on the form thus compromising the
referential integrity of the database. Also, the linking column may
appear in other tables in the database that are not included in the
form currently being used. These tables are not updated.
If your database is set up using constraints_primary and foreign
keys, then you have probably noticed that the global updating option
in forms doesn't work well for you. Most forms are designed with the
primary key table as table one, then the foreign key tables are added
to the form as lower tables. You can't update a primary key value,
however, if there are referenced foreign key values. In most
situations this means that you can't use the global updating feature
of forms since linking column values must be changed in the first
table.
The following techniques put the control back in the hands of the
application developer and ensure data integrity by controlling global
updating of data. Both techniques use just a few lines of code and
update the data in all the tables in the database, not just the
tables associated with the form. The first technique is for databases
using constraints, the second technique can be used in any database.
Global Updates Using Constraints
This technique assumes you are updating a primary key value. Use the
CASCADE option now available in 5.5 to cascade the change from the
primary key to the referenced foreign key columns. The CASCADE option
must be added from the R> prompt using the ALTER TABLE command. For
example:
ALTER TABLE customer ADD CASCADE
The CASCADE option makes sure that with any change to a primary key
value, all the corresponding foreign key values are updated. This
update happens when the data is changed using a form, using the Data
Browser, or using the UPDATE command.
The changes happen automatically once you add the CASCADE option to
your primary key tables. The change occurs in all tables on the form,
but R:BASE does not automatically refresh the data display for the
lower tables. It might look like data has disappeared. With just a
little bit of programming, you can improve the appearance to the user
when editing data with a multi-table form. Use an exit EEP on ID
number field to redisplay the rows in the lower tables on a form. The
exit EEP contains these commands:
SAVEROW
PREVROW
The SAVEROW command processes the change and advances the form one
row. The PREVROW command returns the user to the row they were
editing. The CASCADE option automatically processes the change and
all referenced foreign key tables in the database are updated, even
if the table is not associated with the form.
When using the CASCADE option, you want to turn off the global
updating option in the form. Open the form in the Form Designer.
Select the ID number object and then choose Format: Field Settings.
Check the option "Restrict Changes to the Current Table." That tells
the form to not worry about processing any global changes and lets
the CASCADE option handle it.
You can quickly add a few more commands to execute the EEP only when
the ID number has actually been changed. First, define a form
expression to place the ID column value into a variable. For example:
vcustid = custid
As each row is read, and whenever the value of the custid column
changes, the variable vcustid is updated with the new value.
Place an EEP on entry into the ID number field. The EEP places the
initial vcustid value for a row into a holding variable. For example,
the entry EEP contains this code:
-- entry.eep
SET VAR vhold_id = .vcustid
RETURN
On exit from the ID number field (the primary key column), the field
exit EEP checks to see if the value of the vcustid variable has
changed. The variable vcustid is updated only when the value of the
id column has changed. You can compare these values and perform the
global update and data refresh only when the value has actually
changed.
-- exit.eep
IF vhold_id <> .vcustid THEN
SAVEROW
PREVROW
SET VAR vhold_id = .vcustid
ENDIF
RETURN
Global Updates Without Constraints
This technique also updates the linking column data in tables not
associated with in the form. However, while constraints using the
CASCADE option do the updating automatically, for this method an
UPDATE command must be included in the EEP for each table to be
updated.
To make sure you update all the tables where the column is used, use
the LIST COLUMN command in the "R:BASE R> Prompt" window and specify
the id column name. The column and all its associated tables are
listed. For example:
R>LIST COLUMN custid
Column Name Table Name Attributes
-------------- ---------------- ---------------------------------
Custid Contact Type : INTEGER Comment: Customer
identification number
TransMaster Type : INTEGER Comment: Customer
identification number
Customer Type : INTEGER AUTONUMBER
Comment: Customer
identification number
Set the form up as described above; turn off the global updating
option in the form by opening the form in the Form Designer then,
select the ID number object and choose Format: Field Settings_.
Check the option "Restrict Changes to the Current Table." Add the
expression, vcustid = custid, to the form. The variable vcustid is
used in the UPDATE commands as well determining if an ID number value
has changed. The same entry and exit EEPs as above can be used, all
you need to do is add the necessary UPDATE commands to the exit EEP.
For example:
-- exit.eep
IF vhold_id <> .vcustid THEN
UPDATE contact SET custid = .vcustid WHERE custid =.vhold_id
UPDATE transmaster SET custid = .vcustid WHERE custid =.vhold_id
-- repeat the update command for each table to be updated
SAVEROW
PREVROW
SET VAR vhold_id = .vcustid
ENDIF
RETURN
The UPDATE commands update tables not on the form that have matching
data. The SAVEROW and PREVROW commands process the change and
redisplay the row being edited. The difference between this
technique and the technique using constraints and the CASCADE
option, is the CASCADE option ensures that all referenced tables are
updated. With this technique, the developer must remember to modify
the UPDATE commands in the exit EEP when a table is added or deleted
from the database.