Subject Re: [IBO] Totalising columns & 'Record not located to update' error
Hi Helen & Claudio,
Thanks for your help & patience. Please allow me to provide some
more details.........

I have my single transactions' AutoCommit=false, and a
TIB_Transaction and a TIB_Update bar on the form.
My form has Master TIB_Edits & a Detail Grid.
The details' 'LINE_TOTAL' field is calculated in an OnCalculateField
I have no problem with inserts, but only with edits to *committed*
detail fields. (I can edit these OK via the fields editor or
I was hoping to use the Detail datasource's OnDataChange event
handler to recalculate the Masters' 'TOTAL' field for immediate
display, after the Details LINE_TOTAL has been recalculated.

*** The interesting thing is that my code below works OK if I edit,
for example, the QTY field, & then click on another row or column in
the detail grid. The new LINE_TOTAL is calculated & posted, and the
Masters' TOTAL is updated OK.
*BUT* if I edit the QTY field and then click the Post button of my
TIB_Update bar, I get this 'Record was not located to update' error.
I'm sure I must have a wrong setting somewhere(?).

Please see below....

--- In IBObjects@y..., "Claudio Valderrama C." <cvalde@u...> wrote:
> > -----Original Message-----
> > From: s.beames@m... [mailto:s.beames@m...]
> > 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
> > 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
> > *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-
> > {
> > total += ItemsUsedQry->FieldByName("LINE_TOTAL")-
> > }
> > // :7( ItemsUsedQry->Bookmark = sBookmark;
> > JobsQry->FieldByName("TOTALCOST")->AsCurrency = total;
> > JobsQry->Post();
> > }
> > }
> Why do I have the idea that the code is mangled?
It's my trademark! <g>

> Are both master and detail tied to the same transaction?

> Is ItemsUsedSrc a datasource tied to ItemsUsedQry that connects
> 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!
I don't understand this. I'm changing one field in the Master, but
not the one that selects the detail.

> 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
> insert state.
Inserts work OK, but not updating.

> 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?
Yes, this is what I want. I was trying to get the changes to show
immediately upon posting the changes to the detail row.

> 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
> 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
> modification of the detail, immediately.
I can see this should also work.

> C.

Everything seems to work OK as I have it, except that my code for
summing the column of the detail query, loses its' place in the
dataset(?), but only when using the TIB_UpdateBar to post the
changes. I don't know if this is a bug, or more likely an incorrect
setting. I guess I was hoping there was a way to sum the data in the
memory buffer that doesn't alter the queries' current row? Is this
possible? I still have a poor grasp of this area of IBO.

Thanks again,