I'm curious if it's possible to set a filter using a value in a table other
than the dataset for which the filter value is being set.  That is, I'd like
to be able to do the dBASE type filter where the parent table records will
only display if there is a relevent child record.

It appears from my experimentation that it's not, unless I switch the parent
datasource's dataset from a TTable to a TQuery with "select * from Clients
where (Clients.ClientID=Suspends.ClientID) and (Suspends.Suspended = true)
as the select statement.  I'm not averse to doing this, it's more for my own
edification whether there's another way to do it, or not.

Thanks for any insights anyone may have.

Phil Frank

On Sat, 4 Sep 1999 17:49:14 -0400, "Philip Frank"

Such a type of filter is only possible by using the OnFilterRecord
event handler.  You could assign the result of a Locate to the Accept
var.

Jan

--snip--

I am using the OnFilterRecord procedure, with the following code:

Accept := (DataModule1.TableClients.FieldByName('Status').AsString = 'A')
and
                 (DataModule1.QuerySuspendCalc.FieldByName('Count of
Client_ID').asInteger = 0);

which didn't work.  (In the above code, QuerySuspendCalc has it's master
dataset pointing to TableClients).  I'm not sure what you mean by using a
Locate in the procedure.

Thanks for any additional thoughts you might have.

Phil Frank

On Mon, 6 Sep 1999 00:24:36 -0400, "Philip Frank"

Based on your original query example, I assume that you only want to
see Clients that also show up in the Suspends table with a code of
Suspended = True.

If that's the given problem, I'd put a TTable on the DataModule that
refers to the Suspends Table.  Add a filter expression to that table
'Suspended' or 'Suspended = True'.  Then in the OnFilterRecord
handler, write this code:
  Accept :=
    // suppose you also want to keep this
    (DataModule1.TableClients.FieldByName('Status').AsString = 'A')
    and
    // here's the locate
    DataModule1.TableSuspends.Locate('ClientID',
    DataModule1.TableClients.FieldByName('ClientID').AsString, []);

You can substitute "DataModule1.TableClients" with "DataSet".

If the performance of this is not good enough, tell me what database
you're using and what indexes exist on the Suspends table.

Jan

Jan,

That's true, but Suspended isn't a field.  It's a set of conditions (which
can still be filtered for).

Before I had a chance to read your most recent reply I realized why you were
using a 'locate' rather than the method I had been using.  I had been
relying on the MasterSource property to force the navigation on the 'child'
query.  That works when displaying data on a form, but the child table only
gets repositioned *after* the parent table navigates.  That's too late to be
able to use the child's value in the parent's filter.  By using the locate
you can *force* the navigation in the child table.

The value in the child table is somewhat convoluted, in that it involves a
boolean value (the suspension is currently active), and the suspension date
is less than today and the resume date is greater than today.  I had been
doing a query to get a count of the records that matched.  0 = not
suspended, > 0 there's at least one active suspension that spans today's
date.  I can easily do the same thing in a filter.

Now that it has 'sunk in' as to why my method didn't work, I'll try your
example tonight and see what the response is (currently the only index on
the suspension table is on the Client_ID).  I can create any index that I
need, so I could always index on Client_ID+Valid+Stop_Date if that would
speed the locate.

Thanks for getting this to 'click' for me.
--
Phil Frank