Sum or cal of a portal

[Mushi]

I have a portal between two tables, connected by name. The portal has only one field, a number field. Is there any way I can make a summery or calculation field that can add up all the entries in the portal? B)

[CobaltSky]

Yes, Mushi, there is.

In the table that the layout (where the portal resides) is based on, create an unstored calculation field of result type number and enter a formula along the lines of:

Sum ( RelatedTable::RelatedField )

Where RelatedTable is the name of the table occurrence (in the graph) that the portal is based on and RelatedField is the name of the number field that is appearing in the portal. B)

[Mushi]

Amazing, thank you very much!

I knew it was simple, but I just couldn't figure it out (after looking around for a few days)

thanks!

[-Queue-]

Ray, I'm curious what your opinion is of using a related summary field versus a Sum calculation of the related data field, since both are now possible in FM7. The former seems to be more useful since it can be dynamic. What do you think?

[CobaltSky]

Hi JT,

Interesting question. Both approaches have their merits and may have advantages depending on the circumstances.

However I'm not quite sure what you mean by 'dynamic' in this context. As I see it, both approaches could be said to be dynamic insofar as they will recalculate in real time to reflect changes in the data. In one sense a calc could be viewed as more dynamic, since it will recalculate as soon as the user leaves a field, whereas summary fields don't update until record commit. Both can exhibit latency/refresh issues in certain circumstances in some revs of 7.

I guess that I'm inclined to view the difference as more one of flexibility, since each has its own notable strengths. Eg I particularly like the fact that a progressive summary (eg a running total) field can be included within a portal in v7 and will return progressive results with respect to the related set. That's really neat. But there is a trade off, because doing this negates the capability to use the same field to summarize the relationship (ie placed outside the portal it will return a result WRT the first related record only). So you need two summary fields in the related table to bring this trick off elegantly.

Aggregating calcs on the other hand, offer a flexibility of another type insofar as the expression can bring in other elements and can perform a variety of operations on the data prior to returning the result. This was true in v6, but is more useful in v7 where the aggregate value can be declared as a variable and then referenced multiple times within a subsequent expression (thus avoiding the performance degradation that would be seen in v6). This gives aggregating calcs a kind of flexibility which is more open ended (within the limits of the available functions in FileMaker 7). You can do a certain amount of this using summary fields in conjunction with calcs in the related table, but it rapidly becomes hair raising. In particular, it's difficult to reliably perform operations which draw on values from the parent record.

All other things being equal, I lean towards unstored calcs for this purpose, for what I see as their greater flexibility. But I would not go so far as to create a calc if a suitable summary field already exists in the related table. And if progressive sum data is required, there is no contest, I would use a summary field in v7.

So the choice is partly a question of context, but beyond that, developer style and preferences come into play. :)

[Søren Dyhr]

So the choice is partly a question of context, but beyond that, developer style and preferences come into play.

What I miss in you comprensive post, is mentioning of the speed differences if there are any?? Agregate functions have their drawback's in accounting where a vast number of records are summed, since triggers isn't particular filemaker'ish can you dynamical grap the previous records value in other ways than a lookup next lower evaluation.

The rule in bookkeeping is that you never ever delete a record but instead post something counter the error if such occures. But extraordinary measures must be instated to prevent users from deleting lines.

--sd

[CobaltSky]

Hello Søren,

I must say I thought about commenting on speed issues, but thought better of it on the basis that the post was already long enough... and because to do so doesn't add anything spectacular.

This is largely because though I've seen speed differences, for the most part they have not been significant - too small to be a compelling factor in the choice of technique.

Agregate functions have their drawback's in accounting where a vast number of records are summed...

Yes, true. But then summary functions also have their drawbacks where a vast number of records are involved. For similar reasons.

One thing which may be of interest, however, is that the efficiency of aggregate calcs is adversely affected by their use over multi-predicate relationships which employ theta joins (eg those based on less-than or greater-than ranges). The impact is greater on aggregate calcs in this case than on summary fields, (perhaps because the calcs must evaluate across the complex join, whereas the summary fields are evaluated in the source table). So, there are some (albeit obscure) contexts in which, all other things being equal, it might be worth trying both to see if there is a significant performance differential.

HTH.

[Søren Dyhr]

multi-predicate relationships which employ theta joins

Interesting indeed, which reminds me of Enders experimentations on using Edoshins SmartRanges instead of the implementation filemaker provided us with. He found that the fastest to index still are Michails key-concepts.

--sd

Edited August 20, 2005 by Guest