Subject Re: [IBO] TIBOQuery master detail inserts with different key field names
Author Helen Borrie
At 04:56 PM 27/12/2006, you wrote:
>How can I get automatic (or programmatic) completion of the foreign
>key field when inserting a detail row if the field name is different
>to the master key field name?
>I've been fiddling with the datasource property of the query but I
>think it only works if the field names are the same.

Yes, that is true of TIBOQuery, being a limitation of the TDataset
heritage, which doesn't support explicit masterlinking. So you need
Plan B. :-)

>I'm finding this impossible to figure out. Any sample code would be
>very helpful.

The trick is to use a parameterised statement for the detail set's
SQL property, e.g.

select id, blah1, blah2, FK_ID from aDetail
where FK_ID = :FK_ID

And code the relationship during the OnAfterScroll of the master set:

with aDetailSet do begin
ParamByName('FK_ID').Value := Dataset.FieldByName('PK_ID').Value;

For inserts, you *shouldn't* need to assign (I rarely use TIBO-- and
and almost never for master-detail relationships...but...) but, if
you find that you do, then a simple BeforePost chunk similar to the
above will achieve it:

with Dataset do begin
FieldByName('FK_ID').Value := aMasterSet.FieldByName('PK_ID').Value;

Notice also that the friendly master-detail stuff isn't available so,
if you are inserting both master and detail records in the same task,
you will, unfortunately, need to ensure that you post any inserted
master record before attempting to insert the first detail rows for it.