I tried to make it dynamic and update depending on what team is currently shown (filtered in portal) and for that I use one-line, no-field portal with the same filter portal formula as the portal with players above. But I just doesn't work.
You'll need to describe that in more detail is this method should work for you, provided that the fields you put in this one row portal are summary fields.
How doesn't this work for you? Can't see any data in this portal? See the wrong values? Doesn't refresh correctly when you select a different team in the drop down?
Wrong data shows up.
I used John Mark Osborne's filter portal video tutorial, where he described how, depending on what the minimum product price you enter, the total always updates accordingly. Strangely enough, when I tried to mimic the technique, results don't update.
I have script trigger "OnObjectModify" to run that refresh window script for the filter portal, and it works fine. Do I need another one for summary row? As I've seen in John's example, it's not used.
As I said, I'm using self relations, so i tried to use merge calculation values from both original table occurence and particularly from the relational table occurence (as seen in video), but results just don't update, I see averages of all players, no matter which team I pick.
If both portals refer to the same Table Occurrence (List the same "box" from Manage | Database | Relationships in Show Records From... of Portal Setup) and both use exactly the same filter expression, and the "total" fields in the second portal are summary fields, the correct data should appear in your second portal.
Since it doesn't, you'll need to check each of those requirements and see what needs to be corrected.
1) Both portals use the same table occurence.
2) Both use exactly the same filter expression.
3) Total fields on the layout are merge fields, using data from summary fields, Summary. Summary method: Average of:
Correct data appears only in the portal, but the summary fields don't update. They display averages of all players, but never update when i select particular team.
As a test, try using a straight data field instead of a merge field to make sure that the field is completely within the borders of the portal.
BTW: my understanding is that using the global field as the relationship key field will cause the portal to display only those matching records, as you would expect. And calculations based on that relationship would calculate across all the matching records - as you would expect. But using the 'Filter Portal' function is (as I understand it) a display feature - the portal may be additionally filtered to show a sub-set of the related records, but any calculations will still use all the records matching the relationship. This may not be a problem now, but it might be something that throws you later if you are indeed using the global field to 'filter' the related records, and also the 'Filter Portal' function.
Are you sure you are using the 'Summary' function correctly? I am (was) amazed that simply picking up the Summary Field from the other table works - but I just tried it as simply as you've described it and it works perfectly, so every cloud (your problem) has a silver lining for someone (me: finding a new way of showing data) - thanks for that...
Oh - just noticed one difference (though it should not make any difference) - I didn't bother with the one-line portal to display the summary field, I just set it on the layout, below the portal.
Just tried it using the 1-row-portal, and it still works fine - it's just unnecessary, I think.
Also tried it as a merge field - works perfectly.
Tried it in Form View and List view - works perfectly.
And I didn't need to add any 'Refresh' - as soon as I change the field the summary value updates
Set the key field as a global, and then as an indexed field - still worked perfectly.
So the good news is it will work the way you want. (I'm using FM 11)
You need the one row portal so that the filter controls the total computed. Otherwise, you get a summary that does not summarize only the records that match the filter expression.
As I said, I was amazed that this worked at all, but what I tried was:
Parent Table Child Table
SumQuantity (as average of Quantity)
I entered a series of records in the Child table with various KeyField values and quantities.
In the Parent table I created a layout with the KeyField and a 6-line portal to the Child Table. I added the related field Child Table:SumQuantity directly on the layout. I added another one-line portal to the Child Table directly below the 6-line portal and set another Child Table:SumQuantity in it.
As I changed the Parent Table KeyField value the portal immediately re-drew (no screen refresh needed), and both instances of the average for those records calculated and displayed correctly. It worked if the Parent KeyField was normal or global.
Methinks I have perchance misunderstood what we're trying to achieve...
(But I appreciate the trick it has made me discover, anyway!)
Yes, what you just described is not a filtered portal, just a standard unfiltered portal.
In FileMaker 11, you can specify an expression and only those portal records for which the expression evaluates as true will be displayed. If you want to display summary data that only reflects data from the records that pass through the current filter settings, you need to use summary fields from the portal table inside a second, one row portal that uses the same filter. Otherwise, you will see a summary value based on all the related records, not just those currently visible in the original portal.
I didn't really understand in this scenario what the value was in applying two filters - by relationship and by Portal Filter. I was thinking that they could remove the portal filter.
You have made me re-read the section of FM Help I had glanced over, and now I can see that the filter will apply to calculations across the same records - if it is inside the (a) portal. Thanks for the clarification.
There are two advantages to portal filters over relationship filters:
You can create a variety of different portal based "views" of your data without creating a new Table Occurrence for each.
Some filter expressions can control what records are visible in ways that are difficult or even impossible to achieve with a FileMaker relationship. (Try using a relationship that only relates to records that have the string "xyz" somewhere within a larger body of text. I won't say you can't do it, but it won't be easy or simple.)
I recognise what you say - I've used the Filter Portal to make unnecessarily long portals (ie: a large number of related records) sort much quicker specifically for IWP users, for example, by filtering them by a global date. Maybe I have not understood correctly what the poster is trying to achieve, but I didn't pick up what the advantage was in this case.
I'll duck out now.