Every Caltrans project has a Workplan consisting of who will do what, where, and within what timeframe. These Workplans are fully resourced project schedules for work planned and needed to be updated with the current status of work accomplished and resources expended as well as other project data stored in various databases. The initial solution, conceptualized in 1996 by North Region XPM Administrator Kim Schutz and developed in 1997 by Landscape Architect and FileMaker whiz Stephen Reader, involved manually importing the workplan data from XPM into FileMaker with calculations used to extract data from other sources as well. Although the system worked at first, everything fell apart when it came time to add updated XPM data. 'Two weeks later,' Scott groans, 'when you needed to update it, it was like pulling teeth.'
Early in 1998, upper management decided it was time to put the cross–platform database tool FileMaker Pro, on everyone's desktop and use it to interface automatically with XPM data. The goal was to enable people in the field—not just project managers—to easily find out what work was assigned to them, how many hours they had to do it, and how many hours they'd already charged to date. They also wanted people to be able to see who else was working on the project and what those people were responsible for completing. It also had to be fully functional on either a Mac or PC.
Although people were finally able to access the data from their desktops, the complex calculations and amount of data was stored in one flat file that was so huge (700MB) that 'you'd sit there and watch it spit up one line of data every couple of seconds,' as Scott laments. The data was online, but it still wasn't user friendly enough. In fact, nobody could use it.
The reason what we're doing is working is because it was developed from the bottom up using the tool that's on your desktop. It's the first tool that people have had that lets them get all the data they want.
Michael A. Scott, North Region Program Project Management
The situation improved when, at the beginning of 1998, Management formed a Workplan Status Team, combining five people with varied skills into an innovative powerhouse — Steven Reader, Kim Schutz, Michael Scott, Wendy Wilson, and Tim Morris. Given the challenge of fixing the system, Scott, a longtime FileMaker fan, began converting the one file into 15 to 20 related files, the largest of which was 30 MB. That was a Friday afternoon in April 1998. By Monday morning, he had a fully functional system. Since then it has expanded and has four components: a workplan statusing tool that produces accurate project workplans and workplan agreements, an assignment database for division managers to assign projects, a project initiation database for program advisors, and a reporting database that produces standard reports and project summaries for Functional Managers. To date, there are approximately 500 copies of FileMaker running throughout the North Region.
How Complex is Complex?
Caltrans projects are broken down into 564 different codes—the first is concept, the last is contract acceptance. Projects range in duration from five hours to 34 years. Each functional group working on the project is assigned a number. In the North Region alone, there are approximately 160 different functional units, plus another 130 in the engineering service center. If all of the North Region's 300 units are working on it, you multiply those 300 units times the 564 project codes—giving you 169,200 for one project alone. 'Then you multiply that times a thousand projects,' Scott explains. 'It gets really complicated in a hurry.' With FileMaker as the front end, all the system complexity is completely hidden from end users. They open one file, use one password, and have instant access to data from between 60 and 80 different files located in FileMaker, XPM, and mainframe databases. 'The reason what we're doing is working,' explains Scott, 'is because it was developed from the bottom up using the tool that's on your desktop. It's the first tool that people have had that lets them get all the data they want.'