How to work with a "one-to-many" relationship in a form

[Ducu]

Hi,
I have 2 tables with a "one to many" relationship. I want to create my own form (not using wizard) for this two tables. The fields of the parent table are mostly textboxes of the form and for the child table I have a grid Object.
What I would expected is that whenever I move the record pointer in the parent table and reload the values in the textbox controls ( using scatter memvar and refresh() method of the form), the content of the grid Object will change with the related data. It is not working as I expected. I have the same content, sometimes nothing in the grid. What is wrong ?

The navigation buttons contain:

select 1 && master table
SKIP 1 && SKIP -1, GO TOP, test for EOF(), BOF() etc.
scatter memvar

&& refresh grid
&& refresh



The code is something like this (please ignore the names of the fields, table, objects, the style of programming, they are not important) and I put the part of the code that I thought it is important for my question (no exit button):

select 1
IF !USED('SALARIATI.DBF')
use SALARIATI.dbf in 1 EXCLUSIVE
ENDIF

select 2

IF !USED('INTRETINUTI.DBF')
use INTRETINUTI.dbf in 2 EXCLUSIVE
ENDIF

SELECT 1
SET ORDER TO TAG Cod OF c:\salarii\intretinuti.cdx IN Intretinuti
SET RELATION TO cod INTO Intretinuti ADDITIVE
SET SKIP TO Intretinuti

set order to tag cod
go top
SCATTER MEMVAR

frmMyForm = CREATEOBJECT('Form') && Create a Form
frmMyForm.Width=750
frmMyForm.Height=500
frmMyForm.Caption="SALARIATI"
frmMyForm.Closable = .F. && Disable the Control menu box
frmMyForm.BorderStyle=2

frmMyForm.AddObject('lblcCod','Label')
frmMyForm.AddObject('txtcCod','txtMyTxtBox1')
frmMyForm.AddObject('lblcNume','Label')
frmMyForm.AddObject('txtcNume','TextBox')
frmMyForm.AddObject('lblcPrenume','Label')
frmMyForm.AddObject('txtcPrenume','TextBox')
frmMyForm.AddObject('grdGrdIntretin','MyGrid')

frmMyForm.lblcCod.Visible=.T.
frmMyForm.lblcCod.Left=10
frmMyForm.lblcCod.Top=49
frmMyForm.lblcCod.FontBold=.T.
frmMyForm.lblcCod.BackStyle=0
frmMyForm.lblcCod.Caption="Matricol :"
frmMyForm.txtcCod.Visible=.T.
frmMyForm.txtcCod.Left=72
frmMyForm.txtcCod.Top=47
frmMyForm.txtcCod.Width=50
frmMyForm.txtcCod.MaxLength=5
frmMyForm.txtcCod.InputMask="99999"
frmMyForm.txtcCod.ControlSource="M.Cod"
frmMyForm.lblcNume.Visible=.T.
frmMyForm.lblcNume.Left=10
frmMyForm.lblcNume.Top=77
frmMyForm.lblcNume.FontBold=.T.
frmMyForm.lblcNume.BackStyle=0
frmMyForm.lblcNume.Caption="Nume :"
frmMyForm.txtcNume.Visible=.T.
frmMyForm.txtcNume.Left=72
frmMyForm.txtcNume.Top=75
frmMyForm.txtcNume.Width=200
frmMyForm.txtcNume.ControlSource="M.Nume"

frmMyForm.lblcPrenume.Visible=.T.
frmMyForm.lblcPrenume.Left=10
frmMyForm.lblcPrenume.Top=105
frmMyForm.lblcPrenume.FontBold=.T.
frmMyForm.lblcPrenume.BackStyle=0
frmMyForm.lblcPrenume.Caption="Prenume :"

frmMyForm.txtcPrenume.Visible=.T.
frmMyForm.txtcPrenume.Left=72
frmMyForm.txtcPrenume.Top=103
frmMyForm.txtcPrenume.Width=200
frmMyForm.txtcPrenume.ControlSource="M.Prenume"

frmMyForm.grdGrdIntretin.Visible=.T.
frmMyForm.grdGrdIntretin.Left=5
frmMyForm.grdGrdIntretin.Top=305
frmMyForm.grdGrdIntretin.Width=533
frmMyForm.grdGrdIntretin.Height=120
frmMyForm.grdGrdIntretin.ColumnCount=2
frmMyForm.grdGrdIntretin.RecordSourceType=2
frmMyForm.grdGrdIntretin.RecordSource="Intretinuti.dbf"

frmMyForm.grdGrdIntretin.Column1.ControlSource="Intretinuti.cnp"
frmMyForm.grdGrdIntretin.Column1.Width=100
frmMyForm.grdGrdIntretin.Column1.Header1.Caption="Cod NP"
frmMyForm.grdGrdIntretin.Column1.Header1.FontSize=8
frmMyForm.grdGrdIntretin.Column1.Header1.FontBold=.T.
frmMyForm.grdGrdIntretin.Column2.ControlSource="Intretinuti.nume"
frmMyForm.grdGrdIntretin.Column2.Header1.Caption="Nume"
frmMyForm.grdGrdIntretin.Column2.Header1.FontSize=8
frmMyForm.grdGrdIntretin.Column2.Header1.FontBold=.T.
frmMyForm.grdGrdIntretin.Column2.Width=120

frmMyForm.SHOW(1)

 

[Mike Lewis]

Docu,

Rather than adding all that code, why not add the two tables to the form's data environment, and set the relationship there (by dragging the linking field from one table to the other).

Then, drag each individual field from the parent table, and drop it on the form. These will appear as textboxes with labels.

Next, multi-select a few fields from the child table (in the DE), and drag these to the form. When you drop multiple fields in this way, it will generate a grid, with one column for each of the fields.

Basically, that's all you need to do. You'll still need a navigation control, and you should make sure you refresh the form after each navigation. The navigation code you showed in your example should work fine.

You'll find that as you navigate the parent record, the child grid will stay in sync with it. This doesn't address the issue of updating the fields -- for that, you should buffer the tables, and supply Save and Revert buttons, but that's another issue.

One small point about your code: It's not a good idea to do SELECT 1. It's better to SELECT a specific alias, as you can never be sure that any given table will always be in a given work area.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Ducu]

Hi,

Thank you for your tips, but I rather want to construct the form from scratch. I know how to use the form wizard, and with the form wizard, the grid works perfect. I was not sure what or where is my wrong-doing.

I'll keep looking for the answer, if there is one

Thank you,

Dan C. Nagy

 

[Mike Lewis]

Dan,

OK, I take your point about wanting to do it from scratch. However, I wasn't suggesting using the form wizard -- but rather using the data environment to set the relationship.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Ducu]

Mike,

I was looking in the VFP help, and I have no idea how to create from program the data environment with the relationships between the tables because I have different forms in my program and each one with a different data environment. Would you mind to show me a very short example how to create the relationship with object programming ?

Thank you in advance,
Dan C. Nagy

 

[Mike Lewis]

Dan,

Just to make sure I understand what you are asking. You already have a bunch of form, and these already have a data environment, but with different tables. You haven't yet established the relationships between those tables, and you now want to do that programmatically. Is that right?

It's certainly possible to do that, but wouldn't it be easier just to go into each form's DE and create the relationship manually? After all, the table names and field names will be different in each case, so you won't be able to provide any completely generic code to do the job. You'd probably end up having to go into each DE anyway, just to work out which fields need linking.

My apologies if I've misunderstood.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Ducu]

Mike,

I am sorry that my question wasn't clear. My problem is:
I have a prg file where I create my form using:

frmMyForm = CREATEOBJECT('Form') && Create a Form
frmMyForm.Width=750
frmMyForm.Height=500
frmMyForm.Caption="SALARIATI"
frmMyForm.Closable = .F. && Disable the Control menu box
frmMyForm.BorderStyle=2

In the next step I want to set-up the data environment for the created form, frmMyform. And my questions are: Can I set-up using object-oriented programming the data environment for the form frmMyForm and if the answer is Yes , then How I can do that ? I need a very short example like how to open 2 tables and construct a 1 to manny relationship between them.

My appologies if I've wasn't clear.

Thank you,
Dan

 

[Mike Lewis]

Dan,

OK. I understand exactly what you want to do now.

You can do something like this:

frmMyForm = CREATEOBJECT('Form')  && Create a Form
frmMyForm.Width=750
frmMyForm.Height=500
frmMyForm.Caption="SALARIATI"
frmMyForm.Closable = .F.  && Disable the Control menu box
frmMyForm.BorderStyle=2

frmMyForm.AddObject("DE", "DateEnvironment")
frmMyForm.DE.AddObject("table1","Cursor")
frmMyForm.DE.AddObject("table2","Cursor")
frmMyForm.DE.AddObject("Rel","Relation")
frmMyForm.DE.Table1.CursorSource = "customer"
frmMyForm.DE.Table2.CursorSource = "orders"
frmMyForm.DE.Rel.ChildAlias = "orders"
frmMyForm.DE.Rel.ParentAlias = "customer"
frmMyForm.DE.Rel.ChildOrder = "cust_id"
frmMyForm.DE.Rel.RelationExpr = "cust_id"

That will establish the relationships. You can then add controls to the form in a similar way, and set their control sources or record sources to the fields in the tables.

However, I wonder if this is really the most convenient way to do what you want. I would favour creating the form in the form designer, opening the data environment, adding the tables interactively, setting the relationship by dragging the parent field to the child field, and then dragging individual fields from the DE onto the form. In other words, just as I described in my first post in this thread.

It seems to me that if you do it programmatically, you defeat part of the object of having the DE, that is, the ability to create controls by drag and drop.

That said, there are also benefits in doing it programmatically. Either way, you should get to where you want to go.

Please note that I haven't tested the above code, and cannot guarantee if will work perfectly.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

Just want to add to what Mike said. You said something in your first message about not using the Wizard. The Form Designer is _not_ a wizard; it's a tool to make it easy to create forms. Trying to create visual objects entirely in code is a waste of your time and energy.

Tamar

 

[Ducu]

Hi guys,

Thank you very much for your tips. I am new in the VFP programming and I want to know how the things are working in VFP and then I will choose the proper tool for my projects. I will probably come back to ask new questions.

Thank you again,

Dan

 

[Ducu]

Hello,

Now I am back with my original problem. After some work I create my own data environment (the Relation 1 to many is set), but now I still have the same bug. When I am jumping up and down the parent table, the grid control of the child table is giving me the same output. I was expecting that: when I am on the first record of the parent table in the grid control related I'll have only the record related with the first record in the parent table. No, I have all the child records no matter what I am trying to do. I wrote some parts of the code in program. Any ideas ?:

OPEN DATABASE C:\salarii\personal.dbc exclusive

de=CREATEOBJECT('DataEnvironment')
DE.AddObject("table1","Cursor")
de.AddObject("table2","Cursor")
dE.AddObject("Rel","Relation")


DE.Table1.CursorSource = "salariati"
DE.Table1.Alias = "salariati"
DE.Table1.Database = "c:\salarii\personal.dbc"
DE.Table1.Exclusive=.t.
DE.Table2.CursorSource = "intretinuti"
DE.Table2.Database = "c:\salarii\personal.dbc"
DE.Table2.Alias = "intretinuti"
DE.Table2.Exclusive=.t.
DE.Rel.ChildAlias = "intretinuti"
DE.Rel.ParentAlias = "salariati"
DE.Rel.ChildOrder = "cod"
DE.Rel.RelationalExpr = "cod"
DE.Rel.OneToMany = .t.

frmMyForm = CREATEOBJECT('MyForm')  && Create a Form
de.OpenTables()

select salariati
set order to tag cod
go top
SCATTER MEMVAR
nrpersintr=m.nr_pers_intr

frmMyForm.AddObject('cmdCommand1','cmdMyCmndBtn')   
frmMyForm.AddObject('lblcCod','Label')
frmMyForm.AddObject('txtcCod','txtMyTxtBox1')
frmMyForm.AddObject('lblcNume','Label')
frmMyForm.AddObject('txtcNume','TextBox')
frmMyForm.AddObject('grdGrdIntretin','MyGrid')

frmMyForm.lblcCod.Visible=.T.
frmMyForm.lblcCod.Left=10        
frmMyForm.lblcCod.Top=49
frmMyForm.lblcCod.FontBold=.T.
frmMyForm.lblcCod.BackStyle=0
frmMyForm.lblcCod.Caption="Matricol :"

frmMyForm.txtcCod.Visible=.T.
frmMyForm.txtcCod.Left=72        
frmMyForm.txtcCod.Top=47
frmMyForm.txtcCod.Width=50
frmMyForm.txtcCod.MaxLength=5
frmMyForm.txtcCod.InputMask="99999"
frmMyForm.txtcCod.ControlSource="m.Cod"

frmMyForm.lblcNume.Visible=.T.
frmMyForm.lblcNume.Left=10        
frmMyForm.lblcNume.Top=77
frmMyForm.lblcNume.FontBold=.T.
frmMyForm.lblcNume.BackStyle=0
frmMyForm.lblcNume.Caption="Nume :"

frmMyForm.txtcNume.Visible=.T.
frmMyForm.txtcNume.Left=72        
frmMyForm.txtcNume.Top=75
frmMyForm.txtcNume.Width=200
frmMyForm.txtcNume.ControlSource="M.Nume"

frmMyForm.grdGrdIntretin.Visible=.T.
	frmMyForm.grdGrdIntretin.Left=5       
	frmMyForm.grdGrdIntretin.Top=305
	frmMyForm.grdGrdIntretin.Width=533
	frmMyForm.grdGrdIntretin.Height=120
	frmMyForm.grdGrdIntretin.ColumnCount=2
	frmMyForm.grdGrdIntretin.RecordSourceType=2
	frmMyForm.grdGrdIntretin.RecordSource="Intretinuti"
	frmMyForm.grdGrdIntretin.LinkMaster="Salariati"
        frmMyForm.grdGrdIntretin.RelationalExpr="Cod"
	
	 frmMyForm.grdGrdIntretin.Column1.ControlSource="Intretinuti.cnp"
frmMyForm.grdGrdIntretin.Column1.Width=100
    frmMyForm.grdGrdIntretin.Column1.Header1.Caption="Cod NP"
    frmMyForm.grdGrdIntretin.Column1.Header1.FontSize=8
    frmMyForm.grdGrdIntretin.Column1.Header1.FontBold=.T.
    frmMyForm.grdGrdIntretin.Column2.ControlSource="Intretinuti.nume"
    frmMyForm.grdGrdIntretin.Column2.Header1.Caption="Nume"
    frmMyForm.grdGrdIntretin.Column2.Header1.FontSize=8
    frmMyForm.grdGrdIntretin.Column2.Header1.FontBold=.T.
    frmMyForm.grdGrdIntretin.Column2.Width=120

DEFINE CLASS cmdMyCmndBtn AS CommandButton  && Create Command button
   Caption = '\<Iesire'  && Caption on the Command button
   Cancel = .T.  && Default Cancel Command button (Esc)
   Left =615  && Command button column
   Top = 450 && Command button row
   Height = 20  && Command button height\
   Width=70
   FontSize=8
   
   PROCEDURE Click
        && Stop event processing, close Form
        THIS.Parent.Release()

ENDDEFINE

DEFINE CLASS MyGrid AS Grid

PROCEDURE DELETED
LPARAMETERS nRecNo
if !flgDelete
  flgDelete=.t.
else
  flgDelete=.f.     
endif

RETURN .T.

ENDDEFINE

DEFINE CLASS MyForm AS Form
	Width=750
	Height=500
	Caption="SALARIATI"
	Closable = .F.  && Disable the Control menu box
	BorderStyle=2
	DataSession=1
	dataenvironment=de
ENDDEFINE


DEFINE CLASS txtMyTxtBox1 AS TextBox
Visible=.T.
Left=72        
Top=47
Width=50
MaxLength=5
InputMask="99999"
ControlSource="M.Cod"


PROCEDURE Valid
set escape off
select 6
use salariati.dbf in 6 again alias sala
set order to tag Cod


if !flgAppend
  set filter to Cn=m.cn

	if !seek(m.Cod)
   		wait wind ("Nr. Matricol nu poate fi schimbat !")
   		set filter to
   		select 6
   		use
   		set escape on
   		return .f.
	else
	    set filter to
   		select 6
   		use
   		set escape on
   		
   		return .t. 
 endif
 
else
   if seek(m.Cod)
        wait wind ("Nr. Matricol existent in baza de date")
        select 6
        use
        return .F.
   else
       select 6
       use 
       set escape on
       return .T.     
   endif
endif


select 6
use
set escape on
RETURN .T.

ENDDEFINE