At Santa Rosa Junior College, scheduling is crucial for smooth operations. District police use schedules to program door locks. Custodians rely on them to clean classrooms and event spaces. Maintenance teams need schedules to address technical requirements.

I was hired as a temporary replacement for Heidi Miranda, an administrative assistant nearing retirement. Heidi had held this position for 16 years. At the start of each semester, she would receive the schedule of all courses and manually input them into an Excel file. At the end of each week, she would use this Excel file, along with the Student Information System (SIS) and the Event Management System (EMS), to create the necessary schedules for the relevant parties. This process was highly labor-intensive and, due to reliance on multiple sources and manual data entry, susceptible to errors.

Understanding the Problem

Soon after I began my work, I realized that the process could be significantly improved with a computerized software solution. However, due to the importance of the documents and the parties involved, it was important to understand who used them and how they were used. 

While maintaining the manual process, I began holding meetings with all stakeholders. The output documents were primarily used by two main groups. The first was the district police, who required a schedule detailing the opening and closing times of all campus buildings. They relied on this information to unlock the buildings at the start of the day and secure them after use. They also required a detailed daily schedule of classes, along with a list of events for the day. These two daily documents were essential for maintaining campus security and ensuring they remained informed about ongoing activities.

The second group consisted of custodial and maintenance staff. They also required a daily schedule of classes and a list of events. While they were receiving the same daily schedules and event lists as the district police, I realized their needs were different. Unlike the district police, they needed a schedule that would help them understand what was happening at any given time throughout the day.

The old process involved a single Excel file for the lock schedules, along with seven daily class schedules and a separate list of all events (Figure 1). This process was time consuming, and prone to human error. Since there were multiple sources of truth, it was difficult to determine which source was the most accurate or up-to-date.

Technical Requirements

From a technical standpoint, it was essential to ensure the data remained secure. I chose to keep the data stored locally instead of using a cloud system for two main reasons. First, since the majority of stakeholders were consumers of the exported documents rather than the backend system, direct access to the data wasn’t necessary. Second, although SRJC has a programming department, I found it challenging to communicate with them, and I realized they were not willing to cooperate to enable the use of their cloud system and single sign-on features. However, I kept scalability in mind in the process.

The interface of the software was crucial, as I would not be the only one using it. Given that this position required no technical expertise, the interface needed to be simple and user-friendly.

For the reasons I mentioned, I didn’t have direct access to the database directly. Although it was possible to view the data in HTML and Excel formats (two different sources, one for classes and the other for events), manually entering the data into the software would have been impractical. Doing so would have only maintained the high intensity and risk of human error.

Final Product

After six weeks, the final product was ready for use. Typically, change is challenging in organizations, so my priority was to ensure the transition was smooth. The software, which I named Lock Scheduler, is developed using Python, SQLite for the database, and wxPython for the interface. From the end user’s perspective, it functions like a regular Windows application (Figure 2).

Data is the key component of this software. It is crucial that users can easily import data from two different sources. Importing the data into the software takes less than 15 seconds, whereas the manual process typically took days. (Figure 3)

After testing the result for a few weeks, while maintaining the old process, I made sure the quality of the software was satisfactory. 

The exported documents are easier to use for both groups. Rather than Excel format, the documents are exported in PDF format; this ensures maximum portability. (Figure 4) Custodial and maintenance staff receive a table sorted by day, building, class, and time (in that order), allowing them to easily see what’s happening at any given moment (Figure 5), whereas the District Police receives daily schedules sorted b y start time for each day (Figure 6). Both groups receive a weekly schedule of events on campus as well (Figure 7).

Results

The software delivers significant improvements:

• Scheduling errors have been reduced by 94% (from an average of 18 errors to just 1), largely due to the elimination of human error through automated data importing, duplication detection, and error validation.
• Scheduling time has decreased by an impressive 98% (from an average of 10 hours per week to just 15 minutes), enabling staff to focus on more critical tasks.
• Faculty and administrators have praised the system for its ease of use and reliability.