Solved: Capping Scores Automatically for a Scavenger Hunt - Page 2

Kent_UK · 08-12-2023 04:30 AM

I am organizing a Scavenger Hunt. Teams of players collect image/video artifacts of tasks that I have specified and submit those artifacts for points. App data is organized across four tables (tasks, players, teams, and submissions). Each row in the submission table holds a reference to the player who submitted the artifact and the task against which it is being submitted. Each submission has a status (submitted, approved, rejected) that determines whether the entry should be awarded any points for the team who submitted the artifact. The number of points to be awarded for each entry and the maximum number of entries for each task are maintained in the task table.

taskssubmissions

What is working: The submissions table has a virtual column, "score", that calculates tasks[points_per_item] for submissions with a status of "approved" or zero for any other status. The "teams" table has a virtual column, "score", that sums the team scores of all approved submissions by team.

adds all approved submissions

NOT WORKING (my question): Team scores for any given task should be capped by the tasks[max_items] constraint so that submissions beyond the maximum number (while allowed) do not contribute to the team score. In the example data provided, player_1 and player_2 belong to the same team -- each of them has submitted an item for task_2 which should cap the points for the task at a single item. What is happening now is that (once approved) BOTH submissions are included in the score for the team. How can I build an expression that considers the maximum number of entries for a task?

Also, the app is set up for two different modes of interaction, one where approvals are manually performed by an event manager and a second mode where all incoming submissions are automatically approved (in case the manager gets swamped with entries).

Thank you, in advance, for any insight into how I can achieve this functionality. I'm new to AppSheet so I'm looking forward to any thoughts regarding expressions that would work or suggestions for managing data to avoid performance penalties. Cheers!

RedVox

You could create a table called team_task_summary that creates one new row for each unique combination of team and task. The table would have related team and related task fields and a calculated field (let's call it [team_task_points]) that determines the team's points for a given task:

Min(Count(Select(tasks[points_per_item], And([task] = [related_task], [player].[team] = [related_team]))) * [related_task].[points_per_item], [related_task].[points_per_item] * [related_task].[max_items])

To create these new table rows, you would need a bot that fires with each new submission where there is not already a row with the same team and task combination. Your team score calculation could then be a sum of the [team_task_points] for the team.

Hope this helps.

View solution in original post