OraFAQ Forum: Forms » fetching the master and detail record

Home » Developer & Programmer » Forms » fetching the master and detail record

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

fetching the master and detail record [message #160517]

Mon, 27 February 2006 02:00

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

first of all thanks all of you. you are helping me

how can i fetch data from Master and detail table in form,

master table has one record and detail table have 4 records. it is poosible to fetch in new form all these record

note= master in form and detial in tabular.

Thanks

plz also tell me form where i can get the help.

Kamran

[Updated on: Mon, 27 February 2006 02:09]

Report message to a moderator

Re: fetching the master and detail record [message #160522 is a reply to message #160517]

Mon, 27 February 2006 02:30

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

Senior Member
Account Moderator

That is a trivia question (unless I didn't get it right); just follow Data Block Wizard and there should be no problem designing layout you'd like to.

Report message to a moderator

Re: fetching the master and detail record [message #160528 is a reply to message #160517]

Mon, 27 February 2006 02:51

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

already I have created a form and 20 record i have saved in master table.

now I want to create another form and just fetch one record at a time with master and detail.

Report message to a moderator

Re: fetching the master and detail record [message #160531 is a reply to message #160528]

Mon, 27 February 2006 02:58

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

Senior Member
Account Moderator

First block is master. Use Data Block Wizard and when it comes to the "Select a layout style for your frame ..." select FORM and, when prompted to select number of displayed records, leave it to the default value (1 record).

Second block is detail. Use Data Block Wizard, create relationship and when it comes to the "Select a layout style for your frame ..." select TABULAR and, when prompted to select number of displayed records, enter any number you wish (for example, 20).

Generate and run the form.

Does this solve your problem?

Report message to a moderator

Re: fetching the master and detail record [message #160556 is a reply to message #160531]

Mon, 27 February 2006 04:46

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

sir actual I want to do:

I have table "abc" master & "xyz" detial. already i have created form as you told me and in this form i can show the all records.

but what I want to do i-e.

i created 2 others table master and detail now i want to fetch above mentioned table record one by one in these tables.. it is possible???

Report message to a moderator

Re: fetching the master and detail record [message #160563 is a reply to message #160556]

Mon, 27 February 2006 05:06

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

Senior Member
Account Moderator

I see ... OK, so you'd like to "copy" data from one table to another?

This could be done by POST-QUERY trigger on the block level, which would look like this:

INSERT INTO dept_2 (deptno, dname, lcc)
VALUES
(:dept.deptno, :dept.dname, :dept.loc);

The same would go for another table.
Performing "commit" in form saves data into another table(s).

Report message to a moderator

Re: fetching the master and detail record [message #160570 is a reply to message #160563]

Mon, 27 February 2006 05:37

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

can you please tell me how can i perform this in a form.

Report message to a moderator

Re: fetching the master and detail record [message #160571 is a reply to message #160570]

Mon, 27 February 2006 05:43

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

Senior Member
Account Moderator

For example, to copy data from master block based upon 'abc' table, create POST-QUERY trigger on that block ('abc') which should look like this:

INSERT INTO abc_copy
(col1, col2, ... coln)
VALUES
(:abc.col1, :abc.col2, ... :abc.coln);

Do the same for the detail block.

Commit in form (press "Save" button on the toolbar, press <KEY-COMMIT> button (this might be <F10>) or some other way.

Report message to a moderator

Re: fetching the master and detail record [message #160579 is a reply to message #160517]

Mon, 27 February 2006 06:47

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

Dear Sir,

Thank you very much for helping me, Sir Can I fetch the record from any master and detial table in new form and in new tables?

example:

first I created a form with table master & detail.
second i created another form with table master1 & detail1.
I entered few records in table master & detail through FORM ABC.
now just i want to fetch that data in new form.

it is possible??

hope understand me?

Best regards

Report message to a moderator

Re: fetching the master and detail record [message #160583 is a reply to message #160579]

Mon, 27 February 2006 07:11

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

Senior Member
Account Moderator

I *think* I do Wink

Wink

OK; what you might do is create a database trigger which would fire upon insert into the table, so that - as soon as it is entered - data goes to the second table too. Doing so, data should be visible in both forms: first one written upon original tables, and another written upon "copy" tables.

Trigger looks like this:

CREATE OR REPLACE TRIGGER trg_a_dpt
   AFTER INSERT
   ON DEPT
   FOR EACH ROW
BEGIN
   INSERT INTO DEPT_2
               (deptno, dname, loc
               )
        VALUES (:NEW.deptno, :NEW.dname, :NEW.loc
               );
END;

Report message to a moderator

display the master and detail record [message #160589 is a reply to message #160583]

Mon, 27 February 2006 07:32

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

Dear Sir,

I dont want to copy the data I want to just fetch/display the record and which record I call, display only that record.

[Updated on: Mon, 27 February 2006 09:40]

Report message to a moderator

Re: display the master and detail record [message #160603 is a reply to message #160589]

Mon, 27 February 2006 10:19

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

Senior Member
Account Moderator

OP

first I created a form with table master & detail.
second i created another form with table master1 & detail1.

It seems that I didn't understand you ... reading this, I guessed that you have two forms.
First form has two blocks in master-detail relationship, written upon 'master' and 'detail' tables.
Second form also has two blocks in master-detail relationship, written upon 'master1' and 'detail1' tables which are copies of 'master' and 'detail' tables. Are they not?

Reading your last post, it appears that you have only two tables: 'master' and 'detail'. You also have two forms, both having two blocks in master-detail relationship, and those blocks are 'master' and 'detail' tables. Is that correct?

Your words: "I want to fetch/display the record and which record I call, display only that record".
This leads to another idea: query.

In other words, when you enter the form, press "Enter query" button, enter value into the field(s) in the master block and press "Execute query". Doing so, master block will have only one record (others from the 'master' table will not be displayed), while detail record will show ALL records from the 'detail' table which correspond to the master record.

Additionally, if you need to see one and only one record in the detail block, repeat the procedure: while in detail block, press "Enter query", enter value, press "Execute query". Regarding the implicit WHERE clause, you might get one or more records.

Is that what you meant? If not, I don't have any idea at the moment ...

Report message to a moderator

Re: display the master and detail record [message #160662 is a reply to message #160603]

Mon, 27 February 2006 23:22

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

you are near to me !!!!
I have two forms.
First form "PPSINGLE" has two blocks in master-detail relationship, written upon 'PP_KD' and 'SUB_PP_KD' tables.

Second form "WORKORDER" also has two blocks in master-detail relationship, written upon 'WO' and 'SUB_WO' tables which are not copies of 'PP_KD' and 'SUB_PP_KD' tables. right!!

There is no relationship between forms PPSINGLE & WORKORDER as weel as tables.

But "PPSINGLE" and "WORKORDER" block have some similar fields. like: in MASTER Block (PPNO,ITEM,FABRIC,GSM) and in Detail Block : (PPNO,C_CODE,PCS,KGS,REMARKS)

when I execute the query in form PPSINGLE I can see the all record in master & detial.

now problem is......

I want to display record in form WORKORDER which record I have seen in PPSINGLE. is this possbile???

********I MEAN I ENTERED VALUES IN FORM PPSINGLE: [MASTER]
PPNO=24,ITEM="SHEETS",FABRIC="JERSEY",GSM="115"
[DETAIL]
PPNO=24,C_CODE='1001',KGS=500,REMARKS="NILL"
PPNO=24,C_CODE='1002',KGS=600,REMARKS="NILL"
PPNO=24,C_CODE='1003',KGS=700,REMARKS="NILL"

AL THESE RECORD I WANT TO DISPLAY IN FORM "WORKORDER".

HOPE UNDERSTAND ME

Report message to a moderator

Re: display the master and detail record [message #160687 is a reply to message #160662]

Tue, 28 February 2006 01:35

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

Senior Member
Account Moderator

The way you described it, no - I'd say that it is not possible. Both forms are built upon different tables and, unless they contain the same data, you can't view them in another form.

But, what you might do, is create a VIEW which will be union of both tables (so, you'd have two views - one as union of master tables and second as union of detail tables) and create a form on those views. Doing so, you'd be able to view all data entered in all tables.

Report message to a moderator

Re: display the master and detail record [message #160713 is a reply to message #160687]

Tue, 28 February 2006 03:08

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

Dear Sir,

Can U tell me how should I perform this in form.

Actual I am biggner and I am making a project.

Report message to a moderator

Re: display the master and detail record [message #160723 is a reply to message #160713]

Tue, 28 February 2006 03:29

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

Senior Member
Account Moderator

But WHY would you want to do something like this?

You have two forms, each written upon its own two master-detail tables. As far as I'm concerned, that's the way it should be.

What you could do, is call one form from another (using CALL_FORM or OPEN_FORM); read some documentation about it. But displaying data from one table in a block which is written upon another table - that doesn't make any sense.

Report message to a moderator

Re: display the master and detail record [message #160745 is a reply to message #160723]

Tue, 28 February 2006 04:20

kamran.it
Messages: 265
Registered: September 2005
Location: Karachi

Senior Member

I made "OrderSheet" with master detail record. now I want to make Purchase order sheet.

Ordersheet and purchase order sheet have lot of samilar record than why we entered data again.

Actaul in master table I can do this easily but in detail table......

I create a LOV, with the help of this I called desire data but only in master, detail block shows only one record this is a big problem.

Report message to a moderator

Previous Topic:	see error :FRM-30351: No list elements defined for list item.
Next Topic:	FRM-92101 Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:28:42 CDT 2024