Help with Subsummaries (I believe)

[Lemur]

I need some help with subtotaling information in portals – let me explain specifically:

I have a file called “Players” and another called “Games.” There also another file called “Sessions” – there are join files for PlayerSessionJoin and GameSessionJoin.

Essentially, on portals in sessions, there can be multiple players and multiple games.

For example, let’s assume there are three dates in sessions – Jan 1, Feb 1, Mar 1

Let’s assume there were the following games at each session: Jan 1 – Monopoly, Risk, Chess; Feb 1 – Risk, Sorry; Mar 1 – Monopoly, Sorry, Chess.

Let’s also assume the following people attended each session: Jan 1 – Al, Bob, Carl, Dennis; Feb 1 – Al, Carl, Dennis, Edgar; Mar 1 – Al, Bob, Edgar

Now – there’s another file called GamesPlayed – this features a join between the sessions and the players – each GAME has its own record (and, in the jointable, each player that plays a game has its own record.

Again – let’s assume: Jan 1 – all 4 played the games – including 2 separate games of chess; Feb 1 – all 4 played the games, Mar 1 – all 3 played the games, except Edgar didn’t play Chess – my jointable, in total, has 28 records (of player played games). Included on this portal (on GamesPlayed) is the score each player had, as well.

Now – here’s where it gets tricky:

I need to track the average scores and number of plays per player and per game.

This means, when I go to the GAMES file, and look up Monopoly, using the above examples, I should see that Al and Bob have played twice, and I should see their AVERAGE score; and I should see that Carl and Dennis and Edgar have played once, and I should see THEIR average score.

Only the players that played that game should be listed (for example, if Edgar didn’t play Risk, he shouldn’t appear here, even though he’s in the PLAYERS file).

Also – when I go to the PLAYERS file, and look up a specific player – for example, Dennis – I should see that he played Monopoly, Chess and Sorry, once each, with his score; and he’s played Risk twice with his average score.

Only the games the player played should be listed, with average scores and number of plays (again, for example, Risk shouldn’t be listed for Edgar, since he didn’t play it).

I want this information to be present, immediately – I don’t want to have to run a filtered portal to get the information.

In EXCEL, I could do this with a Subtotals function or a “COUNTIF” function, but I simply don’t know how the best way to get these subtotals etc. would be in Filemaker – this is the LAST thing I need to do to finish porting over my Excel sheet, into a Filemaker database!

A friend at work said it sounds like I need the SQL “Group By” clause. In looking it up, I found something called “sub-summaries” that I THINK might work – however, I’m not 100% sure. It almost sounds (from my initial research) that they’re only available in preview mode (not browse mode) – and I’m unclear if I can do a subsummary that uses the AVG function.

If it’s correct, however, I’ll devote more time to researching (or, I’ll gladly take any help here!)

Please help! 

Chris

[Søren Dyhr]

Yes it is the subsummary reporting function you need to use unless the preview-mode is unbearable. Unfortunately have I removed my Classic environment unde OS X due to rare use of it, this means that I can't run fm5 in order to make you a template.

If say the previewing fills you with a bad gut-feeling, could you turn your attention to this:

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

But by and large is the right place to make the reporting in a many2many structure, in the joinfile/table, which in your case means that you should usher in related values for display purposes via unstored calc'fields you place in the joinfile.

--sd