To create a web-based platform to track approximately 10,000 (that’s right, THOUSAND) interdependent production dates and deadlines for a major publisher. The client had previously been using an Excel spreadsheet to manage the production schedule, with many individuals constantly inputting date adjustments, delays, etc., and the remainder being driven by cell formulas. Many many cell formulas.
The objective here was to get rid of this horrendous Excel file, aptly called “The Monster Spreadsheet”, and replace with with a platform that could be managed via the web, and which provided the kind of functionality one would expect of such an application. Oh, and make this massive application work flawlessly in Internet Explorer 6.
Getting this project off the ground also required that a few conditions be met:
- A platform needed to be written to capture the flexibility of spreadsheet formulas.
- Existing data, all 10,000 or so formulas, needed to be automatically imported from the Monster Spreadsheet, into the new database.
The application we developed is a testament to the ingenuity of our development staff and the PHP wizardry of Adam, our DBA.
What you see above is the main application screen, as visible to employees. It provides:
- A news feed of recent date changes affecting the user’s own production group (none are visible here).
- Instant on-screen filtering of dates based on user’s own context, and text filters.
- Custom user views, allowing each user to select visible content by date group type (e.g., Publishing Dates), region, etc. Accessible via the little “+” button you see at the right.
- Single-click Excel spreadsheet download of filtered dates. Something employees can hang on a wall once their part of the schedule has been locked down for the year.
- Optional grid, daily roll, or printable calendar views, to present dates in formats that are usable to a wide range of actors within the enterprise.
- Colour and glyph-based grouping of dates, allowing the user to quickly scan to find those most relevant to their work.
- On-hover popups, displaying all products referenced by a particular event.
Under the Hood
The platform itself, features:
- Interdependent, formula-based calendar events, employing our own Scheduler Formula Syntax Parser for date and relationship representation.
- Automatic weekend and national/regional holiday avoidance, dependent on responsible production group, and independent work policies.
- Event grouping into affected departments, and roles-based permissions for viewing and editing of events.
- An interactive, web-based, date modification portal, instantly showing events affected by changes to their parents and siblings, and the ability to exclude, include, or adjust the resulting schedule according to either automatic rules, or manual ‘offsets’ decided by managers.
- Complete system-wide logging of all modifications, tracking user ids, and timestamps. In fact, the data layer was written in such a way as to automatically capture any activity and permanently log it for audit purposes.
- A sandbox environment, allowing managers to test the effects of event date changes on processes up and down the production stream.
- Year-to-year data replication, allowing for painless recreation of the prior years schedule, to generate and plan for subsequent years of use.
The Import Process
Importing 10,000 Excel sheet cells into a a completely new platform, preserving their relationships, required that a an Excel parser and translator be written. Once this had been completed, all 10,000 cells were interactively scanned for human error, manually corrected by dedicated staff and, on the 17th attempt, the entire set was imported into the new platform. The original Excel event formulas have been automatically translated to our own syntax.
Finally, no enterprise application is complete if it does not satisfy the desire of one department to keep information from another. This is why we had to make sure that only designated individuals were granted rights to view and to edit calendar entries and entry groups. We accomplished this with strict, database-level, relationships between data and user roles. In addition to this, the application features a user manager, capable of complete user management and rights assignment.