Hi all,

This maybe a bit of a newbie question but here it goes:

I have a master detail relationship that works between 2 tables.... it is used to display 2 grids

Grid 1 has info from the Master table, Grid 2 has from the details table.

it is a one two many relationship one Item in the Grid 1 can have multiple items in Grid2.  
When I click an Item in Grid 1 the associated items from the detail table populates Grid 2


I was wondering how could I filter the Master table so that only items that have entries in the detail table will show up in the Master grid (Grid 1)?

Thanks for the help,
Silven

"silven" <silven@canada.com> wrote in message
news:4818A88F-00F3-4950-BCD6-AC9FCDC48AF4@news.elevatesoft.com...
>
>
> I was wondering how could I filter the Master table so that only items
> that have entries in the detail table will show up in the Master grid
> (Grid 1)?
>

Is there any field in the master table (a total, or something like that)
that will tell you whether or not it has details?

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"silven" <silven@canada.com> wrote in message
news:4818A88F-00F3-4950-BCD6-AC9FCDC48AF4@news.elevatesoft.com...
>
>
> I was wondering how could I filter the Master table so that only items
> that have entries in the detail table will show up in the Master grid
> (Grid 1)?
>

Is there any field in the master table (a total, or something like that)
that will tell you whether or not it has details?

Robert

Thanks Robert,  No there is no Total colum that could help me.

Thanks,
Silven

"silven" <silven@canada.com> wrote in message
news:F75C7633-3ED7-4C40-B760-69F96E546FD2@news.elevatesoft.com...
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:
>
>
> "silven" <silven@canada.com> wrote in message
> news:4818A88F-00F3-4950-BCD6-AC9FCDC48AF4@news.elevatesoft.com...
>>
>>
>> I was wondering how could I filter the Master table so that only items
>> that have entries in the detail table will show up in the Master grid
>> (Grid 1)?
>>
>
> Is there any field in the master table (a total, or something like that)
> that will tell you whether or not it has details?
>
> Robert
>
> Thanks Robert,  No there is no Total colum that could help me.


If you can use SQL, it's trivial. You SELECT the fields you want, and JOIN
the detail

SELECT DISTINCT Field1, Field2, ID from Master
JOIN Detail on Master.ID = Detail.ID
ORDER BY Master.ID

The JOIN causes only rows that have at least one matching detail to be
selected, and the DISTINCT assures that you only select one row regardless
of the number of details.

But the result will not be a live query, meaning that you will have to add
code to post any changes to the underlying table. Of course, if all you need
is a display, that's not an issue.

Also, it will not be as fast as using tTables for master detail scrolling.

I don't know how to do such a filter directly on a tTable, maybe there is
some clever trick.

Robert

Silven,

In addition to what Robert suggested, if you can't use SQL or you need a
live dataset, then you can use the OnFilterRecord event of the master
table to filter the records.
Note however that, depending on the way you are using the tables, this
technique can significantly increase the access times to those tables,
specially if they are big and you are accessing them over a slow network
connection. You must test it and see if it's appropriate to your needs.

--
Fernando Dias
[Team Elevate]

Fernando Dias <fernandodias.removthis@easygate.com.pt> wrote:

Silven,

In addition to what Robert suggested, if you can't use SQL or you need a
live dataset, then you can use the OnFilterRecord event of the master
table to filter the records.
Note however that, depending on the way you are using the tables, this
technique can significantly increase the access times to those tables,
specially if they are big and you are accessing them over a slow network
connection. You must test it and see if it's appropriate to your needs.

--
Fernando Dias
[Team Elevate]


This is an update on an existing client's program therefore SQL is out of the question I think there would be too much code change.
If I use theOnFilterRecord would I simply call an event that querries the Detail table to see if there are child items?


Thanks everyone,
silven

Silven,

> If I use theOnFilterRecord would I simply call an event that querries
> the Detail table to see if there are child items?

Yes.
The OnFilterRecord event handler is automatically called, if the
Filtered property is true, each time the table needs to test if a record
should be included in the visible set of records. Then, inside the event
handler all you need to do is to test if there are detail records, in
the detail table, for the current record. Be aware that because you are
displaying the detail records in a dbgrid, you must use a second
TDBISAMTable component to test for the existence of child records in the
detail table.

--
Fernando Dias
[Team Elevate]

silven


Depending on the probable number of rows there is another option. Its a bit more complex to implement than OnFilterRecord and it isn't fully live.

Use SQL to select a list of IDs in the master table - a very slight code to Robert's code

SELECT DISTINCT ID from Master
JOIN Detail on Master.ID = Detail.ID

run through that and build a string holding the IDs separated by , and surround with [] so you end up with a list like [id1,id2,id3,... idx]

finally add a filter to the master table

ID IN idlist

The advantage is that its a bit of bolt on code so shouldn't cause to much hassle,unlike a pure sql solution the tables are live so edits go back to the database, once the filter is in place walking the table up and down is faster than OnFilterRecord, and its easy to use with other filter conditions. On the negative side if a master picks up details it won't recognise it until the filter is regenerated and it takes a bit longer to start displaying records than using OnFilterRecord.

Roy Lambert [Team Elevate]

Roy,

<< The advantage is that its a bit of bolt on code so shouldn't cause to
much hassle,unlike a pure sql solution the tables are live so edits go back
to the database>>

Yes, but that's not going to work in a multi-user environement because other
users can also change the tables at any time.

<< On the negative side if a master picks up details it won't recognise it
until the filter is regenerated and it takes a bit longer to start
displaying records than using OnFilterRecord.>>

Even in a single-user environement, the filter must also be regenerated
every time detail records are deleted or the detail ID values change (if
that is allowed).

--
Fernando Dias
[Team Elevate]

Fernando


I'm not sure what you're saying, or what criteria you're applying. The approach does work. I guarantee that - I have (single and multi-user) systems in the wild using it. Think of it like a canned sql query. You could say that doesn't work either.

All three approaches work. Each of the three has different strengths and weaknesses. The reason I invented this approach to start with was the speed issues with OnFilterRecord. The latest DBISAM's are a lot faster but I still prefer my approach.

Roy Lambert [Team Elevate]