Re: Forms 4.5 question

From: <stevec_at_zimmer.csufresno.edu>
Date: Fri, 09 Oct 1998 02:43:12 GMT
Message-ID: <6vjt81$n0q$1_at_nnrp1.dejanews.com>

In article <361A11AF.66364A7B_at_ornl.gov>,   "Lisa F. Miller" <luv_at_ornl.gov> wrote:
> I have 2 blocks, BLOCKA & BLOCKB, which have a one-to-many
> relationship. BLOCKB is a multi-record block. If the value of item
> BLOCKA.FIELD1 changes, I need to recalculate value BLOCKB.FIELD1 for
> every record row in that block.

> Does anyone know of a way to deal with
> each record row in a multi-record block without actually navigating to
> that block? If I update BLOCKB.FIELD1 when the
> BLOCKA.FIELD1-POST_CHANGE trigger, I can only update the first record in
> BLOCKB. I cannot navigate to BLOCKB to loop thru the records there
> because "any built-in routine that initiates navigation is
> restricted...and are illegal in triggers that fire in response to
> navigation". Is there another way to determine that BLOCKA.FIELD1 has
> changed so that I can navigate to BLOCKB? Is there a way to mass change
> records in BLOCKB without actually being in BLOCKB? Any help would be
> appreciated.

Yes, there is a way to update a second block based on changes in the first. But it takes a few steps. I'll try to identify them here.

First, use a When-Validate-Item (WVI) trigger, not post-change. (You might read Oracle's cautionary notes about the post-change trigger in the Forms on-line help.)

Also, there is no way to update multiple records in a block without navigating to that block.

So here are the steps:

1. In your BlockA.Field1 WVI trigger, validate the item, then if all is ok, start a non-repeating timer that expires in 1 millisecond. Also store any values you may need in the BlockB calculations. When the process below starts, you won't know which record you started from in BlockA.
2. In a form-level When-Timer-Expired (WTE) trigger, call a procedure to do all the following steps.
3. If necessary, do a Validate(Record_Scope) followed by If Form_Success then... This is necessary if your BlockA has a WVR trigger that needs to validate the record before you can leave BlockA. If the WVR trigger validation fails, before you Raise Form_Trigger_Failure, set BlockA.Field1 := BlockA.Field1; so that Field1's WVI trigger will rerun later, causing this process to restart.
4. Store the Block.Item where the cursor is. The operator may have clicked anywhere on the form. You will use the stored off value to reposition the cursor at the end of the process.
5. If the cursor is not in BlockB, Go_Block('BlockB');
6. Store the :System.Cursor_record of block B. You will use it to reposition the current record at the end of the process.
7. do a First_Record;
8. Create a pl/sql loop to run through all the records in block B, recalculating your values. Exit the loop when system.last_record is True. At the end of the loop do a next_record.
9. Go_Record to the record saved in #6 above.
10. Go_Item back to the item stored in #4 above.

Just a word of caution about this process: If BlockB has hundreds of records, this process will churn through all of them, even causing the form to retrieve all rows yet to be fetched from the server. So the process could take a long time, and wouldn't be advisable.

I have written this from memory, so there may be some other things to do, but I hope this gets you going down the right path.

Good luck!
Steve Cosner

---

http://members.aol.com/stevec5088
Downloadable Quick Access utility form: Display and update any table.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 09 1998 - 04:43:12 CEST