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'
I'm not sure this is working. When I run this, after a few modification (it doesn't give any results as is), I get all the columns in a course. It is not limited to the column that is set as the external grade.
SELECT cm.course_id,cm.course_name,gm.title
FROM gradebook_main gm
LEFT JOIN gradebook_settings gs
ON gm.pk1=gs.public_item_pk1
LEFT JOIN course_main cm
ON gm.crsmain_pk1=cm.pk1
WHERE gm.title <> 'Total'
and cm.course_id like '2016FA%';
This is to show the title of the column that is set as the external grade column from the courses that start with the course_id 2016FA. We do not use terms.
When I run the query above with the gm.title = 'Total' , that doesn't work either.
I need this query to show me the name of the column that is set as the External Grade column (i.e. the one with the green check).
Lee McMinn
Hi Lee -
Try including this statement in the WHERE clause:
gs.public_item_pk1 <> ''
Basically this is asking to return any records that have a value in the public_item_pk1 field that are not blank. Only grade center columns marked as external grade will have a value in the public_item_pk1 field in the gradebook_settings table. Any other grade center columns will have a blank or NULL value for the public_item_pk1 field in the gradebook_settings table.
If you don't include this statement, you will get all the columns like you described. This was in my original query but you may have missed it.
Also I think you could exclude the gm.title <> 'Total' statement in the WHERE clause now if you include the statement above.
Try this and let me know.
Thanks!
Casey
Should the join from course_main be against gradebook_settings.crsmain_pk1 and not gradebook_main.crsmain_pk1?
SELECT cm.course_id,cm.course_name,gm.title
FROM gradebook_main gm
LEFT JOIN gradebook_settings gs
ON gm.pk1=gs.public_item_pk1
LEFT JOIN course_main cm
ON gs.crsmain_pk1=cm.pk1
WHERE cm.course_id like '2016FA%';
I changed my join to be against gradebook_settings.crsmain_pk1 as you suggested and I received the same results as I did when I joined against gradebook_main.crsmain_pk1 so I don't think that makes a difference.
But you definitely have to have gs.public_item_pk1 <> '' statement in the WHERE clause or you will get all columns and not just the ones set as external grade.
Starfish should be pulling all course grade center columns, and not just the external grade column...
Interesting...maybe it's the order of the tables in the query, as I just drafted/tested this against my system, and I'm only getting a single result per course:
SELECT [cm].[course_id], [cm].[course_name], [gm].[title]
FROM [dbo].[gradebook_settings] gs (NOLOCK)
JOIN [dbo].[gradebook_main] gm (NOLOCK) ON gm.[pk1] = [gs].[public_item_pk1]
JOIN [dbo].[course_main] cm (NOLOCK) ON cm.[pk1] = [gs].[crsmain_pk1]
WHERE [cm].[course_id] LIKE 'FAL16%'
ORDER BY [cm].course_id
And I tested this against both Oct 2014 CU4 and Q2 2016 CU3, with the same results.
Help
Privacy
Behind the Blackboard
Blackboard.com
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'