Subject Re: [IBO] How would I go about making a grid show this classification
Author Helen Borrie
At 08:22 PM 23/05/2005 +0300, you wrote:

>Thank you Helen, I thought there was a neat way to do that. I almost got it
>now, except I only get one row in the classifications-grid. I placed an
>extra grid on the form just to see the "intermediate" table, and it seems
>correct, but from there I only get one row in the next grid.
>I set the classification query's masterlink to the intermediate datasource,
>and the MasterLinks property to "id=custtoclass.classid" (custtoclass is the
>intermediate table), isn't that right? What next?

A very good question! This is what you would expect if each of your
queries is "select * from..." The master-detail linking chain in this case
selects the *first* record where there is a full match across the three
sets. The way I described it, you don't have a way to see all of the
corresponding classification records simultaneously. Sorry about that.

As far as the middle set is concerned, it doesn't matter that the
detail-master linking is looking at just the first intermediate
record. The trick is to capture the current customerid from that set and
parameterise the last query to capture *all* of the intersecting
classification records for that customerid.

e.g. the SQL for the classification set would be:

select CL.classid, CL.classdescription from classification CL
where exists
(select CC.classid from custtoclass CC
where CC.customerid = :customerid)

The magic happens by using MasterParamLinks on this set, instead of

Hope this is the missing link in the chain. :-) Sorry to have misguided
you on this.