Global calculation won't update

[Ferdinand]

I have not found the answer to this elementary question in the forums. A calculation field with global storage references a field in a related table. The calculation is simply a sum over the related records. The calc field is on a trailing grand summary part of the layout. The problem is the calculation does not re-evaluate when the data in the related records changes.

I have abstracted the problem from my project into a very simple test database (attached.) On the layout Table Two enter some new values in data1, data2, or anumberfield. On the Table One layout trailing summary there are two versions of the calculations; one global, one not. The non-globals will re-evaluate, while the globals won't. The fields respond the same way to a value change in the Table One key field.

The key field for the relationship in Table One is also global. The key in Table Two is not global. Table One is the "one" side of a one-to-many relationship. (The globalness of the key is not essential to the problem, since re-evaluation still fails if you remove global storage from the key.)

Even the non-global calculations won't refresh automatically; you have to click in the fields to see the values change. But nothing I have tried will make the globals re-evaluate.

If anyone can offer a fix I would be very grateful. If there is some fundamental reason why this setup can never work, does anyone have a suggestion for an alternative method?

testglobal.zip

[Søren Dyhr]

By the look of things are you confusing the aggregate functions with the summaries.

Next thing you have to explain why you're so in love with globalizing in the first place. I'm afraid you template needs some flesh to the bones, why have you solution landed here?

But by and large should a field coexist on the same layout as the field where changes are done, in order to freshen correctly, it has to do with the ownership of a record in a multiuser scenario.

If a result from a related is to be know in every record of the related, shouldn't a global field be the first thing to reach out after, but instead should the cartesian relation type be it!

Even visible related records in a portal can not freshen until the global field they depend on is moved to the related table instead! However will the execution of a script doing this:

Refresh Window [ Flush cached join results ]

Solve some of these issues, but spreadsheet'ish behaviours aren't to be expected in stuff that might be shared by 50 simultanious users looking at the same record, the realm is different.

--sd

[comment]

It is not quite clear what you're trying to achieve here. In general, any STORED calculation - global or not - will not re-evaluate when related records are modified.

[Ferdinand]

[color:black]Comment and Soren,

Thank you for your responses. I see now that my expectation for the field to update was wrong. I will try another way to do the calculation.

I may post another example, with more "flesh on the bones" if I can't get it right based on your suggestions.

[Ferdinand]

Next thing you have to explain why you're so in love with globalizing in the first place. I'm afraid you template needs some flesh to the bones, why have you solution landed here?

The original problem is that some layouts take about 2 minutes to display. When I changed certain calc fields to global storage, there was no delay.

Another skeleton version of the DB is attached. On the expense reports layout, the user chooses a year and a store, then the proper records are displayed, and the labor expense gets calculated. The labor expense is the bottleneck. I thought it should be global because it only needs one value for all records...but as Comment pointed out, it will never update as a global. In the real database the labor is displayed by month so there are 12 labor expense fields. That causes the delay.

I tried doing the calculation in the labor table instead of in the expenses table but it didn't work any better.

Expenses.zip

[Søren Dyhr]

I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships.

...snipped from this reply:

http://www.fmforums.com/forum/showpost.php?post/206543/

So roll up the sleves, and study those instead!

BTW did another template show up some days ago, although it's building on an old algorithm of Edohsins, is the application of $variables making it much faster.

http://www.kevinfrank.com/download/kf-fast-summary.zip

The reason why to do it this way? It does it make you stay in browse mode!

--sd

Edited September 26, 2006 by Guest