One to many query of one to many tables.  
Author Message
Aleniko29139





PostPosted: Visual FoxPro General, One to many query of one to many tables. Top

Hi;

Please forgive me if this is such a basic question:

I have 2 tables: Orders and OrdersDetail.

I need to have a form with 2 grids where the top one shows the orders and the bottom the detail of the order. Both need to show info for a specific customer_id.

I am not sure what is the best way to produce this - using a sql command, creating a parameterized view or something else I am not aware of I know that a filter is out of question.

Thx.


Visual FoxPro1  
 
 
AndyKr





PostPosted: Visual FoxPro General, One to many query of one to many tables. Top

Check out the SOLUTIONS.APP that ships with Visual FoxPro. You will find an example form, with source code in the "CONTROLS" section that shows a One-Many-Many form that shows a Customer/Order Header/Order Detail using navigation and two grids.

Plus a zillion other useful little examples and techniques....



 
 
CetinBasoz





PostPosted: Visual FoxPro General, One to many query of one to many tables. Top

One of the easiest ways is to use grid's LinkMaster,childorder and relationalexpr properties.ie:

Public oForm
oForm = Createobject('mySample')
oForm.
Show

Define Class
mySample As Form
Width
= 420
Height = 460
DataSession = 2

Add Object txtCustomer As TextBox With ;
left=10,Top=10,Width=90,ControlSource = 'customer.cust_id',ReadOnly=.T.
Add Object cmdPrev As CommandButton With ;
left=110,Top=10,Width = 50,Caption='\<Previous'
Add Object cmdNext As CommandButton With ;
left=170,Top=10,Width = 50,Caption='\<Next'
Add Object grd1 As Grid With ;
left = 10, Top=45, Width=400,Height=200,RecordSource='Orders',;
LinkMaster='Customer',ChildOrder='cust_id',RelationalExpr='cust_id'
Add Object grd2 As Grid With ;
left = 10, Top=250, Width=400,Height=200,RecordSource='OrdItems',;
LinkMaster='Orders',ChildOrder='order_id',RelationalExpr='order_id'

Procedure Load
Use
(_samples+'data\customer') In 0
Use (_samples+'data\orders') In 0
Use (_samples+'data\orditems') In 0
Endproc

Procedure
cmdPrev.Click
If
!Bof('customer')
Skip -1 In 'customer'
Endif
Thisform
.Refresh()
Endproc

Procedure
cmdNext.Click
If
!Eof('customer')
Skip 1 In 'customer'
Endif
Thisform
.Refresh()
Endproc
Enddefine