Trouble with getting a summary field in a linked portal to update

[owangolama]

Hello. I have a portal that shows a number of records from another table. That table also includes several summary fields (that are not shown in the portal) that are used elsewhere in my layout.

It seems that sometimes the summary fields do not update when I change data in the portal.

If I click in blank space on the layout (i.e., not in the portal and not in any field) then the summary fields are jolted to their correct values. If I click in other fields, however, this does not happen. I can also get them to update by doing a GoToField call (w/o designating an actual field), but this has the ugly repercussion of causing the whole portal to flash.

So...

1. Is there a way to programatically simulate clicking in blank space on the layout?

2. Or perhaps the better question would be... what is causing the portal to lag and how can I make it update accordingly?

3. Alternatively, is there a way to avoid the terribly ugly flash that happens when I do a GoToField call?

4. As a related note, I find myself doing the GoToField call (w/o an argument) somewhat frequently in other layouts. Is this a common tactic and is there a better way?

Thanks TONS in advance for any help!

[comment]

The summary fields update when you commit the record. Exiting any active field, either by clicking on the background or by going to [no field], causes the record to be committed (unless you have set your layout not to commit records automatically).

I'm not sure why your portal flashes.

[owangolama]

Hmm... well... I've put the summary field directly on the layout and I can actively click around in other fields (after leaving the portal where I've changed data) and the summary field doesn't update until I actually click on the background. I.e., I can see the exact action that I take that makes it update.

My layout is set to change records automatically.

I'm assuming that my the record is committed as soon as you enter a field that is not in that record, which I am doing. So something's amiss somewhere, either with my setup or your explanation.

Are there any rookie moves that might be causing this to happen?

UPDATE: I've noticed that if I put a copy of the summary field IN each portal record, THAT version of the summary field is updated as expected. But the copy that lives outside the portal is not. So I have two copies of the same field that are showing different values simultaneously. Perhaps there is a context issue of which I am unaware?

Edited September 22, 2010 by Guest

[comment]

I can actively click around in other fields (after leaving the portal where I've changed data) and the summary field doesn't update until I actually click on the background.

Exiting any active field

meaning deselecting ANY active field - in the portal or in the parent record.

I suggest you read more about committing records in the help. It's an important concept when working with Filemaker.

[owangolama]

See UPDATE above. Okay, I'll read more about committing records, but from the above description and experiments, do you still think that is the problem? The summary field is updating in the portal, so I believe that the record is committed.

Edit: Okay, read about committing. As far as I can understand, the record must be committed. I'm certainly clicking somewhere outside the current field.

New Observation, if it helps:

If I use the Data Viewer, the summary field (as seen in the Data Viewer) updates correctly while I'm still in the portal, and then reverts back to the un-updated data when I move out of the portal. Until, of course, I click on the background, when it updates again, this time for good.

Edited September 22, 2010 by Guest

[Vaughan]

I've come in late... shouldn't a calculation field be used instead of a summary field?

[owangolama]

Yes, it should! I was taught that late yesterday by somebody else and was going to update my posts here today. You are absolutely right!

Unfortunately, I am still having the same problem.

In the portal, the user clicks on a graphic to toggle a field {called portal_table::hit} between 1 and 0. This is accomplished with the SetField command.

Back in the main table, I have a calculation field, HitCount, which is NOW defined to be

{ sum (portal_table::hit }.

So as the user clicks graphics (each in its own record in the portal), HitCount is supposed to keep a running tally. And it APPEARS to do so (based on visual feedback of the HitCount field that I've temporarily placed in the main layout, but actually it doesn't.

It seems that the key to solving this problem is discovering why the value shown in the HitCount field in the layout DIFFERS from the value shown when HitCount is tracked by the DataViewer (which is the same value that is used by operations performed in other code).

Hopefully this defines all the relevant parameters of the problem. And again, thanks in advance for any ideas/help/suggestions.

[Vaughan]

The calc field in the main table won't update and sum the related records until the main record is committed.

[owangolama]

Yes, I've come to understand that now. Previously I have used the GoToField call (w/o an argument) to achieve this, but it produces an ugly flash (the whole portal flashes).

I've just discovered the Commit Records call, which also does the trick. Unfortunately, the portal still flashes.

Then I added Freeze Window, which killed the flash, so that's all good.

Nonetheless, I remain confused as to why the field hasn't REALLY updated yet (before Commit Records is called), but nonetheless the summed field *appears* to have updated in the layout. I.e., the tally is actually adjusted, though it retains the old value for the code until committed. Weird.

In any case, I appreciate your help, and you've been right on all fronts. If I hadn't gotten great tips elsewhere, you would be my knight!

[comment]

why the value shown in the HitCount field in the layout DIFFERS from the value shown when HitCount is tracked by the DataViewer

Because the field on the layout is not being refreshed until you exit the portal.

I think you are making this more complicated than it needs to be. The button in the portal should toggle the field AND commit the record. It doesn't matter much if you use a related summary field or a local calculation field.

[owangolama]

Actually, you have it backwards; that's the crazy thing. The field on the layout updates BEFORE the record is committed!

And you're right, the records should just be committed. Though now I think the best way to do that is with the command of that name.

And some research has revealed that Summary fields, while working well for their original intent (e.g., in reports) don't seem to be as reliable as Calculation fields in normal operations, so I'm going to go with a Calculation field.

Nonetheless, I think the mystery of the the layout fields updating BEFORE the records are committed has yet to be satisfactorily explained to me.

Edited September 23, 2010 by Guest

[comment]

Actually, you have it backwards

I don't see what "it" is.

[owangolama]

Because the field on the layout is not being refreshed until you exit the portal.

I inferred that you meant that the field on the layout lags in terms of its updating b/c the records aren't yet committed. And actually, it is the opposite. The layout field updates BEFORE the records are committed (i.e. it precedes the committing action). That seems odd to me, while it wouldn't seem odd if it was delayed until after the committing action somewhat.

If that's not what you meant by the above statement, then I guess I don't understand what you were saying.

[comment]

Well, I said exit the portal, not (necessarily) commit the record. There are more nuances here (for example, if you click into the field it will refresh), but at the end there is no "correct" answer: one could say that once you have modified a value, you should see the impact on the total; OTOH, the record is not committed, and other users will certainly see the "old" value until you commit.

[owangolama]

Okay... not sure how to end this. You certainly have more experience than I do.

That said, I'm seeing what I'm seeing. At this point it is only intellectual curiosity that is making me try to figure out why the layout field updates ahead of the committal.

I appreciate your advice and expertise, and I think we've probably taken this about as far as it needs to go. Thanks!

[Vaughan]

You need to consider the state of the database when there are multiple users. You need to consider when updated records should be available to all other users. You need to consider the impact that incomplete records may have on the rest of the system.

When editing a record, the other users don't see the data being updated. This is because the record may be incomplete and therefore invalid. Therefore FMP only updates the database when the record is committed, and only then if the record validates correctly. Then the updated information is available to all users.

Say for instance there is a field that must have a non-zero value, and any zero values would cause major problems (say, a divide by zero). When a new record is created the initial value may be empty which mathematically is the equivalent to zero. If the database updated at record creation there would be a huge problem. Instead it only updates when the record is committed and when the values are validated.

[owangolama]

Yes, all understood - thx!

This db is actually single user, as are most of mine, but your points are all valid and significant.

At this point I've found my solution. Though I remain curious about what seems to be some oddball visual updating.