Running Total by Username

[mdrennan]

Hello everyone. I'm looking for a specific solution to a problem, and I have not been able to find it explained clearly (at least not clearly enough for my understanding ). I'm not sure if this is a portal type solution, because, to be quite honest, I don't fully understand portals. If anyone could help me with some step by steps on how to accomplish this, it would be greatly appreciated.

Okay-so I have a file with only one table. It is basically a week by week survey. Each week the questions change based on some date rules and global fields. I have about 50 different users that login and answer the questions, and my database will span roughly fourteen weeks. Thus, there will be 50 users with 14 records each when all is said and done. Each record will produce a "weekly score" based on the user responses. I have scripts and layouts set up to report the weekly rankings based on high scores. What I cannot figure out is how to also keep running point totals for each unique user name that can be reported. In other words, each week, I would like to be able to generate a weekly "leader" report, and also an overall "leader" report. I really don't know where to start. I've looked into summary fields, etc. but can't figure out how to selectively sum the weekly point total field based on username. It is very important that I be able to display the usernames and total running scores in a table so that rankings can be visualized by the users.

Any help would be very much appreciated. If you have a few minutes, please try to be as specific as possible; I've only been using Filemaker for a few months.

[comment]

You should have another table of Users, where each user has one record, and a relationship between the two tables based on matching username. Then you can use a calculation field in the Users table =

Sum ( WeeklyTable::Score )

to get the overall results so far.

Another option is to produce a summary report from your current table. For this, you will need a summary field (Total of Score). Then sort the records by username and show them using a layout with a sub-summary part by username (and no body).

[mdrennan]

You should have another table of Users, where each user has one record, and a relationship between the two tables based on matching username. Then you can use a calculation field in the Users table =

Sum ( WeeklyTable::Score )

to get the overall results so far.

Another option is to produce a summary report from your current table. For this, you will need a summary field (Total of Score). Then sort the records by username and show them using a layout with a sub-summary part by username (and no body).

Thank you very much. I think I got the subsummary method working. Is there any reason why one method would be preferable to the other? As I said, I don't have a very strong grasp of relating multiple tables to one another, so if it's all the same, the sub summary report might work well for my needs. One peculiarity-I had tried the sub summary report once before, set to sort against a field called "accountname" that I had set as a calculation = get(userName). I noticed that sorting against this field did not alphabetize the names. When I created a second field, auto-enter by creator's account name, I was finally able to generate a sub summary report as expected, with proper sorting of the users. Any ideas why the first field would not sort properly? Thank you so much for your help. You've really solved many hours of frustration.

[mdrennan]

You should have another table of Users, where each user has one record, and a relationship between the two tables based on matching username. Then you can use a calculation field in the Users table =

Sum ( WeeklyTable::Score )

to get the overall results so far.

Another option is to produce a summary report from your current table. For this, you will need a summary field (Total of Score). Then sort the records by username and show them using a layout with a sub-summary part by username (and no body).

Uh oh. Another issue I've found. Using the sub-summary method you mentioned with a total score summary field, each new "weekly total" seems to be adding to all users. In other words, if user A has a total score of 50 and user B has a total score of 30, this is what happens upon user B scoring 15 more points:

user A: 65

user B: 45

Obviously, user A's score should remain the same and user B should increase by 15. Any ideas what I've done wrong?

***Sorry! I realized that when I switched the sort field to the auto enter field as mentioned above, my summary "total scores" field was still referencing the get(userName) calc field. I edited the summary field and the totals are working as expected.

[comment]

Is there any reason why one method would be preferable to the other?

Yes, there are many reasons why you would choose one method over the other. It depends on what you want to do with the results. If you only need to display them in a report, then using sub-summaries may be a bit simpler. Also, it's easy to adjust the report to reflect only selected results, since summary fields summarize the found set only. OTOH, the summary values are calculated "on-the-fly" when viewing the report, so using them for further calculations is more difficult.