IWC’S success in cost efective mobile applications

IWC’S success in cost efective mobile applications

Jeff Dieterlen
Manager Planning & Systems Development
Indianapolis Water Company

Charline M Avey
Manager, Research & Development Utility Data Corporation
1220 Waterway Blvd PO Box 1220 Indianapolis, Indiana 46202

Summary
One of the nation’s largest water utilities has fully implemented customized Palm Computing applications using off-the-shelf software and hardware at minimal cost. Serving over 260,000 customers in central Indiana, IWC daily uses Palm devices for hydrant, valve testing and repair and field service customer work orders. Field personnel with minimal computer experience have gone into 100% production within hours of training with simple, effective Palm solutions. Over the past year, field data accuracy has improved, obsolete equipment has been replaced and redundant paperwork has been eliminated.

Automating field activities is the “last mile” for utilities in their effort to have information on facilities and services from cradle to grave. Including your field employees in the process of data collection benefits the whole organization. These employees are your subject matter experts for your facilities and customers. They know the most and if given the tools can accurately update your systems. The Palm platform is flexible and robust enough to handle most field processing needs. Two features separate it from all the other handheld platforms. One is the “hotsync” process. The Palm OS has the intelligence built in to recognize changes in the handheld/desktop data and assures that each environment is kept current. The other is battery life. Applications created for the Palm are much simpler to create because the operating system has taken care of the complexity of staying in sync with your desktop applications.

IWC, with the help of Utility Data Corporation, has implemented one additional field application for Customer Service and is in the process of creating three more for other distribution work orders. UDC has also developed a stand-a-lone customer service work order system called “Field Director” that includes Palm work order capabilities.

Additional applications are being planned with ongoing enhancements that include wireless two-way communications with corporate customer service and GIS/mapping databases to allow real-time updates to maps, status reports for managers and service representatives.

Getting Started
Since 1999 Palm PDA’s have being used daily by 30 field employees at IWC. Approximately 160,000 distribution system work orders and 69,000 customer nonpayment work orders have been processed. No data has been lost through hardware failure, and only three units have been replaced because of breakage. The employees were trained in-house for 1 hour and given Palms with demo data over a weekend. On Monday, they were given a brief training sheet and sent on their route.

Most of the employees had little or no computer training prior to using the Palm device. A nonpayment form was created to be similar to the computer card order; however, enhanced interaction with the serviceman allowed greater information flow and eliminated errors due to incorrect marking. The distribution system order forms were greatly enhanced due to the ability of the developer to work with the department heads to revamp the type of information collected in the field.

Software used to develop the Palm applications was Pendragon Forms developed by Pendragon Software Corporation. This software uses Microsoft Access and allows rapid application development as well as the conduit for communicating between the Palms and backend databases. These databases can reside in Access as well as most ODBC databases. One feature of Pendragon Forms is that it can import the structure of database tables to create an initial form. The initial applications used Access on the desktop to query and analyze the data. Several manual reports were eliminated for both departments because they now had access to the raw field data in the Access database. The report creation function was moved to the employee managers due to fact that many of the managers were familiar with Access.

This success led to a much more ambitious project for IWC’s Customer Service Department. The application automates fieldwork order processing for all work preformed by 20 servicemen. The scope included integrating the Utilities new customer information system UtiliSuite© developed by Utility Data Corporation and the company wide work order management system Cassworks© developed by RJN.

Both systems are stored in Oracle 8i databases residing on Microsoft Windows 2000 servers. Pendragon Forms is used for Palm forms development, Pendragon Distribution Toolbox for providing unlimited forms to unlimited users and Pendragon SyncServer for wireless communication through the company web server. All palms can hotsync across the LAN as well as hotsync by PC modems from remote locations or Palm modem. Several other applications are used to ease the setup of the 30 Palm V, PalmVx and Palm M505.

Distribution System Applications
The first application to be built was the hydrant testing form. The utility was using mark sense cards with a very old card reader that had been out of warranty for several years. The most recent breakdown required a part to be brought in from Puerto Rico. In addition, the hydrant testers would sometime get the cards wet causing them not to be able to be read by the machine. Something needed to be done to replace this process. At the time several office employees were using Palm Pilots as personal data assistants. Investigation found that several software tools were available as well as specific hardware to address the necessary field orders. An analysis was performed and the Palm platform and the Palm V was chosen because of its ease of use, minimal training requirement, its metal case and inexpensive implementation costs.

All paper work orders were collected and the department manager identified the information that they wanted captured. A mainframe database and a GIS database were analyzed and a single datasource was created as a basis for the new system. Several additional database tables were created to support district scheduling, Palm assignment, and data validation for most fields. This single datasource was used to develop the initial form. The hydrant testing form has 35 fields of information, which includes:

Map Seq, Hydrant Make, Location, Attempted To test, Testers Name, Tested which Nozzle, Opening Valve Size, Was Visited: (which removes record from Palm), Hydt Tests Okay, Unable to test, Nozzle Test Response, Bonnet Test Response, Upper Barrel Response, Internal Assembly Test Response, Lower Barrel Response, Obstructions, Cap, Add Extension, Remove Extension, Vandal Proof Response, Painting Required, Misc Notes, MAP, Test Sequence, Year Installed, Valve Indicated, Branch Size, Branch Length, Vandal Proof, Hydrant Status, Last test Date, Last Test, Key;

As the tester fills out the record he is prompted for the correct response. The form has a field that timestamps the record. This feature has helped the utility on many occasions. The forms are very easy to change and if necessary the lookup lists can be linked to backend databases for dynamic changes. As many as 700 hydrant tests can be loaded on the Palm V’s 2mb of ram.

The tester does as many as he can during the day and when the Palm is hotsync in the evening, the tests that he has completed are removed and the databases are updated. If more have been assigned, they are added to the palm through the one sync station located in the report room. The database is located on the LAN so managers in several locations can schedule work to a specific palm no matter where the palm is synced.

This same process happens for other types of work. Palm applications for hydrant repair, valve testing and main flushing are on the same palm. Should the field service employee be called to perform one of these types of orders he just has to open the form and add the requested information. These records are matched with their desktop database record and updated or a test/repair transaction is added to the database. Should the employee be wirelessly connected this update/add could happen within moments of the event. Calculations within applications are handled with ease due to Pendragon Forms powerful scripting language. For example, the flushing application required calculation of the water used based on a reading and time.

Customer Service Applications:
The same antiquated card reader was being used for customer non-payment work orders. A palm application was created to process these orders. The requirements were similar with a few differences. The orders were created each day on the former customer information system residing on an IBM mainframe. A text file was created and imported into an Access database specially created for the palm scheduling.

Queries were created in Access to create the export file that was uploaded to the mainframe for database update. In addition to the standard lookup fields for field response, the form required a signature field for the customer to sign. The palm development software includes this type of field. The nonpayment orders were routed with other types of orders printed on paper forms. Most customer work orders were computer routed giving each order a route and sequence. The orders sent to the palm were sorted using this sequence making it easy for the servicemen find the service locations. A hotsync station was placed in the data processing room and each evening the routing was done after the payments were applied to the customer information. Packets were created each evening that included the palm, route sheet and the other paper orders. These packets were handed out the next day for field processing. Data sent to the palms included:

District Seq, Service Address, Delq Amount, Priority, Cust Name, Mtr Num-Size-Make, Mtr Loc-Direction, DOG, Attempted:, Action, Order Complete, Cust Signature, Comments, No Charge, Disc Date, Trouble Code, Bill date, Account Number, Box Measurements, Srv Remarks, Mtr Remarks, DMAP, Mtr Type, Current Amt, Total Amt, acct_map, CITY, Irrigation Cd, Order ID

Reports were created against the updated information in the Access database to manage completed orders by the collection department. Again the Access database resided on the LAN giving field service managers as well as the collection manager access to the updated information.

Having the Access database available to the managers allowed capturing additional information in the field without changing the mainframe database, which was due to be replaced.

This system had been in production since November 1999 and has since been replaced by a new work order system that field processes all work orders on Palms. This new system integrates data from two new information systems to create the record for field processing. Several tables and programs were created to manage the data requirements. The data necessary for field processing of 90 different types of work orders required developing three linked forms. Lookup lists for field responses needed to be dynamic and easily changeable. The palm development software provides capabilities to link lookup lists, which are actually mini-forms to back-end databases. This allows any manager or supervisor with access to the database, to make changes to look-up lists so the next time the Palm is synced, the new information is there. These dynamic look-up lists include employee lists, work order types, action codes, and correct condition codes. Several forms not directly needed for work order processing were created to provide instructions on what is expected for each order and 12 month listing of meter readings for each account that has a work order on the Palm.

The challenge when creating such a complicated system was to make the palm process flow simple. The system we call “UtiliField’ consists of a form for keeping information about the day such as the date, employee, route name, shift, type of work, beginning mileage, ending mileage and vehicle number. The next form holds the general information for each work order. Fields include:

Work order number, address, work order code, description of work required, detailed instruction from the order taker, meter serial number, meter make, meter size, time requested, order sequence, delinquent amount, customer name, customer phone number, special tools required, special handling, dog, meter reader remarks and site id.

The third form adds more information about the customer site and is the form where most update fields are kept. Because different types of orders use the same system of forms this form, has 90 different fields. Navigation is made easier with jumps created for each of the major sub-groups of servicemen and the data areas they use most. One jump takes you to the area that is used to put in meter readings, another for meter installation and another for new customer service installation. Every effort has made to minimize typing on the palm. Look-up lists provide the correct response easily. Comment fields are used by the servicemen to communicate additional responses that need to be added to these lists. Several work orders require long responses, for these keyboards are used.

A board was made to Velcro the Palm and keyboard together to facilitate their use.

A fourth form has a one to many relationships to the previous form and tracks the activities the serviceman performs for each order. It also reports the conditions the need to be corrected at the customer site. These actions and correct conditions are used to create billable events and referral work orders. An example of a completed work order would have these responses from the palm:

Work order type: Turn on reoccupy

Work order status- complete, Visited on 11/05/2001 9:00:23, Meter reading, 456632, action code- 195 angle valve found off turned on, 83 compression valve tightened, correct condition- 82 dog

All work order forms can accept new records by the servicemen in the field. A work order is created in the Cassworks Work Order Management System, where information about the customer is populated from the customer information system and routed to the serviceman by e-mail to their pagers. The serviceman adds the work order record into the palm. He only has to put the work order number into the palm and completes the order just like those that were loaded that morning. When the palm is synced the responses update the work order record that resides in the Cassworks system using the work order number as the unique key.

At this point the palms are not wirelessly connected; however, when modems are added to the palms units, the order will be assigned to the serviceman the next time a hotsync is performed. The steps for a wireless hotsync are the same as desktop. The serviceman presses the hotsync button or icon, the updated work is sent to the database and the newly assigned work is downloaded.

Training for this new system was more extensive due to the increased amount of information we were asking the servicemen to enter. The forms were loaded with demo work orders and short sessions of no more than five servicemen were trained. Printed documentation was created using software from Palm called the Emulator to show the processing steps.

This is a small section of the documentation and you can see what the Palm screens look like.

Benefits
Using palms for fieldwork processing is saving the utility the amount of time represented by three positions in Customer Service and Distribution. Prior to the using palms, clerks or data processing personnel did update of data. Work order creation in many cases, was manual. Developing reports of work complete was manual. Turnaround time of data was days and in some cases weeks. On many work orders the office reviewer needed to create a return trip because the paperwork was unreadable or incomplete.

Having all the information in digital form allows the manager to see patterns of problems and responded to them. Because all work orders are created in a software system, orders are not lost as paper orders often were. Referral work orders are created for incomplete work or no-time work so that staffing levels can be developed. A management system is being prototyped with Access to show the number of work orders being scheduled for upcoming days as the call center and others, an impossible task with the previous paper system. More huge benefits are being anticipated by capturing the serviceman’s activities while at a customer site as well as the time of day. Customers calling the call center can be given this information. Systems are in place to automatically e-mail status of work to the customer should they request it. Palm hardware and software has had a significant impact on how the utility manages fieldwork. Using palms along with information system integration has enabled improved information flow, improved information quality, reduced service costs and increased productivity.