I have created a gradebook using several Canvas tables with the majority of the data coming from assignment_dim, assignment_group_dim, submission_dim, course_dim, and course_score_fact.
What I am hoping to get help with is identifying assignments that do not count towards the final grade. Here are two scenarios that seem to be an issue:
- Instructor creates a placeholder assignment called "Final Grade" and then enters in the total points possible in the class. It looks like they are doing this so that a student can visualize the points in the class.
- I have already tried filtering out assignments that are not worth points and have indicated the assignment must be in a published state; however, there are many examples of the placeholder assignment being worth points and is also placed in a published status, so this does not work.
- I also tried using the grading_type column which has values of gpa_scale, pass_fail, percent, points, not_graded, letter_grade - unfortunately, I cannot use this field to correctly filter out assignments that do not count towards the grade. For example, in the scenario where the instructor makes a "Final Grade" placeholder, let's say the enter the grading type as letter_grade. If I filter out records that have a grading_type of letter_grade, this will also filter out courses where the instructor uses a letter_grade for each assignment.
- Instructor drops assignments as part of how they structure the course - most of the time it is drop the lowest assignment/quiz score or something like that.
- I assume I can write some logic that looks at the assignment_group to see if drop_lowest is true - if so, I can remove the lowest assignment for each student; however, it seems like there should be an easier approach to this issue as well as the one mentioned above.
For both scenarios presented above, when I go to the individual gradebook for a student I can see the following:
Scenario 1 (Placeholder Assignment)
Scenario 2 (Dropping Assignment)
It seems there should be a "counts_towards_final_grade" field within both the assignment_dim and submission_dim tables. The assignment_dim could have a counts_towards_final_grade (1 or 0) for the placeholder assignments where the instructor wants to publish the assignment and make it worth points. The submission_dim could have the same field but I would assume that wouldn't populate until after all assignments have been submitted.
If a field like this already exists, could someone please point me in the right direction?