The Power of Record Groups

2017-11-03 09:05:02

Since their inception in Forms 4.0, record groups have played a significant role in enhancing the development of forms, whether it be LOV manipulation, multiple selection, or intermediate sorting, to name a few uses. In this section, I will discuss techniques for

changing a record group dynamically, for example, to repopulate it based on runtime criteria.

adding rows to the beginning of a record group.

adding rows anywhere in a record group and to the end of a record group.

multiple selection.

intermediate sorting.

Changing a Record Group Dynamically

Record groups can be created either at design time or programmatically at runtime (dynamically). Also, dynamically created record groups can be locally scoped or globally scoped. Locally scoped means that the data contained in such a record group is accessible to the particular form in which it is created. Therefore, such a record group is not sharable across forms in a multiform application. A globally scoped record group has its data sharable across all forms in a single multiform session.

There are varied uses for creating and manipulating record groups dynamically, the most common ones being

populating list items dynamically.

simulating multiselection pick lists.

populating control blocks dynamically.

performing intermediate actions such as sorting, searching, and computing, which require in-memory computations but are either not possible or not efficient when done using local or server-side PL/SQL.

I begin by providing a brief introduction to creating and manipulating dynamic record groups, both query and nonquery. In the subsequent subsections, I then discuss the techniques not often encountered in the normal creation and manipulation procedures.

Dynamically creating record groups involves creating a nonquery record group at runtime. This involves the following steps:

Creating the record group. This creates only the internal ID for the record group and is done using CREATE_GROUP.

Adding columns to the record group. This is done using ADD_GROUP_COLUMN.

Adding rows to the record group and setting the row-column cell values (populating the group). This is done in a loop, using ADD_GROUP_ROW and SET_GROUP__CELL, where is one of NUMBER, DATE, and CHAR.

The following code illustrates this method:

DECLARE rg_id RECORDGROUP; rg_name VARCHAR2(20) := 'RG_DEPT'; gc_id1 GROUPCOLUMN; gc_id2 GROUPCOLUMN; row_cnt NUMBER := 5; BEGIN /* First check whether the record group already exists. If Yes, delete it. */ rg_id := FIND_GROUP(rg_name); IF NOT ID_NULL(rg_id) THEN DELETE_GROUP(rg_id); END IF; /* Create the record group to derive a handle or internal id */ rg_id := CREATE_GROUP(rg_name); IF ID_NULL(rg_id) THEN MESSAGE('ERR: Creating Record Group 'rg_name); RAISE FORM_TRIGGER_FAILURE; END IF; /* Add two group columns, deptno and dname */ gc_id1 := ADD_GROUP_COLUMN(rg_id, 'deptno', NUMBER_COLUMN); gc_id2 := ADD_GROUP_COLUMN(rg_id, 'dname', CHAR_COLUMN, 20); /* Create 5 rows and populate them in a loop. The constant 5 is chosen at random to illustrate the concepts */ FOR i in 1..row_cnt LOOP ADD_GROUP_ROW(rg_id, END_OF_GROUP); SET_GROUP_NUMBER_CELL(gc_id1, i, i); SET_GROUP_CHAR_CELL(gc_id2, i, 'Department 'TO_CHAR(i)); END LOOP; END;

Creating a query record group at runtime involves the following steps:

Creating the record group based on a SELECT statement. This creates the internal ID for the record group, as well as the structure of the record group (that is, the columns). This is done using CREATE_GROUP_FROM_QUERY.

Populating the record group with the rows retrieved by the query that was used in the CREATE_GROUP_FROM_QUERY built-in or a different query having one-to-one correspondence with this query. It automatically adds rows to it. This is done using POPULATE_GROUP if the query is the same as the one used to create the group or using POPULATE_GROUP_WITH_QUERY if the data is from a different query.

The following code implements this method:

DECLARE rg_id RECORDGROUP; rg_name VARCHAR2(20) := 'RG_DEPT'; query_string := 'SELECT deptno,, dname FROM dept ORDER BY dname'; ret_code NUMBER; BEGIN /* First check whether the record group already exists. If Yes, delete it. */ rg_id := FIND_GROUP(rg_name); IF NOT ID_NULL(rg_id) THEN DELETE_GROUP(rg_id); END IF; /* Create the record group from an input query to derive a handle or internal id, as well as the column structure */ rg_id := CREATE_GROUP_FROM_QUERY(rg_name , query_string); IF ID_NULL(rg_id) THEN MESSAGE('ERR: Creating Record Group 'rg_name); RAISE FORM_TRIGGER_FAILURE; END IF; /* Populate the created record group with data from the query used to create the group. This need not be done in a loop. */ ret_code := POPULATE_GROUP(rg_id); IF (ret_code <> 0) THEN MESSAGE('ERR: Populating Group'); RAISE FORM_TRIGGER_FAILURE; END IF; /* This line containing the call to POPULATE_GROUP can be replaced by the following line if the query to populate the data is different. ret_code := POPULATE_GROUP_WITH_QUERY(rg_id, 'SELECT deptno, dname FROM dept WHERE loc = ''NEW YORK'' ORDER BY dname'); */ END;

Dynamically manipulating record groups amounts to adding, changing, and deleting row(s) in the following ways:

At the beginning of an existing group

At the end of an existing group

Anywhere in the middle of an existing group, both query and nonquery

Adding, changing, and deleting entire columns of a group, both query and nonquery

Tip

Here is an important tip that reminds us of an often-ignored point about creating record groups dynamically. You can change a record group dynamically only if it's created dynamically (query or nonquery) or if it's a query record group created at design time.

Changing GLOBAL_SCOPE record groups is discussed in Chapter 4, "Advanced Forms Programming" under the section "Sharing a Record Group across Forms."

Adding to the Beginning of a Record Group

How many of you are aware that in Forms 4.5 you can add rows to the beginning of an already created record group? This seems trivial at first thought, but is a powerful and flexible technique to be used in many demanding situations.

To do this, use ADD_GROUP_ROW and specify the constant 1 for the row index, as follows :

ADD_GROUP_ROW(rg_id, 1);

This displaces all the existing rows to one position below and makes room for the new row with row number 1. Do not specify the constant instead of 1.

Adding in the Middle or at the End of a Record Group

To add a record in the middle of a record group, use ADD_GROUP_ROW and specify the index number of the new row as

current row index + 1

where current row is the row after which the new row should be added. This displaces all the remaining rows to one position below and makes room for the new row.

To add a record to the end of the record group, specify the constant END_OF_GROUP for the index number.

Multiple Selection

Multiple selection is a common requirement in almost all applications in which the user expects a choice of elements to choose from by click-and-highlight. The multiple selection is made possible by giving the user the flexibility of choosing from a SELECT_ALL list on the left. After the entire selection is made , an ADD button enables the selected records to be transferred to the right side. The question of retaining the selected records on the left hand side (LHS) is application and user-requirement specific. Here, I will retain the selected records from the LHS as soon as the user presses the ADD button.

Now I will discuss the technique of multiple selection by using dynamically created record groups and using Select and De-select toggle by click operation only.

To illustrate this technique, consider a payroll application in which users are presented with an initial selection screen displaying a list of all departments. Selection of multiple departments is allowed. The payroll must be processed for the employees of the multiple-selected departments.

The design is as follows:

A multirecord ALL_DEPT block based on the DEPT table on the LHS. This block is a query-only block and will automatically display all records on screen startup. The items DEPTNO and DNAME are displayed.

A multirecord SELECTED_DEPT control block on the right hand side (RHS) to hold the departments selected by the user. Here also the items DEPTNO and DNMAE are displayed.

There will be no facility to perform INSERT, UPDATE, DELETE, or QUERY records in both the blocks.

A CTRL_BLK control block with two items, ADD_COUNT and REMOVE_COUNT, to keep the count of records selected for either adding to or removing from LHS or RHS.

Two push buttons , PB_ADD and PB_ADD_ALL, to enable transfer of some or all of the selected records from the LHS to the RHS.

Two push buttons, PB_REMOVE and PB_REMOVE_ALL, to enable transfer of some or all of the selected records from the RHS to the LHS.

These buttons belong to the control block.

Two visual attributes, VA_SELECTED and VA_DESELECTED, to highlight and un-highlight the particular record when the user selects or deselects by clicking on a particular record.

Selection and deselection are a toggle operation by point-and-click. This is the case when adding or removing records.

Figure 2.7 shows a typical multiselection functionality. To create it, follow these steps:

Figure 2.7. Multiple selection of departments.

The block ALL_DEPT has the following properties and their corresponding values set:

`DATABASE BLOCK`	`YES`
`Number of Records Displayed`	`10`
`Query Allowed`	`Yes`
`Insert Allowed`	`No`
`Update Allowed`	`No`
`Delete Allowed`	`No`
`Query Data Source Type`	`TABLE`
`Query Data Source Name`	`DEPT`
`QUERY ALL RECORDS`	`YES`

Note the special property QUERY ALL RECORDS. This is set to YES to enable all the records to be fetched into the Forms buffer at the very beginning of the query.

The block SELECTED_DEPT has the following properties and their corresponding values set:

`DATABASE BLOCK`	`NO`
`Number of Records Displayed`	`10`
`Query Allowed`	`No`
`Insert Allowed`	`Yes`
`Update Allowed`	`No`
`Delete Allowed`	`No`

The items DEPTNO and DNAME have the Insert Allowed and Update Allowed properties set to No. The block property Insert Allowed is set to Yes to enable programmatically creating a record while populating the selected records from the record group.

A WHEN-NEW-FORM-INSTANCE trigger queries the ALL_DEPT block on form startup:

GO_BLOCK('ALL_DEPT'); Check_package_failure; EXECUTE_QUERY;

A POST-QUERY trigger on the ALL_DEPT block deselects all records whenever the ALL_DEPT block is queried. This block is requeried more than once, as will be evident from the steps that follow:

SET_ITEM_INSTANCE_PROPERTY('ALL_DEPT.DEPTNO', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'VA_UNSELECTED'); SET_ITEM_INSTANCE_PROPERTY('ALL_DEPT.DNAME', CURRENT_RECORD, VISUAL_ATTRIBUTE, 'VA_UNSELECTED');

The WHEN-MOUSE-CLICK trigger for the ALL_DEPT block toggles between selection and deselection whenever the user clicks on a particular row:

DECLARE ret_code NUMBER; BEGIN ret_code := selection_toggle('ALL_DEPT'); :ctrl_blk.add_count := NVL(:ctrl_blk.add_count,0) + ret_code; IF :ctrl_blk.add_count > 0 THEN SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD', ENABLED, PROPERTY_TRUE); IF :ctrl_blk.add_count > 1 THEN SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD_ALL', ENABLED, PROPERTY_TRUE); END IF; ELSE SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD', ENABLED, PROPERTY_FALSE); SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD_ALL', ENABLED, PROPERTY_FALSE); END IF; END;

The function SELECTION_TOGGLE has the following logic in it:
- A nonquery record group, RG_LHS, is created dynamically to populate each highlighted record from the LHS. The record group has a selection feature in it to mark a record as selected or deselected. This will remove a very serious hazard in the process of record selection and deselection, as detailed in step 2. This record group is included as part of a package named rg_lhs that has the record group ID, the record group name, individual group column IDs and names , and the group row and selection counts. The advantage of choosing a package is twofold. It ensures, first, the availability of the preceding variables 'retaining their global scope and, second, better performance.
- As the user clicks and highlights a record on the LHS, a row is fed into the record group and also into the group selection. If a record is deselected, the row is removed from the group selection, but not from the record group . This is because the user might click and select n of them at first and then deselect the m th one out of the n selected; this would involve a search from the record group. The records can be grouped as a selected group or a deselected group inside the same record group, but this is also a costly affair with respect to intergroup transfer. The selection in the record group saves the following important step: the process of keeping a third control item in the LHS block(s) and LOOP ing through the blocks, which is not as efficient. You would have had to LOOP through the LHS block to get all the selected records when the user clicks the ADD button.
- This record group also has a third column to keep track of records that have already been added. This eliminates looping through the RHS block.
  
  The function code is as follows:
  
  FUNCTION selection_toggle(block_name VARCHAR2) RETURN NUMBER IS row_num Number; row_no Number; v_num Number; v_added VARCHAR2(1); curr_rownum NUMBER; present_in_selection BOOLEAN := FALSE; present_in_group BOOLEAN := FALSE; already_added BOOLEAN := FALSE; BEGIN IF GET_ITEM_INSTANCE_PROPERTY(block_name'.' GET_BLOCK_PROPERTY(block_name, FIRST_ITEM), CURRENT_RECORD, VISUAL_ATTRIBUTE) = 'VA_UNSELECTED'THEN DISPLAY_ITEM(block_name'.' GET_BLOCK_PROPERTY(block_name, FIRST_ITEM), 'VA_SELECTED'); DISPLAY_ITEM(block_name'.' GET_BLOCK_PROPERTY(block_name, LAST_ITEM), 'VA_SELECTED'); -- Creating the Record Group rg_lhs.rg_id := FIND_GROUP(rg_lhs.rg_name); IF FORM_SUCCESS THEN IF ID_NULL(rg_lhs.rg_id) THEN rg_lhs.rg_id := CREATE_GROUP(rg_lhs.rg_name); IF FORM_SUCCESS THEN IF ID_NULL(rg_lhs.rg_id) THEN MESSAGE('ERR: Creating Group 'rg_lhs.rg_name); RAISE FORM_TRIGGER_FAILURE; END IF; END IF; END IF; END IF; -- Add Group Columns rg_lhs.gc_id1 := FIND_COLUMN(rg_lhs.rg_name'.'rg_lhs.column_name1); IF ID_NULL(rg_lhs.gc_id1) THEN rg_lhs.gc_id1 := ADD_GROUP_COLUMN(rg_lhs.rg_id, rg_lhs.column_name1, NUMBER_COLUMN); END IF; rg_lhs.gc_id2 := FIND_COLUMN(rg_lhs.rg_name'.'rg_lhs.column_name2); IF ID_NULL(rg_lhs.gc_id2) THEN rg_lhs.gc_id2 := ADD_GROUP_COLUMN(rg_lhs.rg_id, rg_lhs.column_name2, CHAR_COLUMN, 20); END IF; rg_lhs.gc_id3 := FIND_COLUMN(rg_lhs.rg_name'.'rg_lhs.column_name3); IF ID_NULL(rg_lhs.gc_id3) THEN rg_lhs.gc_id3 := ADD_GROUP_COLUMN(rg_lhs.rg_id, rg_lhs.column_name3, CHAR_COLUMN, 5); END IF; -- Getting group row count rg_lhs.row_cnt := get_group_row_count(rg_lhs.rg_id); -- current row num is row cnt incremented by 1 row_num := NVL(rg_lhs.row_cnt,0) + 1; -- If rg cnt is 0, i.e., the very first time, add rg row. IF rg_lhs.row_cnt = 0 THEN Add_group_row(rg_lhs.rg_id, row_num); set_group_number_cell(rg_lhs.gc_id1, row_num, TO_NUMBER(name_in('ALL_DEPT.DEPTNO'))); set_group_char_cell(rg_lhs.gc_id2, row_num, name_in('ALL_DEPT.DNAME')); set_group_char_cell(rg_lhs.gc_id3, row_num, 'N'); END IF; -- Search if row already present in the record group. for i in 1 .. rg_lhs.row_cnt loop v_num := get_group_number_cell(rg_lhs.gc_id1, i); if :all_dept.deptno = v_num then curr_rownum := i; present_in_group := TRUE; exit; end if; end loop; if present_in_group then -- Search if row already selected. rg_lhs.sel_cnt := GET_GROUP_SELECTION_COUNT(rg_lhs.rg_id); for j in 1 .. rg_lhs.sel_cnt loop row_no := get_group_selection(rg_lhs.rg_id, j); v_num := get_group_number_cell(rg_lhs.gc_id1, row_no); if :all_dept.deptno = v_num then present_in_selection := TRUE; exit; end if; end loop; if not present_in_selection then set_group_selection(rg_lhs.rg_id, curr_rownum); end if; else -- not present in group, so add new row Add_group_row(rg_lhs.rg_id, row_num); set_group_number_cell(rg_lhs.gc_id1, row_num, TO_NUMBER(name_in('ALL_DEPT.DEPTNO'))); set_group_char_cell(rg_lhs.gc_id2, row_num, name_in('ALL_DEPT.DNAME')); set_group_char_cell(rg_lhs.gc_id3, row_num, 'N'); SET_GROUP_SELECTION(rg_lhs.rg_id, row_num); end if; Return(1); ELSIF GET_ITEM_INSTANCE_PROPERTY(block_name'.' GET_BLOCK_PROPERTY(block_name, FIRST_ITEM), CURRENT_RECORD, VISUAL_ATTRIBUTE) = 'VA_SELECTED'THEN -- If already selected, de-select it rg_lhs.sel_cnt := GET_GROUP_SELECTION_COUNT(rg_lhs.rg_id); for idx in 1 .. rg_lhs.sel_cnt loop row_no := get_group_selection(rg_lhs.rg_id, idx); v_num := get_group_number_cell(rg_lhs.gc_id1, row_no); v_added := get_group_char_cell(rg_lhs.gc_id3, row_no); if :all_dept.deptno = v_num then if (v_added != 'Y') then unset_group_selection(rg_lhs.rg_id, row_no); end if; exit; else null; end if; end loop; DISPLAY_ITEM(block_name'.' GET_BLOCK_PROPERTY(block_name, FIRST_ITEM), 'VA_UNSELECTED'); DISPLAY_ITEM(block_name'.' GET_BLOCK_PROPERTY(block_name, LAST_ITEM), 'VA_UNSELECTED'); message(rg_lhs.sel_cnt); pause; Return (-1); END IF; END;

A WHEN-BUTTON-PRESSED trigger for the PB_ADD button has the following logic in it:

DECLARE row_no NUMBER; sl_cnt NUMBER; BEGIN IF (:ctrl_blk.add_count > 0) THEN GO_BLOCK('SELECTED_DEPT'); CLEAR_BLOCK(NO_VALIDATE); sl_cnt := GET_GROUP_SELECTION_COUNT(rg_lhs.rg_id); for idx in 1 .. sl_cnt loop row_no := get_group_selection(rg_lhs.rg_id, idx); COPY(GET_GROUP_NUMBER_CELL(rg_lhs.gc_id1, row_no), 'SELECTED_DEPT.DEPTNO'); COPY(GET_GROUP_CHAR_CELL(rg_lhs.gc_id2, row_no), 'SELECTED_DEPT.DNAME'); SET_GROUP_CHAR_CELL(rg_lhs.gc_id3, row_no, 'Y'); CREATE_RECORD; end loop; go_block('ALL_DEPT'); execute_query(no_validate); -- RESET_GROUP_SELECTION(rg_lhs.rg_id); :ctrl_blk.add_count := NULL; SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD', ENABLED, PROPERTY_FALSE); SET_ITEM_PROPERTY('CTRL_BLK.PB_ADD_ALL', ENABLED, PROPERTY_FALSE); END IF; END;

The WHEN-BUTTON-PRESSED trigger for PB_ADD_ALL is done in a tricky way. Rather than loop through the LHS block, populating the record group and then populating the RHS block, you dynamically set the base table query source and query source name, query the SELECTED_ALL block, and reset their values to NULL after querying.

This method works well from Forms 5.x onwards. In Forms 4.5, you have three choices:
- Hide the RHS block and display a second base table block only when the user presses ADD ALL. Then, when all the records from RHS have been moved to LHS, hide this second base table block.
- Loop through the LHS block, highlighting each record. Populate the record group and then the RHS block so that no second base table block is involved.
- Use only one block for the RHS. Make it a base table block, and perform an EXECUTE_QUERY(ALL_RECORDS) in the WHEN-BUTTON-PRESSED trigger of ADD ALL. Remember, in this case, to set the record property to QUERY_STATUS in the code for PB_ADD, while populating the RHS from the record group as outlined earlier.
  
  The actual code for WHEN-BUTTON-PRESSED of the ADD ALL button is as follows:
  
  SET_BLOCK_PROPERTY('SELECTED_DEPT',QUERY_DATA_SOURCE_NAME, 'DEPT'); GO_BLOCK('ALL_DEPT'); CLEAR_BLOCK('NO_VALIDATE'); SET_BLOCK_PROPERTY('ALL_DEPT', CURRENT_RECORD_ATTRIBUTE, 'VA_UNSELECTED'); GO_BLOCK('SELECTED_DEPT'); EXECUTE_QUERY(ALL_RECORDS);
  The WHEN-BUTTON-PRESSED trigger for PB_REMOVE_ALL is
  
  GO_BLOCK('SELECTED_DEPT'); CLEAR_BLOCK(NO_VALIDATE'); SET_BLOCK_PROPERTY('SELECTED_DEPT', CURRENT_RECORD_ATTRIBUTE, 'VA_UNSELECTED'); GO_BLOCK('ALL_DEPT'); EXECUTE_QUERY(ALL_RECORDS);

The POST-QUERY is not necessary for the SELECTED_DEPT block because no query is involved here. The WHEN-MOUSE-CLICK trigger for this block is similar to the one for ALL_DEPT except that the block name ALL_DEPT is replaced by SELECTED_DEPT and the record group name RG_LHS is replaced by RG_RHS. The function selection_toggle is also similar except that the form variable CTRL_BLK.ADD_COUNT is replaced by CTRL_BLK.REMOVE_COUNT and the button names PB_ADD and PB_ADD_ALL are replaced by PB_REMOVE and PB_REMOVE_ALL.

Intermediate Sorting Using Record Groups

Imagine, in the preceding illustration, if the selected records in the intermediate record group were required to be sorted by DNAME before populating them to the RHS block. This could be a requirement if the user selects records from the LHS block at random.

The description of the bubble sort algorithm is as follows:

To sort an array of n integers, a1, a2, ..., aN, For i in 1 to n loop For j in (i+1) to n-1 loop if a(i) > a(j) then temp = a(i) a(i) = a(j) a(j) = temp end if end loop end loop

The following piece of code illustrates the preceding bubble sort algorithm for sorting rows in a record group:

DECLARE I NUMBER; J NUMBER; Row_count NUMBER; Rg_id recordGroup; Gc_id1 GroupColumn; Gc_id2 GroupColumn; BEGIN /* Check for the existence of the Record Group */ Rg_id := FIND_GROUP('RG_LHS'); IF ID_NULL(rg_id) THEN Return (-1); END IF; /* Determine the sort columns */ Gc_id1 := FIND_COLUMN(rg_id, 'deptno'); Gc_id2 := FIND_COLUMN(rg_id, 'dname'); /* Get the Record Group Row Count */ Row_count := GET_GROUP_ROW_COUNT(rg_id); /* Implement the bubble sort algorithm */ FOR i IN 1..(rg_lhs.sel_count - 1) LOOP FOR j in (i+1)..rg_lhs.sel_count LOOP temp1_id := GET_GROUP_NUMBER_CELL(gc_id1,i); temp1_name := GET_GROUP_CHAR_CELL(gc_id2,i); temp2_id := GET_GROUP_NUMBER_CELL(gc_id1,j); temp2_name := GET_GROUP_CHAR_CELL(gc_id2,j); IF temp1_name > temp2_name THEN SET_GROUP_NUMBER_CELL(gc_id1,i, temp2_id); SET_GROUP_CHAR_CELL(gc_id2, i, temp2_name); SET_GROUP_NUMBER_CELL(gc_id1,j, temp1_id); SET_GROUP_CHAR_CELL(gc_id2, j, temp1_name); END IF; END LOOP; END LOOP; /* End of sorting */ code for populating RHS block from record group follows> < END;

Категории