Cleanup Enrollment Data 2023 Cycle

Owned by Helen Williams, created
Last updated: Jan 25, 2024
4 min read

Slate Instance

 

Requestor

 

TTS Staff

 

Date

 

Status

 

Summary Description

 

Entity Creation

 

Data Cleanup

 

PTCAS 2022-2023 Complete Apps

 

PTCAS 2022 - 2023 Submitted but Not Reviewed

 

Applications in PTCAS without Slate application records:

DPT - 45

PHX - 19

 

 

  1. Run export from Slate of all application records for DPT Summer 2023 entry term w/CAS ID and Slate person ID and app ID.

  2. Run export from PTCAS of all application records for DPT 2023 w/CAS ID and local status.

  3. Compare the two lists to identify the records in #2 not present in #1 to create list of Submitted but Incomplete DPT 2023 (using XLOOKUP to compare CAS IDs)

  4. Create list in PTCAS of the local statuses containing the records for #3. Use the existing Export in PTCAS for complete applications to export a spreadsheet of the application data using the list. This gives us a file in a format we can import to Slate using a copy of the existing source format, eliminating the need to remap and build a new source format and importing the fields that would’ve been brought over had these apps been imported in 2023.

    1. Some records in these local statuses were already in Slate (likely because it wasn’t discovered until after they were complete/imported that they were missing items or otherwise ineligible for review) so needed to delete rows that were not present in the records for #3.

    2. Added a column for CAS Application ID and used the established format (CAS_ID-program_ID) and TEXTJOIN to generate an ID, rather than using field fusion like is done normally. BOS is 299992, PHX is 299993.

    3. For the application verified and application submitted date columns performed a search to remove the text “ at *” so that excel and Slate will recognize these columns as a date.

    4. Added 4 new columns with the status change dates to add in an ID for import to the entity for reporting data.

  5. In Test, made a copy of the source format PTCAS Complete Application and named the copy WebAdMIT Application Data. Uploaded the CSV file created in #4, then made these changes in remap:

    1. CAS ID (WebAdMIT) - kept this as the unique for merging person-scoped ID (see note). Only one record in the import didn’t already have this value, so for 241808805 in Slate I added the CAS ID to the old field in this record.

    2. Mapped CAS_app_ID to App: Other - CAS/Liaison Application ID

    3. Remove the field fusion for old app ID

    4. Remove static mappings:

      1. CAS status

      2. Interaction codes

      3. generate PIN

    5. Updated the mappings for the 4 status change dates to add in app: submitted date, app:complete date, plus the 4 entity values, and the IDs

  6. Ran the import to generate application records. Checked for errors:

    1. Created this query to look at the applications created by the source imports and got 64, which is the total (19 + 45) https://tup.test.technolutions.net/manage/query/query?id=f2ba6e65-f79b-4ded-89cd-5ae091c34fb6

    2. Checked records that were in the DPT inquiry populations to make sure they weren’t newly added.

    3. Ran records through rules preview to see if anything is being added or changed.

 

 

Fields for CAS ID

During the 2022-2023 cycle we used CAS ID (WebAdmit) sys:CAS_ID to store the CAS ID (the unique identifier across the CAS/Liaison systems) for applicants. When we implemented the API for 2023 - 2024, the pre-configurations provided by Slate created a new field called CAS/Liaison Person ID sys:cas_person_id to use for this value. Data from the old field needs to be moved to the new one, but since the applications I’m working with for this process mapped to the old field, I verified that all records had a value in the old field and used that in the source format mapping to ensure that no records were missed or duplicated.

 

Production import - test file

7704496518 - normal BOS

2329669391 - normal PHX
6248424646 - Has Su24 apps for BOS and SEA
2613218257 - has both a BOS and PHX app for Su23
3108159553 - DPT inquiry but no CAS ID

 

2329669391 Richelle Stevenson - normal PHX, did have a submitted BOS application but CAS/Liaison app id wasn’t overwritten, reporting data went into the correct entity/app. In PHX inquiry pop, but isn’t there in production.

NEED TO CHANGE THIS RULE IN PROD TO ENSURE NOT ADDED TO INQUIRY POP

image-20240124-202831.png

 

Change the rule to also exclude applications in the 2023 round (regardless of decision). Could also exclude anyone whose created date for program of interest is earlier than 6/1/2023? Seems riskier.

 

6248424646 Chantel Nishimura - has Su24 apps for BOS and SEA. CAS/Liaison app ids not overwritten for the other two apps, Su23 app created with correct IDs. Reporting data went to the correct place. Is being added to inquiry population, but that is OK because she has active apps this cycle.

In the source format for the webadmit data I had not mapped the entity correctly for VE, fixed in production but double check

 

OK to import DPT program dates, double check source format is properly setup. Can also do O-MPH. Need to run rest of MPH and the MBS out of production and then import through source format.

OK to do test import of 6 apps for dpt submitted but incomplete files as “soft launch” MAKE SURE TO “TURN OFF” POPULATION RULE BEFORE YOU DO THIS. Then wait 1 day to see if any emails are sent or anything else that would be a problem (app status rules won’t run so they don’t have statuses, but not sure about “created” email sending? Looks like probably not…). Check with Dmitri on feed to be sure nothing went through.

After dates have been imported, build queries to look for one date earlier than another (when it shouldn’t be) etc. Check MPH against SOPHAS records and notes from last cycle OIR report.

Consider additional app reporting fields? Notes? Flags for admitted, enrolled, etc.?

What about PA program? Maybe ask Ariana. Could be nice to have “submitted” apps in Slate for testing for the new cycle…