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
     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
     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: 
     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
     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
     -- exit.eep
     IF vhold_id <> .vcustid THEN
       SET VAR vhold_id = .vcustid
     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
     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
       SET VAR vhold_id = .vcustid
     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.