Related Summaries - Undocumented Feature?

[comment]

I am discovering - rather accidentally - that summary fields in version 7 can summarize child records, if placed on the parent table's layout. Even running total, placed in a portal, seems to work correctly (see attached demo).

AFAIK, this is not a documented feature. If this could be relied upon to work consistently, a single summary field could replace many aggregate calc fields.

Is there a way to get FMI's position on this?

relatedSummaries.fp7.zip

[Søren Dyhr]

Yes indeed what a great discovery!!! If you continue that way will all the use for calcfields vanish in thin air But isn't it so that it's tied to the sortorder defined in you relation ...if so has Ray Cologon been very close to reveal this feature a fortnight or so ago ...if I just could remember where he mentions it???

But it seems to be a new thing in Filemaker caused by perhaps by the bidirectionaly to of relations?? Edoshin wrote in his "Fast Summaries":

GetSummary() doesn't work with related fields!

In most cases you run a report from a file that doesn't have real data from related files, only internal identifiers. In real database solution a file similar to our sample won't have city names, since they aren't guaranteed to be unique. Instead it will have city identifers, most likely serial numbers. Of course, in a report cities must be sorted by their names, not by their identifiers.

All this looks simple until you discover that GetSummary() refuses to accept a reference to a related field as the break field parameter. If you try to do it, you'll get an empty result. Since this behavior isn't documented and there's no problems with related fields in standard summary reports, I consider it a bug. The function just doesn't know what a related field is; if you create a local calculation that gets that field data and sort by it, GetSummary() will work just fine. By the way, this is the main workaround to this problem. Another possible solutions are to lookup the necessary data as records are being created (not quite safe) or to sort records twice: first by the related filed and then by the serial and use GetSummary() with the latter (too complex).

excerpted from: http://www.onegasoft.com/tools/fastsummaries/index.shtml

--sd

[comment]

isn't it so that it's tied to the sortorder defined in you relation

No - it works without the relationship being sorted.

In the demo, one of the relationship is sorted, just to keep the transactions in chronological order. But the running total will just as happily report on unordered records, and the Total/Count/Average etc. summaries are completely unaffected by this.

[The Shadow]

It seems to me this is similar to the concept of getting related data from multiple tables away without "tunneling".

[Søren Dyhr]

I noticed some month ago that summaries actually are a little buggy in the new implementation and further more slightly slower than they used to be under 6.0.

It was when investigating of a migrated .fp5 template of Kevin Frank and Scott something made for a usergroup...

So what could this discovery be used to, what points are there in moving the sum to the file where data lives??

--sd

[comment]

Example: a single field can return the totals for Products, Services and Invoice (see attached picture).

The same field could give the total of Sales to a Client, in a Month and in a Year and so on... all depending on the TO it belongs to and on the layout where you placed it.

Can you be more specific about "little buggy"?

[comment]

> similar to the concept of getting related data from multiple tables away

I can't see a connection between the two. Anyway, getting data from remote tables is more than documented - it's practically flaunted as 'the' feature of v.7.

Conversely, this summary business could be an accidental by-product, and removed in subsequent releases. Hard to know if one can build on it, and what are the associated gotchas. I have already noticed a refresh problem when the key is changed - though not when the data itself is modified.

[Søren Dyhr]

Can you be more specific about "little buggy"?

Indeed try to migrate both solutions here and compare them with the performance of 6.0:

http://www.kevinfrank.com/dig-fm/07-unique-attendance.zip

--sd

[comment]

I don't have 6, so I am not sure I get the point. I think I can understand how a summary depending on another summary in the same file would get a bit confused. Did both files work correctly in 6?

[Søren Dyhr]

They did!

--sd

[LaRetta]

I have read this thread ... and re-read it time and again. And I have played with the concepts here extensively over the past few months (although my tests aren't conclusive as yet). But it's time I speak up for the power of summaries in vs. 7. Whether intentional or not is simply not the point, IMO, but rather how long we'll be able to take advantage of such power. Other 'unintended feature/bugs' have remained in FM for years! Developers who have taken advantage of these feature/bugs are ahead of the game because the REASON they are applied is because they are better!!! I simply choose to take advantage.

And I also believe The Shadow. This new ability to *see* through relationships is a feature and I think (although maybe not intended) it is a byproduct of the new power of vs. 7, ie, looking through relationships also means looking UP the relationship chain as well (providing ALL parents dynamic summaries easily just by placing it on the parent layout). I would rather have ONE summary field in LineItems, which counts the quantity - and then use that ONE field throughout my solution (filtering the results automatically depending upon the table occurrence), than have multiple Sum() calculation [color:blue]aggregate fields in multiple parent files (whereever I need them). Whew! That was a mouthful but so is the concept. Please see again the power of the visual that Comment displayed above, placing the same single field on multiple table occurrences (translate::ANY OTHER RELATED TABLE). Period.

My comparisons also show summaries are much quicker than Sum(); in fact, in some (yet unidentified) circumstances they increase their speed x10 and become lightening fast! I shall identify these variances. But not once have summaries been slower than their equivalent Sum() calculations (I've tested both in same set of relationship/records of 80,000 parent to 450,000 related). Confession ... I know nothing about GetSum() ... never used it. So I have no idea how this function fits into the picture. And some calculations may simply require Sum() but that's not the point.

I would be most interested in knowing the specific instances of slowdowns in using summaries as Soren indicates. And also examples of the 'buggy results' described. If these tests were conducted on converted files, I wouldn't be surprised by anything. But I'm interested in vs. 7-created structures only. Because I believe that even good conversions create convolutions within the resultant structures. And I also believe that there are internal dependencies that hone 7-generated algorithms better than adopted files. Well, that's my opinion, anyway.

Now I'll probably get blasted for taking such a strong stance ... bring it on. Because if I'm wrong, I want to know that also. But I doubt many of you have considered it deeply and fewer still have tested it. Only by challenging prior understandings can improved understandings be formed. Thank you, Michael, for starting this thread and focusing me on taking another hard look at what I THOUGHT was a limited feature reserved for reports or checkbooks. Naming this an 'undocumented feature' is probably most fitting. Simply, summaries rock my world.

LaRetta

[Ugo DI LUCA]

Soren,

The file submitted by Kevin was corrected by Jason deLooze by including a summary field in the body.

flagging_unique_-_jason_fix.zip

[Søren Dyhr]

If these tests were conducted on converted files, I wouldn't be surprised by anything. But I'm interested in vs. 7-created structures only. Because I believe that even good conversions create convolutions within the resultant structures. And I also believe that there are internal dependencies that hone 7-generated algorithms better than adopted files. Well, that's my opinion, anyway.

LaRetta - You're probably right here - I havn't kept my correspondance with Kevin ...but at the time he saw the slowdowns as well. But what we really saw wasn't examinend thoroughly.

One thing though is that the field is hanging since it comes a relation away ...which is an issue that Scott Love in his TEC401 session (Advanced Relationship Graph Techniques) brought up at Devcon. He advocates for putting all calc's in the leftmost TO which he dedicated the layout part of the graph in each TOG.

This is why I believe you find a 10x factor, because the dependencies for the layout to trig the freshing isn't there. The good question is if we can yield better results from GetNth in an unstored field. Comparing between table timestamps to freshen is clunky to say the least. JMO showed a different technique where he added a nonsense evaluation with Case(Filter;"") & ...followed by the issue to freshen.

But, should we embark on testing this empiricly or have any done it already?:

--sd

[LaRetta]

Hi Soren,

With (almost) unlimited structuring possibilities in 7/8, testing must encompass varied configurations. Otherwise, all we can say is that (in one particular configuration) results are thus but may be different if the relations are more distant, etc. To glean the principle from these speed shifts, I must include many configuration tests and I simply have not (yet).

I've read about tabs and various other goodies in 8 but I'm more interested in whether they've shaved the carbs or enriched the fuel mixture. If popup speed has increased; if other areas of 8 are now faster, how was this accomplished if they didn't also tweak the engine (so to speak)? And if they did, all bets are off until I can thoroughly test 8 across a network.

I would bet others are well ahead of me here and I look forward to reading their results. In the meantime, I'm focusing on finishing critical connections in our 7 solution. Then I will be taking a month off so I can focus on network speed-testing of 8. Only then will I either incorporate all of 8's great new features in our design or trash (most or all of) it and rewrite in 8. It will be a few months before all licensed clients (and our Server Advanced) is upgraded. My version of Developer (oh, I mean Advanced) will be here Tuesday (plus 2 more vs. 8 clients). I will use Advanced for enhanced diagnostics only. Okay ... I may play a BIT with it also, but I'm trying to remain focused on priorities.

LaRetta

[Søren Dyhr]

whether they've shaved the carbs or enriched the fuel mixture.

Havn't you heard that Atkins is both dead and bankrupt??

From his arbituary in CNN is it perhaps relevant to quote this:

"The weight loss comes primarily from water," said Kathleen Zelman, a spokeswoman for the American Dietetic Association, among Atkins' critics over the years. "But you can also burn muscle, and body protein can be in the form of muscle -- your heart muscle."

...and keeping in mind the cause of his dead.....

Well it's the later, a new code have definately found it's way into the multicriteria relationship ...how far they else have managed to tighten things up, is hard to tell.

But Andy Lecates showed this improvement alone without letting us know if other improvements have arrived. I see that the new features very well might loosen the tight spots, by giving new paths away from the usual workarounds, we have become to believe is straight forward thinking.

--sd

[LaRetta]

Indeed, Soren. I scroll through my 122 various vs. 7 tests and wonder whether they still hold true.

It would be simpler to say that, across the board, vs. 8 is probably just faster in each instance. However, what if the pendulum has swung backwards in a few situations (in a balancing effect)? Or what if a process (simply because something else was improved) now is faster? A process dismissed in my tests before may prove to be quite virile in 8! I now distrust every one of my calculation, relationship, popup, portal, global vs. unstored, import/export etc. series of tests.

I wish I knew all the subtle changes in behavior that may occur in the tweaking of 8; because, truly, it is the dependencies, packet handling capabilities, and other internal changes which will effect efficiency overall. And that subtle ripple will bring all kinds of surprises and new possibilities. My 'month off' will be for me ... I will run two vs. 8 systems (Mac and Win) simultaneously through every conceivable test while simultaneously devouring the new vs. 8. Then I will take those same tests to our network at night and run them again. And I will get enough sleep EVERY DAY for a MONTH straight so my mind and spirit are totally UP and open to absorb!!! I will be in ultimate heaven.

LaRetta

[Søren Dyhr]

And that subtle ripple will bring all kinds of surprises and new possibilities.

It's my belief that especially the server issues have undergone heavy changes.

But take the thread where we (here) spend considerable time on fixing a portal avoiding the scrolling ...now is it so that the behaviour can be managed much much more elegantly with a single small calc tool GetNthRecord( ....

Think for a moment what a repeating calcfield using it and Get(CalculationRepetitionNumber), and where it isn't a global field to makes the selection but a $$Variable.

--sd

[Søren Dyhr]

Well I needed to straighten my mind on this portalfixing....please await a template when the php errormessages ends!!

Well now it's back again, while the template actually belongs to that old thread "scrolling" - anyways enjoy

--sd

portalfix8.zip

[LaRetta]

When you mentioned GetNthRecord and scrolling, my mind exploded!!! YES! Thank you so much, Soren!!

Uh, I won't allow myself to see vs. 8 until my work is done (this part of this project in vs. 7). But you can bet I'll devour this concept as soon as possible. :wink2:

Edited September 4, 2005 by Guest

[Ugo DI LUCA]

Just wanted to let you know that if a subtil fix was needed to make the file (from Kevin Frank attached by Soren) work with 7, FileMaker 8 corrected the bug.

[LaRetta]

Yep ... all new testing must commence. This is all very exciting stuff! Thank you, Ugo! 8's getting harder to resist by the moment! :wink2:

[Ugo DI LUCA]

For thos who care, Kevin just uploaded a new modified demo that currently identifies the problem as only happening on 7, while it is solved with 8

Like to the File

[Søren Dyhr]

Now have I due to this thread:

http://www.fmforums.com/forum/showtopic.php?tid/171134/post/181337/hl//#181337

...discovered, that the feature is much older than originating from the migration to FM7 ...the ability existed under fm5 as well ...behaving just as reluctant to be freshened as present version.

--sd

[comment]

Update:

Seems like there's a good reason why this feature is 'undocumented'. In the attached file, the running total in portal is calculated for the first 12 or 13 records - then nothing.

Clicking on the missing summaries makes them appear - but when you close the file and re-open it, they are missing again.

This happens in version 7 - seems to work well in version 8.

Can anyone confirm? Or, even better, explain?

BugInRelatedSummary.fp7.zip

[LaRetta]

Hi Michael,

I've been using this process since 7.01. In 7.01 it worked perfectly no matter how many rows. I could scroll up and down and it always displayed perfectly! I wonder if it worked in 7.02 (we skipped that version).

We just updated (two weeks ago) to 7.03. It breaks just as yours does! I hadn't noticed it because I decreased my portal display to 10 rows and auto-jump users to last row when they switch to the layout. Running summary displays fine and Users never scroll upwards - only the last few entries is all they care to view.

It works again in vs. 8? I don't have 8 on this box (we are currently mixed versions). But, since we're mixed, I guess I'll have to 1) be sure those that view these portals (only Management) have vs. 8, 2) quickly move us all to vs. 8 or 3) fix it.