- It's up to California's Department of Transportation (Caltrans) to keep traffic rolling in California -- a big job, since California has one of the largest transportation infrastructures in the United States. The job is simplified now that FileMaker Pro is in the driver's seat at Caltrans, tracking hundreds of projects ranging from maintaining highways to seismic retrofitting of bridges.
Caltrans Finds Smooth Driving
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.
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.
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 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.'
The Workplan Status program has certainly generated a lot of interest among its users. People have taken ownership of their data now that they can finally see what it is. Moreover, the people doing the work have a say in what numbers the project managers generate; previously, the two groups operated almost autonomously. 'Basically,' Scott says proudly, 'we now have two-way communication, whereas before we didn't have any.' Now that everybody can get into the system from their desktop, they can eliminate discrepancies between management goals and realistic projections.
The system works so well that other Caltrans regions are looking to implement it as well. Original estimates were that it would cost $1.7 million and take until March of 2000 to get the same system up and running in another region. However, Scott's team has been able to reduce that estimate considerably. They can load an entire region's data onto their servers in two days and have the staff trained and files fully converted within a month.
The system's success might not be immediately evident to the average California taxpayer. People might still be slowed by those telltale Caltrans orange cones and the occasional blocked lane. But now that the North Region's right hand knows what the left hand is doing, you can rest assured that critical roadways, bridges, and overpasses are maintained with greater precision and efficiency than ever before.