Playing with Relations

Relations are Forms internal objects that are created

• implicitly when a detail block is created and a master-detail relationship is specified between this detail block and an existing master block.
• explicitly using the Relations node in the Object Navigator.

The default behavior of a master-detail relation is as follows :

• When navigating to a different master record, clear the existing detail block(s), and then requery all its details automatically for the new master ”that is, while scrolling through the master block records.
• Copy the master record primary key while creating a new detail record at commit time.
• Use the master record primary key to prevent deletion; delete the corresponding detail records when the master record is deleted.

These implicit actions are controlled by certain properties of a relation, which are

• Master-Deletes ”It can take one of the values Non-isolated, Isolated, or Cascading. Specifying Non-isolated prevents the deletion of the master record if detail records exist. Isolated deletes the master record, leaving the detail records dangling. Cascading first deletes the detail records and then deletes the master record.
• Co-ordination ”It can be either Immediate, meaning that the clearing and querying of the detail records happen as soon as the focus shifts to a new master record, or Deferred, meaning that this behavior can be postponed till a later point. Till what point is determined by the supplemental Auto-Query property. This property can be either Yes or No. A Yes means that the process is delayed till the control navigates to the detail block, and a No delays it till the control navigates to the detail block and the user initiates an Execute Query using the physical function key.
• Prevent Master-less Operation ”This prevents query or data entry in the detail block in the absence of a corresponding master record.

The first behavior is controlled by the Co-ordination property, the second by the Copy Value From Item property of the foreign key item in the detail block, and the third partly by the Copy Value From Item property and partly by the Master-Deletes property of a relation.

The techniques given in this section pertain to the following:

• Suppressing the default behavior as controlled by the Co-ordination property. Here we refer to Immediate coordination.
• Toggling between Immediate and Deferred coordination, that is, switching from Immediate to Deferred, and vice-versa, depending on what the current coordination is.

Suppressing Master-Detail Behavior

Suppressing master-detail behavior is a requirement when searching through the master block based on an item value. To speed the search process, it is worthwhile to suppress the automatic query of the detail block for every master record temporarily for the duration of the search.

You can do this by changing the master-detail relation properties dynamically before starting the search and by resetting them after the search is complete. The automatic clearing and querying of the detail block for every new master record are by means of the ON-CLEAR-DETAILS trigger at the form level and the ON-POPULATE-DETAILS trigger at the master-block level. This is due to the default Immediate coordination taking place when the master record changes. The firing of these two triggers can be suppressed by changing the Immediate coordination to Deferred with Auto-Query.

In the appropriate trigger, set the AUTOQUERY and DEFERRED_COORDINATION properties for the relation involved to TRUE before looping through the master block, and reset both of them to FALSE after the find is over. This delays the query of the detail block until the user does an explicit EXECUTE_QUERY and thus prevents automatic population of the detail block for every master record. The code involved is similar to the following:

WHEN-BUTTON-PRESSED trigger of search button. DECLARE relation_id Relation; BEGIN /* Find the relation id corresponding to the relation name. If the resulting ID is NULL, the given relation is invalid. Otherwise, set its properties to DEFERRED with AUTOQUERY */ relation_id := Find_Relation(); IF NOT Id_Null(relation_id) THEN SET_RELATION_PROPERTY(relation_id, AUTOQUERY, PROPERTY_TRUE); SET_RELATION_PROPERTY(relation_id, DEFERRED_COORDINATION, PROPERTY_TRUE); ELSE MESSAGE('InvalidRelation'); RAISE FORM_TRIGGER_FAILURE; END IF; /* Proceed with the search operation */ /* RE-query the detail block to ensure that it gets populated after the search. This is required because no automatic query of the detail block takes place due to DEFERRED coordination. */ GO_BLOCK(); EXECUTE_QUERY; GO_BLOCK(); /* Reset the relation properties after the search is complete */ SET_RELATION_PROPERTY(relation_id, AUTOQUERY, PROPERTY_FALSE); SET_RELATION_PROPERTY(relation_id, DEFERRED_COORDINATION, PROPERTY_FALSE); END;

The EXECUTE_QUERY for the detail block is necessary; otherwise, the detail block is left empty after the search is over.

Toggling Between Immediate and Deferred Coordination

This section presents a technique to toggle between immediate and deferred coordination. This is very useful in situations like the search operation discussed above. Before the search process, you switch to deferred coordination from the default immediate coordination. After the search is over, you can toggle back to immediate coordination.

You can toggle between the IMMEDIATE and DEFERRED coordination by dynamically changing the relation's Coordination property. To do so, you change the master-detail relation DEFERRED_COORDINATION property dynamically to the opposite of the current setting. The AUTOQUERY property has to be reset to TRUE if automatic requery is necessary after DEFERRED coordination is turned on. It is turned off automatically because of the cascading effect of DEFERRED coordination. This method can be implemented as a procedure p_toggle_ coordination given below. You can use code like the following in the actual trigger to make a call to the above mentioned procedure:

WHEN-BUTTON-PRESSED DECLARE v_retcd NUMBER; v_errm VARCHAR2(100); BEGIN -- Call the togglre procedure before the search p_toggle_coordination(, v_retcd, v_errm); IF (v_retcd <> 0) THEN MESSAGE(v_errm); RAISE FORM_TRIGGER_FAILURE; END IF; -- Search process -- Call the toggle procedure after the search END;

The code for the procedure p_toggle_coordination is given below:

PROCEDURE p_toggle_coordination(p_relation_name IN VARCHAR2, retcd OUT NUMBER, errm OUT VARCHAR2) IS relation_id RELATION; BEGIN relation_id := FIND_RELATION(p_relation_name); IF NOT Id_Null(relation_id) THEN IF GET_RELATION_PROPERTY(relation_id, DEERRED_COORDINATION) = 'FALSE' THEN SET_RELATION_PROPERTY(relation_id, DEFERRED_COORDINATION, PROPERTY_TRUE); ELSE SET_RELATION_PROPERTY(relation_id, DEFERRED_COORDINATION, PROPERTY_FALSE); END IF; IF FORM_SUCCESS THEN retcd := 0; ELSE retcd := -1; errm := 'Toggle Relation Failure'; ELSE retcd := -1; errm := 'Invalid Relation'; END IF; END p_toggle_coordination;