|
Home > Archive > Programming with dBASE > April 2005 > Parent/Child Tables - Indexing
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Parent/Child Tables - Indexing
|
|
| Dave Edwards 2005-04-20, 8:23 pm |
| Greetings,
I have two tables with a "one to many" parent/child relationship...
Table 1 - Firstpart.dbf has two fields...
Firstpart (character field)
FirstpartID (autoincrement field with an index)
Table 2 - Nextword.dbf has two fields...
FirstpartID (long integer field with an index)
Nextword (character field with an index)
In order to get the parent/child relationship to work correctly, it
seems that I have to activate the FirstpartID index in table 2.
However, when I display table 2 in a grid on my form, I want to see
the items indexed on the Nextword field.
How can I achieve this while still maintaining the parent/child
relationship?
Any help gratefully received,
Dave Edwards
| |
| Marko Mihorko [dBVIPS] 2005-04-21, 7:23 am |
| Hello Dave!
"Dave Edwards" je napisal v sporocilo...
> I have two tables with a "one to many" parent/child relationship...
....
> In order to get the parent/child relationship to work correctly, it
> seems that I have to activate the FirstpartID index in table 2.
>
> However, when I display table 2 in a grid on my form, I want to see
> the items indexed on the Nextword field.
As usual mark/copy/paste/save everything below to a file named f.e. <dave.wfm>
and later on doubleclick on it under the <Forms> tab of the <Navigator> window.
Then just navigate in the upper grid and at the same time check the order of
the items in the NextWord column of the lower grid...
Marko Mihorko [dBVIPS]
set database to; close tables
if file('FirstPart_M.dbf'); drop table FirstPart_M
endif
create table FirstPart_M(FirstPar
t char(10), FirstPartID autoinc)
insert into FirstPart_M (FirstPart) values ('First one')
insert into FirstPart_M (FirstPart) values ('Second one')
insert into FirstPart_M (FirstPart) values ('Third one')
if file('NextWord_M.dbf'); drop table NextWord_M
endif
create table NextWord_M(FirstPart
ID integer, NextWord char(10))
insert into NextWord_M values (3,'Yellow')
insert into NextWord_M values (2,'Purple')
insert into NextWord_M values (1,'White')
insert into NextWord_M values (3,'Red')
insert into NextWord_M values (2,'Magenta')
insert into NextWord_M values (1,'Grey')
insert into NextWord_M values (3,'Blue')
insert into NextWord_M values (2,'Black')
insert into NextWord_M values (1,'Brown')
use NextWord_M exclu
index on str(FirstPartID) + NextWord tag First_Next; use
** END HEADER -- do not remove this line
//
// Generated on 21.04.2005
//
parameter bModal
local f
f = new daveForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class daveForm of FORM
with (this)
text = "Dave - Parent/Child Tables - Indexing"
endwith
this.FIRSTPART_M1 = new QUERY()
this.FIRSTPART_M1.parent = this
with (this.FIRSTPART_M1)
onOpen = class::FIRSTPART_M1_
ONOPEN
sql = 'select * from "FirstPart_M.DBF"'
active = true
endwith
this.NEXTWORD_M1 = new QUERY()
this.NEXTWORD_M1.parent = this
with (this.NEXTWORD_M1)
sql = 'select * from "NextWord_M.DBF"'
active = true
endwith
with (this.NEXTWORD_M1.rowset)
indexName = "FIRST_NEXT"
masterRowset = form.firstpart_m1.rowset
masterFields = "First_Next"
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.firstpart_m1.rowset
height = 7.0
left = 1.0
top = 0.5
width = 38.0
endwith
this.GRID2 = new GRID(this)
with (this.GRID2)
dataLink = form.nextword_m1.rowset
height = 7.5
left = 1.0
top = 8.0
width = 38.0
endwith
this.rowset = this.firstpart_m1.rowset
function firstpart_m1_onOpen
f = new field()
with (f)
fieldName = 'First_Next'
beforeGetValue = {||str(this. parent['FirstPartID'
].value)}
endwith
this.rowset.fields.add(f)
return
endclass
| |
| Roland Wingerter 2005-04-21, 7:23 am |
| Hello Marko,
Marko Mihorko [dBVIPS] wrote:
>
> "Dave Edwards" je napisal v sporocilo..
>
> ....
>
> As usual mark/copy/paste/save everything below to a file named f.e.
> <dave.wfm> and later on doubleclick on it under the <Forms> tab of
> the <Navigator> window.
-----
As usual, your sample is very instructive.
I wonder if this can be done with three tables where one table ist the
intermediate table.
Let's say we have Products and Colours and a many-to-many relationship
between both.
Again the form has two grids, one for products, one for the colours these
products are available in.
We want the colours for each product displayed in alpabetical order.
It can be done with a Join and Order By in SQL, but can it also be done with
masterRowset/MasterFields?
In case you have an idea, the code for creating the tables is below.
Thanks
Roland
// Create tables xProduct, xColour, xProdCol.
set database to; close tables
if file('xProduct.dbf'); drop table xProduct
endif
create table xProduct (Product char(20), ProdID autoinc)
insert into xProduct (Product) values ('Product one')
insert into xProduct (Product) values ('Product two')
insert into xProduct (Product) values ('Product three')
insert into xProduct (Product) values ('Product four')
insert into xProduct (Product) values ('Product five')
if file('xColour.dbf'); drop table xColour
endif
create table xColour(Colour char(10), ColID autoinc)
insert into xColour (Colour) values ('Yellow')
insert into xColour (Colour) values ('Purple')
insert into xColour (Colour) values ('White')
insert into xColour (Colour) values ('Red')
insert into xColour (Colour) values ('Magenta')
insert into xColour (Colour) values ('Grey')
insert into xColour (Colour) values ('Blue')
insert into xColour (Colour) values ('Black')
insert into xColour (Colour) values ('Brown')
if file('xProdCol.dbf'); drop table xProdCol
endif
create table xProdCol(ProdID Integer, ColID Integer)
Insert into xProdCol values (1, 1)
Insert into xProdCol values (1, 2)
Insert into xProdCol values (1, 3)
Insert into xProdCol values (2, 4)
Insert into xProdCol values (2, 5)
Insert into xProdCol values (2, 6)
Insert into xProdCol values (3, 7)
Insert into xProdCol values (3, 8)
Insert into xProdCol values (3, 9)
Insert into xProdCol values (3, 10)
Insert into xProdCol values (4, 4)
Insert into xProdCol values (5, 1)
Insert into xProdCol values (5, 2)
Insert into xProdCol values (5, 3)
Insert into xProdCol values (5, 4)
Insert into xProdCol values (5, 5)
Insert into xProdCol values (5, 6)
Insert into xProdCol values (5, 7)
Insert into xProdCol values (5, 8)
Insert into xProdCol values (5, 9)
Insert into xProdCol values (5, 10)
| |
| Dave Edwards 2005-04-22, 3:23 am |
| On Thu, 21 Apr 2005 10:43:52 +0200, "Marko Mihorko [dBVIPS]"
<xyz@xyz.com> wrote:
Marko,
Many thanks for your reply and demo form. I have your example working
and now I have the job of building your method into my own program.
This seems to be complicated by the fact that I have used a Data
Module, created with dQuery/Web. Is it going to be possible for me to
encapsulate your method into the Data Module or should I simply work
with tables on a form?
Thanks again for the help.
Dave Edwards
| |
| Dave Edwards 2005-04-22, 8:23 pm |
| On Thu, 21 Apr 2005 10:43:52 +0200, "Marko Mihorko [dBVIPS]"
<xyz@xyz.com> wrote:
Marko,
>As usual mark/copy/paste/save everything below to a file named f.e. <dave.wfm>
>and later on doubleclick on it under the <Forms> tab of the <Navigator> window.
>
>Then just navigate in the upper grid and at the same time check the order of
>the items in the NextWord column of the lower grid...
I am beginning to get an understanding of how your form works and I
would appreciate your confirmation that my understanding is correct.
It seems that you have created a calculated field called First_Next in
the first table. This calculated field contains a string version of
my original FirstpartID field.
You have created a new index in the second table called First_Next
The linking between the two tables is the achieved via the First_Next
field in the first table and the First_Next index in the second.
If this interpretation is correct, I just have one further question...
When my original tables were linked via the FirstpartID fields of both
tables, the FirstpartID field of the second table was assigned its
value automatically whenever I added a new row to the second table.
Am I right in assuming that this is no longer the case and that I have
to specifically assign a value to this field?
Many thanks,
Dave Edwards
| |
| Marko Mihorko [dBVIPS] 2005-04-23, 9:23 am |
| Hello Dave!
"Dave Edwards" je napisal v sporocilo...
> I am beginning to get an understanding of how your form works and I
> would appreciate your confirmation that my understanding is correct.
>
> It seems that you have created a calculated field called First_Next in
> the first table. This calculated field contains a string version of
> my original FirstpartID field.
Yes.
> You have created a new index in the second table called First_Next
Indeed. ;-)
> The linking between the two tables is the achieved via the First_Next
> field in the first table and the First_Next index in the second.
Yes.
> If this interpretation is correct, I just have one further question...
>
> When my original tables were linked via the FirstpartID fields of both
> tables, the FirstpartID field of the second table was assigned its
> value automatically whenever I added a new row to the second table.
>
> Am I right in assuming that this is no longer the case and that I have
> to specifically assign a value to this field?
Yes (and for a possible solution check the example in my other response).
> Many thanks,
You are welcome.
Marko Mihorko [dBVIPS]
| |
| Marko Mihorko [dBVIPS] 2005-04-23, 9:23 am |
| Hello Dave!
"Dave Edwards" je napisal v sporocilo...
> Many thanks for your reply and demo form. I have your example working
> and now I have the job of building your method into my own program.
> This seems to be complicated by the fact that I have used a Data
> Module, created with dQuery/Web. Is it going to be possible for me to
> encapsulate your method into the Data Module or should I simply work
> with tables on a form?
Well, almost everything is possible... ;-)
As usual mark/copy/paste/save everything below _together_ to a file named
f.e. <dave1.wfm> and later on doubleclick on it under the <Forms> tab of the
<Navigator> window.
> Thanks again for the help.
You are welcome.
Marko Mihorko [dBVIPS]
set database to; close tables
if file('FirstPart_M.dbf'); drop table FirstPart_M
endif
create table FirstPart_M(FirstPar
t char(10), FirstPartID autoinc)
insert into FirstPart_M (FirstPart) values ('First one')
insert into FirstPart_M (FirstPart) values ('Second one')
insert into FirstPart_M (FirstPart) values ('Third one')
if file('NextWord_M.dbf'); drop table NextWord_M
endif
create table NextWord_M(FirstPart
ID integer, NextWord char(10))
insert into NextWord_M values (3,'Yellow')
insert into NextWord_M values (2,'Purple')
insert into NextWord_M values (1,'White')
insert into NextWord_M values (3,'Red')
insert into NextWord_M values (2,'Magenta')
insert into NextWord_M values (1,'Grey')
insert into NextWord_M values (3,'Blue')
insert into NextWord_M values (2,'Black')
insert into NextWord_M values (1,'Brown')
use NextWord_M exclu
index on str(FirstPartID) + NextWord tag First_Next; use
** END HEADER -- do not remove this line
//
// Generated on 23.04.2005
//
parameter bModal
local f
f = new dave1Form()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class dave1Form of FORM
// set procedure to dave.dmd additive
with (this)
text = "Dave - Parent/Child Tables - Indexing"
endwith
this.DAVEDATAMODULE1 = new DAVEDATAMODULE()
this.DAVEDATAMODULE1.parent = this
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.davedatamodule1.firstpart_m1.rowset
height = 7.0
left = 1.0
top = 0.5
width = 38.0
endwith
this.GRID2 = new GRID(this)
with (this.GRID2)
dataLink = form.davedatamodule1.nextword_m1.rowset
height = 7.5
left = 1.0
top = 8.0
width = 38.0
endwith
endclass
class daveDATAMODULE of DATAMODULE
this.FIRSTPART_M1 = new QUERY()
this.FIRSTPART_M1.parent = this
with (this.FIRSTPART_M1)
onOpen = class::FIRSTPART_M1_
ONOPEN
sql = 'Select * from "FirstPart_M.DBF"'
active = true
endwith
this.NEXTWORD_M1 = new QUERY()
this.NEXTWORD_M1.parent = this
with (this.NEXTWORD_M1)
sql = 'Select * from "NextWord_M.DBF"'
active = true
endwith
with (this.NEXTWORD_M1.rowset)
onAppend = class::ROWSET_ONAPPE
ND
indexName = "FIRST_NEXT"
masterRowset = form.firstpart_m1.rowset
masterFields = "First_Next"
endwith
function firstpart_m1_onOpen
f = new field()
with (f)
fieldName = 'First_Next'
beforeGetValue = {||str(this. parent['FirstPartID'
].value)}
endwith
this.rowset.fields.add(f)
return
function rowset_onAppend
this. fields['FirstPartID'
].value = ;
this.parent.parent.FIRSTPART_M1.rowset;
. fields['FirstPartID'
].value
this.modified = false
return
endclass
| |
| Marko Mihorko [dBVIPS] 2005-04-23, 9:23 am |
| Hello Roland!
"Roland Wingerter" je napisal v sporočilo...
> As usual, your sample is very instructive.
Thanks. ;-)
> I wonder if this can be done with three tables where one table ist the
> intermediate table.
> Let's say we have Products and Colours and a many-to-many relationship
> between both.
>
> Again the form has two grids, one for products, one for the colours these
> products are available in.
> We want the colours for each product displayed in alpabetical order.
>
> It can be done with a Join and Order By in SQL, but can it also be done with
> masterRowset/MasterFields?
Well, a sort of...
> In case you have an idea, the code for creating the tables is below.
Of course I have... ;-)
Thanks for the "creating the tables" code and as usual mark/copy/paste/save...
Marko Mihorko [dBVIPS]
// Create tables xProduct, xColour, xProdCol.
set database to; close tables
if file('xProduct.dbf'); drop table xProduct
endif
create table xProduct (Product char(20), ProdID autoinc)
insert into xProduct (Product) values ('Product one')
insert into xProduct (Product) values ('Product two')
insert into xProduct (Product) values ('Product three')
insert into xProduct (Product) values ('Product four')
insert into xProduct (Product) values ('Product five')
if file('xColour.dbf'); drop table xColour
endif
create table xColour(Colour char(10), ColID autoinc)
insert into xColour (Colour) values ('Yellow')
insert into xColour (Colour) values ('Purple')
insert into xColour (Colour) values ('White')
insert into xColour (Colour) values ('Red')
insert into xColour (Colour) values ('Magenta')
insert into xColour (Colour) values ('Grey')
insert into xColour (Colour) values ('Blue')
insert into xColour (Colour) values ('Black')
insert into xColour (Colour) values ('Brown')
create index Colour on xColour (Colour)
if file('xProdCol.dbf'); drop table xProdCol
endif
create table xProdCol(ProdID Integer, ColID Integer)
Insert into xProdCol values (1, 1)
Insert into xProdCol values (1, 2)
Insert into xProdCol values (1, 3)
Insert into xProdCol values (2, 4)
Insert into xProdCol values (2, 5)
Insert into xProdCol values (2, 6)
Insert into xProdCol values (3, 7)
Insert into xProdCol values (3, 8)
Insert into xProdCol values (3, 9)
Insert into xProdCol values (3, 10)
Insert into xProdCol values (4, 4)
Insert into xProdCol values (5, 1)
Insert into xProdCol values (5, 2)
Insert into xProdCol values (5, 3)
Insert into xProdCol values (5, 4)
Insert into xProdCol values (5, 5)
Insert into xProdCol values (5, 6)
Insert into xProdCol values (5, 7)
Insert into xProdCol values (5, 8)
Insert into xProdCol values (5, 9)
Insert into xProdCol values (5, 10)
create index ProdID on xProdCol (ProdID)
** END HEADER -- do not remove this line
//
// Generated on 23.04.2005
//
parameter bModal
local f
f = new rolandForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class rolandForm of FORM
with (this)
height = 20
text = "Roland - Parent/Child Tables - Indexing"
endwith
this.XPRODUCT1 = new QUERY()
this.XPRODUCT1.parent = this
with (this.XPRODUCT1)
sql = 'select * from "xProduct.DBF"'
active = true
endwith
with (this.XPRODUCT1.rowset)
onNavigate = class::ROWSET_ONNAVI
GATE
endwith
this.XCOLOUR1 = new QUERY()
this.XCOLOUR1.parent = this
with (this.XCOLOUR1)
sql = 'select * from "xColour.DBF"'
active = true
endwith
with (this.XCOLOUR1.rowset)
indexName = "COLOUR"
endwith
this.XPRODCOL1 = new QUERY()
this.XPRODCOL1.parent = this
with (this.XPRODCOL1)
sql = 'select * from "xProdCol.DBF"'
active = true
endwith
with (this.XPRODCOL1.rowset)
indexName = "PRODID"
masterRowset = form.xproduct1.rowset
masterFields = "ProdID"
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.xproduct1.rowset
height = 7.5
left = 1.0
top = 0.5
width = 38.0
endwith
this.GRID2 = new GRID(this)
with (this.GRID2)
dataLink = form.xcolour1.rowset
height = 11.0
left = 1.0
top = 8.5
width = 38.0
endwith
this.rowset = this.xproduct1.rowset
function open
this.xProduct1.rowset.next(0)
return super::open()
function rowset_onNavigate(ty
pe, nRows)
oF = this.parent.parent
temp = ''
do
temp += iif(temp.length = 0, 'ColID=', ' or ColID=') + ;
oF.xprodcol1.rowset.fields['ColID'].value
until not oF.xProdCol1.rowset.next()
temp += iif(temp.length = 6, 0, '')
oF.xColour1.rowset.filter = temp
return
endclass
| |
| Roland Wingerter 2005-04-23, 11:23 am |
| Hello Marko,
thanks for your reply and the code.
> "Roland Wingerter" je napisal v sporočilo...
>
> Well, a sort of...
>
>
> Of course I have... ;-)
>
> Thanks for the "creating the tables" code and as usual
> mark/copy/paste/save...
------
I see your solution works with a filter in the detail table (colours). A
viable solution with a small detail table, but it would be slow with large
tables.
I need this sort of thing in an app where I have a many-to-many relationship
between two tables, where the second main table has > 50.000 records. The
solution I have come up with is as follows:
- intermediate table is masterRowset for second main table
- in Notebook.onSelChange setRange() in the intermediate table
- loop through the intermediate table and copy records to a temp table
- display records from temp table in grid
This works for me, but I was wondering if there is a better solution.
Thanks very much.
Roland
| |
| Marko Mihorko [dBVIPS] 2005-04-23, 11:23 am |
| Hello Roland!
"Roland Wingerter" je napisal v sporočilo...
> I see your solution works with a filter in the detail table (colours). A
> viable solution with a small detail table, but it would be slow with large
> tables.
But there are not so many colours. ;-)
> I need this sort of thing in an app where I have a many-to-many relationship
> between two tables, where the second main table has > 50.000 records. The
> solution I have come up with is as follows:
> - intermediate table is masterRowset for second main table
> - in Notebook.onSelChange setRange() in the intermediate table
> - loop through the intermediate table and copy records to a temp table
> - display records from temp table in grid
>
> This works for me, but I was wondering if there is a better solution.
Not for the time being, so I would do something very similar, too.
> Thanks very much.
You are welcome.
Marko Mihorko [dBVIPS]
| |
| Roland Wingerter 2005-04-23, 11:23 am |
| Marko Mihorko [dBVIPS] wrote:
>
> Not for the time being, so I would do something very similar, too.
------
Thanks for the confirmation.
Roland
| |
| Dave Edwards 2005-04-26, 3:23 am |
| On Sat, 23 Apr 2005 16:22:49 +0200, "Marko Mihorko [dBVIPS]"
<xyz@xyz.com> wrote:
Marko,
>
>Yes (and for a possible solution check the example in my other response).
Your example works fine and although I had already worked out a
solution, I have to say that your solution is more elegant!
Thanks again for your help.
Dave Edwards
|
|
|
|
|