Subject Re: CONNECTIING 2 ROWSETs
From Mustansir Ghor <mustan31@hotmail.com>
Date Thu, 22 Jun 2017 08:36:45 -0400
Newsgroups dbase.getting-started

Dear Mervyn

Thank you. the demo is very useful.

It means when we connect 2 rowsets master and child, then child has to be indexed not the master, the navigation is done by master, i.e when we navigate in master corresponding child records are selected. This is just the opposite of what we used to do with SET RELATION TO where we navigate by child (default) and corresponding master was selected. If I were to get this setup do you think sqllookup might seem to be appropriate solution.

However the use of inner join to put fields from both rowsets might be more useful in reports I guess, as the resulting rowset is not updatable.

Thanks Mervyn

Best Regards
Mustansir



Mervyn Bick Wrote:

> On 2017-06-21 11:01 AM, Mustansir Ghor wrote:
> > Dear All
> >
> > I refer to in XDML SET RELATION TO
> >
> > I have a master file with fields CODE , NAME (rowset1). rowset is indexed on CODE
> >
> > I have a child file with fields TRANS_NO, DATE, ITEM, QTY, AMOUNT (rowset2). The field ITEM stores same information as CODE in the master file.
> >
> > In rowset2 in set MasterRowset = rowset1
> > When I set Masterfields:= "ITEM" . it gives error table is not indexed.
> >
> > I wish to know which table he is referring to.
> >
> > Further how we can display in the grid fields from both these tables.
> >
> > Anybody can guide on this matter.
>
> 'Date' is a reserved word and you shouldn't really use it as a
> fieldname.  It can, however, be used but it requires special treatment.
>
> The child rowset, rowset2 here, must be indexed on the linking field.
> Create an index on ITEM and then set the index using the rowset's
> indexName property.  Once you have done this selecting a record in
> rowset1 will ensure that rowset2 only contains records where the
> contents of ITEM matches the contents of CODE.
>
> Using masterRowset/masterFields it is usual to show the two rowsets in
> separate grids.
>
> If you want to show fields from both tables in the same grid you will
> need to JOIN the two tables.
>
> A little example is attached.
>
>
> Mervyn.
>
>
> if file('parent_table.dbf')
> //   drop table parent_table
> endif
>
> if not file('parent_table.dbf')
>    create table parent_table  (id autoinc,code character(15),name character(15))
>    insert into parent_table  (code,name) values ("1","James")
>    insert into parent_table  (code,name) values ("2","John")
>    insert into parent_table  (code,name) values ("3","Paul")
> endif
>
> if file('child_table.dbf')
> //   drop table child_table
> endif
>
> if not file('child_table.dbf')
>    create table child_table  (trans_no autoinc,item character(15),child_table.'date' date,;
>      qty numeric(10,2),amount numeric(10,2))
>    insert into child_table  (item,child_table.'date',qty,amount) values ("1",'06/21/2017',4.00,23.00)
>    insert into child_table  (item,child_table.'date',qty,amount) values ("2",'06/21/2017',5.00,19.00)
>    insert into child_table  (item,child_table.'date',qty,amount) values ("3",'06/21/2017',2.00,21.00)
>    insert into child_table  (item,child_table.'date',qty,amount) values ("1",'06/21/2017',3.00,11.00)
>    insert into child_table  (item,child_table.'date',qty,amount) values ("2",'06/21/2017',5.00,22.00)
>    insert into child_table  (item,child_table.'date',qty,amount) values ("3",'06/21/2017',1.00,31.00)
>    
>    use child_table exclusive
>    index on item tag item
>    use
>    
> endif
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 2017-06-21
> //
> parameter bModal
> local f
> f = new parent_childForm()
> if (bModal)
>    f.mdi = false // ensure not MDI
>    f.readModal()
> else
>    f.open()
> endif
>
> class parent_childForm of FORM
>    with (this)
>       height = 30.0455
>       left = -2.8571
>       top = -0.8182
>       width = 131.7143
>       text = ""
>    endwith
>
>    this.PARENT_TABLE2 = new QUERY(this)
>    with (this.PARENT_TABLE2)
>       left = 32.0
>       top = 1.0
>       width = 10.0
>       height = 1.0
>       sql = 'select p.code,p.name,c.trans_no,c."date",c.qty,c.amount from parent_table p inner join child_table c on p.code = c.item'
>       active = true
>    endwith
>
>    this.PARENT_TABLE1 = new QUERY(this)
>    with (this.PARENT_TABLE1)
>       left = 4.0
>       top = 1.0
>       width = 10.0
>       height = 1.0
>       sql = 'select * from "parent_table.DBF"'
>       active = true
>    endwith
>
>    this.CHILD_TABLE1 = new QUERY(this)
>    with (this.CHILD_TABLE1)
>       left = 21.0
>       top = 1.0
>       width = 9.0
>       height = 1.0
>       sql = 'select * from "child_table.DBF"'
>       active = true
>    endwith
>
>    with (this.CHILD_TABLE1.rowset)
>       indexName = "ITEM"
>       masterRowset = form.parent_table1.rowset
>       masterFields = "code"
>    endwith
>
>    this.GRID1 = new GRID(this)
>    with (this.GRID1)
>       dataLink = form.parent_table1.rowset
>       height = 4.5
>       left = 7.0
>       top = 3.5
>       width = 65.0
>    endwith
>
>    this.GRID2 = new GRID(this)
>    with (this.GRID2)
>       dataLink = form.child_table1.rowset
>       height = 6.0
>       left = 7.0
>       top = 10.0
>       width = 92.0
>    endwith
>
>    this.GRID3 = new GRID(this)
>    with (this.GRID3)
>       dataLink = form.parent_table2.rowset
>       height = 8.0
>       left = 7.0
>       top = 19.0
>       width = 118.0
>    endwith
>
>    this.rowset = this.child_table1.rowset
>
> endclass
>