OraFAQ Forum: Forms » Master -Detail Record relation problem

Home » Developer & Programmer » Forms » Master -Detail Record relation problem (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Master -Detail Record relation problem [message #387453]

Thu, 19 February 2009 04:58

satishveluru
Messages: 8
Registered: September 2007

Junior Member

I have one master block (multirecord )another child block which is showing only one record for every master record in the screen . as i shown in the attachment .

my problem is

for first record ------
when clicking reject_flag(check box)in master block we are giving remarks field in child block .

for second record -----------
while going to check the same reject _flag for the second record ,are providing remarks column in the child block.
but finally saving only one record is saved . that is last updated one

but changes to both child recors should save .
it means child table should have two records .....

now it is one record.....

any one can tell how to save each child for every changes made....

Report message to a moderator

Re: Master -Detail Record relation problem [message #387462 is a reply to message #387453]

Thu, 19 February 2009 05:41

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

satishveluru

I have one master block (multirecord )another child block which is showing only one record for every master record in the screen.

Oh? That's some strange design; in most master-detail cases, one master record can have one or many child records (not vice versa). Therefore, why did you choose such a layout? If master record contains several children, you'll have to scroll up and down in the detail block. But OK, fine with me if you are happy with it.

Quote:

as i shown in the attachment .

There's no attachment in the message.

The rest (actually, the most important part) of message is a little bit fuzzy (perhaps an attachment would help?) - however, how do you save these changes? Did you write your own UPDATE logic (behind one of form triggers)? Because, if you did everything by default (using Forms Wizard), every change made in a data block is successfully saved in the database. Forms just *know* how to properly do that.

So: did you change something? If so, what and how does it look like? Also, I'd suggest you to revert to default Forms behaviour.

Report message to a moderator

Re: Master -Detail Record relation problem [message #387464 is a reply to message #387462]

Thu, 19 February 2009 05:48

satishveluru
Messages: 8
Registered: September 2007

Junior Member

yes iam storing that child into table only....
block leve (on_commit ) trigger.

when i checking reject_flag for both the two master records ,at finally i am saving the form in form level

we know that obviously first block level one record only is saving ...
u understood it means second time cheking reject _flag that record only saving ????

and saving the child in block level ....

i will tell you the exact problem iam facing

if we have master-details ....going one record sto another it is executing Query by means relaion....
then what happend to my changed child saved record....???
it is not saving.....

Report message to a moderator

Re: Master -Detail Record relation problem [message #451799 is a reply to message #387453]

Fri, 16 April 2010 15:24

kojot
Messages: 27
Registered: December 2009
Location: Poland

Junior Member

Dear all,

I decided to refresh this topic, because my problem seems to be similar.
I've got two tables connected by master-detail relationship:

DESC komplet
Name                           Null     Type                                                     
------------------------------ -------- --------------------------------------------
ID_KOMPLETU                    NOT NULL NUMBER                       
NAZWA_KOM                      NOT NULL VARCHAR2(30)       

DESC segment
Name                           Null     Type                                                      
------------------------------ -------- --------------------------------------------
ID_SEGMENTU                    NOT NULL NUMBER                       
ID_KOMPLETU                           	NUMBER                  
NAZWA_SEG                      NOT NULL VARCHAR2(35)

I created a master-detail relationship using data block wizard. Everything seems to work fine, it's populating, updating etc. like it should do. But, if I create a new record in master table (forms cleans detail block) and I put some value there (for example 'LOL'), then, using a mouse, I put the cursor in detail block and insert some values there ('ROTFL1', 'ROTFL2') and do commit I can see that forms sent 3 records successfully. Now, I'm changing records in master block, I see that 'LOL' is added properly, but if I choose it no of data is showing up in detail block.

ID_KOMPLETU            NAZWA_KOM                      
---------------------- ------------------------------ 
69                     LOL                            

ID_SEGMENTU            ID_KOMPLETU            NAZWA_SEG                           
---------------------- ---------------------- ----------------------------------- 
87                     1                      ROTFL1                              
88                                            ROTFL2

I was trying to do something like commit and execute query in master block using when-new-block-instance trigger, but it makes form strange behaviour. How can I fix the problem ?

Forms [32-bitowe] wersja 10.1.2.0.2 (Produkcyjna)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Regards,
Wojtek

Report message to a moderator

Re: Master -Detail Record relation problem [message #451805 is a reply to message #451799]

Fri, 16 April 2010 15:40

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Master-detail relationship is created on ID_KOMPLETU column, right? If so, how come it is missing for the ROTFL_2 record?

Did you modify triggers/procedures created by wizard? If so, well, you shouldn't have. Try to remove relationship (delete it) and - using wizard - create it once again.

Run the form and try again.

Basically, what you described seems to be obvious and easy-to-do. I can't imagine what might go wrong here.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451811 is a reply to message #387453]

Fri, 16 April 2010 17:01

kojot
Messages: 27
Registered: December 2009
Location: Poland

Junior Member

Thanks for your reply, Littlefoot.

Yes, master-detail relationship is created on ID_KOMPLETU. I didn't change anything, what wizard made. I tried to delete and make once again relationship but it didnt help.
I even made a new form with those tables to check it again. It behaves exactly the same.
That's how it looks, when I try to insert data in the way i described in previous post.

http://papierosek.pl/zdjecia_wojtek/master-detail.png

ID_KOMPLETU and ID_SEGMENTU values is inserting by a trigger in database, maybe something goes wrong with it ?

CREATE OR REPLACE TRIGGER komplet_t
  BEFORE INSERT ON komplet
  FOR EACH ROW
BEGIN
  SELECT komplet_s.nextval INTO :new.id_kompletu
  FROM dual;
END komplet_t;

So I tried to make a pre-insert trigger in forms in both blocks to insert id from sequence, but after commit it gives me an error ORA-02291. Looks like it try to commit detail table before the master is commited. When-validete-item trigger doesn't solve the problem neither, got the same error.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451869 is a reply to message #451811]

Sat, 17 April 2010 16:03

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

kojot wrote on Fri, 16 April 2010 23:01

So I tried to make a pre-insert trigger in forms in both blocks to insert id from sequence, but after commit it gives me an error ORA-02291.

That'd be because you did it on both blocks. The master detail relationship automatically copies the values of the joined columns to the detail block. Your pre-insert trigger will have over-ridden it with a new value. Try just putting the pre-insert on the master block.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451889 is a reply to message #387453]

Sun, 18 April 2010 04:48

kojot
Messages: 27
Registered: December 2009
Location: Poland

Junior Member

Thanks cookiemonster for your reply.

I tried to put pre-insert trigger only on the master block, but it didnt solve my problem.
Both of tables have got a sequence and a trigger in database, which is inserting the correct value in primary key column (id_kompletu, id_segmentu) before commit.
Everything works fine, if user is adding records in master block first, do commit, and then adds records in detail block.
But let see whats happend in this particular case that I show on picture in my previous post.
Last record in master block is with id 72. When I enter to this record, forms automatically copies this value to id_kompletu in detail block, correct. Now I create a new record in master block. Id_kompletu is ofc null (for users id doesn't even exist), I put the value 'super5' in nazwa_kom, then, using a mouse, I put the cursor in nazwa_seg on the detail block. Id_segmentu is null (correct), id_kompletu is 72 (nothing changes) and I put the value 'super6' in nazwa_seg. Next created record in detail block 'super7' doesnt have a vulue in id_kompletu column, thats because 'super5' id_kompletu in master is null ? Ok, I do commit, 3 records are sent successfully, but not in the way I want it too. How can I handle with it ?

Report message to a moderator

Re: Master -Detail Record relation problem [message #451956 is a reply to message #387453]

Mon, 19 April 2010 04:14

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well the obvious problem is that it's DB that's populating the pk in the master record, but forms needs to populate the corresponding columns in the child record.

You need to have the "DML Returning Value" property on the master block set to Yes so that the pk value set by the db trigger gets returned to the datablock.

This may or may not fix it - it depends on whether the code to copy the value to the detail block can kick in between applying the master record to the db and applying the detail record.
If it still doesn't work then you're going to have to get rid of the DB trigger and use a pre-insert trigger in the form to assign the pk value.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451980 is a reply to message #387453]

Mon, 19 April 2010 06:53

kojot
Messages: 27
Registered: December 2009
Location: Poland

Junior Member

Thank you cookiemonster for your help.

I've changed the 'DML Returning Value' to Yes and it works. I've made a few transaction, and every of them was added properly.
It's my first application in forms. Next time, to make a good form, should I use pre-insert trigger in form on my pk column ?

Anyway, you solved my problem. Thank you a lot.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451982 is a reply to message #387453]

Mon, 19 April 2010 07:07

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the trigger works when DML Return is set to Y then I'd just do it that way. I just couldn't remember how the timing worked and wasn't in a position to test.
Bear in mind that you need to set the DML Return to Yes for every block that's based on a table that has DB triggers.

Report message to a moderator

Re: Master -Detail Record relation problem [message #451992 is a reply to message #387453]

Mon, 19 April 2010 09:11

kojot
Messages: 27
Registered: December 2009
Location: Poland

Junior Member

Thanks for good advice ;]

Regards,
Wojtek.

Report message to a moderator

Previous Topic:	Import from java class into oracle form
Next Topic:	Forms 11g and exec_sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:20:21 CDT 2024