Subject Re: Master/Child Tables
From Mustansir Ghor <mustan31@hotmail.com>
Date Thu, 08 Feb 2018 13:47:37 -0500
Newsgroups dbase.getting-started

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.

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.

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.


Best Regards
Mustansir

Mervyn Bick Wrote:

> On 2018-02-08 8:59 AM, Mustansir Ghor wrote:
> > Dear Mervyn
> >
> > Thank you it worked fine.
> >
> > However I made following observation.
> >
> > The master table has about 100 records (assuming 2 fields rno, rname). The child table has about 60,000 (assuming 3 fields sno, reg (link field), sname.
> >
> > For a master table records which has few child records (1-5) it takes longer time to update child grid , the greater the child records lesser is time to update child grid.  I wanted  the child table to sort on same so I used child rowset.indexname=sname. But here when you navigate on child grid if using mouse was fine but using keyboard it displayed different record while child row  was different. By scrolling it went beyond grid display rows.
> >
> > With little experiment I added in the child SQL statement order by reg. then All worked fine. So I dont know how navigation link between these 2 tables behind the scene work, but issue was of SQL execution.
> >
>
> MasterSource is meant to be used with tables on an SQL server.  It does,
> however, work well with .dbf files.
>
> Because it is designed to be used with SQL tables one should never use
> standard dBASE indexes with masterSource.  Use an ORDER BY clause in the
> appropriate select statement instead.
>
> An index on a SQL table is not accessible to dBASE.  It is not essential
> and is used internally by the SQL engine to speed up record selection if
> it exists.
>
> MasterSource is particularly suitable for use where tables are JOINed.
> Because the resulting rowset is a "virtual" rowset existing only in the
> computer's memory it is not possible to build an index on any of the
> fields.  An ORDER BY clause makes the SQL engine order the records
> before they are sent to the rowset.  Any field, or combination of
> fields, irrespective of their types, can be included in the ORDER By
> clause.  If necessary some fields can be in ascending order and some can
> be in descending order.
>
> With .dbf files only one index can be active at a time so any index on
> your two child tables, joined with UNION, can only cover the records
> from the table used to create it.  No wonder dBASE got confused. :-)
>
>
> "Behind the scenes" masterSource uses a parameter driven query for the
> child table.  Changing the record selection in the master rowset passes
> the link-field value to the child query's appropriate parameter and the
> child query is then automatically requeried.  This fetches only the
> records that match the parameter(s).
>
> Mervyn.
>
>