Mapping of cross-listed courses to course cross-listed into in CD2

[Doug9]

In which table(s) in my school's Canvas Data 2 Postgres database would I find a mapping of cross-listed courses linking SIS ID of each course to the SIS ID of the course cross-listed into?

From the Admin > Settings > Reports menu I can run a Provisioning report and select "X list CSV".  The generated report shows the cross-listed course's SIS ID and SIS ID of the course it's cross-listed into.  I would like to query the same data from CD2.  

Thank you

[KeithSmith_au · Accepted solution]

The question is a little confusing.  Courses are not cross listed - cross listing "moves" a section from one course to another.

If you look at the course_sections table for the section, it contains two canvas course identifiers - course_id is the unique canvas course identifier of the course the section is currently in.  If the section has been cross listed into that course, then the unique canvas course identifier of the course the section was originally created in is found in the nonxlist_course_id field.  (This may not be the most recent course the section was in if it has been cross listed multiple times, but it the course where the section was originally created).

To get what you want (sis_id of the courses, which they may not necessarily have). you need something along the lines of

select   a.sis_source_id as section_sis_id, a.name as section_name, a.id as canvas_section_id, b.sis_source_id as original_course_sis_id, b.id as original_canvas_course_id, c.sis_source_id as current_course_sis_id, c.id as current_canvas_course_id

from course_sections a

inner join courses  b on b.id = a.nonxlist_course_id

inner join courses c on c.id = a.course_id

where a.nonxlist_course_id is not null

[Doug9]

Thank you @KeithSmith_au , your reply answered my question and improved my understanding.

My school pulls in course grades to our in-house SIS via Canvas API, and since we have the course number in our Canvas SIS IDs for both course and section, we can query for enrollments and find each student's grades.

I was asked to consider if we could also allow instructors to cross list sections into a different Canvas course than what a student is signed up for in our in-house system.

At the moment it looks to me to be tricky and it would need a lot thought to implement it.

"... if it has been cross listed multiple times" 😱 I never considered that possibility.

[JamesSekcienski]

Since this is something new you are considering, you may want to pilot the idea with only a few courses/teachers at first. 

At our institution, most of our cross-listed courses are designated as such in our SIS so we handle cross-listing these courses on behalf of the instructors.  For courses to be cross-listed that aren't designated as such in the SIS, a request has to be submitted and approved before students can start accessing a course.  We then cross-list those approved courses on behalf of the instructors.  This is to ensure courses are cross-listed after students have already submitted work.  If students already submitted work and then you cross-list, then that submitted work won't show in the new course it now appears in.  The only way to recover the work that was submitted before is to undo the cross-listing.  In addition, if you are using third party tool integrations for assignment submissions, it is important to understand how the integration works with cross-listing.