October 29, 2012 By Wayne Hanson
In Issaquah, Wash. -- a city of about 30,000 that's 17 miles southeast of Seattle -- the Public Works Department handles water, sewer, storm water and streets, and all of their maintenance and repair requests. To improve upon its process for handling these requests, the department is implementing a new system that will soon allow real-time maintenance and repair records, updated via mobile devices to a back-end database.
The city is replacing the previous system -- a DOS database updated by paper work orders -- with a Microsoft SQL Server database linked to Motorola MC75 handheld field units. "They are hardened handheld computers, PDA-style devices, and they have built-in barcode scanners, GPS and cameras," said Bret Heath, director of Public Works Operations "They run Windows mobile software." The devices also contain the city's Standard Operating Procedures, as well as infrastructure location maps.
At around $2,000 each, the Motorola units, which have docking capabilities and use cellular connectivity, are the most expensive part of the system, Heath said, adding that he tested a number of devices by dropping them and leaving them out in the rain -- and the Motorola units passed every test. "These things are in the field with field guys," he said, "and that's a hard environment."
Heath said the parts inventory is all bar coded, as are all field infrastructures such as street signs, fire hydrants, valves and catch basins. The city is currently populating the database with parts and infrastructure.
"To get from the mobile device to the back end, we use Webalo," Heath said. "We built the database, and we wanted to be able to connect with the mobile devices through a software/hardware system that would allow us to make modifications on the fly without going back and paying for programming time for somebody to change their program."
Though a number of software products would have worked, Heath said, very few actually allow the user to tunnel all the way back to the database and set things up, making the connection yourself without any programming. "The Webalo product lets you go from your mobile devices, into the server and into your back-end database using SQL code to query the database," he said. "So if we make a change in the database, we don't have to hire the vendor to go back in and reprogram their software. We just reconfigure the connection and it's up and running on the mobile devices."
When people call or report via the Web that they have a pothole, for example, that information is loaded into the SQL database. A work order is created and then made available to all city computers and mobile devices, as the SQL server database has a Web browser interface. The technician performs the work, and then records, via the mobile device, what was done, parts used and time required to complete the work. The work order is then closed out, and is verified by the manager as completed.
"It's a pretty straightforward system -- instead of taking a piece of paper and filling it out, you just take it on the mobile device," Heath said. "What we're looking forward to with this is everybody will have real-time access to all of the work orders, and we'll know exactly what was done on what day, and we don't have to chase paper down, which has been a problem. Someone has to turn it in, it has to get entered, and there are time delays there. So with the electronic system … it should all be at or near real time. And that's our goal here as we unroll things."
Heath said that except for larger jurisdictions that may use Hansen software or Oracle, he hasn't seen too many electronic systems; most still use a paper-based system. Currently there are about 30 mobile devices in the field, and the city is still "ironing out the kinks," as Heath termed it. He said that some technicians dive right in and adopt the new system, even making suggestions on how to improve it, while others are struggling a bit because that technology isn't something they use in everyday life.
"Right now we're collecting field data," said Heath."Getting all those fire hydrants, catch basins and signs entered into the database so that information is there and can be run against the work orders. And it links the data so if you want to see how many times this stop sign was hit, the work history is there as well."
The city will roll out a new website around Thanksgiving that will better capture reports from the public and speed the production of work orders, Heath said. And by the first of next year, valid Web reports will create work orders directly.
"The bottom line is we're just trying to help everybody be more efficient, and to get real-time reporting and not have to pass paper around -- have the guys in the field enter in exactly what they did," Heath said. "Right now a lot of it is freehand, so we're creating default dropdowns so they don't have to type in what they did, they can select it from the menu."