FY24.1.x: Icebox (Slate+Advising)

Functional Requirements

Purpose

Capture the desired functionality to be completed.

• Tutoring
  • Allow students to submit a tutoring request that can be routed to Phong/Erin for fulfillment.
• CDL Inclusion
  • Allow the CDL team to view the student profiles, and integrate some of their services into the platform.
  • Requires more review to determine functional requirements for these users.
• Birthday Notifications
  • Send birthday messages to students
  • Show upcoming birthdays on advising landing page(s)
• Data Management
  • Alumni data integration
    • Add the degree details to the Schools table for new degree conferrals
      • Update the source format to place data in the right locations when status becomes Alumni in feed
    • Backload all alumni degrees to the Schools table (for history)
  • Add new detail fields for the person record
    • Other (not in source)
      • CU employee flag
      • From Admissions
        • Home institution (for visiting population)
        • Home high school (for pre-college)
        • Part-time/full-time preference
  • Incoming Data
    • Include the Expected Return Date for students that have an active Leave Status (students feed)
    • Create scheduled exports based on last modification date for the required data points in the following systems
      • Canvas
        • Course progress information
    • Create one-time exports from the following systems
      • Slate+Admissions
        • Additional personal data not available in SIS
          • Job history, previous institutions, etc.
          • Potentially extended contact details
    • Other potential fields/data to include
      • Pronouns?
      • Transcripts
      • HBCU fellow
      • Student Government
  • Reports and queries
    • Determine best reports for leadership view of advising status
    • Audit queries to validate imported data
• Event & Appointment Management
  • For Advisors
    • Event Template for Registration Webinars
      • Custom CSS to align request form layout with SPS Advising home page
      • Default settings stored in the template
      • Includes defined email communications for typical milestones
        • Event announcements, RSVP instructions, upcoming date reminders, follow-up after attendance or no show
        • Pending final communications text from Advising team
  • For Students
    • Form to see primary advisor's calendar for availability and book an appointment
    • Personal calendar view of scheduled appointments and events with Advising
      • Allow update and delete to scheduled entries
• Student Relationship Management
  • Custom form(s) to collect information from students as required to support requests and process flows and store in the student profile
    • Develop Slate-based forms
      • Create Return from Leave of Absence Form in Slate (specs here: https://docs.google.com/document/d/1N1G1y5ZIGxK7bEWSIOoKzsa-edssQkkl5FTLRec14uI)
      • College Edge course interests
    • Make improvements to existing forms
      • Leave of Absence Form
        • Create queries for different statuses of form processing (unprocessed, in review, and complete)
        • Create a query based on students' planned return date.
      • Advanced Standing Application:
        • Divide internal processing section into an "Advisor" section and a "Processing" section.
        • Trigger an automated email to Jean, Xavier, and Sarah when the advisor has completed his/her section. 
      • Certificate of Professional Achievement
        • Phone number is currently required, but it does not use a system field. Should it?
        • Break the person's mailing address into separate Street, City, State, Country, Zip fields.
        • The email address uses the student's Email Address system field. It is read only. Should it be?
  • Advisor Snapshot Dashboard (Student Profile)
    • Add button link to send a canned communication to the student directly from the dashboard
      • E.g., nudge to register, follow-up about return from leave, reminder of upcoming meeting, etc.
    • Create additive dashboard snapshots based on the population to allow for more specific information to be displayed based on the audience
      • Auditors, Visiting, Pre-college?
  • Homepage widgets
    • Advisors
      • Create links to queries and reports that are tailored for the current user
        • Graduates (pending graduate load from SIS)
      • Advisors will have a list of upcoming birthdays on their portal landing page.
  • Define and build interaction categories required for reporting and tracking purposes
    • Placeholder for specifics; need to confirm if we will be using interactions for tracking
      • E.g., Phone calls, Zoom meetings
  • Advising Notes
    • See if the user can default to currently logged in user
      • Not currently possible in an Entity Widget; results in an error on form submission; revisit in a future Slate release
  • Portals
    • Student Portal
      • Show relevant details to students, including their history, interactions with advising, upcoming events, others?
      • Connect appointment confirmation/cancellation form to the actual event so that the message sent upon submission can go to the advisor or other staff member that the meeting is with.
      • Links to external and PDF-based process forms
• Communications & Outreach
  • For Advisors
    • Message templates for common communications (non-event)
      • Registration "starts soon/now open" reminders
      • Student-specific messages based on population criteria
        • Hold notifications
      • General message templates for ad-hoc announcements via email
        • Students will receive an automated email on their birthdays.
        • Pending list of common comms to students from working group
  • Create automated email to send to students upon approval of their Certificate of Professional Achievement, describing how their name would appear on their certificate, etc. (See model here: )
• Import latest swipe and login history (not allowed according to campus safety, but can be requested any time)

Technical Design

Purpose

Define in detail the technical work that will need to be completed to deliver the final solution.

• Birthday Notifications
  • Create a mailing (Get text from advising team) that runs each morning in the 10:00 AM - 12:00 PM timeblock. Generate the list using the following query:
    • Persons with a Status of New Student or Continuing Student.
    • Exclude persons with a tag name of Test Record.
  • To ensure the mailing gets sent to students with a Birthday of February 29th in years when that date does not exist, create subqueries that performs the following actions:
    • Capture the student’s birthdate without the year (format mask: MM-dd).
    • Capture the current date without the year (format mask: MM-dd).
    • Capture the current date with only the year (format mask: yyyy)
    • Capture the remainder when the current year is divided by 4 (Current Year % 4 = 0).
    • Return an adjusted birthdate value based on the following case… when conditions:
    • If the student’s birthdate is 02-29 and the year is divisible by 4, return 02-29.
    • If the student’s birthdate is 02-29 and the year is not divisible by 4, return 03-01.
    • If the student’s birthdate is not 02-29, return the student’s birthdate (format mask: MM-dd).
    • Filter for students whose adjusted birthdate is equal to the current date (format mask: MM-dd).
  • Birthdays on Landing Page - Create a widget to go on to each advisor’s landing page. Populate the widget with students based on the following criteria:
    • Persons with a Status of New Student or Continuing Student.
    • Exclude persons with a tag name of Test Record.
    • Persons whose “Staff Assigned” value is IN (Current User).
    • To ensure the list includes students with a Birthday of February 29th in years when that date does not exist, create subqueries that perform the following actions:
      • Generate an adjusted student birthdate in the year 2000 (format mask: 2000-MM-dd).
      • Generate an adjusted current date in the year 2000 (format mask: 2000-MM-dd).
      • Capture the current date without the year (format mask: MM-dd).

• • Capture the current date with only the year (format mask: yyyy).
  • Capture the remainder when the current year is divided by 4 (current year%4)
  • To capture birthdays for the current date and 2 days in the future, use the following case… when conditions to generate a maximum_date_diff value:
    • If the current year is not divisible by 4 and If the current date (without the year) is either 02-28 or 02-27, set the maximum_date_diff to 3. (We do this because we are treating the birthdays as if we are in a leap year so that 2/29 birthdays will be included. But in this case, we are not in a leap year. So if, for example, it is 2/28. We want to include students whose Birthday is either 3/1 or 3/2. So we need to go 3 days into the future, grabbing students whose birthdays are 2/28, 2/29, 3/1, or 3/2.
    • In all other cases, set the maximum_date_dif_to 2.
  • Filter students based on a datediff between the current date (adjusted to the year 2000)  and the birthdate (adjusted to the year 2000). Include only students where the difference in dates (in terms of days) is >=  0 and <= the maximum_date_diff value.
  • Display the student’s actual birthday including year on the page sorted by the datediff value (or convert 2/29 birthdays to 3/1 as in the method above).
  • To display an age column: 
    • If the date difference is 0 days, display the student’s “Current Age Based on the Birthdate” field value.
    • If the date difference is greater than 0 days, add 1 to the “Current Age Based on the Birthdate” field value.