TUP Prerequisite Course Data missing

Issue Description:

DPT-PHX faculty report that when viewing the prerequisite course data in the reader the column containing the year the course was taken is blank and has no data.

Troubleshooting/Research

Started with a CJ on the Prerequisite Course base (this is a custom entity unique to TUP). Confirmed that fields in this entity are getting populated with data, but year is missing on all. So is College Name.

Reviewed the Source Format (WebAdMIT API - PTCAS PHX Prerequisite Courses) used to import this data, and found that the mapping for these two fields was set to “Prerequisite Courses” not “Prerequisite Course (Application).” This is why data isn’t saving to these rows when imported.

Open

Open

Moved to test to determine best way to fix this:

• Updated source format mapping to change the destination to the correct version of the entity. Tried a retroactive refresh through the source format, but this ended up creating a new set of rows and not updating the existing rows (not surprising, but hope springs eternal):

• Possible solution is to delete all prerequisite course rows for DPT-PHX applications in 2024 round and then retro-refresh the source format to add them back. This is probably fine, but if any manual changes were made to the prerequisite courses after import, this would delete/overwrite them.

• Hypothesis: If we could set a unique identifier for each row, a retro refresh could update instead of create new rows. Realized that the prereq_course_code (prefix in PTCAS) field is mostly unique, so decided to try it out:

  • In Field Editor, change the settings for Prerequisite Courses > Course Code so that it is “unique for merging”

  • Refresh field cache

  • Refresh configurable joins library

  • Return to Source Format and check the mapping for Course Code; should now be “unique

  • Run a retroactive refresh in the source format

  • Looking at records after doing this, a good portion did update correctly (yay!) but there were a few where the course code was used more than once (which updated one but not both courses). Decided to do it for all records in test, then ran a query to see how many applicants had prerequisite courses without any year data after that, and only got 8. Reviewing those, it was a mix of duplicate prerequisite courses (probably accidentally imported twice) and the issue with some courses entered with the same course code by the student. Also reviewed records that were no longer missing years against their production environment records and confirmed the only changes to the rows were the update date, adding the year, and adding the college (no additional rows created).

  • This is a small enough number to manually fix, so decided to go this route in production environment, rather than fully wiping course data.

Resolution Steps:

1. Use Database > Field Editor to edit “Course Code” field in Prerequisite Course entity and change so it is “unique for merging”

2. Correct the field mapping in the WebAdMIT API - PTCAS PHX Prerequisite Courses source format so College Name and Course Year are mapped to the correct destination (Prerequisite Courses (Application)).

3. Run query to pull applicants with total prerequisite course rows and total missing year and college name: https://tusmgp.admissions.tufts.edu/manage/query/query?id=55072f2c-c698-4c3b-8095-cff00d5af80d

4. Run retroactive refresh in source format… next time, make sure to check off Custom: Skip Import, because I did not do that this time, any courses that were “not approved” by the program got added, so I had to do a lot of manual deleting of new entity rows created when I did the refresh (d’oh).