Data Sub – Update Residence Guarantee and Waitlist Application Status  


Data Sub – Update Residence Guarantee and Waitlist Application Status  

Table of Contents

Prerequisites 

  • Responsibility for updating application statuses and application terms  
  • Knowledge of application modification and management 
  • Knowledge of the Bulk Edit/Set Value function 

Background

This article provides background information on how the Residence Guarantee (RG) and Waitlist (WL) application (app.) status data subscriptions function. The purpose of these data subs is to update an entry's RG or WL application status automatically when a student's Residence Placement application status is updated. There are five data subscriptions that will update the RG or WL application status to Offered, Cancelled, Accepted, or Past Deadline. For a summary of how they work, see the table below. 
 

Data Subscription Details

Purpose  To update an entry's Residence Guarantee (RG) application status to Offered and the Offer Sent Date to today's date when their Placeholder application is updated to the Residence Placement term and the application status is updated to Offered 
Subscription Type  Update a Record 
Trigger  When data is updated in the selected table, specifically when the term ID is updated to the Residence Placement term ID
Table  Entry Application 
Field Name  Term ID
Filter Records 

( ([EntryApplication].TermID IN (410, 400, 412, 402, 414, 394, 416, 396)) 

AND ([EntryApplication].ApplicationStatusID  = 7) )  

 

Note: Term IDs will be updated annually 
On Data Updated  Update a record on another table 
Table  Entry Application 
ID Query 
 

SELECT EntryApplicationID FROM EntryApplication WHERE EntryID = {NewData_EntryID} AND TermID IN (387) AND ApplicationStatusID NOT IN (7) 

 

Note: Term ID will be updated annually 
Advanced -Record Details 

<EntryApplication> 

    <ApplicationStatusID>7</ApplicationStatusID> 

    <OfferSentDate>{Date}</OfferSentDate> 

</EntryApplication> 

 

Purpose  To update an entry's Waitlist (WL) application status to Offered and the Offer Sent Date to today's date when their Residence Placement application status is updated to Offered 
Subscription Type  Update a Record 
Trigger  When data is inserted into the selected table, specifically when a residence placement application is inserted 
Table  Entry Application 
Filter Records 

( ([EntryApplication].TermID IN (418, 419, 410, 400, 412, 402, 414, 394, 416, 396)) 

AND ([EntryApplication].ApplicationStatusID  = 7) )  

 

Note: Term IDs will be updated annually 
On Data Updated  Update a record on another table 
Table  Entry Application 
ID Query 

SELECT EntryApplicationID FROM EntryApplication WHERE EntryApplicationID = {NewData_EntryID.EntryApplicationID} AND TermID IN (388) 

 

Note: Term ID will be updated annually 
Advanced -Record Details 

<EntryApplication> 

    <ApplicationStatusID>7</ApplicationStatusID> 

    <OfferSentDate>{Date}</OfferSentDate> 

</EntryApplication> 

 

Purpose To update an Entry's RG/WL application status to Cancelled and populate the Cancel Date to today's date when their Residence Placement application status is updated to Cancelled
Subscription Type  Update a Record 
Trigger  When data is updated in the selected table, specifically when the application status is updated to Cancelled 
Table  Entry Application 
Field Name  Application Status ID 
Filter Records 

( ([EntryApplication].ApplicationStatusID IN (3, 34, 36, 6, 17)) 

AND ([EntryApplication].TermID IN (418, 419, 410, 400, 412, 402, 414, 394, 416, 396)) ) 

 

Note: Term IDs will be updated annually 
On Data Updated  Update a record on another table 
Table  Entry Application 
ID Query 

SELECT EntryApplicationID FROM EntryApplication WHERE EntryApplicationID = {NewData_EntryID.EntryApplicationID} AND TermID IN (387, 388) AND ApplicationStatusID NOT IN (3) 

 

Note: Term ID will be updated annually 
Advanced -Record Details 

<EntryApplication> 

    <ApplicationStatusID>3</ApplicationStatusID> 

    <CancelDate>{Date}</CancelDate> 

</EntryApplication> 
Purpose  To Update an entry's RG/WL application status to Accepted when their Residence Placement application status is updated to Accepted, Assigned, Complete Pending Assignment, Deposit #1 Paid, or Offer Accepted 
Subscription Type  Update a Record 
Trigger  When data is updated in the selected table, specifically when the application status is updated to Accepted 
Table  Entry Application 
Field Name  Application Status ID 
Filter Records 

([EntryApplication].ApplicationStatusID IN (9, 32, 21, 39, 24)) 

AND ([EntryApplication].TermID IN (418, 419, 410, 400, 412, 402, 414, 394, 416, 396)) 

 

Note: Term IDs will be updated annually 
On Data Updated  Update a record on another table 
Table  Entry Application 
ID Query 

SELECT EntryApplicationID FROM EntryApplication WHERE EntryID = {NewData_EntryID} AND TermID IN (387, 388) AND ApplicationStatusID  IN (7) 

 

Note: Term ID will be updated annually 
Field

Application Status ID: Accepted 
Purpose  To update an Entry's RG/WL application status to Past Deadline and populate the Cancel Date to today's date when their Residence Placement application status is updated to Past Deadline 
Subscription Type  Update a Record 
Trigger  When data is updated in the selected table, specifically when the application status is updated to Past Deadline 
Table  Entry Application 
Field Name  Application Status ID 
Filter Records 
 

( ([EntryApplication].ApplicationStatusID  = 8) 

AND ([EntryApplication].TermID IN (418, 419, 410, 400, 412, 402, 414, 394, 416, 396)) )  

 

Note: Term IDs will be updated annually 
On Data Updated  Update a record on another table 
Table  Entry Application 
ID Query 

SELECT EntryApplicationID FROM EntryApplication WHERE EntryID = {NewData_EntryID} AND TermID IN (387, 388) AND ApplicationStatusID  IN (7) 

 

Note: Term ID will be updated annually 
Advanced -Record Details 

<EntryApplication> 

    <ApplicationStatusID>8</ApplicationStatusID> 

    <CancelDate>{Date}</CancelDate> 

</EntryApplication> 

Examples of Use

Notes

1. This data sub will only run on entries that have an existing RG or WL application for the upcoming academic year. This data sub will not affect old RG and WL applications from previous academic years.

2. The TermIDs in the Filter Records and ID Query values in the data subs will be updated annually to the upcoming academic year's TermID

3. The data sub is only active for the following application terms:

  • Chestnut - Academic Year 
  • Innis - Academic Year 
  • New College – Academic Year 
  • SMC - Academic Year 
  • Trinity - Academic Year 
  • UC - Academic Year 
  • Victoria - Academic Year 
  • Woodsworth - Academic Year 

4. Applicable Only to Data Sub 1 - Update RG App to Offered Upon Term Change and Data Sub 2: Update WL App to Offered Upon Insert:

When bulk editing or updating an app. termID from "Placeholder" to the Residence Placement termID or inserting a new app., you must also update the app. status to "Offered" at the same time. If you aren't updating the app. status while you’re updating the term session or inserting the app., the data sub will not run.

5. If you wish to undo the cancellation on an entry's Residence Placement application, the RG or WL application's cancellation will not be reversed and must be manually updated to the correct status. 

  • Example: A student has a cancelled application for the Innis 2022-23 term and wants to undo the cancellation. You change their Innis application status from "Cancelled" to "Offered." The student's RG 2022-23 application status will remain as Cancelled, so you will have to manually update the status to "Offered" and clear the Cancel Date.

Additional Resources From StarCare Online