| GISdevelopment.net ---> GITA 1998 ---> User Perspective |
Trouble-call Management: An application solution
John P. Ohlson
Tacoma City Water
3628 S. 35ti St., PO BOX 11007
Tacoma, WA 98411-0007 USA
John P. Ohlson
Tacoma City Water
3628 S. 35ti St., PO BOX 11007
Tacoma, WA 98411-0007 USA
Abstract
One of Tacoma City Water’s goals is to respond to all incidents of water quality problems within the same business day. To support this goal — and to respond to other types of trouble calls — the utility needed to improve the processes for dispatching trouble crews, providing uniform incident response, tracking responses and collecting data for statistical analysis.
A trouble-call management database was developed that streamlined the paper and radio dispatching process and provided on-line help so customer service representatives and dispatchers could provide uniform responses and track response activities. The on-line help program provides response instructions for various types of incidents, and time-stamps each step in the incident handling process, enabling the response time to be captured. Additionally, crew work details are captured to record who did what and when. The type of water condition and location of the problem also are recorded, so information can be analyzed to identify problem areas in the system.
Introduction
Tacoma City Water has a strategic goal to respond to all water quality complaints by the end of the business day. The paper-based system of assigning work to trouble crews was not doing a foolproof job and allowed some complaints to languish at someone’s desk while the problem remained unattended. To further complicate matters, a particular type of call could be handled differently depending on which person handled it. To alleviate these problems, and to capture statistical data to support future decision-making, City Water decided to automate the trouble call recording and response process with a computer-aided dispatch database developed by an in-house programmer.
This database helps track the status of water quality complaints and records. the timelines and crew work details performed on the job. A custom on-line help system contains procedural guidelines and policies for handling the different types of incidents and system problems that may arise. As a result, managers can expect office and field staff to respond in a more uniform manner, rather than “flying by the seat of your pants.”
The purpose of this paper is to look at the processes involved in developing the database and application, including:
One of the main drawbacks to the old system was the way the information was handled. In most cases, a call was received in the Distribution Center and recorded on a paper document called an “X-Order.” (Why it is called an X-Order has been lost to time, but apparently this term came into being way back in City Water history.) The X-Order was given to the dispatcher, who called a crew and assigned the job. If the call was received somewhere other than the Distribution Center, call details were written on a notepad and the information relayed to the dispatcher by telephone, where an X-Order was written and a crew assigned. In either case, the X-Order was then placed in the supervisor’s in-box and handed to the crew at the end of the day. Carbon copies were kept of all X-Orders at the dispatcher’s desk as a method of tracking unfinished jobs.
Once the crew completed its work, workers would make some notes about what they did on a scrap of paper. These notes were then transferred to the X-Order. The supervisor would review what the crew had written and assign another crew to do follow-up work if necessary. If another crew was required, the X-Order was routed to the next crew for completion, and so on, until the job was completed. The completed X-Order was given to the dispatcher, who paired it with the carbon copy and filed it for future reference. One of the many problems with the old paper system was that an X-Order maybe stuck in the paper shuffle for several days following completion of the work before a supervisor or dispatcher would become aware that follow-up tasks were necessary.
Strategies to meet the work requirements
A water quality complaint, if handled improperly, had the potential to create a public health hazard. It became clear that the old system was deficient and City Water needed a better way to respond to and track water quality complaints. A timely response to a taste and odor complaint could prevent a major system incident. City Water decided to require a response to all water quality complaints within the same business day. One of the main tasks of the new system would be to ensure this goal was met.
One of the main requirements of a new system was to keep track of important jobs without having to shuffle paper or check piles of X-Order copies to find something. Another requirement was to have the ability to cut down the number of times information was written and copied from one source to another. This was accomplished by having a central data entry point for all calls and by connecting the dispatcher to this data store.
In the past, procedural standards and policies were committed to memory. Depending upon the situation or who was involved, the same type of call could be handled in several different ways. To bring a measure of consistency to the utility’s responses, a new system was required to contain guidelines and procedures for handling various types of routine calls and water quality conditions.
Finally, data were needed to analyze the trends of unsuitable water quality in the system and to allow engineers and operators to make system adjustments in response. This made the case for using a database to store the call data, enabling decision-support querying and analysis.
Building the database
To capture the details of a call and also allow for one or more crews to respond to the same call, a primary or “parent” table was created to store the details of each call. These details include the location, type of call, date received, a description of the problem and other location-specific data.
A secondary “child” table was created to store the crew’s work details, such as the names of all the parties involved in the dispatch and completion of the work; the actual tasks involved and the date and time stamps for each phase of the work. The two tables are related by using the trouble call ID number as the primary key.
There was also a need to categorize the type of water quality condition from a fixed list of conditions. City Water identified common conditions such as brown color, chlorine taste and rust particles. These data are chosen from a lookup table called WATER_QUALITY_TYPES.
Since follow-up calls with the customer could be required, a FOLLOW_UP table was created to store the details of the follow-up calls.
The client server architecture allows multiple users to log in and enter calls to the system, regardless of location or work group affiliation. Because the City of Tacoma’s local area network extends to most City Water offices, anyone can enter calls directly into the database, eliminating the transfer of data from paper to person to database. Users in remote locations can use dial-in connections and access the database as well.
Window navigation and design
Simple, intuitive point-and-click methodology was employed in the design of the application windows. A combination of command buttons and clickable record selectors allow the user to navigate within the application. For example, selecting a trouble call record from a list and double-clicking the row opens a window showing the work details window for that call.
The Dispatcher’s Screen
Figure 1 shows the dispatcher’s screen. All pending calls are displayed in chronological order from the latest call to the earliest call. When a new call comes in, it is automatically displayed at the top of the list. The information displayed includes the type of call, the responsible workgroup, when it was received, the address, call status, and the number of days elapsed since the call came in. The screen refreshes every 90 seconds looking for new calls and displays an audio and visual announcement when new calls have been entered into the system.
To access a trouble-call record, the dispatcher simply double-clicks on a record to open the work details screen.
Figure 1. The dispatchers window. Double clicking on a row opens the work-details window
The Work-Details Windows
Figure 2 shows the work-details window. This window contains all the information about a trouble call: the location, problem information, crew work details, water quality conditions and follow-up calls.
Figure 2. The work detail window. Double clicking the highlighted row will open a detail window for each crew. The command buttons also provide point-and-click functionality.
The work-details section contains a summary of the action required, the crew name and radio number, the assignment dates and times and the dates and times work was completed. Double-clicking on a work-detail record opens a window that displays the actual work performed and the job site arrival time and date. The dispatcher enters information gathered from the crew over the radio on this screen (Figure 3). This information describes what the field crew did to complete the task. It may also include strategic information about the job that would be useful to the next crew assigned, such as the position of shutoff valves or hydrants in a main break, or as in this fictional example, the number of worms found in the water.
Each crew assigned gets its own work-detai 1record, which stores only the work the crew personally performed. As additional crews are assigned, more work-detail records are added. There is no limit to the number of crews that can be assigned to a trouble call.
Figure 3. Work Details Edit Screen.
Water Quality Conditions
The water quality conditions section shows the selections made from the lookup table that describe the condition of the water. These categories are then used for statistical analysis to help identi~ chlorinating problems, areas that need flushing, etc. Having users select fiom a pre-defined list allows evaluators to use queries to find occurrences of specific water quality conditions more easily. Instead of searching for random occurrences of criteria such as “brown dirty color” within a freeform comment field, the category type code for “Brown/Dirty Color” can be used to accurately find all instances of this type in the database.
Follow-Up Calls
The follow-up section displays the follow-up calls made on a trouble call. Follow-up calls maybe accessed either by clicking on the follow-up command button or by double-clicking on the window itself. Each follow-up record contains the date and time the call was made, who made the call and what was discussed. There is no limit to the number of follow-up calls that can be made.
Currently, a water quality technician is assigned to monitor all water quality calls that come in to the dispatcher to ensure each call is handled in a timely and appropriate manner.
On-line policy and procedure system
In order to achieve uniform crew response to various types of calls, an on-line policy and procedure help system was created. This system stores the policies and procedures in a database table. These can then be accessed by selecting from a menu. A dynamic window displays the information based upon criteria in the menu selection (Figure 4). If a user chooses “Brown/Dirty Color” from the menu, the window would display the help topic for the “Brown/Dirty Color” subject. Subjects range from help on water quality issues to crew call-out procedures for emergency situations.
Figure 4. The dynamic on-line policy and procedure help screen
Deployment and testing
Because of the urgent nature of ensuring water quality, the traditional application development process for this project did not follow the usual design, test, and deployment timeline. Instead, the bare bones of the system was deployed as soon as it was ready to enable the call entry and monitoring process to begin. Additional screens and features were deployed as they became available. After a four-month- “trial by fire” test period, the database was moved from the test server to the production server. This approach is not recommended, although it did provide some challenges along the way and exposed many overlooked business rules that needed to be incorporated into the application.
While application code was written to enforce specific business rules, various reports were created to verify others. One was to provide a method for checking the “done” records to make sure that all required work details were complete. Another checked to see if all the follow-up calls had been made. The “overtime report” provides a summary of calls that took place after normal business hours and displays the work performed and timelines of these crews. This provides an “at a glance” view of all the pertinent details a supervisor needs to determine whether to pursue a particular call the next day.
Because of its lookup table-based design, the system is also adaptable to many different kinds of work, not just water utility tasks and call types. The lookup tables can be populated with data to meet the needs of any industry, and can grow with the business.
Summary
Using all of these on-line tools, a call can be handled entirely within the system without any paper ever being generated. If a paper record is desired, a summary printout that replicates the old X-Order form can be made that includes all details of a call. Supervisory personnel can also log in and review any call in progress and may also assign additional crews as needed.
The strategic water quality goal can now be automatically monitored using data from the system tables. The data are also now available to support decision-making. With continuous monitoring by water quality technicians, no serious water quality call is likely to develop into a major disaster. With company policies and procedures now available on-line, even the most inexperienced operator has the resources available to make the right decision.
One of Tacoma City Water’s goals is to respond to all incidents of water quality problems within the same business day. To support this goal — and to respond to other types of trouble calls — the utility needed to improve the processes for dispatching trouble crews, providing uniform incident response, tracking responses and collecting data for statistical analysis.
A trouble-call management database was developed that streamlined the paper and radio dispatching process and provided on-line help so customer service representatives and dispatchers could provide uniform responses and track response activities. The on-line help program provides response instructions for various types of incidents, and time-stamps each step in the incident handling process, enabling the response time to be captured. Additionally, crew work details are captured to record who did what and when. The type of water condition and location of the problem also are recorded, so information can be analyzed to identify problem areas in the system.
Introduction
Tacoma City Water has a strategic goal to respond to all water quality complaints by the end of the business day. The paper-based system of assigning work to trouble crews was not doing a foolproof job and allowed some complaints to languish at someone’s desk while the problem remained unattended. To further complicate matters, a particular type of call could be handled differently depending on which person handled it. To alleviate these problems, and to capture statistical data to support future decision-making, City Water decided to automate the trouble call recording and response process with a computer-aided dispatch database developed by an in-house programmer.
This database helps track the status of water quality complaints and records. the timelines and crew work details performed on the job. A custom on-line help system contains procedural guidelines and policies for handling the different types of incidents and system problems that may arise. As a result, managers can expect office and field staff to respond in a more uniform manner, rather than “flying by the seat of your pants.”
The purpose of this paper is to look at the processes involved in developing the database and application, including:
- Strategies employed to meet the work requirements.
- Window navigation and design techniques.
- Capturing the appropriate data for statistical analysis.
- Improvements to existing paper, phone and dispatch processes.
One of the main drawbacks to the old system was the way the information was handled. In most cases, a call was received in the Distribution Center and recorded on a paper document called an “X-Order.” (Why it is called an X-Order has been lost to time, but apparently this term came into being way back in City Water history.) The X-Order was given to the dispatcher, who called a crew and assigned the job. If the call was received somewhere other than the Distribution Center, call details were written on a notepad and the information relayed to the dispatcher by telephone, where an X-Order was written and a crew assigned. In either case, the X-Order was then placed in the supervisor’s in-box and handed to the crew at the end of the day. Carbon copies were kept of all X-Orders at the dispatcher’s desk as a method of tracking unfinished jobs.
Once the crew completed its work, workers would make some notes about what they did on a scrap of paper. These notes were then transferred to the X-Order. The supervisor would review what the crew had written and assign another crew to do follow-up work if necessary. If another crew was required, the X-Order was routed to the next crew for completion, and so on, until the job was completed. The completed X-Order was given to the dispatcher, who paired it with the carbon copy and filed it for future reference. One of the many problems with the old paper system was that an X-Order maybe stuck in the paper shuffle for several days following completion of the work before a supervisor or dispatcher would become aware that follow-up tasks were necessary.
Strategies to meet the work requirements
A water quality complaint, if handled improperly, had the potential to create a public health hazard. It became clear that the old system was deficient and City Water needed a better way to respond to and track water quality complaints. A timely response to a taste and odor complaint could prevent a major system incident. City Water decided to require a response to all water quality complaints within the same business day. One of the main tasks of the new system would be to ensure this goal was met.
One of the main requirements of a new system was to keep track of important jobs without having to shuffle paper or check piles of X-Order copies to find something. Another requirement was to have the ability to cut down the number of times information was written and copied from one source to another. This was accomplished by having a central data entry point for all calls and by connecting the dispatcher to this data store.
In the past, procedural standards and policies were committed to memory. Depending upon the situation or who was involved, the same type of call could be handled in several different ways. To bring a measure of consistency to the utility’s responses, a new system was required to contain guidelines and procedures for handling various types of routine calls and water quality conditions.
Finally, data were needed to analyze the trends of unsuitable water quality in the system and to allow engineers and operators to make system adjustments in response. This made the case for using a database to store the call data, enabling decision-support querying and analysis.
Building the database
To capture the details of a call and also allow for one or more crews to respond to the same call, a primary or “parent” table was created to store the details of each call. These details include the location, type of call, date received, a description of the problem and other location-specific data.
A secondary “child” table was created to store the crew’s work details, such as the names of all the parties involved in the dispatch and completion of the work; the actual tasks involved and the date and time stamps for each phase of the work. The two tables are related by using the trouble call ID number as the primary key.
There was also a need to categorize the type of water quality condition from a fixed list of conditions. City Water identified common conditions such as brown color, chlorine taste and rust particles. These data are chosen from a lookup table called WATER_QUALITY_TYPES.
Since follow-up calls with the customer could be required, a FOLLOW_UP table was created to store the details of the follow-up calls.
The client server architecture allows multiple users to log in and enter calls to the system, regardless of location or work group affiliation. Because the City of Tacoma’s local area network extends to most City Water offices, anyone can enter calls directly into the database, eliminating the transfer of data from paper to person to database. Users in remote locations can use dial-in connections and access the database as well.
Window navigation and design
Simple, intuitive point-and-click methodology was employed in the design of the application windows. A combination of command buttons and clickable record selectors allow the user to navigate within the application. For example, selecting a trouble call record from a list and double-clicking the row opens a window showing the work details window for that call.
The Dispatcher’s Screen
Figure 1 shows the dispatcher’s screen. All pending calls are displayed in chronological order from the latest call to the earliest call. When a new call comes in, it is automatically displayed at the top of the list. The information displayed includes the type of call, the responsible workgroup, when it was received, the address, call status, and the number of days elapsed since the call came in. The screen refreshes every 90 seconds looking for new calls and displays an audio and visual announcement when new calls have been entered into the system.
To access a trouble-call record, the dispatcher simply double-clicks on a record to open the work details screen.
Figure 1. The dispatchers window. Double clicking on a row opens the work-details window
The Work-Details Windows
Figure 2 shows the work-details window. This window contains all the information about a trouble call: the location, problem information, crew work details, water quality conditions and follow-up calls.
Figure 2. The work detail window. Double clicking the highlighted row will open a detail window for each crew. The command buttons also provide point-and-click functionality.
The work-details section contains a summary of the action required, the crew name and radio number, the assignment dates and times and the dates and times work was completed. Double-clicking on a work-detail record opens a window that displays the actual work performed and the job site arrival time and date. The dispatcher enters information gathered from the crew over the radio on this screen (Figure 3). This information describes what the field crew did to complete the task. It may also include strategic information about the job that would be useful to the next crew assigned, such as the position of shutoff valves or hydrants in a main break, or as in this fictional example, the number of worms found in the water.
Each crew assigned gets its own work-detai 1record, which stores only the work the crew personally performed. As additional crews are assigned, more work-detail records are added. There is no limit to the number of crews that can be assigned to a trouble call.
Figure 3. Work Details Edit Screen.
Water Quality Conditions
The water quality conditions section shows the selections made from the lookup table that describe the condition of the water. These categories are then used for statistical analysis to help identi~ chlorinating problems, areas that need flushing, etc. Having users select fiom a pre-defined list allows evaluators to use queries to find occurrences of specific water quality conditions more easily. Instead of searching for random occurrences of criteria such as “brown dirty color” within a freeform comment field, the category type code for “Brown/Dirty Color” can be used to accurately find all instances of this type in the database.
Follow-Up Calls
The follow-up section displays the follow-up calls made on a trouble call. Follow-up calls maybe accessed either by clicking on the follow-up command button or by double-clicking on the window itself. Each follow-up record contains the date and time the call was made, who made the call and what was discussed. There is no limit to the number of follow-up calls that can be made.
Currently, a water quality technician is assigned to monitor all water quality calls that come in to the dispatcher to ensure each call is handled in a timely and appropriate manner.
On-line policy and procedure system
In order to achieve uniform crew response to various types of calls, an on-line policy and procedure help system was created. This system stores the policies and procedures in a database table. These can then be accessed by selecting from a menu. A dynamic window displays the information based upon criteria in the menu selection (Figure 4). If a user chooses “Brown/Dirty Color” from the menu, the window would display the help topic for the “Brown/Dirty Color” subject. Subjects range from help on water quality issues to crew call-out procedures for emergency situations.
Figure 4. The dynamic on-line policy and procedure help screen
Deployment and testing
Because of the urgent nature of ensuring water quality, the traditional application development process for this project did not follow the usual design, test, and deployment timeline. Instead, the bare bones of the system was deployed as soon as it was ready to enable the call entry and monitoring process to begin. Additional screens and features were deployed as they became available. After a four-month- “trial by fire” test period, the database was moved from the test server to the production server. This approach is not recommended, although it did provide some challenges along the way and exposed many overlooked business rules that needed to be incorporated into the application.
While application code was written to enforce specific business rules, various reports were created to verify others. One was to provide a method for checking the “done” records to make sure that all required work details were complete. Another checked to see if all the follow-up calls had been made. The “overtime report” provides a summary of calls that took place after normal business hours and displays the work performed and timelines of these crews. This provides an “at a glance” view of all the pertinent details a supervisor needs to determine whether to pursue a particular call the next day.
Because of its lookup table-based design, the system is also adaptable to many different kinds of work, not just water utility tasks and call types. The lookup tables can be populated with data to meet the needs of any industry, and can grow with the business.
Summary
Using all of these on-line tools, a call can be handled entirely within the system without any paper ever being generated. If a paper record is desired, a summary printout that replicates the old X-Order form can be made that includes all details of a call. Supervisory personnel can also log in and review any call in progress and may also assign additional crews as needed.
The strategic water quality goal can now be automatically monitored using data from the system tables. The data are also now available to support decision-making. With continuous monitoring by water quality technicians, no serious water quality call is likely to develop into a major disaster. With company policies and procedures now available on-line, even the most inexperienced operator has the resources available to make the right decision.