This is a sample of five related database tables (TOPAZ 7.0, DELPHI 3+).

Relations are not documented in the TOPAZ manual well enough and we have
received quite a few questions from our customers. We need to do a better
job with the manual next time.

First of all, many people get confused when they look at SetWhileTo() and
SetFilterTo() methods in TOPAZ.

    SetFilterTo() works with both sorted and unsorted databases. The records
    within the filter may be scattered all over the database. This is
    good, but it makes filters slow. When you set a filter it is recommended
    that you call GoTop. TOPAZ will jump to the very first record and check
    all the records to see if they match the filter until it finds the first
    one. Imagine that you have a 1000 record database and call:

     customer.SetFilterTo('(RECNO() > 900) .AND. (RECNO() <= 950)');
     customer.GoTop;

    TOPAZ will jump to very first record and see that record number is not
    within the range, then it will check the next one, etc.  Knowing this the
    programmer could speed up the filter by repositioning the database to the
    first record within the filter:

     customer.SetFilterTo('(RECNO() > 900) .AND. (RECNO() <= 950)');
     customer.Go(901);

    NOTE: If you are using the second method, make sure that you jump to the
          first valid record. Otherwise your application will have a strange
          behaviour. In that sense the first method is the most
          straightforward.

    Still Filters will be quite slow when TOPAZ hits the edges of the filtered
    range. Imagine using the following code:

     customer.SetFilterTo('(RECNO() > 900) .AND. (RECNO() <= 950)');
     customer.Go(902);
     customer.Skip(-1);  { very fast }
     customer.Skip(-1);  { we hit the top... }

    The last Skip() will take a long time. TOPAZ will jump to record #900 and
    see that record number is not within the range, then it will check the
    prior one, etc. Finally it will reach the beginning of file and decide
    that it cannot go up. But before it knows about it TOPAZ will need to skip
    and check 900 records.

    If you have a 1000 record database this filter will not be too slow to
    notice, but as you have more records in the database your application that
    uses this filter will go slower and slower.

    SetWhileTo() works with ranges of records and is best for sorted
    databases. It is fast, since WHILE does not need to skan the database.
    Let us use the same code with a slight modification:

     customer.Go(902);  { we must be within WHILE range when we start !!! }
     customer.SetWhileTo('(RECNO() > 900) .AND. (RECNO() <= 950)');
     customer.Skip(-1);  { very fast }
     customer.Skip(-1);  { very fast }

    The last Skip() will be fast since as soon as TOPAZ will jump to record
    #900 it will be out of the WHILE range. It will reach the "virtual"
    beginning of file and decide that it cannot go up anymore.

    Once you have the whole picture in your mind SetWhileTo() and
    SetFilterTo() are quite easy to use, and the programmers may decide which
    one of the two will work best for this or that application.

Now back to relations. There are several ways of making them work.

The most straightforward and bulletproof way is as follows:

begin
  id := parent.GetCField('CUSTID');      { get the relational field
                                           from the parent }
  child.DisableControls;
  try
    child.SetFilterTo('CUSTID="'+id+'"');  { set filter to that ID }
    child.GoTop;                           { position to the first valid
                                             record starting from the top }
  finally
   child.EnableControls;
  end;
end;

,OR,

begin
  id := Trim(parent.GetCField('CUSTID')); { get the relational field }
  child.DisableControls;
  try
    child.SetFilterTo('TRIM(CUSTID)=="'+id+'"');  { set filter to that ID }
    child.GoTop;                   { position to the first valid record }
  finally
    child.EnableControls;
  end;
end;

Trim() is optional, since in most cases the relational field lengths in both
databases are the same (recommended!!!) you do not need to truncate the
tailing spaces.

There is a faster way if the database is indexed. Find() will position you
on the first valid record.  We avoid going to the top of the database and
skanning the file. If Find() fails then we end up at the end of file and the
following SetFilterTo will make the dataset empty for the outside world:

begin
  id := parent.GetCField('CUSTID'); { get the relational field }
  child.DisableControls;
  try
    child.SetFilterTo('');            { reset filter before Find() }
    child.Find('id');                 { we assume the dbf is indexed by ID }
    child.SetFilterTo('CUSTID="'+id+'"');   { set filter to that ID }
  finally
    child.EnableControls;
  end;
end;

If you have a big subset within a big database you may also use SetWhileTo().
It is better to use SetFilterTo() if you have small datasets, but if you have
a bigger database and notice that the relations slow down you may want to try
using SetWhileTo(). So that is all for Relations 101. Hopefully this text
will clear up some issues for you.

