One form contains some data (in tables) which the second form uses
2nd form is like this
One content canvas form - with 3 blocks based on different tables
Block 1 - Header Block
Block 2 - Child Block (child of block 1) - Multi Record Block
Block 3 - Child Block (child of block 2 ) - Multi Record Block
(All the 3 block have parent child relatonshp / master child relationship established)
The child block3 has 3 non editable fields B3_F1, B3_F2, B3_F3 (database fields) which should be automatically populated based on values that are entered in block 2 i.e once I tab out from a record in block 2 - multiple records in block 3 should automatically be calculated and displayed. (There are multiple records based on some allocation % - say 50% for Price 1 10 % for Price 2)
How should this be achieved. What is the trigger suggested and at what level (in which block)
The problem is not in tab or content canvas but in the logic behind the calculation intended to be performed u well describe ur form , blocks and fields which is very nice to imagine but the most important part in the scenario is ambiguous ...
From ur description u need to loop through some records in block 2 and do the calculation required then assign the output of this calculation...
what so ever it is to the next block once u tabbed the second block so u can use when new block instance trigger or pre-block trigger in the third block to do the calculation required or u could use a simple small button for testing purposes.
Do I understand correctly that this is not a standard Master-Detail relationship between Block 2 and 3?
If it is not.... here's what I would do (haven't tried it, but it should work)
Create a Package Spec in the form. Recommend pkg named P1. Use it for all the pkg variables required below.
declare key values in P1 that can be used to compare with B2 values.
To populate block 3 based on the block 2 record...
In a WNRI (When-new-record-instance) trigger on B2 (block 2)
check B2 key values.
If the B2 key values are null or don't qualify as valid for use in B3...
Make sure B3 is cleared:
If get_block_property('B3',Status) <> 'NEW' then
Go_Block('B3'); Clear_block; Set P1 key values null. Go_Block('B2');
use a Return; at this point, since coming back to the block re-starts the WNRI trigger.
If the B2 key values appear to qualify for a lookup in B3, then...
Compare the key values in the record with saved Key values in P1.
If the key values are equal to the saved key values, then do nothing. (use a Return; at this point)
If the key values are different...
If not form_success then
Store the key values from B2 (used to populate B3) into the saved key values in P1.
Go_Block(B2) --this returns focus back to the record you entered in the first place.
-- returning to B2 re-starts the WNRI trigger, but it should do nothing.
In a WVR (When-validate-record) trigger in B2:
Check the values in the record. If they are equal to the saved key values in P1, do nothing.
If the key values are different, you need to clear and repopulate B3. (But cannot do that from WVR, so...)
Store :System.trigger_record in P1, example: Rec_number varchar2(5)
Start a non-repeating timer that expires in 20 milliseconds.
In a form-level WTE (When-timer-expired) trigger:
(WTE runs after navigation completes, so the cursor will have moved to a different block or record.)
If the timer expiring is named the one you started ( it must be upper-case, no spaces)
(Use GET_APPLICATION_PROPERTY(TIMER_NAME) to get the name of the timer)
save the :System.Cursor_Item in P1 package: Curs_Item varchar2(60);
Check :system.Cursor_Block. If = 'B2' AND :System.Cursor_Record not= P1.Rec_number, then do nothing (use a Return; at this point) ...because user has moved to a different record, and the WNRI trigger above will populate B3.
If :System.Cursor_Block is NOT B2, or is = B2 and in the same record as P1.Rec_number, then....
...same as above WNRI:
If not form_success then
Store the key values from B2 (used to populate B3) into the saved Key values in P1.
Go_Item(P1.curs_Item); --this returns cursor to its proper location