A One To Many CA Example Form
For a start you will have to create a base CA class and 2 CA’s based on this for the CUSTOMERS and ORDERS tables as described in my Tut #2.
Create a new form and add the CA’s to the form’s DE as described. Make sure that the builder for the form DE looks like this:



Use Existing connection handle must be on and enter the name of the connection you will use. I use oConn. On the next tab just set the 2 CA’s as described in the tut as below:




- Use DE datasource is ticked.
- Name the CA’s as CACUSTOMER and CAORDERS.
- Close and save.

Drag and drop each CA from the DE onto the form to create 2 grids. These will be named (by default) grdCrsCustomer and grdCrsOrders. Add a button as shown and in its click code add this: (purists please note I cobbled this up very quickly so no niceties as declaring the vars etc.)

bb=INPUTBOX("Enter a single alphabet.(eg. C)")
IF EMPTY(bb)
    RETURN
ENDIF
THISForm.custid = ALLTRIM(bb)
ThisForm.filldata()

As you will see from the code above there is still a bit to be done.

- Add a property to the form – custid.
- Add a method to the form – filldata

This is the code in FILLDATA()

WITH Thisform.dataenvironment.caCustomer
    This.grdCrscustomer.RecordSource = ""
    .lnodataonload = .F.
    .cfilterClause = [customerid like ']+THISForm.custid+[%']
    IF !.CursorFill()
        AERROR(aErMsg)
        MESSAGEBOX(aErMsg[2],16,"SQL Error",0)
        RETURN
    ENDIF
    This.grdCrscustomer.RecordSource = "crsCustomer"
ENDWITH
WITH Thisform.dataenvironment.caOrders
    .lnodataonload = .F.
    .cfilterClause = [orders.customerid like ']+THISForm.custid+[%']
    .cSelectCmd = [SELECT Orders.* from orders]
    This.grdorders.RecordSource = ""
    IF !.CursorFill()
        AERROR(aErMsg)
        MESSAGEBOX(aErMsg[2],16,"SQL Error",0)
        RETURN
    ENDIF
    This.grdorders.RecordSource = "crsOrders"
ENDWITH
ThisForm.Refresh()

Couple of things more
In the AfterRowColChange Event of the grdCrsCustomer add this code that will be run whenever you click on a new row:

Custid = crsCustomer.customerid
ThisForm.grdorders.RecordSource = ""
WITH ThisFOrm.dataenvironment.caOrders
    .cFilterClause = [orders.customerid like ']+custid+[%'] 
    .filldata(.F.)
ENDWITH
ThisForm.grdorders.RecordSource = "crsOrders"

Lastly you can either add a prg to run this form and put this code in it or open the DE of the form and in the BeforeOpenTables event add this code:

RELEASE oConn
PUBLIC oConn
* use your server name instead of the ZFS04
oConn=sqlstringconnect([Driver={Sql Server}; Server=zfs04;Database=Northwind;Int Security;])

Please note that if you open the builder of the DE again and save it, this code just added will be overwritten by the builder. Also the sqlconnect is on one line!

oConn=sqlstringconnect([Driver={Sql Server}; Server=zfs04;Database=Northwind;Int Security;])

Now all that is left is to set your Default dir to the location where you unzipped these files (so that the form can find the classes) and run the form.

If all is well you will have 2 blank grids and no errors. Also 2 blank cursors have been created. Click Search and enter an alphabet “A” (without quotes) and you will see the grids fill with data. Click on the records of the upper grid and the bottom grid data will change.

Voila! 1 to many.

This data is fully updatable. If you set the BufferModeOverride property of the CA’s then if you make a change and move to the next record, a TABLEUPDATE() is run, or you can leave the property to 5 and run a TABLEUPDATE(1,.F.) to update the whole table. Remember to include the Alias or only the currently selected table will be updated.

This is only an example. In reallife apps., you will NEVER EVER do a SELECT * especially on a client table. Your search should just bring down a single client record and the related orders for that client.

I have included the source code for this form with this document. Please examine the methods and properties set and refer back to tutorial 1 & 2.

Download code
Click here to download the code that is discussed in this article. The download is a zipfile. Its size is 9,295 bytes.

Very nice Mr. Bernard! I tried your simple CA and it works, i used mysql as my backend. thanks a lot! :)