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]
(
@COURSE_USERS_PK1 INT,
@COURSE_PK1 INT,
@NAME VARCHAR(500)
)
RETURNS INT
AS
BEGIN
DECLARE @Return AS INT
SET @Return = (SELECT
COUNT(mm.PK1) COUNT_DISCUSSION
FROM dbo.MSG_MAIN mm WITH(NOLOCK)
LEFT JOIN dbo.FORUM_MAIN fm WITH(NOLOCK)
ON mm.FORUMMAIN_PK1 = fm.PK1
LEFT JOIN dbo.USERS u WITH(NOLOCK)
ON mm.USERS_PK1 = u.PK1
LEFT JOIN dbo.COURSE_USERS cu WITH(NOLOCK)
ON u.PK1 = cu.USERS_PK1
WHERE cu.PK1 = @COURSE_USERS_PK1
AND cu.CRSMAIN_PK1 = @COURSE_PK1
AND fm.NAME = @Name)
RETURN @Return
CTE usage is a follows:
;WITH cGrades AS
SELECT
gm.PK1 GRADEBOOK_MAIN_PK1,
cu.PK1 COURSE_USERS_PK1,
gm.TITLE TITLE,
gm.DISPLAY_TITLE DISPLAY_TITLE,
gm.POSSIBLE POSSIBLE,
a.GRADE GRADE,
CASE
WHEN a.STATUS = 7 THEN 'GRADED'
ELSE
WHEN gm.SCORE_PROVIDER_HANDLE = 'resource/x-bb-forumlink' THEN
WHEN dbo.GetDiscussionCounts(cu.PK1, cm.PK1, gm.TITLE) >= 1 THEN 'COMPLETE'
ELSE 'NOT COMPLETE'
END
WHEN a.STATUS = 1 THEN 'NOT COMPLETE'
WHEN a.STATUS = 3 THEN 'IN PROGRESS'
WHEN a.STATUS = 6 THEN 'COMPLETE'
END 'STATUS'
FROM dbo.GRADEBOOK_MAIN gm WITH (NOLOCK)
LEFT JOIN dbo.COURSE_MAIN cm WITH (NOLOCK)
ON gm.CRSMAIN_PK1 = cm.PK1
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
AND gg.COURSE_USERS_PK1 = cu.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.
Mike
Mike,
Thank you so much! This is exactly what we needed!Lauren