One to many query of one to many tables.

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

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....

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