Subject Re: CONNECTIING 2 ROWSETs
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 21 Jun 2017 16:31:50 +0200
Newsgroups dbase.getting-started
Attachment(s) parent_child.wfm

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