I have a three table relationship that I figure must be so common that
someone must have come up with a better solution for this than I have. If
so, please expound your solution (if willing):

Here's the scenario: I have a client table, a transactino table, and a
junction table. A client can have more than one transaction, and a
transaction can have more than one client - hence the junction table which
links client ID's with transaction ID's.

Placing three TTables, and three TDataSources on the form gives me access
to all three (obviously). The main part of the form deals with the client
details. As the user scrolls through each client using a DBNavigator the
main part of the form fills in with the client's details and a grid at the
bottom of the form updates with all the transactions that the user has
against them. Initially I had the MasterFields property of the Junction's
TTable set to the Client's TTable, and the MasterFields property of the
Transaction's TTable set to the Junction TTable. But (you guessed it) the
transaction grid at the bottom of the form only ever shows one transaction
(even when there are more) because it is linked to just the active record
in the Junction's TTable, not the whole range.

So, I replaced the TTable for the Transaction table with a TQuery and used
a simple SQL that was updated every time the Client's TTable was scrolled.
Worked great! Except that I the resul was read only because the SQL
involved more than one table (even though all the retrieved columns were
from the transaction table). I needed it to be live.

So, I still used the TQuery, I put back the TTable for the Transactions
table, and I created a new filter for the Transactions TTable each time the
Client's TTable was scrolled, based upon the results of the TQuery.

This works fine. But, as I said at the start, I'm sure there must be a
better way?

Any comments?


Phil Middlemiss

    New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
                  Website: http://www.delphi.org.nz

Reply via email to