The FDQuery SQL is select b.*, a.csc_id as refcsc_id, a.cscdsc as cscdsc, a.cstpls as refcstpls, a.gstinc as refgstinc, a.gstpct as refgstpct from csc a left join estdtl b on a.csc_id = b.csc_id and b.est_id = !est_id so a fairly straightforward left out join (excuse the 3letter mnemonics)
The FDUpdateSQL insert statement is the one generated by FireDac and is:-
INSERT INTO ESTDTL
(CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, GSTPCT,
QTYCLC, QTY, CST, GST, SLE, VAL)
VALUES (:NEW_CSC_ID, :NEW_ESTDTLDSC, :NEW_CSTPLS, :NEW_GSTINC, :NEW_GSTPCT,
:NEW_QTYCLC, :NEW_QTY, :NEW_CST, :NEW_GST, :NEW_SLE, :NEW_VAL)
RETURNING ESTDTL_ID
By default the FDQuery would try and update a query "record" consisting of a real left part and an completely null right part (other than the Est_ID which comes from a parameter in the FDQuery set when choosing the Estimate in a seperate combobox). But it failed with the "update affected [0] rows, while [1] was requested" and did not insert the record in EstDtl.
So I then added the code shown in the original post in an OnUpdateRecord event on the FDQuery, basically changing the ARequest from arUpdate to arInsert if the right part is null. This actually inserts the record then fails with the same message. So maybe there is some way to suppress the message (ideally just in this case) but it all happens somewhere in the depths of livebindings...
Thanks for looking at this - my first time combining FireDac which seems very powerful and livebindings. I have normally used IBX components. In case its useful its connecting to a Firebird Database.
Al