Des2000 / Forms 4.5 master-detail problem
Mike Daniell
I have a form generated by Designer 2000 that has a simple master-detail
structure. The detail table contains a not null column that is
displayed using a poplist.
The problem is that if a particular master record has no associated
detail records, then the form displays default values in the first
detail row on the form, which is mis-leading as it implies a detail row
exists.
How or with what trigger can I clear the detail block?
Thanks,
Mike
Jomarlen
Hi Mike
To achieve this you've got to get a bit cute. The following
is based on Oracle's demo tables CUSTOMER and ORD. The ORD
items ordedate and commplan were given default values for testing.
The following procedure clears the detail block
if no records were queried. I've parameterized it to
make it generic. The three parameters are the name of
the detail block, the name of the primary key item (or
some other item which will not be null if records were
retrieved) and the name of the item to return to when
we leave the detail block, which we must do within
this procedure because we are playing with the validation
property. I could have used query_hits but that would
entail a call to the database.
PROCEDURE Clear_Detail_Block(p_Block VARCHAR2
,p_PK_Item VARCHAR2
,p_Start_Item VARCHAR2) IS
BEGIN
-- First set validation off to allow navigation even if there are required
items
set_form_property(:system.current_form,validation,property_false);
go_block(p_Block);
-- Check the primary key item which will be null if no detail records exist
-- If it is null set the default value items to null
IF name_in(p_PK_Item) IS NULL THEN
:ord.orderdate := NULL;
:ord.commplan := NULL;
-- Set record status to avoid "do you wish to commit changes" messages
set_record_property(:system.cursor_record,:system.cursor_block,status,
query_status);
END IF;
-- Navigate back to the item this procedure was called from
-- and set validation back on
go_item(p_start_item);
set_form_property(:system.current_form,validation,property_true);
END;
You call this function from the block level ON-POPULATE-DETAILS
trigger which was created by forms along with the relationship.
Because of the crappy way this code is written you need to call
the procedure in two places - You should NEVER exit a procedure
using a RETURN statement, but Oracle do.
ON-POPULATE-DETAILS TRIGGER
--
-- Begin default relation declare section
--
DECLARE
recstat VARCHAR2(20) := :System.record_status;
startitm VARCHAR2(61) := :System.cursor_item;
rel_id Relation;
--
-- End default relation declare section
--
--
-- Begin default relation program section
--
BEGIN
IF ( recstat = 'NEW' or recstat = 'INSERT') THEN
Clear_Detail_Block('ORD','ORD.ORDID',startitm); --<< CALL PROCEDURE HERE
RETURN;
END IF;
--
-- Begin ORD detail program section
--
IF ((:CUSTOMER.CUSTID is not null)) THEN
rel_id := Find_Relation('CUSTOMER.CUSTOMER_ORD');
Query_Master_Details(rel_id, 'ORD');
END IF;
Clear_Detail_Block('ORD','ORD.ORDID',startitm); --<< CALL PROCEDURE HERE
--
-- End ORD detail program section
--
IF ( :System.cursor_item <> startitm ) THEN
Go_Item(startitm);
Check_Package_Failure;
END IF;
END;
--
-- End default relation program section
--
Hope this solves your problem. Similar code
works for us.
Regards
John
-------------------------------------------------------
John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103
FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com
e-mail: joma...@aol.com
Web Site: http://members.aol.com/jomarlen/
The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.
-------------------------------------------------------
>The problem is that if a particular master record has no associated<BR>
>detail records, then the form displays default values in the first<BR>
>detail row on the form, which is mis-leading as it implies a detail row<BR>
>exists.<BR>
><BR>
>How or with what trigger can I clear the detail block?<BR>
><BR>
>Thanks,<BR>
>Mike<BR>
John
-------------------------------
John C. Lennon
Utility Partners Inc.
Las Vegas
Web Site: http://members.aol.com/jomarlen/
The views expressed in this message
are those of the author and not
necessarily those of U.P.
and/or its employees.