Capping Scores Automatically for a Scavenger Hunt

[Kent_UK]

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!

[Suvrutt_Gurjar]

I think in general what you are looking for would be possible with referencing between tables.

You have mentioned some column details and some expression(s). However, if you could mention the four tables' referencing relationship with relevant column details involved in expressions and results you are wanting to achieve in which column in which table, I think the community could help you better.

The overview of relevant columns could be given in the following format as well. ( The following is just an example.)

Order details table references Orders and Products tables.

Order table has Order Details table as a child table.

and so on........

[Kent_UK]

If I understand your suggestion, it would help if I provided information in the following format showing that the Submissions table holds references to both Task and Player tables and through Player the associated Team. 

Players refers to the associated TeamSubmissions table references Tasks and PlayersTasks information including max itemsTeam information builds a score based on associated submissions

[RedVox · Accepted solution]

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.

[Kent_UK]

Here is how I interpreted your suggestion:

The new scores table with references to team and taskExpression to build a list of approved submissions by this team for this task.Working. One of the three approved solutions for task 4 scores 100 while 3 of the 5 approved submissions for task 6 score 50 each for 150.Expression to calculate the total score for this task by this team.

Based on these outcomes, I'm increasingly confident that this solution works as intended.  However, I'm interested in any feedback you or others might have regarding my implementation of this solution.  In my specific application, we are only talking 100's of records, so hopefully any inefficiencies are tolerable.

[RedVox]

I think it could be a bit more elegant.  If you think of the  Approved Submissions Column as more of a Count of Point-worthy Submissions column, you could wrap your current formula in a Count() function.  Further, if you wrap the Count() result in a Min() function where the next argument is [related task].[points_per_item], the column will show the number of completed tasks that are earning points.   Then, the Score column is simply [Approved_Submissions] * [related_task].[points_per_item]

The reason I like this idea of adding the scores table is that scores summarized by task and team are probably a very primary point of focus for your users.  Also, when looking at an individual team, it will be nice to see the child score records summarizing points earned for each task.  

[Kent_UK]

Thank you for taking the time to help me build my knowledge.  Yes, performing a count of qualifying submissions and then a minimum between that and the max items per task results in an elegant determination of the number of items for which a team will receive point credit.  While I was working through the expressions, I left the approved submissions in their own column so that I could verify the correct items were present.  Now, I'm going to do as you suggest and reduce them to a count capped by a min statement.  Thanks.

[Marc_Dillon]

You could add a condition into your submission[score] calculation. I assume the existing expression is simply:
[task].[points_per_item]

You can make it:
IF(
  COUNT( FILTER(
    submission ,
    AND(
      [team]=[_THISROW].[team] ,
      [task]=[_THISROW].[task] ,
      [_Rownumber]<[_THISROW].[_Rownumber]
    )
    ) ) < [task].[max_items] ,
  [task].[points_per_item] ,
  0
)

[Kent_UK]

Thanks so much -- I'm not sure why I do not need to add [status] = "approved" to the AND clause so that the filter only picks up rows that have been approved; however, when I discovered that when I do add that condition, suddenly items with a status of "rejected" also begin to be awarded points.  That doesn't seem to be an issue if I go with the exact solution suggested although I'm at a loss to explain why.  

[Kent_UK]

No, I went back and did further testing and it the app IS awarding points for all submissions regardless of status.  It does cap the points at the maximum, which was my original question, however every entry receives a score regardless of status.  I've attempted to fix that by amending the filter with an additional AND check but that doesn't seem to make any difference.

attempted to filter to accept only approved tasksBoth of these entries are rejected and should have 0 points. However, the expression generates a value of 100 for each.

Obviously, I'm missing something.  Also, as I've already accepted a solution, should I open a new conversation thread?

[Marc_Dillon]

Add another and, to check both the count, and the current record's status, before assigning the points.

[Kent_UK]

Based on your suggestion, I modified the expression as shown below.

An additional AND to ensure only approved status are included in the total

This appeared to work in some conditions but when one or more submissions were rejected before several were being accepted, the expression did not consistently return the expected value.  For example, in the screenshot below, because the row of the rejected expression occurs before the accepted submission, the cap prevents the second (approved) expression from scoring.

The second submission should have been awarded points but did not receive them.

This well may be that I misinterpreted your suggestion and should have written my expression differently.  If so, I'd appreciate any feedback or insight.  However, after a good night's sleep I thought I'd try the suggestion from @RedVox and it is working.  Thank you, again, for your proposed solution as it has helped me understand better some of AppSheets expressions and the use of _THISROW within a FILTER. 

[Marc_Dillon]

You'd still need the [status]='approved' in the innermost AND() as well.

[Kent_UK]

Thanks for the update.  While I've already accepted a different solution, I can see where my missing this conditional qualifier in the inner AND led to my errors.  Thanks for confirming that I would need an "approved" check on both the inner and outer ANDs for this approach to work.  Cheers!