Update frames let the user retrieve records from tables, then update the retrieved data. You also can define an Update frame to include any of these additional operations:

You also can specify for an Update frame:

The specifications shown in the figure correspond to the examples used in the discussion below.

The user of an Update frame can add data to the Master or Detail table (or both) in either of these ways:

Vision generates menu items for both these operations when you allow users to add records on an Update frame.

On the visual query window, adding new records is controlled by a simple field labeled "Insert (y/n)" for the Master table and a similar field for the Detail table. By default, Vision lets users add new records to the Detail table but not to the Master table.

You can specify insertions separately for each table. For example, you can let users of an Update frame add new items to existing orders, but not add new orders.

The process of updating records on an Update frame can involve deleting records, also. By default, Vision lets users delete records from the Master and Detail tables. The following table describes the visual query specifications that control the deletion of records on an Update frame:

Specifying Frame Behaviors describes how to set the Delete Cascades frame behavior.

If an Update frame has only a Master table, you can control deletions by setting the value of the Delete field for the Master table, as described in the procedures at the end of this section.

For an Update frame with a Master table and a Detail table there are two considerations involved in allowing Detail table row deletions:

In the previous table, "N/A" indicates that the value for this specification does not affect the user actions allowed. In these cases, you must accept the default value.

Using the order entry example, you could let users delete order items from the Detail table, but not delete entire orders from the Master table. In this case, you would specify Detail table deletes only.

You could have another Update frame that displays open orders. You could specify Master table deletions allowed and Restrict Cascades frame behavior. Users now can delete orders from the Master table only if there are no open items in the Detail table.

If necessary, change the Delete Cascades frame behavior as described in Specifying Frame Behaviors.

You can restrict the records that users can retrieve on an Update frame. You do this by specifying a query restriction in the visual query.

You can specify query restrictions on as many columns as you like in both the Master and Detail tables. These restrictions can be in addition to any query qualifications that the user enters when running the frame.

For example (see the preceding figure), you could specify that users only can retrieve orders whose total is less than $500. To do this, enter "<500" as a query restriction for the "order_total" column in the Orders table.

By default, Vision retrieves records in whatever order they appear in the tables in the database. You can use the Visual Query Editor to indicate a specific order in which to sort retrieved records.

You can sort retrieved records:

For example, you can display retrieved records in order by dollar amount first and then by customer number, even though a different column appears first in the table.

To change the sort sequence after you specify it, move the cursor to the row for the column you want to change and enter a new sequence number. Vision adjusts the sort number for each column that comes later in the sequence.

By default, Update and Browse frames on which the qualification processing frame behavior is enabled (see Specifying Frame Behaviors) are cleared each time a user selects Go. You can prevent the window from being cleared by setting the internal 4GL variable "IIclear2" to "n" in escape code.

For example, put the following statement in a Form-Start escape:

For instructions on how to write escape code, see Using Vision Advanced Features.

The tables that you use with Update frames must have unique keys defined. Otherwise, when a user attempts to update or delete a record on the Update frame, Vision considers each column in the table to be part of a "unique key." This can lead to two types of problems:

To avoid these problems, use the SQL modify statement or create unique index statement to define a unique key on a table. (See the SQL Reference Guide for more information on these statements.) You then can use the Vision Reconcile utility so that the visual query reflects the table's new unique key. For more information on the Vision Reconcile utility, see Reconciling Tables and Frame Definitions.

When defining a table to use as a Master or Detail table on an Update frame, avoid creating any of the key columns as nullable. Nullable key columns cause Vision to generate additional source code, including a more complicated where clause, additional hidden fields, and an if-endif block before the update or delete statement.

This additional code prevents the generated where clause from failing when a user enters a null value into a key column. However, the additional code also makes the source code file larger and more difficult to read, and decreases the efficiency of the frame.