Data Filtering

[bluscape]

I'm a bit new to dbExpress and DB programming. I hope I'm in the correct thread list.

I'm using Delphi XE 2010 with MySQL 5.1 and dbEpxress.

I have two MySQL tables to which I interface using the following data diagram

Code: Select all

              
             TSQLConnection
                   |
          |-----------------|
TSQLDataSetA          SQLDataSetB
          |                 |
TDatasetProviderA  TDataSetProviderB
          |                 |
TClientDataSetA       TClientDataSetB
          |                 |
 TDataSourceA           TDataSourceB

Table A has two columns: CustomerID, UnitCount
Table B has two columns: CustomerID, UnitType

I have a DBGrid displaying the contents of Table B.
I have an DBEditBox into which I type the CutomerID for Table.

Problem:

I would like to setup a filter that should do the following:

When I SELECT an entry in Table A where CustomerID = x, the DBGrid should only display (filter) those entries in Table B where CustomerID = x.

Now this should be simple with a manual query but I see that both the TClientDataSet and the TDataSource has design time filter properties.

Question:

Will I be able to setup a filter to filter the contents of Table B for the above mentioned scenario using design time properties? And how would I do that?

At what point should I implement the filter, the TClientDataSet or the TDataSource?

Or what will be the best method to imlement my filter?

Any advice will be appreciated.

Thank you

[AndreyZ]

Hello,

In this case it's better to establish a master/detail relationship. Here is a code example:

Code: Select all

SQLDataSetA.CommandText := 'select * from TableA';
SQLDataSetB.CommandText := 'select * from TableB';
ClientDataSetB.MasterSource := DataSourceA;
ClientDataSetB.MasterFields := 'CustomerID';
ClientDataSetB.IndexFieldNames := 'CustomerID';
ClientDataSetA.Open;
ClientDataSetB.Open;

ClientDataSetB will be automatically filtered with CustomerID value from ClientDataSetA. You can find more information about the master/detail relationship in the Delphi documentation.