Re: Oracle Developer/2000: mandatory master-detail relationship.

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/08/18
Message-ID: <871918932.21574_at_dejanews.com>#1/1


In article <33F34AD0.627E_at_Boeing.Com>,   Ken Niedermeyer <Ken.Niedermeyer_at_Boeing.Com> wrote:
>
> I am working with Developer/2000 - Forms4.5 and need to have the form
> maintain a mandatory master-detail relationship between two multi-record
> blocks. The operator MUST enter information into the detail record when
> a master record is created. The twist to this is there is a database
> constraint stating that the master record MUST exist prior to the detail
> record being created. This sounds kinda simple at first, but with GUI
> navigation and all the problem becomes tricky. For example, the
> operator should not be able to navigate to the next record of the master
> block if their are not any detail records, additionally, if the user
> enters records then deletes them from the detail block, the commit
> should not complete if there are not any detail records associated with
> the master record. If anyone has any ideas or better yet a solution, I
> would love to hear about it.
>
> Thanks!

First, create a hidden non-base-table item in your master block. Use this to indicate whether or not there are any detail rows. Set it when the detail block is queried, and when detail rows are added or deleted. Check this item in a WVR (When-Validate-Record) master-block trigger. In the WVR trigger, if no detail rows exist, set a "row" parameter (or global) in the form to indicate the Master block row that needs a detail row. Do not issue an error message from the WVR trigger.

In a form-level when-new-item-instance trigger, check this "row" parameter. (Make sure any item or block-level WNII triggers run AFTER the form-level one.) If the row parameter is set, check the cursor item's location. If the cursor is still in the master-block, but not in the proper row, go_record back to the proper row, go_block to the detail block, and issue an error message. If the cursor is in a different block (neither master nor detail), go_block to the detail block and issue an error message. If the cursor is in the detail block, everything should be ok--user is ready to enter a detail row.

In a post-block trigger in the detail block, make sure there is at least one record (unless the master-block row status is 'NEW'). If not, set the same row parameter as above. If the user navigates anywhere outside of the detail block or the one master block row, then go back to the detail block and issue the same error message.

In the WNII trigger, if the row parameter is set, set it to null immediately, before any navigation or error messages are issued. If the user has navigated back to the master block row, set some item in the master block row to its current value so the WVR trigger will run again if the user tries to leave the row before making a change.

I hope the above gets you started in the right direction. Unfortunately, I haven't worked on a similar situation, so I'm not sure how well the solution will work. In other words: beware, you get you pay for, and this advice is free. ;-)

Steve Cosner



http://members.aol.com/stevec5088 Download QA, a dynamic data utility form-- Quick display and update access to any table.
-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Mon Aug 18 1997 - 00:00:00 CEST

Original text of this message