Red Rover Substitute and Vacancy Import Settings

Modified on Thu, Oct 31 at 10:50 AM

What is it

This task will take a TAB Delimited file that contains year to date history of substitutes subbing for teachers and/or vacancies.

File Format

TAB Delimited File in UTF-8 encoding.

The first row contains headers.

Column	Sample Data	Description
Staff ID	1234	Local Staff Id of the Substitute
Substitute Full Name	Mouse, Mickey	The last name, first name of the Substitute. This field is used only to log the name if the employee cannot be found in the system by their Staff ID.
SubType Code	TRSUB	The Sub Type used to pay this substitute as found on the Personnel - Substitutes - Types screen.
Date		Date of the attendance transaction
Subbed for Employee	5678	Local Staff Id of the Teacher being subbed for.
Subbed for Employee Name	Duck, Donald or Vacancy: Elementary	The last name, first of the Teacher being subbed for. This field is used only to log the name if the employee cannot be found in the system by their Staff ID. If this value starts with "Vacancy: " AND the Subbed for Employee field is blank, this row will be considered a vacancy and not a substitute record.
Units	.5	Values of 1 and .5 are accepted. This is used to match the daily attendance record. See below.
Unit Type	D	D (Daily) or H (Hourly) If D = A search for existing Attendance Daily matches the first record where the date matches and the value = the units column If H = A search for existing Attendance Daily matches the first record where the date matches. The number of hours in the record is stored in the Attendance Daily - Substitute Hours field. This field is used in the Pay Subs Screen
Date Verified	9/11/2024 8:25 AM	Date and time this record was verified. This is the result of a verification process in Red Rover. This value is stored on the StaffVacancy or StaffAttendanceDaily record.

* Existing Attendance Daily records are never overwritten or updated *

File Processing Logic

The file is first retrieved from the SFTP Server or by manually uploading it from the Modify System Task Configuration screen.

The contents of the file are read into memory.

Depending on the options checked, Vacancy and/or Substitute records will be processed.

Vacancy Record Processing

Vacancy records are rows in the file where the Subbed for Employee column is blank, and the Subbed for Employee Name starts with "Vacancy:".

The records will be imported to be used on the Personnel - Substitutes - Pay Vacancies screen.

The process starts by identifying the unique employees in the file, and then processing each employee one at a time

Existing StaffVacancy records are loaded into the memory for that employee

For Each Vacancy Row in the file for this employee...

Sub Type Code is determined from the SubType Code field in the row

If the row does not contain a value, the primary sub type code is used (Demographics page)

If no Sub Type is defined, an error is reported and this row is not imported.

If a StaffVacancy with the same Note Field (Subbed for Employee Name) and Vacancy Date does not exist...

A new StaffVacancy Record is created; setting the note to the Subbed for Employee Name, Attendance Date, Units, Sub Type Code and Date Verified.

Substitute Record Processing

Substitute records are rows in the file where there are values in the Staff Id and Subbed for Employee columns and the phrase Vacancy: does not exist in the Subbed for Employee Name column.

The records will be imported to be used on the Personnel - Substitutes - Pay Subs screen.

The process starts by identifying the unique employees in the file, and then processing each employee one at a time

For Each Substitute Row in the file for this employee...

Sub Type Code is determined from the SubType Code field in the row

If the row does not contain a value, the primary sub type code is used (Demographics page)

Attendance Dailies for the Attendance Date for the Subbed for Employee is loaded into memory

Look for an AttendanceDaily where the Date, Value (Units) match the file record AND the substitute field is blank.

If the above record exists, the substitute, subtype, verified datetime is set on the StaffAttendanceDaily record.