Enter query on a master detailed form 1

[silke]

Hi everybody

I have a forms with a master table canvas, and three detailed canvases.

I want to make a query using fields from one of detailed canvases, and this query must produce result on all the canvases of the forms.

I'm using enter query from toolbar, but the result only takes effect on the canvas i am querying. I wish that the other canvases take the correspondent value. How can i do that?

Kind regards

 

[Hehenka]

Maybe this bulletin from metalink will help you:

To query a master record from a detail record.

DESCRIPTION
===========
The user would like to enter a query criteria in the detail block and then query the master record based on the above user input.

SOLUTION
========
Create the master and detail blocks and the relationship in the usual manner. We will consider here the blocks DEPT and EMP based on the SCOTT schema.

1. Create a KEY-ENTQRY trigger at the block level of the detail block (EMP) and add the following code in it :

GO_BLOCK('dept');
CLEAR_BLOCK(no_commit);
GO_BLOCK('emp');
ENTER_QUERY;

2. Create a KEY-EXEQRY trigger for the detail block and add this :

EXECUTE_QUERY;
:global.deptno := :emp.deptno;
:global.flag := 'T';
GO_BLOCK('dept');

This will store the value of the deptno (primary key) in a global variable :global.deptno and set another global variable :global.flag to 'T'. This will be explained as we progress.

3. Create a WHEN-NEW-RECORD-INSTANCE trigger for the detail block and add the following :

/*
This is used to populate the MASTER block with the corresponding record whenever the user navigates through all the records in the DETAIL block
*/

if get_record_propertysystem.cursor_record,:system.cursor_block,status) = 'QUERY' then
SELECT rowid,deptno,dname,loc
INTO :dept.rowid,:dept.deptno,:dept.dname,:dept.loc
FROM dept
WHERE deptno = :emp.deptno;
/*
This is to set the status of the record populated to QUERY and not to create a new record
*/
SET_RECORD_PROPERTY(1,'dept',status,QUERY_STATUS);
end if;


4. Create a WHEN-NEW-BLOCK-INSTANCE trigger for the master block again and add this :

if :global.flag = 'T' then

-- set the variable to a different value
:global.flag := 'F';
:dept.deptno := :global.deptno;
/*
This will query the master table for the record based on the deptno of the detail table which is stored in :global.deptno

For ex: if an employee of department 10 has been queried in the detail, then the global.deptno will have the value 10, which is used in the query below to fetch the master record.
*/
SELECT rowid,deptno,dname,loc
INTO :dept.rowid,:dept.deptno,:dept.dname,:dept.loc
FROM dept
WHERE deptno = :global.deptno;
set_record_propertysystem.cursor_record,'dept',status,QUERY_STATUS);
GO_BLOCK('emp');

end if;

EXPLANATION
===========
Actually in the above method we are using the base table blocks as a non-base table block when we query the master from detail. We are displaying the master record fetched from the table based on the query supplied in the detail. So after the fetch, if we clear the block or form then we get a "Do you want to save the changes you have made"
alert. So in order to supress this while entering a normal master-detail query, we have created the global variable, :global.flag.

There is a limitation though, if you query detail records and then navigate to the master block and then press the down arrow( i.e., navigate to the next record) and then presses the up arrow to navigate back to the same record, then the detail records that were originally populated will change and a new set of records will get displayed. This is because the normal master-detail query is taking place during MASTER record navigation.

This can be controlled by creating a flag (global variable) and setting its value and thus preventing the user from navigating to the next master record. Do the following :

1) In the KEY-EXEQRY trigger of the detail add the following

:global.control_master := 1;

2) Create a KEY-EXEQRY for the master and add this :

:global.control_master := 0;
EXECUTE_QUERY;

3) Create a KEY-DOWN in the master with the following in it:

IF :global.control_master <> 1 THEN
down;
END IF;

Declare all the global variables before running the Form.