Re: Des2000 / Forms 4.5 master-detail problem

From: Jomarlen <jomarlen_at_aol.com>
Date: 15 May 1998 01:11:49 GMT
Message-ID: <1998051501114900.VAA25069_at_ladder03.news.aol.com>

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: jomarlen_at_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. Received on Fri May 15 1998 - 03:11:49 CEST