We use a third party application called StarFish. This application pulls the grade from the column that's been set as the External Grade. I need to pull a report from OpenDB that shows me which column in each course has been set to the External Grade column. I cannot find the table or the filed that shows which column is set as the External Grade column. Can anyone tell me which table and which field contains that information?
Thanks,
Lee McMinn
Hi Lee -
The grade center column that is set as external grade is stored in the public_item_pk1 field in the gradebook_settings table.
The SQL query below should give you the CourseID, CourseName, and Grade Center Column Title for each column set as external grade. It also has in the WHERE clause to specify a list for a specific term. Make sure to replace Name of Term with your term name and make sure to keep the single quotes around the term name. You can comment it out if you want all terms.
SELECT
cm.course_id,
cm.course_name,
gm.title
FROM
[BBLEARN].[dbo].[gradebook_main] gm
LEFT JOIN [BBLEARN].[dbo].[gradebook_settings] gs
ON gm.pk1=gs.public_item_pk1
LEFT JOIN [BBLEARN].[dbo].[course_main] cm
ON gm.crsmain_pk1=cm.pk1
LEFT JOIN [BBLEARN].[dbo].[course_term] ct
ON cm.pk1=ct.crsmain_pk1
LEFT JOIN [BBLEARN].[dbo].[term] t
ON ct.term_pk1=t.pk1
WHERE
gs.public_item_pk1 <> ''
AND t.name = 'Name of Term'