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
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
