Calculated field does not update but flashes update

[Jeff Fried]

Very strange, and I can't figure it out.

There's a field on the parent form that counts the number of records in the child browse. This is a field that calculates rather than a calculated field, meaning I have a calculated field that works but the field in the parent that tries to calculate the count of records in the child region does not.

For example, I can see 5 records in the browse but the calculation of the field shows only 2. When I try to update the region by either adding another record or changing an existing record, then save, it briefly flashes the correct count (5 in this case) then reverts back to the previous value (the erroneous 2). The calculated formula is correct, especially since it does briefly show the correct amount but doesn't stick.

What in the world is going on? I tried turning off referential integrity, which I would like to keep but thought maybe that might be a factor, so that's factored out. Any idea where I can look for an answer to this puzzle?

Thanks.

Jeff

[Tom Cone Jr]

Re: Calculated field does not update but flashes update

Jeff,

Help us by clarifying your situation.

A "calculated field" is usually meant to refer to a table field for which a calculated field rule has been defined.

A "calculated value" is usually meant to refer to an object defined in the form layout for which a computation has been specified. This object is akin to a variable and is not stored in a table.

I would not expect a "calculated field" in the parent table to be automatically recomputed when data entry occurs in the related child table.

[Jeff Fried]

Re: Calculated field does not update but flashes update

Jeff,

Help us by clarifying your situation.

A "calculated field" is usually meant to refer to a table field for which a calculated field rule has been defined.

A "calculated value" is usually meant to refer to an object defined in the form layout for which a computation has been defined. This object is akin to a variable and is not stored in a table.

I would not expect a "calculated field" in the parent table to be automatically recomputed when data entry occurs in the related child table.

OK, let me clarify.

I know what you're saying about the nomenclature of "calculated field" vs. "calculated value". I should have expressed it this way. I'm just in the habit of saying calculated field when it should be calculated value.

itemcount is a calculated "value" on the form, defined as follows: "# M/M/V items: "+ltrim(str(dbcount("market","opno",opno))) This calculated "value" is always correct.
I also have a field in the parent table named mktcount n 3 0. I will tell you its purpose below. In the parent field rules for this field, I have it defined as a calculated "field" and the calculated field expression is as follows:
DBSUM("market","Opno",OPNO,"counter")
Note that this is one of a few expressions with which I experimented and it would not be the final one. The final one would count the child records in the region.
I have done this type of calculation in a parent field before, successfully, but in this case, though it appears to calculate properly, something keeps the calculation from populating the field. I know this because there is a very brief display of the correct value after a save, then it disappears and the original, incorrect value remains.

While I would like to solve this very strange and perplexing issue, the only reason I am doing this to begin with is I would like to have a parent calculated field react to changes in child records of the parent. The reason for this is I have on the form fields for date, time, and user of the last edit. These three fields are calculated fields, the first resulting from date(), the second from left(time(),5), and the third, from user_name(). These updates work properly when a change is made at the parent level. However, I need to also update these field when a change is made at the child level such as adding a record in the region or editing a record in the region (which is why dbcount would not be relevant since the count could stay the same at the parent level even if changes are made).

Basically, I need to sense changes in the child records that would update the calculated fields in the parent. In the past, I have accomplished this by creating a parent field that calculates based on a change made at the child level, but for some reason, this strange phenomenon of briefly seeing the value flash by and disappear has been happening here.

If you know of a better way to update my date, time, and user of the last change, whether made at the parent or child level, I would love to hear it. Then I would throw out the artificial calculated field that exists only to create a parent-level change.

I hope this all makes sense to you.

Thanks.

Jeff

[Tom Cone Jr]

Re: Calculated field does not update but flashes update

Jeff, if it were me, I'd look at attaching a script to an event that fires when a change or enter is saved in the child table. Events are available in the embedded browse, and also in the "record events" section of the field rules for the child table. The script would set the desired field values in the related parent table record, and commit those changes to disk.

[Jeff Fried]

Re: Calculated field does not update but flashes update

Jeff, if it were me, I'd look at attaching a script to an event that fires when a change or enter is saved in the child table. Events are available in the embedded browse, and also in the "record events" section of the field rules for the child table. The script would set the desired field values in the related parent table record, and commit those changes to disk.

Tom, I've been that route, attaching field updates to OnSave events, OnChange, and several other things with which I've experimented. But I'll take a fresh look at this if, in your opinion, this is the way to achieve the "sensing" of a change at the parent level when a change is made at the child level. I think the same issue cropped up, where I would see a flash of the updated value and it would disappear and revert to the previous value.

I think what you're saying is to keep the three parent calculated field still as calculated fields but update those values with the event scripts, right? Or are you saying that I should make those field user entered and cover changes both from the parent and child using event scripts? Any advice on this?

Thanks.

Jeff

[Tom Cone Jr]

Re: Calculated field does not update but flashes update

Two quick responses:

1) No. You can't write (assign) values to "calculated fields". The calc field rule blocks any such edits, whether manual or scripted.

2) Most would not do what you're doing. They'd store the last update date, user name, etc in each child record, not in a single parent record. Sort the children by update date and you can find the "last" edit whenever you need it.

[Jeff Fried]

Re: Calculated field does not update but flashes update

Two quick responses:

1) No. You can't write (assign) values to "calculated fields". The calc field rule blocks any such edits, whether manual or scripted.

2) Most would not do what you're doing. They'd store the last update date, user name, etc in each child record, not in a single parent record. Sort the children by update date and you can find the "last" edit whenever you need it.

Good advice, Tom. Thank you.

Jeff

[Tom Cone Jr]

Re: Calculated field does not update but flashes update

Jeff,

Check this screencast

[Jeff Fried]

Re: Calculated field does not update but flashes update

Jeff,

Check this screencast

Sorry, I don't know how flash fits with this issue. Maybe you picked up on the quick display of the new value that then reverted back to the previous value. I think that was related to the defining of the three fields to update as calculated fields which I have changed.

Thanks.

Jeff

[Ray in Capetown]

Re: Calculated field does not update but flashes update

After trying Tom's method do you still have it flashing back to 2? That's a good indicator actually. If it were too quick to notice that would be more difficult to track.
The flash of one value before changing to another suggests that you have something else going on after the correct count is calculated, that recalculates back to 2 and does another refresh.
Try strip down everything that may have an influence.
Or post a sample.

[Jeff Fried]

Re: Calculated field does not update but flashes update

After trying Tom's method do you still have it flashing back to 2? That's a good indicator actually. If it were too quick to notice that would be more difficult to track.
The flash of one value before changing to another suggests that you have something else going on after the correct count is calculated, that recalculates back to 2 and does another refresh.
Try strip down everything that may have an influence.
Or post a sample.

I thought, like you, that there was something going on that caused this. In the end, I think maybe it was a result of having calculated fields defined and I was trying to change the values in these fields under script control and the calculated fields resisted this change. I made those calculated fields into user-entered fields and that issue has now gone away.

The reason I thought I could get away with updating calculated fields is my regular use of Visual Foxpro EXE's to run procedures within Alpha that do various things, one of which, sometimes, is to update the field values of calculated fields. There is no problem with this, but in this case I was trying to update the field values of the calculated fields from within Alpha, something I probably haven't tried before.

So this would appear to be the explanation for the quick flash I was seeing.

Jeff