When many departments adopted new scheduling rules, it became clear that significant work was needed to adapt the existing systems to the rule changes. It seemed an ideal time to migrate the three largest departments to a single new overtime scheduling application that handled all their diverse rules, was flexible enough to accommodate future rule changes and the addition of other departments later, and got the company out of legacy DOS apps that were becoming increasingly difficult to support.

Trilogy built a new application using an ACCESS database of about 40 normalized tables to encapsulate the business data about employees status, job vacancies, and schedule calendar information. All data entry is done in ACCESS. When the operator is ready to calculate the coming weekend's overtime assignments, a separate calculation engine (compiled VB provides a good balance of performance and maintainability) is called to do the actual overtime calculation. A variety of reports and calendar-based screens in ACCESS then give the operator visual feedback about the results. Powerful audit trail tools allow the operator to answer questions when the program's logic is challenged by union reps.

Since multiple departments needed support and scheduling logic might change frequently, we knew we needed to build a system not just to current overtime specifications, but flexible enough to accommodate a wide variety of scheduling rules without need for significant recoding. To accomplish this, we went a step beyond most custom systems and created a high-level scripting language within the application, stored as data for each department. The VB scheduling engine starts a schedule calculation by loading all the relevant information from the ACCESS database, and then loads the scheduling script that defines the rules for calculating overtime in that department. Three of the company's largest departments now use the system to schedule over 500 employees every week, and nearly all changes needed in scheduling logic have been accomplished with simple script modifications. Three more departments are currently considering adopting the system.

Many hours a week calculating overtime schedules by hand, and countless manual errors, have been eliminated. The union generally trusts the overtime calculations and can see quickly how assignments are derived using the system's logging tools. And every change to scheduling rules is no longer accompanied by the need for a major new programming initiative to adapt to the change.

> Return to Case Studies