Loadit Unravels School Timetables -- A Case Study!
16 July 2019
You’re back from your long summer holidays – refreshed and eager to start learning. The bell rings and you look down at your timetable to find your first session. Only to discover there’s nothing there!
You look around you and see that everyone else is just as confused as you are – there is no timetable. Teachers and pupils are wandering around with no clue about who is supposed to be where – total chaos!
At this time of year, while we’re all looking forward to summer holidays filled with sun drenched beaches, schools are already planning for the return of students after the holidays, including, critically, making sure there is a timetable in place for when the students return.
One such school is Berkhamsted School in Hertfordshire, UK, who we have been working with lately.
They came to us with an interesting challenge – how to import their school timetable data (from an Excel Gantt chart) into their iSAMS software (iSAMS is the market leading school management information system). Sounds simple, right?
However, the timetabling requirements for Berkhamsted are complicated. The school uses a “diamond formation”, which means girls and boys are taught separately at middle school level (11-16 years), but together at prep school (7 - 11 years) and sixth form (16 years up). The school is spread over five sites - four in the town of Berkhamsted itself and one in another nearby town, Amersham. Multiple locations means that the timetable must allow teachers time to travel between sites and also to factor in separate lessons for girls and boys.
So, the timetable is created as a detailed Gantt chart in Excel that looks something like this:
Note: The colours in this chart have a relevance: Green means Girls school, Blue means Boys and Grey means mixed.
The information in the Gantt Timetable needs to be converted into a format that can be imported into iSAMS, looking like the example below:
Ordinarily this would be a really difficult challenge, involving lots of manual work or the writing of some dodgy VBA code. However, using our Loadit data automation tool, the task was a breeze.
We created a simple Loadit package to automate the process, with just four key steps, that looked like this:
Load Gantt Chart
This step utilizes the “Excel Gantt Chart” data source in Loadit.
This module knows how to read Gantt charts created in Excel, and can extract tasks by looking at the colour coding of each block. The data output from the first step looks like this:
Using the “Derive Columns” transformation in Loadit, we then derive the columns we need from the source columns read from the Gantt chart.
- Perform a vLookup on Task Start to derive a PeriodId column
- Perform a lookup on Task Colour to determine whether the class is for boys or girls
- Perform a Regular Expression match to extract Room, Subject and Year from the Task Name.
- Concatenate year, subject and girls/boys to derive SetCode
Using the “Adjust Columns” module built into Loadit, we can reorder the columns into the order required for importing into iSAMS.
Export to Excel
Lastly, we use the “Excel Target” module in Loadit to write all the data out to an Excel file ready to import into iSAMS.
And that’s it! A potentially awkward and time-consuming task tamed and handled in minutes.
Prad Samtani, Director of IT at Berkhamsted School said:
“Loadit is an invaluable tool which has saved us a huge amount of manual effort at one of our busiest and most high-pressure times of the year – we can’t imagine life without it!”
If you’d like to know more about how Loadit could help you massively improve simple or complex data management and automation projects, please drop us a line – we’ll be glad to set up a demo!
Back to Blog