Summary of a unstored calculation

[freecheese]

The problem is this: the summary field (total of all records) doesn’t update because the calc field (total of this record) is unstored. Obviously I need the sum field to be updated the moment the calc field is updated.

summaryofcalcfield.fp7.zip

[Søren Dyhr]

You write that you don't wan't extra relationships, well bon voyage on your next vessel (RDBMS tool), you should change the summary fields to aggregate over an extra cathesian relation, Summary fields belongs to reporting in previewmode, to be honest. There are several ways to remain in browse mode but they hit the another one of your conditions, namely to rely on scripting.

You're simply smashing a borrowed metaphor down on a database, which seemingly belongs to spreadsheets??? As such have unstored fields in other records, than the one you're in no value, the display of it is just a ghost seen from the summary function.

--sd

[comment]

If I really wanted this, I suppose I would use a custom function to sum the values of the found set. I am assuming you want the found set, since that's what a summary field would look at.

However, it would be simpler to modify the user interface, so that editing of the child records is done in a pop-up edit window, rather than directly in the portal.

summaryofcalcfield.fp7.zip

Edited April 16, 2007 by Guest

[freecheese]

The misunderstanding is that I don't really need the summary to be 'live' updated. Use it mainly for reports. Just changing (any) layouts didn't (always) do the trick. That's where the confusion started.

Bottom line is that I need to hit the lines table for the summary to be updated. Right?

Thanks for replies. The custom function solution is very nice btw.

[Søren Dyhr]

So it's a small company, since your CF isn't a tail-recursion?

--sd

[freecheese]

Yep small company. What is tail recursion?

[comment]

The misunderstanding is that I don't really need the summary to be 'live' updated. Use it mainly for reports.

Sheesh, I could have sworn you said earlier:

Obviously I need the sum field to be updated the moment the calc field is updated.

I think your best option is to make your report from the LineItems table, subsummarized by Invoice. Then the summary field can live in LineItems, where it properly belongs.

The invoice total should be a calculation using the Sum() aggregate function, and the total of found invoices is meaningless, so you can get rid of it. You can get this total in your report by placing the summary field in a grand summary part.

[comment]

So it's a small company, since your CF isn't a tail-recursion?

This is nitpicking. The CF is equally unsuitable for a found set of 10,000 as it is for 50,000. It has never occured to me someone would be editing a found set of >10k, and expecting to see a live feedback of the total.

[Søren Dyhr]

equally unsuitable

This was the point I liked to be raised here, to expand on my statement "wrong metaphor" ...instead of saying it like. I have somthing up my sleves, giving you a homerun. You had it comming I'm afraid...

Yep small company. What is tail recursion?

As if I havn't guessed! But since you ask:

http://en.wikipedia.org/wiki/Tail_recursion

--sd

[Genx]

Wikipedia really is getting quite useful isn't it? Lol, my StockXchange & Derivs professor loves it, he even made an entry and at his age hehehe.

[comment]

This was the point I liked to be raised here

Then perhaps you should have addressed that, instead of picking on the custom function. If you read carefully, you might have noticed I am not exactly happy with the original 'metaphor' myself - and I didn't like the alternative you suggested ("aggregate over an extra cathesian relation") any better. But changing the CF to tail recursion will not make any difference here.

[Mikhail Edoshin]

Summary field always update when the found set changes. So if there's no other way, you can Show Omitted twice to ‘refresh’ your found set.

[Søren Dyhr]

But changing the CF to tail recursion will not make any difference here.

Aren't they usually slower?

If I really wanted this

Are you aspirering for a political carreer??

--sd

[comment]

I think you can do better than that, Søren.

[comment]

Yes - although there's a very small chance of picking up a newly created record along the way.