California has one of the largest transportation infrastructures in the United States, spanning 58 counties along more than 15,000 miles of state highways. Busy roadways mean constant upkeep and expansion needs, all of which are performed by the State of California's Department of Transportation (Caltrans). Each Caltrans project — whether the addition of a bike lane or the seismic retrofitting of a bridge — passes through hundreds of hands between concept and completion. Staying on top of each step of every project is not only critical for saving taxpayer dollars but it also ensures the safety of California's transportation infrastructure. After numerous attempts to reconcile data on hundreds of projects involving everything from Microsoft Access and Excel to email and hand–delivered reports, Caltrans' North Region has found a winning solution with FileMaker Pro, the database software from FileMaker, Inc.

Business Challenge

Caltrans' North Region, which encompasses twenty–two counties and 4,300 centerline miles of state highways, had trouble keeping track of all its projects. Data was tucked away in mainframe systems dating from the '60s and '70s or project workplans scheduled with XPM (Expert Project Manager) to which only project schedulers had direct access. The XPM system—still in use today—was strong in some realms but lacked an effective front end for viewing its data. Michael A. Scott, North Region Program Project Management, compares XPM to a television without a picture tube. 'It's great if you're inside the TV receiving the data directly, but for the 99.9 percent of us outside, it's about as user friendly as stubbing your toe.'

Project data was made available to Project and Functional Managers in the form of hundreds of printed reports every month. Regardless of the report format or mode of delivery, the reports generated little feedback from the Functional Managers. As a result, the data in the XPM scheduling files and on the mainframe wasn't in synch with what was being done in the real world.

Successful Solution

Initial Beginnings
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.'