FY24.2.x: Technical Design (Slate+Advising)

Executive Summary

The purpose of this document is to provide the full details of the technical solution to be used for the build of the Slate+Advising solution. Everything that is needed by the developer(s) to produce the required data model, forms and workflows will be included in this specification.

Technical Components

The following sections will detail the technical components within each functional area that are required to successfully implement this project.

Data Requirements

Purpose

Define any integrations or data that must be provided to deliver the solution.

Return from Leave of Absence:

The return from leave of absence confirmation needs to be CCed to the student's advisor BUT:

a) Some students don't have advisors.

b) the advisor email address is not available on the email form.

This is true on other forms. To get around this issue:

1. Create a hidden textbox on the form that is connected to the Advisor UNI system field on the student's record.
2. Use the following code in the CC line of the confirmation email: 
   {% if {sys-field-person_advisor_uni.size} > 0 %}{{sys-field-person_advisor_uni | append: "@columbia.edu"}}, {% endif %}

FERPA Release Request:

• Create an entity in Slate with the following fields:
  • ferpa_organization_or_name
  • ferpa_address
  • ferpa_email
  • ferpa_phone_number
  • ferpa_waiver_status:
    • Active
    • Inactive

Incomplete Grade Request Form:

• Create a new entity in Slate called "Incomplete Grade Requests." It should have the following fields:
  • Incomplete Grade Request Class Schedule Row Guid (This field points to a class in the student's academic history) (textbox)
  • Incomplete Grade Request Date (textbox)
  • Incomplete Grade Request Approval Status (select). Options:
    • Awaiting Submission
    • Pending
    • Denied
    • Approved
  • Incomplete Grade Request Default Grade
  • Incomplete Grade Request Completion Requirements
  • Incomplete Grade Request Completion Due Date
  • Incomplete Grade Request Student Signature
  • Incomplete Grade Request Instructor Signature
• Create a Materials type: Incomplete Grade Request Reason

Technical Design

Purpose

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

• Leave of Absence Fully Executed Communication:
  • Create a communication in Slate Deliver with a query that checks against:
    • Leave of Absence Form review_status = "Fully Executed."
    • Leave of Absence Form suppress_notifications = "no."
    • Status of "approved" by advisor and program representative
    • Either not a medical leave of absence OR approved by the wellness representative.
  • If the leave is medical, the wellness alias should be CCed.
  • If the leave is voluntary or military, the student's advisor should be CCed.
  • The communication should be based on this template: https://docs.google.com/document/d/1wBMscCr-P_wG10yv4klIj21BSJbfNnDt3Rdl3zAs4nI/edit#heading=h.gjdgxs

• Return from Leave of Absence Form
  

  • Create a form in Slate based on the existing form here: https://docs.google.com/document/d/1gnsfPTn_p4uE18fOW9mgFipxRqgQzDvvVX6qvukLOp4/edit?usp=sharing with the following fields:

    Label	Field Type	System Field for Prompt	Export Value	System Field?
    First name	Textbox	N/A	first_name	Yes
    Last name	Textbox	N/A	last_name	Yes
    Semester student requested LOA	Select	school_year_terms	semester_student_requested_loa	No
    Semester returning from LOA	Select	school_year_terms	semester_student_returning_from_loa	No
    Are You Applying for On-Campus Housing	Select	Yes/No	applying_for_on_campus_housing	No

  • Create a confirmation email to be sent upon form submission with the following text: (The confirmation email text should be based on the form text.)
  • Make the form require SSO for completion.
• FERPA Release Request
  • Create a form in Slate with the following fields based on the existing form here: https://assets.sps.columbia.edu/pdf/slar/slar-ferpa-release-form.pdf

  • The form should have the following fields:

    Label	Field Type	System Field for Prompt	Export Value	Read Only	System Field?
    First Name	Textbox	N/A	first_name	Yes	Yes
    Last Name	Textbox	N/A	last_name	Yes	Yes
    UNI	Textbox	N/A	uni	Yes	Yes
    Date of Birth	Date	N/A	date_of_birth	Yes	Yes
    Academic Program	Select	program_of_study	program_of_study	Yes	Yes
    Advisor	Select	staff_assigned	staff_assigned	Yes	Yes
    Person/Organization Name	Textbox	N/A	ferpa_organization_or_name	No	Yes
    Person/Organization Address	Address	N/A	ferpa_address	No	Yes
    Person/Organization Email Address	Email	N/A	ferpa_email	No	Yes
    Person/Organization Phone Number	Phone	N/A	ferpa_phone	No	Yes
    Person/Organization FERPA release status	Hidden Select List	ferpa_waiver_status	ferpa_waiver_status	(hidden)	Yes
    Electronic signature	Textbox	N/A	electronic_signature	No	No
    Signature date	Textbox	N/A	signed_date	No	No

  • Set the FERPA release status to "Active" by default.
  • Create a confirmation email that students receive upon submitting the form. The spsadvising email alias will be CCed on the email.
  • Create a FERPA waiver tab on students' profile page. It will consist of two entity widgets:
    • Active FERPA waivers
    • Inactive FERPA waivers
  • A script should be added to the FERPA waiver widget forms to allow users to edit phone numbers, email addresses, etc, but not create new records.

• Change of Modality Form

  • Create a form in Slate based on the existing form here: https://docs.google.com/document/d/1AVZhM0ydJRiOIVr7KOiOGeyoN-nH6A5tlRMuLxkjU7E/edit

  • The form should include the following fields:

    Label	Field Type	System Field for Prompt	Export Value	Read Only	System Field?
    STUDENT FIELDS
    UNI	Textbox	N/A	UNI	Yes	Yes
    First name	Textbox	N/A	first_name	Yes	Yes
    Last name	Textbox	N/A	last_name	Yes	Yes
    Advisor UNI	Hidden textbox	N/A	sys:field:person_advisor_uni	Yes (hidden)	Yes
    Academic program	Select	person_program	person_program	Yes	Yes
    Current modality	Select	in-person/remote	current_modality	No	No
    New modality	Select	in-person/remote	new_modality	No	No
    Electronic signature	Textbox	N/A	student_signature	No	No
    Date	Date	N/A	student_signature_date	No	No
    INTERNAL FIELDS
    The change of modality request has been	Select	Approved/Denied	approval_decision	No	No
    The change of modality will go into effect at the start of the following term	Select	advising_term	advising_term	No	Yes
    Academic program representative signature	Textbox		academic_program_representative_signature	No	No

  • A confirmation email should be sent to the student with their advisor and spsadvising@columbia.edu CCed.
  • Create a script that displays the internal only fields for the student to see but makes them Read-Only when the student is completing the form. That script can be found here in the AIS repository here: slate_advising/forms/incomplete_grade_request/form_script.js.

• Incomplete Grade Request Form

  • Create a form in Slate based on the existing form here: https://docs.google.com/document/d/1IaMZCzNWwsFlJnmb_mHwxyHgewwIL5bMZJJN8yChLFk/edit

    Label	Field Type	System Field for Prompt	Export Value	Read Only?	System Field?
    STUDENT FIELDS
    CUID	Textbox (hidden)	N/A	sys:field:person_cuid	Yes	Yes
    External ID	Textbox (hidden)	N/A	(incomplete_grade_request_entity_id)	Yes	Yes
    Request date	Date	N/A	incomplete_grade_request_date	No	No
    First name	Textbox	N/A	first_name	Yes	Yes
    Last name	Textbox	N/A	last_name	Yes	Yes
    UNI	Textbox	N/A	UNI	Yes	Yes
    Advisor UNI	Textbox (hidden)	N/A	sys:field:person_advisor_uni	Yes (hidden)	Yes
    Columbia email address	Textbox	N/A	sys:field:person_cu_email	Yes	Yes
    Course name	Textbox	N/A	course_name_from_form	Yes	No
    Section number	Textbox	N/A	section_number_from_form	Yes	No
    Class Schedule Row GUID	Textbox (hidden)	N/A	sys:entity:field:incomplete_grade_class_guid	Yes	Yes
    Term/Year	Select	school_year_terms	sys:entity:field:advising_term	Yes	Yes
    Instructor name	Textbox	N/A	instructor_name_from_form	Yes	No
    Instructor email address	Textbox	N/A	instructor_email_address	Yes	No
    Course requirement to complete	Textbox	N/A	course_requirement	No	No
    Sign here to indicate that you agree to the conditions above	Textbox	N/A	e_signature	No	No
    INTERNAL FIELDS
    Approval status	Select	incomplete_grade_request_approval_status	incomplete_grade_request_approval_status	No	Yes
    Default grade	Textbox	N/A	incomplete_grade_request_default_grade	No	Yes
    Completion requirements	Textbox (paragraph)	N/A	incomplete_grade_request_completion_requirements	No	Yes
    Completion due date	Date	N/A	incomplete_grade_request_completion_due_date	No	Yes
    Instructor signature	Textbox	N/A	incomplete_grade_request_instructor_signature	No	Yes

  • Create a portal which will consist of:
    • A query to generate a list of classes for which a student in eligible to request an incomplete grade.
    • A query to generate a list of the student's existing incomplete grade requests.
    • A view page, consisting of:
      • A hidden table consisting of the students' full list of classes - the name, section number, term, instructor name, and email address. Each field in the table should have an id defined by what the field represents and the GUID of the class's row in the student's class list (e.g. "section_number_[guid]")
      • A table presenting the student's incomplete grade requests along with their statuses, requirements, etc.
        • If the request has been approved and the student has not yet signed the request, there will be a button that the student can click to sign the form.
      • A link to the form on which to make the request.
    • Create JavaScript functionality to accomplish the following:
      
      • Provide a drop-down list of the student's classes that are eligible for an incomplete grade.
        • Classes for which the student has already submitted a request should be removed from the drop-down options.
        • If there are no available classes," an error should pop-up saying that the student does not have any classes eligible for an "incomplete grade request." 
        • Upon selecting a class, the "class name," "section number," "term," "instructor name," and "instructor email address" fields should be automatically populated (this can be. easily accomplished by using the hidden table at the top of the page). It should also update the external id field with the guid of the class and the date the form is filled out.
      • Make instructor-specific fields as well as the "student signature" field read-only when the student enters the initial request.
      • The request status field should show "awaiting submission," but the status should be set to "pending" after the student makes the initial submission. (This can be accomplished by setting the real field to "Pending,", hiding it, and having a fake field locked on "Awaiting submission").
      • Make student-specific fields read-only when the instructor updates the form.
      • Force the following conditions when the instructor updates the form:
        • If the request is being left as "pending," "default grade," "completion requirements," "completion due date," and "instructor signature" fields be left blank. 
        • If the request is denied, the "default grade" and "completion requirement" fields should revert to "N/A," the "requirement completion date" field should be blank.  The "instructor signature" field should be required.
        • If the request is approved, the "default grade," "completion requirements," "completion due date," and "instructor signature" field should be required.
      • Make all fields other than "preferred name" and "student signature" read-only for when the student enters their signature. Make "student signature" required.
  • Upon submission of the form, the student should see a confirmation page. A confirmation email should go to the student, their advisor, and spsadvising@columbia.edu.
  • Upon update of the form by the advisor, a confirmation email should go to the student, their advisor, and spsadvising@columbia.edu.
  • Upon the student signing the form, a confirmation email should go to the student, their advisor, and spsadvising@columbia.edu.