How can i display unique rows in a portal with summary calculation fields?
Currently I am able to display the unique values in a portal using self-relationship but I cannot get the summary calculation fields correct. Please see pictures:
I'm surprised that you are getting any value from the get Summary function. It's set up to sum values from the layout's found set, not related records in a portal and that's the sort order that it references in order to compute a sub total.
But a sum function written like this:
Sum ( Resource_Allocation 2::Hours Allocated )
Defined to calculate from the context of Resource_Allocation, will compute based on all records in the table with the same resource type.
ExecuteSQL can also be used to compute sub totals.
"I'm surprised that you are getting any value from the get Summary function. It's set up to sum values from the layout's found set, not related records in a portal..."
As I understand it, should work fine. A portal is a "found set" of records and many uses of summary fields within portals work fine. But a getSummary function is about giving results when sorted by the designated break field, and it isn't clear whether the OP is actually defining the portal to be sorted by the break field.
You are referencing a calculation field and your filtered portal is showing you the first item in the array. That is the expected and correct behaviour for that object.
What you want to see is a summary of hours. That will only occur if you define a summary field in the table that contains the raw data. You can place summary fields into portals and they will do their magic. Your summary field may be one step further away than the portal you are using. Provided it is a valid relationship, it will display the correct results.
Just because your calculation field includes the sum() function does not make it a summary field.
It is Phil that recommended the sum() function.
You will not be able to use summary fields for this purpose. As mentioned by Phil, summary fields work across a found set. Though this "found set" can be the set of records contained within the portal. By filtering the portal, you're asking for it to not include certain records. Then, magically, you're asking for a total of what isn't there in the first place. That won't work.
My guess is that you'll probably need to use a scripted process to generate these totals.
Summary fields can produce totals from a related set, but getsummary does not, in my experience.
It would be interesting to explore this. Perhaps with a test file.
At first glance though, based on the unfiltered portal screenshot, they did provide a correct result.
It would be interesting, Bruce! SUMMARY fields (if placed in the related data table) show correctly in the filtered portal. CALCULATIONS in a parent (using related fields to summarize) would not be accurate in a portal, AFAIK. GetSummary is a calculation/function that would be in the same table as the data, but I have not tested with filtered portals. HMMMM....
* Aggregates (Summary Fields) in Filtered Portals | FileMakerHacks
Retrieving data ...