Technical Details

Current Release Version: 2023.2.2

Release Date: 03 Oct 2023

Application Location: Google Drive / AIS / Applications

Security Roles: Slate Operations (for Query Export)

Notes

The following are some notes and assumptions to consider during the development cycle.

Summer Dual Applications (Campus & Online)

Students are required to have two applications in this scenario. The sheet combines the two rows into a single comprehensive row that can be processed through the same business logic for validation.

Requested Features

Current Cycle

Release 2024.1.0 [February 2024 - Summer Release]

Features to Complete

Validation testing when applications get admitted (January) and registrations open (February)

Features to Consider

Unified Query
- Repurpose the CU Employee Discount column to register "Yes" for "(P)artner", "(A)lumni" and "(E)mployee" values (currently just E)
Many of the cases that are detected during validation in the sheet could actually be handled within Slate, and then returned as a filter or export value
- This would allow the use of Slate automations to send remediation emails automatically to students
- For example, when a student registers for more than one course in a session that allows only one, the filter would return the error state for the student registration, and a regular mailing could be configured to send a request to the student to drop one of the courses by a certain date, or to contact for help
- Similarly, billing inconsistencies could be detected automatically, and triggered emails could go to the admissions team to remediate, rather than watching the sheet for the cases
Restore the number formatting for the Slate Ref ID when the Slate table is reloaded from CSV
Add the UUID to the query and then use that to link directly to the Slate account from any row throughout the sheet for easier review of details
Add method to re-sync the updated values from the Slate data tab with the Manual Registration tab
- Launch the code during the data upload process to make it automatic
- If it takes too long, include a UI button to trigger the sync instead
Tracker Google sheet
- Add tab showing registered students where the REG-GRP needs to be removed
Think about how we can catch cases of applications that are being excluded from the query based on incorrect data (e.g., wrong or missing application/enrollment terms, etc.)

Features Ready for Release

Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Summer 2024 term using the previous term's query
- Inactivate the Session (AYI) column and activate the Session (Summer) column
  - Update the Session (Summer) column to use new hsp_session_date prompt values instead of the term_of_entry prompt (new app process)
- Add 'Reports - Pre-College Operations' role to the run permissions list for new query
Tracker Google sheet
- Create a new version of the sheet for the Summer 2024 term using the previous term's tracker
  - Clear out old data from the Slate tab (and others as needed)
- Flip the business logic in the Control Panel from AYI to Summer
- Confirm new sheet is shared with current Operations team
- Unhide Summer billing tabs: Billing Check, Billing to Post, and Completed Billing

Previous Cycles

Release 2023.2.3 [March 2024 - Summer Release]

Tracker Google sheet
- Create a new version of the sheet for the Summer 2024 term
  - Clear out old data and move to the shared application folder
- Confirm sheet is shared with current Operations team
- Added Dashboard enhancements - Add Program code to full registration and financial panel
- Added a count for dual degree students and unregistered students
- Added billing cycle dates to billing cycle comms panel
Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Spring 2024 term and AYW specific programs
- Updated the Billing Code field to use new hsp_session_date prompt values instead of the term_of_entry prompt (new app process)
- Updated the Session (Summer) column to use new hsp_session_date prompt values instead of the term_of_entry prompt (new app process)
- Added SIS Application Program (SHGENx) to the end of the query
- Add 'Reports - Pre-College Operations' role to the run permissions list
- Change CU Employee Discount field name to Discount
- Change filters
  - Enrollment Term instead of Application Term
  - Enrollment Response Decision = YES

Release 2023.2.2 [October 2023 - Spring Release]

Tracker Google sheet
- Create a new version of the sheet for the Spring 2024 term
  - Clear out old data and move to the shared application folder
- Confirm sheet is shared with current Operations team
Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Spring 2024 term and AYW specific programs
- Add 'Reports - Pre-College Operations' role to the run permissions list
- Change CU Employee Discount field name to Discount
- Change filters
  - Enrollment Term instead of Application Term
  - Enrollment Response Decision = YES

Release 2023.2.1 [August 2023]

Dashboard
- Added billing feed details for Fall comms cycle

Release 2023.2.0 [July 2023 - Fall Release]

Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Fall 2023 term and AYI specific programs
- Inactivate the Session (Summer) column and activate the Session (AYI) column
- Add 'Reports - Pre-College Operations' role to the run permissions list
Tracker Google sheet
- Create a new version of the sheet for the Fall 2023 term
  - Clear out old data and move to the shared application folder
- Flip the business logic in the Control Panel from AYI to Summer
- Confirm sheet is shared with current Operations team

Release 2023.1.0 [February 2023 - Summer Release]

Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Summer 2023 term
- Inactivate the Session (AYI) column and activate the Session (Summer) column
- Add 'Reports - Pre-College Operations' role to the run permissions list
- NEW: Replace the Admit Term with Submit Term (per request)
Tracker Google sheet
- Create a new version of the sheet for the Summer 2023 term
  - Clear out old data and move to the shared application folder
- Flip the business logic in the Control Panel from AYI to Summer
- Confirm sheet is shared with current Operations team
- Unhide Summer billing tabs: Billing Check, Billing to Post, and Completed Billing
- NEW: Add Submit Date and Session (Summer) to the REG-GRP tab
- NEW: Add Billing Cycle indicator to show which set of emails are being sent

Release 2022.3.0 [October 2022- Spring Release]

Tracker Google sheet
- Create a new version of the sheet for the Spring 2023 term
  - Clear out old data and move to the shared application folder
- Update the prerequisite course list in the Control Panel
- Remove the Registration Calls tab and underlying functionality since a single tab appears to be adequate for the workflow
- Disable the Upload and Validate buttons until the validation process has completed to keep it from being interrupted once started
Unified Query
- Create a version of the query with the same structure, but new parameters to pull for the Spring 2023 term and AYI specific programs
Adjust the synched data to accommodate the overlapping registration windows between Summer and Fall

Release 2022.2.0 [July 2022- Fall Release]

Add business logic to validation procedure to support checking for prerequisite course completion
Disable the Upload and Validate buttons until the validation process has completed to keep it from being interrupted once started
Adjust the synched data to accommodate the overlapping registration windows between Summer and Fall
Tracker Google sheet
- Remove the Registration Calls tab and underlying functionality since a single tab appears to be adequate for the workflow

Release 2022.1.4f [July 2022]

Update the Billing Check sheet to include students that have withdrawn from the program so any billing courses on these student accounts can be removed through the same process
Slate Query: Remove the filter to exclude students that responded 'No' but have paid or waived deposits to allow for tracking forfeitures in the same sheet
- Sheet: Update filtered views to exclude these cases from normal operations
- Sheet: Update dashboard calculations to remove the declines
- Sheet: Add a filtered view to show all forfeitures

Release 2022.1.4e [June 2022]

Added session column to the Immunization Holds tab

Release 2022.1.4d [June 2022]

Updated business logic to exclude WRIT0110 course from validation
- This course is free to all HSP students, and does not follow any of the session requirements
Added fees to the SIS query pulling into Slate and synched to include all records
Added logic to drop fees into a new column in the Slate data sheet
Refinements required after live data is processed and available from SIS
- Confirm discount functionality with live data
Build feed export for discounts that conforms to the required format for the SIS admin team
- Should use the same file format as deposits, but the payment/charge codes will be different
Changed the Deposits to Post tab to only include cases where tuition has been assessed
- This will ensure that we do not send deposits for students that are not actually billed for courses, and decrease the cases where we need to reverse deposits for withdrawn students.

Release 2022.1.4c [June 2022]

Google Sheet
- Change the conditional formatting on the Manual sheet to light green for successfully completed registrations, and add a new light gray format for cases where the student withdrew and removed their registrations.

Release 2022.1.4b [May 2022]

Google Sheet
- Added Billing Messages tab for sending bill reminders to students and parents
BUG: Fixed inconsistent BATCH-ID values for deposit feed files

Release 2022.1.4a [May 2022]

Performance Refactor
- Use Sheets API to write CSV data during import to speed up the loading process
- Remove unnecessary columns from all sheets
- Remove padded rows for all sheets

Release 2022.1.4 [May 2022]

Add a function to export the DROP version of the billing feed to clear out any REGI courses for students that show up on the Billing Check sheet
- Need to confirm file format with Sheila before proceeding
- Include a separate button on the Finance tab, and a new field and link placeholder for the file created

Release 2022.1.3a [April 2022]

Unified Query
- Use Enrollment Term for the Billing Code to check (REGI)
- Update the course counter to look only at the registered courses, and not include the billing courses

Release 2022.1.3 [April 2022]

Add a new Finance UI panel for creating and exporting the data files to be sent to the SIS team
Update the stats region to show the date of the latest feed file build for each type
- Link to the file that was generated for quick access
Build feed export for billing that conforms to the required format for the SIS admin team
- Store in a .TXT file in the same folder as the current sheet, with a date stamp in the name
- Requires a fixed-length text file format for SIS import
  - Requires the section code for the REGI course; add to the SIS sync SQL procedure to make available in the underlying data
    - Add the section code to the Slate query for comparison
  - Add the Year and Code to the control panel for retrieval and insertion in the file
Build feed export for deposits that conforms to the required format for the SIS admin team
- Store in a .TXT file in the same folder as the current sheet, with a date stamp in the name
- Requires a fixed-length text file format for SIS import
- Requires the deposit received date in the Slate query output for the SIS output file
  - Use this date to format the header fields and the payment received date in the output

Release 2022.1.2a [March 2022]

Combine the deposit amounts for dual applications into the single row in the Slate data tab

Release 2022.1.2 [March 2022]

Add a special exception case for the Astronomy course that has two registered classes in SIS
- ASTO0100 & ASTO0102
- Ensure these courses do not count as more than one in the counter functions
Switch the Session column in the Slate query to pull from Enrollment Term instead of Application Term since that is where sessions changes are being recorded in Slate
Update the REG-GRP sheet to only show missing REG-GRP students that have completed their Enrollment Forms
- Change the page description to reflect the new logic

Release 2022.1.1 [March 2022 - Billing, Deposits, Discounts]

Add a counter to each of the lookup sheets to make it easy to see how many records exist for each criteria
Billing Validation
- Business logic to tie REGI codes to the student's program(s) and then to check against the actual registrations from SIS
- Create a sheet that displays the records that need to be sent to the registrar to apply the correct REGI codes based on the student's registrations
  - All rows that have complete and valid registrations but have no REGI courses posted
- Create a sheet to catch cases where the posted REGI code does not align with what is expected based on the student's current status
  - This will highlight cases where the student changed something after a posting was sent
- Create an error report for all cases where the posted REGI codes do not align with the sessions
Deposit Validation
- Function to update "Deposit Posted" column in sheet with "Yes" when the SIS data reflects a post with a matching dollar amount
- Create a sheet that displays the records that are pending a batch upload
  - Deposit Amount > 0 and Deposit Posted = NULL
- Create a query sheet showing all of the successfully processed deposits
  - Deposit Added to SIS = Yes and Deposit Posted = Yes
- Create an error report for all cases where posted deposits do not match what was collected
Employee Discount Validation
- Update "Discount Posted" column in sheet with the amount that was posted (negative)
- Create a sheet that displays the records that are pending a batch upload
  - CU Employee Discount = Yes and Discount Posted = No
- Create a query sheet showing all of the successfully processed discounts
  - CU Employee Discount = Yes and Discount Posted = Yes
- Create an error report for all cases where posted deposits do not match what was collected
Unified Query
- Add field showing when the CU Dependent Decision Status = YES
SIS Synchronization
- Extend the JSON for the SIS Courses field to included additional traits in the data structure
  - Add billing_courses (REGI), deposits (S5DP+), employee discounts (SHSE), and tuition charged (TUIT)

Release 2022.1.0 [February 2022 - Summer Release]

Tracker Google Sheet

Create a new version of the sheet for the Summer 2022 term
- Clear out old data and move to the shared application folder
Flip the business logic in the Control Panel from AYI to Summer
Summer Only: Replace course checking logic with session only
Add an indicator to rows that represent dual application cases (Application Case column)
- Include a counter on the dashboard page showing the total dual application cases
Add a tab that filters cases based on the following:
- Missing REG-GRP
- Missing Enrollment Forms
- Immunization Holds

Unified Query
- Add the new boolean pre-enrollment form column
- Add the new registration holds field from the SIS sync
- Add a boolean Immunization Hold column
- Hide the course columns and update the underlying code to accommodate the change
  - Keeping the columns and business logic in case the program decides to revert to required courses for AYI
- Create a version of the query with the new structure required for Summer 2022, using the new round key as the filtering parameter
  - Add the two new subject area fields to the output
  - Update the session calculation to use the new application term values to determine the valid sessions
Slate Data Configuration

Coordinate the development of a single boolean field that is updated by a Slate rule when all pre-enrollment forms are completed (Sandra dependency)

SIS Synchronization
- Include any registration HOLDS on the student’s account and sync back to field in Slate

Release 2021.3.0 [October 2021 - Spring Release]

Tracker Google sheet

Create a new version of the sheet for the Spring 2022 term
- Clear out old data and move to the shared application folder
Add a tab to show cases where students have not started the registration process

Unified Query

Create a version of the query with the same structure, but new parameters to pull for the Spring 2022 term and AYI specific programs

Slate Data Configuration

Ensure that all of the courses have the correct course code associated in the export values for the prompts

Release 2021.2.0 [August 2021 - Fall Release]

Tracker Google Sheet

Create a new version of the sheet for the Fall 2021 term
- Clear out old data and move to the shared application folder
Remove all functionality and visual indicators around the registration webinars
- No longer required or monitored
Add a cell to the Control Panel that will serve as the indicator for which logic to use when processing the sheet data
- AYI and SUMMER will be the two options
- Update all validation procedures to include this new variable to determine which checks to process

Unified Query

Add a column showing the number of courses that are registered in SIS
Create a version of the query with the same structure, but new parameters to pull for the Fall 2021 term and AYI specific programs
Change the logic for the Session column to include a universal placeholder that can fit into the validation check (e.g., {“id”:”0”,”name”:”Any Session”})
Additional fields for Unified query
- Add a new column to show the total number of courses registered
Remove registration webinar fields

Slate Data Configuration

Ensure that all of the courses have the correct course code associated in the export values for the prompts

Release 2021.1.1 [June 2021]

Tracker Google Sheet

BUG: Cases where a person has enrolled in multiple approved courses in the same subterm are being incorrectly flagged as valid registrations

Release 2021.1.0 [March 2021 - Summer Release]

Tracker Google Sheet

Clearing the Slate data sheet before the upload eliminates the frequent error in the upload, make this the first step in the upload function
The Slate named range needs to automatically grow to the size of the new dataset
Cases where there are two applications, one for the 1-week and the other for the 3-week, are not being calculated properly
- Pre-sort the Slate query by Ref ID to make processing faster
- During the validation procedure, analyze the full sheet for these cases and combine the multiple rows into single comprehensive row before proceeding with the check
- E.g., 776046274 Karina Kadia and C004523125 Justin Riggi

Release 2021.0.9 [March 2021]

Tracker Google sheet
- There are the same number of courses as sessions (for dual)
- Only one course per session
- Each session has a registered course
- All courses are in allowed sessions
- Reason why a registration failed validation
- WORKAROUND: Add some text to the first row beneath the header, then copy the row into the sheet. Remove the dummy row and proceed.

Add a registration validation check to compare the session code and the session that the student signed up for
Add text info to the Actions column to guide next steps
BUG: When there is no data in the Manual Registration or Call Tracker sheets, the first row to be copied inherits the header row formatting and loses the conditional formatting
For the registered course counter, factor in the subterm to avoid double counting those courses that have two registered components, but are technically a single course for revenue purposes

Release 2021.0.8 [February 2021]

Tracker Google sheet
- Add the following indicators to the query and then add counters to the dashboard
- Registered course count
- Add dates for last data upload and course validation into the Statistics section; pull from Control Panel
- Only allow a single file to be selected and uploaded

Dashboard
Sidebar UI for Data functions

Unified Query
- Confirmed access should be for Laura Fields and Alan Barnhart
- Include in the SIS Registered Courses field in Slate
- Session Code Key (K = Session 1, L = Session 2, M = Session 3)

Give run access to Admissions Admins for the HSP Unified query so they can retrieve the daily export file
SIS session registered; subterm code determines session
Add Attended Date to the exports
Student application type (e.g., deferral, re-enroll, new)

SIS Sync Query:
- This will ensure that the sync will catch new registrations even when the application record has not been modified

Set the SIS_Record_Date to the latest available date between the APPL and TSTA Last_Mod_Date

Committed, Attended

Add conditional formatting to show if rows exist on either of the manual sheets

Release 2021.0.7 [February 2021]

Unified Query

Add Student Phone
Add Parent Phone
Add Session/Term of Entry (Session1, Session 2, Session 3)
Review all data columns from the Slate query for necessity; remove any that are unneeded for the workflow

Tracker Google sheet
- Stores instructions, variables and data that are needed sheet-wide
  - Add code to update the cell whenever a new file is uploaded
- Move "SIS Registration Group" and "STUD Exists" fields to column G & H; Freeze columns A-H
- Move "SIS Registration Group" and "STUD Exists" fields to column G & H; Freeze columns A-H

Control Panel
Registration Completed
Committed, Attended
BUG: Retain leading zeros in all of the Slate Ref ID cells

Release 2021.0.6 [February 2021]

Tracker Google sheet
- File chooser and CSV upload interface
- Registration Check
  - Match the tab color on the underlying sheet
  - Match the tab color on the underlying sheet
- Select the entire row to copy then push a button to copy
- Any rows that are in the Registration Completed sheet should be set to gray to indicate they no longer require action
- Add the date to the first cell for each row when it is added to the sheet
- Include a series of columns for manual tracking detail at the front of each row
- Any rows that are in the Registration Completed sheet should be set to gray to indicate they no longer require action
- Add the date to the first cell for each row when it is added to the sheet
- Include a series of columns for manual tracking detail at the front of each row

Sidebar UI for Data functions
Conditional formatting to make elements more readable on sheets where action is expected
Sidebar UI to add a row(s) from the Registration Check sheet to any of the manual monitoring sheets (e.g., Registration Calls, etc.)
Registration Calls tracking sheet
Manual Registration tracking sheet

Release 2021.0.5 [January 2021]

Tracker Google sheet
- Committed Students
- Missing STUDs
- Session Attendees
- Complete Registrations
- Registrations to Review
- Validate the course columns and update the ‘Valid Registration’ column with ‘TRUE/FALSE’ to designate if the registration needs to be reviewed by an admin
- Views to include
  - Filter on [STUD Exists in SIS]
  - [Deposit in SIS]<>N
  - [Attended…]=1
  - [Enrollment in SIS Value]=Y
  - [Attended…]=1
  - [Enrollment in SIS Value]<>Y

Dashboard counters for basic metrics
UI for Data functions
Create a data view page that uses QUERY to display rows from the unified data extract that requires admissions review and action

SIS → Slate sync process updates
- If present, sync the code back to an application-scoped field in Slate

Add a check to the STUD check process to look for a REG-GRP indicator (STUD_STUDENT.Registration_Group_Code)
Add a check for any registered courses for the APPL entering term and store them in an application-scoped field in Slate (comma delimited list of course identifiers)

Unified query in Slate to pull all data that is required to track the status of any incoming admitted immersion program student
- Target enrollment term (Summer 2021, etc.)
- Target program name (AYI, Summer Immersion, etc.)
- Decision (First) = Admit

All students in the:
For course interest fields, include the export value in addition to the course name for easy comparison to the SIS side of the data
SIS Enrollment Reply value (Y/N) to assess commits

Browser not supported