In order to tie discussion posts stored in MSG_MAIN to
the appropriate forum and then back to the GRADEBOOK_MAIN table (if possible),
we need to determine how to join FORUM_MAIN to GRADEBOOK_MAIN. We've only been able to identify the actual
forum title and gradebook entry title as the best way to join. However, this is not ideal. Is there any other way to tie discussion
posts back to a gradable activity in the GRADEBOOK_MAIN table?
I've looked at the ERD for discussions in the 9.1
documentation, and I don't see an obvious way to join from discussion-related
tables to the gradebook tables, but we're hoping someone can help identify this
linkage for us.
This join will also us to help determine the STATUS of discussion-related activities in our gradebook. We have a UDF to determine discussion post counts grouped by course and user. The third grouping needs to be by gradebook entry so that we can determine the status of the gradable discussion activity. Simply stated, if at least one discussion post exists for a user for a particular discussion activity, it is considered COMPLETE as long as it does not have a grade yet. If no discussion posts exist for a user for a particular discussion activity, it is considered NOT COMPLETE.
Hopefully I've provided enough details to get an answer! Thank you in advance!
UDF is as follows:
ALTER FUNCTION [dbo].[GetDiscussionCounts]
DECLARE @Return AS INT
SET @Return = (SELECT
LEFT JOIN dbo.FORUM_MAIN fm WITH(NOLOCK)
LEFT JOIN dbo.USERS u WITH(NOLOCK)
LEFT JOIN dbo.COURSE_USERS
ON u.PK1 = cu.USERS_PK1
WHERE cu.PK1 = @COURSE_USERS_PK1
AND fm.NAME = @Name)
CTE usage is a follows:
;WITH cGrades AS
WHEN a.STATUS = 7 THEN 'GRADED'
WHEN dbo.GetDiscussionCounts(cu.PK1, cm.PK1, gm.TITLE) >= 1 THEN 'COMPLETE'
ELSE 'NOT COMPLETE'
WHEN a.STATUS = 1 THEN 'NOT COMPLETE'
WHEN a.STATUS = 3 THEN 'IN PROGRESS'
WHEN a.STATUS = 6 THEN 'COMPLETE'
gm WITH (NOLOCK)
LEFT JOIN dbo.COURSE_MAIN cm WITH (NOLOCK)
LEFT JOIN dbo.COURSE_USERS cu WITH (NOLOCK)
ON cm.PK1 = cu.CRSMAIN_PK1
LEFT JOIN dbo.GRADEBOOK_GRADE gg WITH (NOLOCK)
ON gm.PK1 = gg.GRADEBOOK_MAIN_PK1
LEFT JOIN dbo.ATTEMPT a WITH (NOLOCK)
ON gg.PK1 = a.GRADEBOOK_GRADE_PK1
When forums are set to be gradeable, they are linked to the grade center column using the linkrefid via the forum_regristry table, i.e. gradebook_main.linkrefid = forum_registry.registry_value, and forum_registry.forummain_pk1 = forum_main.pk1.
Thank you so much! This is exactly what we needed!Lauren
© Blackboard, Inc.