Subject Re: Master/Child Tables From Mervyn Bick <invalid@invalid.invalid> Date Fri, 9 Feb 2018 10:57:04 +0200 Newsgroups dbase.getting-started On 2018-02-08 8:47 PM, Mustansir Ghor wrote: > Dear Mervyn > > I had to use the indexname because the grid for child table has many records such that I had to use seekersql. For this indexname is must. seekerSQL.cc is designed to work as a normal seeker if it finds an index otherwise it looks for an ORDER BY clause and uses applyLocate() on the first field in the ORDER BY clause.  As applyLocate() has to look at each record until it finds what it's looking for it is much slower than a normal seeker. > all appear ok but only issue is time factor. If i can improve on time for a master navigation of 5-10 sec that can be par excellent. > Fortunately there's usually more than one way to do something in dBASE. It's time to go to plan B. :-) Instead of using masterSource we can use a filter.  The comparison operators that can be used in a filter are limited but we only need  = here so a filter will work nicely. For your parent query set up an onNavigate event handler.  Use an ORDER BY clause in the SQL statement if necessary.     function rowset_onNavigate(type, nRows)        this.parent.parent.childqueryname.rowset.filter = "reg = '"+this.fields['rno'].value+"'"        return In the form's onOpen event handler     function form_onOpen()        form.parentqueryname.rowset.first()        return This will navigate the parent rowset (although it doesn't actually move the rowset pointer as this opens on the first record anyway) which will filter the child records for the first parent record. For the child query set the sql property to     sql = 'select reg,name from xyz1 union select reg,name from xzy2 order by name) The fields selected from xyz1 must match the fields selected from xyz2. Union on its own will suppress duplicate entries.  Union ALL will include duplicate entries. Don't use any indexes.  Don't set masterSource. > You mention that for a joined table it is virtually on memory. How about a SELECT statement with a single table and with parameter query. Any rowset is created in memory but there is always the underlying table which lives on disk.  dBASE uses the table on disk to create an index which can then be read into memory to be used with the rowset. With joined tables the individual tables are on disk but dBASE can't create a single index that spans both tables.  On the other hand, an ORDER BY clause will cause the SQL engine to order the records before they are given to dBASE to place in the rowset.  This means that not having a dBASE index is not a problem. Mervyn.