Home » Developer & Programmer » Forms » multiple row relationship
icon4.gif  multiple row relationship [message #242821] Tue, 05 June 2007 04:08 Go to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

i have query can anyone solve it

eg

i have two tables ie emp and emp_add which contailn 14 records each

now i want to show multiple row(supose 5 rows) at a time of both table with join condition.
how can i do this
when i create relationship with each other
it shows all records of emp table but only one record of emp_add table (ie. related to current cursor on emp.).
so plz help me. Cool
Re: multiple row relationship [message #242887 is a reply to message #242821] Tue, 05 June 2007 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sounds like a classic master-detail relationship. Is it not?
Re: multiple row relationship [message #243076 is a reply to message #242887] Tue, 05 June 2007 23:38 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

hi dear

thanks for replyeing

i m sending u excel file where u can get detail explaination of
the problem.

regards,

ganesh
  • Attachment: doubt(1).xls
    (Size: 18.00KB, Downloaded 178 times)
Re: multiple row relationship [message #243118 is a reply to message #243076] Wed, 06 June 2007 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, but I'm not in the mood to open a potentially [i]dangerous[i] content from an unknown source. Also, some people here are unable to download a file from the Internet. Could you, therefore, explain it without the Excel file?
Re: multiple row relationship [message #243338 is a reply to message #243118] Thu, 07 June 2007 00:01 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

suppose I have two tables dept and tbladd


in table dept in table tbladd
deptno job dept_no add
10 account 10 abc rd
20 finance 20 xay
30 sales 30 mah
40 manager 40 sodi

now I want to show data in the forms in design mode as

dept_no job add
10 account abc rd
20 finance xay
30 sales mah
40 manager sodi

text properties
number of items displayed
4

with join condition of
delete record behaviour cascading
dept.deptno = tbladd.deptno

I put relation property as
deffered no
prevent masterless operation no
auto query no

n my problem is
(IF COURSOR IS ON 10 THEN it will show data of add only one value (of current cursor)
like
dept_no job add
10 account abc rd
20 finance
30 sales
40 manager
(cursor is on deptno 10 then add shows(ONLY ONE) that value)

dept_no job add
10 account xay
20 finance
30 sales
40 manager
(cursor is on deptno 20 then add shows that value)

dept_no job add
10 account mah
20 finance
30 sales
40 manager
(cursor is on deptno 30 then add shows that value)

Re: multiple row relationship [message #243371 is a reply to message #243338] Thu, 07 June 2007 02:20 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
try this

select a.deptno, a.job, b.deptno, b.add
from dept a, tbladd b
where a.deptno = b.deptno
Re: multiple row relationship [message #243381 is a reply to message #243371] Thu, 07 June 2007 02:47 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

means to create block by view or what?

i had create two sepearate block for each tbl.

if to create block by view then what about update/delete?

Re: multiple row relationship [message #244532 is a reply to message #242821] Wed, 13 June 2007 04:16 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

thanks for replying

but i don't want to make it by view

is there any relationship property to join or any new relationship bcoz
i use insert statement to show the data
and wrote trigger to update and delete data

thanks
Re: multiple row relationship [message #245030 is a reply to message #244532] Fri, 15 June 2007 00:56 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
No. Use a view. Why do you people always do things the difficult way by writing your own code? This isn't VB. Forms WILL do it for you.

David
Re: multiple row relationship [message #245681 is a reply to message #242821] Mon, 18 June 2007 08:11 Go to previous messageGo to next message
indrani31
Messages: 7
Registered: May 2007
Location: www
Junior Member

you can do the following


1. create a block having items using all the columns of the table
2.In the Property of the block mention the block as the databast item and name of the database is one of the table..suppose table1
3.the columns of the table1 are some items of the block so in the property of that items mentiomn column name
4.In the items of table2 , mention that they are not a database item
4.in the block level use trigger 'post query'
5. fetch the items of table2 in a cursor and use join condition as
column(table2)=:block_name.item_name(table1)
6.in when new form instance trigger
go_block(block_name)
execute_query;


using this you can join tables....best of luck...reply if you are able to join
Re: multiple row relationship [message #247175 is a reply to message #245681] Mon, 25 June 2007 00:08 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
indrani,

Your reply only works for the last record that is retrieved in block1. It also does not handle inserts or updates that are to be done to the two tables at the same time. The problem is quite complex and that is one of the reasons why they created 'views'. Use them, they work.

David
Re: multiple row relationship [message #247511 is a reply to message #242821] Tue, 26 June 2007 03:05 Go to previous message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
USE views ...

Or if the records have a 1-1 relation fetch the add from the dtl table in the POST-QUERY trigger of the DEPT block. It will work.
BUT ONLY IF the dept and dtl tables have a 1-1 relation.
Previous Topic: Help Help Help Help Plzzzz?
Next Topic: Show Printer List
Goto Forum:
  


Current Time: Wed Dec 07 02:58:56 CST 2016

Total time taken to generate the page: 0.09089 seconds