I love Grist and have convinced my school to host a self-managed version on our intranet! I teach at a school and I’m trying to use Grist to replace our existing LMS. I feel super close, but there are a few things keeping it from taking over. One of the issues is that students can submit assignments multiple times and I cannot seem to structure the data to support a few views that students and teachers would like.
Here’s a simplified schema:
Students
- id: int
- Name: str
Assignments
- Title
- Due Date
Submissions
- FK: Student
- FK: Assignment
- Attachment
- Submission Date
- Grade
Students can submit assignment multiple times. Their final grade for any given assignment is just the grade of their latest submission for that assignment.
View 1: Student grades for every assignment, including missing assignments
I would like students to be able to have a view of all assignments, how many submissions they have for the assignments, and their grade for that assignment. Also, crucially, the system needs to display assignments for which they have no submissions, and to indicate that they are missing the assignment.
Right now I have a somewhat complicated python formula printing out a “report cell” for each student but I would prefer to implement this using relations rather than brute calculation.
I already know how to use access controls to limit students to their own grades… I just can’t figure out how to show something like:
Grades View/Table (Selected by Student)
| Assignment | Grade | Status |
| ---------- | ----- | ------ |
| HW1 | 100 | |
| Quiz 1 | 98 | |
| HW 2 | 0 | Missing! |
| HW 3 | 0 | Missing! |
| HW 2 | 0 | Due 2/4 |
I would normally use a FULL JOIN, or use a RIGHT JOIN if I already knew what student I was querying. Summary tables won’t include assignments without submissions in my schema.
Maybe I could create a Grades table with a reference to every student x every assignment, but that would require me to add a row for every student every time I add an assignment. And if I delete an assignment I would need to go through the table and remove all references to that assignment. I would like to avoid this error-prone step and have the relationships be declarative if possible.
View 2: When grading, see all related submissions
When I’m grading submissions, sometimes students have already made multiple submissions before I get around to grading any of them. I would like to only grade the most recent submission, but also be able to look at a submission history, filtered by both student and assignment. Currently my workspace is
Assignment Table
| Title | Count of Ungraded |
| ----- | ----------------- |
| HW1 | 0 |
| HW2 | 15 |
| Quiz 1| 20 |
Submissions Table (selected by Assignment)
| Submitted Time | Student | Attachment | Grade |
| -------------- | ------- | ---------- | ----- |
| 3 Oct 2043 | SMITH | <pdf> | 0 |
What I would like to have is a third widget that shows submissions selected by both Assignment AND Student. I would use this to look at their submission history so when students resubmit work I can refer back to previous submissions and grades. Students would also use such a view when selecting rows in my View 1 example above. They would select an assignment and it would show them all their submissions for that assignment.
Are either of these possible? Did I overlook something? Thank you in advanced for reading my long post!