Refreshing calc (sum) fields

In a "Bookings" table, I have a calc field, "TotalBookingCost", which
is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
total of the costs of the individual lines that make up the booking.
BookingLineCost is also a calc field, based on rates, duration, etc.

I have a layout based on Bookings, with a the related BookingLines in
a portal. All well so far. But if the individual costs in a
BookingLine change in the portal, the value in TotalBookingCost is not
updated.

It seems I can force a refresh by a variety of means, such as toggling
the Status Area off and on, switching to a different layout, etc., but
I'm sure I shouldn't have to. Any ideas what I'm doing wrong?

(FM9, PC)

Carla

Re: Refreshing calc (sum) fields

Carla wrote:
> In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> total of the costs of the individual lines that make up the booking.
> BookingLineCost is also a calc field, based on rates, duration, etc.
>
> I have a layout based on Bookings, with a the related BookingLines in
> a portal. All well so far. But if the individual costs in a
> BookingLine change in the portal, the value in TotalBookingCost is not
> updated.
>
> It seems I can force a refresh by a variety of means, such as toggling
> the Status Area off and on, switching to a different layout, etc., but
> I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
>
> (FM9, PC)
>
> Carla


define the calc as unstored

also consider trying the line item calcs as number/auto enter calculated
value, with do not replace existing value unchecked.

Re: Refreshing calc (sum) fields

On Dec 5, 1:28 pm, Chris Brown
wrote:
> Carla wrote:
> > In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> > is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> > total of the costs of the individual lines that make up the booking.
> > BookingLineCost is also a calc field, based on rates, duration, etc.
>
> > I have a layout based on Bookings, with a the related BookingLines in
> > a portal. All well so far. But if the individual costs in a
> > BookingLine change in the portal, the value in TotalBookingCost is not
> > updated.
>
> > It seems I can force a refresh by a variety of means, such as toggling
> > the Status Area off and on, switching to a different layout, etc., but
> > I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
>
> > (FM9, PC)
>
> > Carla
>
> define the calc as unstored
>
> also consider trying the line item calcs as number/auto enter calculated
> value, with do not replace existing value unchecked.

The calc will have to be unstored if it's referencing a related table.

I've seen FM be slow on the uptake in refreshing unstored values. It
could be that you're not committing the related record or it could
just be a gremlin. It helps to have a trigger. You could put a button
labelled "Refresh" or "Update" or some such and attach a script to
it. That script should include the steps:

Commit Record/Request
Flush cache to disk
Refresh Window

Re: Refreshing calc (sum) fields

In article
<98a0d974-0ff4-4dc7-88b1-640c0da96f4a@a35g2000prf.googlegroups.com>,
Grip wrote:

> On Dec 5, 1:28 pm, Chris Brown
> wrote:
> > Carla wrote:
> > > In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> > > is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> > > total of the costs of the individual lines that make up the booking.
> > > BookingLineCost is also a calc field, based on rates, duration, etc.
> >
> > > I have a layout based on Bookings, with a the related BookingLines in
> > > a portal. All well so far. But if the individual costs in a
> > > BookingLine change in the portal, the value in TotalBookingCost is not
> > > updated.
> >
> > > It seems I can force a refresh by a variety of means, such as toggling
> > > the Status Area off and on, switching to a different layout, etc., but
> > > I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
> >
> > > (FM9, PC)
> >
> > > Carla
> >
> > define the calc as unstored
> >
> > also consider trying the line item calcs as number/auto enter calculated
> > value, with do not replace existing value unchecked.
>
> The calc will have to be unstored if it's referencing a related table.
>
> I've seen FM be slow on the uptake in refreshing unstored values. It
> could be that you're not committing the related record or it could
> just be a gremlin. It helps to have a trigger. You could put a button
> labelled "Refresh" or "Update" or some such and attach a script to
> it. That script should include the steps:
>
> Commit Record/Request
> Flush cache to disk
> Refresh Window

Yep, it must already be unstored, so there's only two possible answers
.... well, three if you include a corrupted file.

Either the user is still in the field they've just changed, in which
case FileMaker has actually got that change yet. They have to leave the
field for the change to be taken into affect.

OR, it's the silly "non-committing" of records that newer versions of
FileMaker now have, which apparently the only way to fix is to have a
script as you've just written above that forces FileMaker to accept the
new changes to the record. :o(

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Refreshing calc (sum) fields

On Dec 5, 4:54 pm, Helpful Harry
wrote:
> In article
> <98a0d974-0ff4-4dc7-88b1-640c0da96...@a35g2000prf.googlegroups.com>,
>
>
>
> Grip wrote:
> > On Dec 5, 1:28 pm, Chris Brown
> > wrote:
> > > Carla wrote:
> > > > In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> > > > is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> > > > total of the costs of the individual lines that make up the booking.
> > > > BookingLineCost is also a calc field, based on rates, duration, etc.
>
> > > > I have a layout based on Bookings, with a the related BookingLines in
> > > > a portal. All well so far. But if the individual costs in a
> > > > BookingLine change in the portal, the value in TotalBookingCost is not
> > > > updated.
>
> > > > It seems I can force a refresh by a variety of means, such as toggling
> > > > the Status Area off and on, switching to a different layout, etc., but
> > > > I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
>
> > > > (FM9, PC)
>
> > > > Carla
>
> > > define the calc as unstored
>
> > > also consider trying the line item calcs as number/auto enter calculated
> > > value, with do not replace existing value unchecked.
>
> > The calc will have to be unstored if it's referencing a related table.
>
> > I've seen FM be slow on the uptake in refreshing unstored values. It
> > could be that you're not committing the related record or it could
> > just be a gremlin. It helps to have a trigger. You could put a button
> > labelled "Refresh" or "Update" or some such and attach a script to
> > it. That script should include the steps:
>
> > Commit Record/Request
> > Flush cache to disk
> > Refresh Window
>
> Yep, it must already be unstored, so there's only two possible answers
> ... well, three if you include a corrupted file.
>
> Either the user is still in the field they've just changed, in which
> case FileMaker has actually got that change yet. They have to leave the
> field for the change to be taken into affect.
>
> OR, it's the silly "non-committing" of records that newer versions of
> FileMaker now have, which apparently the only way to fix is to have a
> script as you've just written above that forces FileMaker to accept the
> new changes to the record. :o(
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

It's not a non-commiting error, the data is committed, but the
unstored calc doesn't get refreshed. And it's not just newer
versions...I haven't used 6 in a few years, but unstored fields need
something to trigger their refresh, even in 6. An unstored calc field
= Status(CurrentTime) doesn't just tick off the seconds like a system
clock, it needs an event to trigger it (like a screen redraw or a
forced refresh), no?

Re: Refreshing calc (sum) fields

In article
<516eb0fe-8d6e-4190-b645-ce7a8637fea1@j44g2000hsj.googlegroups.com>,
Grip wrote:

> On Dec 5, 4:54 pm, Helpful Harry
> wrote:
> > In article
> > <98a0d974-0ff4-4dc7-88b1-640c0da96...@a35g2000prf.googlegroups.com>,
> >
> >
> >
> > Grip wrote:
> > > On Dec 5, 1:28 pm, Chris Brown
> > > wrote:
> > > > Carla wrote:
> > > > > In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> > > > > is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> > > > > total of the costs of the individual lines that make up the booking.
> > > > > BookingLineCost is also a calc field, based on rates, duration, etc.
> >
> > > > > I have a layout based on Bookings, with a the related BookingLines in
> > > > > a portal. All well so far. But if the individual costs in a
> > > > > BookingLine change in the portal, the value in TotalBookingCost is not
> > > > > updated.
> >
> > > > > It seems I can force a refresh by a variety of means, such as toggling
> > > > > the Status Area off and on, switching to a different layout, etc., but
> > > > > I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
> >
> > > > > (FM9, PC)
> >
> > > > > Carla
> >
> > > > define the calc as unstored
> >
> > > > also consider trying the line item calcs as number/auto enter calculated
> > > > value, with do not replace existing value unchecked.
> >
> > > The calc will have to be unstored if it's referencing a related table.
> >
> > > I've seen FM be slow on the uptake in refreshing unstored values. It
> > > could be that you're not committing the related record or it could
> > > just be a gremlin. It helps to have a trigger. You could put a button
> > > labelled "Refresh" or "Update" or some such and attach a script to
> > > it. That script should include the steps:
> >
> > > Commit Record/Request
> > > Flush cache to disk
> > > Refresh Window
> >
> > Yep, it must already be unstored, so there's only two possible answers
> > ... well, three if you include a corrupted file.
> >
> > Either the user is still in the field they've just changed, in which
> > case FileMaker has actually got that change yet. They have to leave the
> > field for the change to be taken into affect.
> >
> > OR, it's the silly "non-committing" of records that newer versions of
> > FileMaker now have, which apparently the only way to fix is to have a
> > script as you've just written above that forces FileMaker to accept the
> > new changes to the record. :o(
>
> It's not a non-commiting error, the data is committed, but the
> unstored calc doesn't get refreshed. And it's not just newer
> versions...I haven't used 6 in a few years, but unstored fields need
> something to trigger their refresh, even in 6. An unstored calc field
> = Status(CurrentTime) doesn't just tick off the seconds like a system
> clock, it needs an event to trigger it (like a screen redraw or a
> forced refresh), no?

Semi-true.

An unstored Calculation field that is set to grab the current time will
only update when the layout is refreshed ... BUT an unstored
Calculation field that sums other fields WILL refresh (once you leave
the altered field) when any of the source fields' data is changed.

For example, in older versions of FileMaker:

- create three Number fields (Num1, Num2, Num3)

- create an unstored Calculation field
= Num1 + Num2 + Num3

- put all four fields on a layout and enter some data
into Num1, Num2 and Num3

- the Calculation field will display the total

- go back to Num2 and change the data - the total doesn't
change

- Tab out of the field (probably into Num3) or click
anywhere to exit the field or record, and the total
changes

If you put the "current time" Calculation field on the same layout it
will not update unless you refresh the window (usually by going to the
next record).

The problem is that newer versions of FileMaker have this silly
"non-committing" problem (based on what I've read since I haven't used
them yet) where changed data is not actually saved when you exit the
field, and therefore Calculations don't change either. :o(

*BUT*
having re-read the original question I've thought of another, more
likely, reason why it may not be updating.




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Refreshing calc (sum) fields

In article <061220071844253668%helpful_harry@nom.de.plume.com>, Helpful
Harry wrote:

> In article
> <516eb0fe-8d6e-4190-b645-ce7a8637fea1@j44g2000hsj.googlegroups.com>,
> Grip wrote:
>
> > On Dec 5, 4:54 pm, Helpful Harry
> > wrote:
> > > In article
> > > <98a0d974-0ff4-4dc7-88b1-640c0da96...@a35g2000prf.googlegroups.com>,
> > >
> > >
> > >
> > > Grip wrote:
> > > > On Dec 5, 1:28 pm, Chris Brown
> > > > wrote:
> > > > > Carla wrote:
> > > > > > In a "Bookings" table, I have a calc field, "TotalBookingCost",
> > > > > > which
> > > > > > is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> > > > > > total of the costs of the individual lines that make up the booking.
> > > > > > BookingLineCost is also a calc field, based on rates, duration, etc.
> > >
> > > > > > I have a layout based on Bookings, with a the related BookingLines
> > > > > > in
> > > > > > a portal. All well so far. But if the individual costs in a
> > > > > > BookingLine change in the portal, the value in TotalBookingCost is
> > > > > > not
> > > > > > updated.
> > >
> > > > > > It seems I can force a refresh by a variety of means, such as
> > > > > > toggling
> > > > > > the Status Area off and on, switching to a different layout, etc.,
> > > > > > but
> > > > > > I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
> > >
> > > > > > (FM9, PC)
> > >
> > > > > > Carla
> > >
> > > > > define the calc as unstored
> > >
> > > > > also consider trying the line item calcs as number/auto enter
> > > > > calculated
> > > > > value, with do not replace existing value unchecked.
> > >
> > > > The calc will have to be unstored if it's referencing a related table.
> > >
> > > > I've seen FM be slow on the uptake in refreshing unstored values. It
> > > > could be that you're not committing the related record or it could
> > > > just be a gremlin. It helps to have a trigger. You could put a button
> > > > labelled "Refresh" or "Update" or some such and attach a script to
> > > > it. That script should include the steps:
> > >
> > > > Commit Record/Request
> > > > Flush cache to disk
> > > > Refresh Window
> > >
> > > Yep, it must already be unstored, so there's only two possible answers
> > > ... well, three if you include a corrupted file.
> > >
> > > Either the user is still in the field they've just changed, in which
> > > case FileMaker has actually got that change yet. They have to leave the
> > > field for the change to be taken into affect.
> > >
> > > OR, it's the silly "non-committing" of records that newer versions of
> > > FileMaker now have, which apparently the only way to fix is to have a
> > > script as you've just written above that forces FileMaker to accept the
> > > new changes to the record. :o(
> >
> > It's not a non-commiting error, the data is committed, but the
> > unstored calc doesn't get refreshed. And it's not just newer
> > versions...I haven't used 6 in a few years, but unstored fields need
> > something to trigger their refresh, even in 6. An unstored calc field
> > = Status(CurrentTime) doesn't just tick off the seconds like a system
> > clock, it needs an event to trigger it (like a screen redraw or a
> > forced refresh), no?
>
> Semi-true.
>
> An unstored Calculation field that is set to grab the current time will
> only update when the layout is refreshed ... BUT an unstored
> Calculation field that sums other fields WILL refresh (once you leave
> the altered field) when any of the source fields' data is changed.
>
> For example, in older versions of FileMaker:
>
> - create three Number fields (Num1, Num2, Num3)
>
> - create an unstored Calculation field
> = Num1 + Num2 + Num3
>
> - put all four fields on a layout and enter some data
> into Num1, Num2 and Num3
>
> - the Calculation field will display the total
>
> - go back to Num2 and change the data - the total doesn't
> change
>
> - Tab out of the field (probably into Num3) or click
> anywhere to exit the field or record, and the total
> changes
>
> If you put the "current time" Calculation field on the same layout it
> will not update unless you refresh the window (usually by going to the
> next record).
>
> The problem is that newer versions of FileMaker have this silly
> "non-committing" problem (based on what I've read since I haven't used
> them yet) where changed data is not actually saved when you exit the
> field, and therefore Calculations don't change either. :o(
>
> *BUT*
> having re-read the original question I've thought of another, more
> likely, reason why it may not be updating.

Oops!!! Forget that last paragraph - I meant to delete it, but pressed
"Post" by accident. :o\ I have not got another reason for the
problem, I misread something in the original.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Refreshing calc (sum) fields

On Dec 5, 6:05 pm, Carla wrote:
> In a "Bookings" table, I have a calc field, "TotalBookingCost", which
> is defined as "sum ( BookingLines::BookingLineCost)", i.e. it's the
> total of the costs of the individual lines that make up the booking.
> BookingLineCost is also a calc field, based on rates, duration, etc.
>
> I have a layout based on Bookings, with a the related BookingLines in
> a portal. All well so far. But if the individual costs in a
> BookingLine change in the portal, the value in TotalBookingCost is not
> updated.
>
> It seems I can force a refresh by a variety of means, such as toggling
> the Status Area off and on, switching to a different layout, etc., but
> I'm sure I shouldn't have to. Any ideas what I'm doing wrong?
>
> (FM9, PC)
>
> Carla

Well, thanks everyone for the suggestions. I now have a button on the
main layout that does a simple "Refresh window", which seems to do the
trick. It seems a bit clunky to have to force FM to do what should
come naturally, but I guess that's something we have to live with!

Thanks again.

Carla.

Re: Refreshing calc (sum) fields

In article
,
Carla wrote:
>
> Well, thanks everyone for the suggestions. I now have a button on the
> main layout that does a simple "Refresh window", which seems to do the
> trick. It seems a bit clunky to have to force FM to do what should
> come naturally, but I guess that's something we have to live with!
>
> Thanks again.

The whole "non-comitting" nonsense seems like a VERY silly idea and I
don't know why they changed it. They should at least have the option of
not doing it that way - if I enter data it's because I want that data
there (barring the odd accidental mistake of course). :o(


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Refreshing calc (sum) fields

On Dec 6, 10:14 pm, Helpful Harry
wrote:
> In article
> ,
>
> Carla wrote:
>
> > Well, thanks everyone for the suggestions. I now have a button on the
> > main layout that does a simple "Refresh window", which seems to do the
> > trick. It seems a bit clunky to have to force FM to do what should
> > come naturally, but I guess that's something we have to live with!
>
> > Thanks again.
>
> The whole "non-comitting" nonsense seems like a VERY silly idea and I
> don't know why they changed it. They should at least have the option of
> not doing it that way - if I enter data it's because I want that data
> there (barring the odd accidental mistake of course). :o(
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

I'm pretty sure the data is committed just fine (assuming one has done
the standard 'commit' action of clicking into a non-field). It's that
the display of the unstored calc lags. Or are you referring to
something else?

Re: Refreshing calc (sum) fields

In article
<15024424-69f5-4ac1-9447-bd50c8aa3b84@d4g2000prg.googlegroups.com>,
Grip wrote:

> On Dec 6, 10:14 pm, Helpful Harry
> wrote:
> > In article
> > ,
> >
> > Carla wrote:
> >
> > > Well, thanks everyone for the suggestions. I now have a button on the
> > > main layout that does a simple "Refresh window", which seems to do the
> > > trick. It seems a bit clunky to have to force FM to do what should
> > > come naturally, but I guess that's something we have to live with!
> >
> > > Thanks again.
> >
> > The whole "non-comitting" nonsense seems like a VERY silly idea and I
> > don't know why they changed it. They should at least have the option of
> > not doing it that way - if I enter data it's because I want that data
> > there (barring the odd accidental mistake of course). :o(
>
> I'm pretty sure the data is committed just fine (assuming one has done
> the standard 'commit' action of clicking into a non-field). It's that
> the display of the unstored calc lags. Or are you referring to
> something else?

"Non-committing" is probably the wrong phrase. The problem is that data
HAS to be committed for some things to work properly - when you enter
data into a field or later change data in a field, newer versions of
FileMaker (apparently) refuse to acknowledge it's existence until the
record has been "comitted".

This means that, unlike my previous example and wanted by the original
question, totals do not update on-the-fly, which is painfully silly and
screws up some techniques / functions that I know many of us have used
on various databases under older versions.

If I leave a field by clicking in another field or pressing Tab, then I
want that just-exited field's new / changed data to be acknowledged by
the database. I don't want to have to click an extra unnecessary button
or somewhere else to "commit" the entire record first.

Having not used these newer versions, I am only able to base this on
second-hand information, but if true as I understand it, then whoever
it was at FileMaker Inc that decided this was a good idea needs to be
"committed" to a padded room at the insane asylum. :o(


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)