Summary of Portals

[Delita]

I'm trying to make a summary of some portal fields but I'm not sure how to do it. I'll try to explain this as best I can.

My first table has just one field in it thats an ID number and it's the primary key. Then in my second table I have three fields: an ID number, an objective number, and a score. In the second table there are muliple records with the same ID number each with different objectives.

I created a layout (with the first table as the base table) that shows the ID number, and then a portal of the objectives and scores. I wanted to have it in list mode though so it would show every ID number from the first table and the corresponding scores from the second table. I want the scores in order by objective, so we're using the horizontal portal hack. Then in the body I have three portals for the objective scores. The initial row is incremented up one each portal. So the first one shows the score of the first objective, the second shows the score of the second objective and so on.

What I would like to do is have summary fields at the bottom for each objective. I want to show an average of all the objective scores from each particular objective. For example, for the average of objective 1 score, I need to have the calculation based on portal row 1 from record 1 and portal row 1 for record 2, etc.

Edited January 31, 2006 by Guest

[Ender]

Welcome Delita,

I think I might try this within a layout based on the second (Objective) table, using traditional sub-summary parts. This is the easiest to do, although it would not format the results quite like you specified. It would result in the Objectives being grouped and sub-totaled by objective.

There are also ways to calculate the aggregates over relationships, which could provide the format that you desire, but this depends on relationship filters to narrow things down. This can get complicated if you're trying make your summaries act on the found set, since the relationships to the Objectives would then need to filter by something that's true for the found set.

Hopefully the first option will be sufficient. If not, let us know.