> -----Original Message-----
> From: s.beames@... [mailto:s.beames@...]
> Sent: Sábado 2 de Junio de 2001 21:32
>
> What is the best way to totalise a column in a grid?
>
> JobsQry is Master to ItemsUsedQry, & "LINE_TOTAL" is a calculated
> field. IB3.6Dg
>
> My approach below causes a 'Record not located to update' error when
> the ItemsUsedQry gets posted, presumably because I've hit the Eof
> within this handler. The SQLMonitor shows it's trying to post most
> Detail fields with nulls, after the Master has posted OK.
>
> void __fastcall TJobsForm::ItemsUsedSrcDataChange(TIB_StatementLink
> *Sender, TIB_Statement *Statement, TIB_Column *Field)
> {
> Currency total = 0;
> if (Field &&
> (Field->FieldName == "QTY" || Field->FieldName == "UNIT_PRICE") &&
> Field->IsModified &&
> ItemsUsedQry->State == dssEdit)
> {
> // :7( AnsiString sBookmark = ItemsUsedQry->Bookmark;
> for (ItemsUsedQry->First(); !ItemsUsedQry->Eof; ItemsUsedQry->Next
> ())
> {
> total += ItemsUsedQry->FieldByName("LINE_TOTAL")->AsCurrency;
> }
> // :7( ItemsUsedQry->Bookmark = sBookmark;
> JobsQry->FieldByName("TOTALCOST")->AsCurrency = total;
> JobsQry->Post();
> }
> }

Why do I have the idea that the code is mangled?
Are both master and detail tied to the same transaction?
Is ItemsUsedSrc a datasource tied to ItemsUsedQry that connects ItemsUsedQry
to the grid? If this is the case, you are using the dataChange event of the
datasource to do a full loop in the associated dataset! Or is the case that
your master datasource that links the detail to the master has the name of
the detail?

When IBO handles M/D automatically, it needs to do a post in the master when
the first record is entered in the detail or the whole thing would fail. In
this case, master.IsPostRetaining is true. The master remains in logical
insert state.

Perhaps if you explain what you're trying to achieve it would be easy to
suggest a solution. Are you trying to keep always in the master the sum of
some detail's field? You could have a separate TIB_Cursor with code
select sum(field) from detail
where Mfield = :Mfield

then in the AfterPost event of the detail, you feed MField with the PK of
the master, assuming the typical case of the master's PK used to select all
associated detail records. As far as the two queries and the cursor share
the same transaction object, they will see the same data, no need to commit.
I assume you aren't using cached updates, so uncommitted changes go to the
server directly. After calling the First method of the ib_cursor, you plug
that value in the master. This is if you need to show the sum on every
modification of the detail, immediately.

Now, if you only want to calculate the sum when the form is being closed or
you press the OK button, etc. (after all operations in the detail), you
don't need to react to every change in the detail. If your master dataset
can be scrolled, the datachange seems too late for me. You need the
BeforePost event of the master:

if not master.IsPostRetaining
then begin
Load the master's PK in the ib_cursor's param
// See the idea about the cursor above
ib_cursor.first
get the value and plug it on the master's field
end

For the general case, you may be adding detail records with a master that
already exists, in whose case your master isn't in edit or insert state, so
you would want to activate a "dirty" flag on the AfterPost event of the
detail and before the master moves to another record, change its state from
dssBrowse to dssEdit, load the cursor's params and call its First method,
then plug the value in the master and disable the dirty flag, unless you
want to clear it only on the master.afterPost event in case the post may be
rejected.

C.