Article: Backflow Prevention Compliance and Data Management

Responsible Backflow Prevention Program
Compliance and Data Management

by
Randy Engle, President
XC2 Software - EngSoft Solutions
© 2001-2006 Randy Engle
Presented at AWWA Annual Conference
New Orleans - June 2003

Abstract:

Keeping accurate records is an essential part of a successful backflow prevention program. Creating a software system to effectively manage the records can be a daunting process. It is essential to allow the software system users to get the information entered quickly and accurately.

The interface and functions for such a backflow prevention software system must be well thought out. Successful backflow data management systems have several key elements that will take some significant up-front investment in time and perhaps finances, but will easily pay off over the years if done correctly.

Purpose of a data management system for a backflow prevention program:

To keep backflow incidents from happening by knowing that the proper backflow prevention assemblies are in place where they need to be and that the assemblies are functioning correctly.
To better allocate resources in order to achieve purpose #1
To provide summary documentation for state agency compliance purposes

Besides maintaining records for existing backflow prevention assemblies in active service, a thorough backflow prevention data management program will maintain records for the following:

Existing backflow prevention assemblies in inactive service locations, e.g. vacant buildings
Locations where a backflow prevention assembly is required but not yet installed
Due to a survey or other inspection of the site
Due to new service requirements
Due to retrofit or replacement requirements
Locations where it has been determined that a backflow prevention assembly is not required
Locations where a backflow prevention assembly has been removed due to a change in type of service

Facility management:

There may be more than one backflow prevention assembly at a single location or “facility”, e.g. domestic service, irrigation and fire service. As well, a single owner may have several locations in the same city. To effectively manage these situations, a mechanism to group the backflow prevention assemblies needs to be established so that the same facility/owner data is not entered more times than necessary, as well, this will keep the names of the group “normalized” for purposes of searching and sorting.

Other entities that need to be maintained:

On-site contact information for the sites listed above
Mailing/owner address tables to store information of the parties responsible for the sites listed above. It is possible that one owner or one customer of record may be responsible for multiple sites.
Certified Backflow Tester list
Certification number certification expiration dates
Backflow Test kits and their accuracy/calibration status
Licensing information, business and/or professional
Insurance information

Resource/Lookup tables:

All information entered into the system that is going to be queried and/or summarized needs to have some kind of control established so that data is not entered in multiple ways. These tables may include the following:

Backflow Assembly Types
Backflow Assembly Sizes
Backflow Assembly Manufacturers
Backflow Assembly Models
Backflow Test Kit Models
Backflow Test Kit Manufacturers
Protection Type
Backflow Assembly Status
Test Result Status
Hazard Conditions
Hazard Levels
Schedule Code Scheme
Service Types
Cities and Zip codes
Activity/Event Types
Geographical Category, e.g. “Map Page, Region, etc.”
Street Names
Street Prefix, e.g. W, E, S, N
Street Suffix, e.g. Blvd, Rd, Ave

If the data entered does not match any of the values in the corresponding lookup tables, controls need to be set to offer the user options, e.g. “Try Again, Create a New Item or Cancel”. It is certainly best if the user does not have to enter the exact value, but may enter the first portion of the value.

If there is more than one match, the user may then select from a list of matched values. “Access levels” should be set for each user so that it can be controlled as to whether or not they are allowed to add new values to the list or must use existing values.

Data entry controls, automatic calculations and formatting:

There should be mechanisms in place to assure that data is entered in a “normal fashion” and that the information entered makes sense. As well, some automatic formatting functions can significantly speed up data entry and make the information easier to read. These mechanisms might include:

Stripping trailing and leading spaces
Verification of dates, e.g.
Test result date cannot be greater than the current date
Tester certification expiration date must be greater or equal to the test date
Test result status calculations, including:
Test result value minimums and maximums
Test result value differentials
Telephone number formatting, e.g. 8885551212 returns (888) 555-1212
Automatic date entry, examples:
“91” gets Sept. 1, of the current year: 09/01/01
“Today” gets the current date
“Yest” get yesterday’s date
Automatic text formatting, uppercase of first letters, e.g.:
Contact names: “bill smith” returns “Bill Smith”
Street names: “main st” returns “Main St”
Automatic calculation of “Next Dates”
Next Test Date
Backflow Test Kit Calibration Due Date
Backflow Tester Certification Expiration Date
Verification of Tester’s status
Verification of a specific backflow assembly at for a specific hazard
Verification of a repair kit used on a specific model of backflow prevention assembly
Verification of mandatory data entered before a record can be saved

Other helpful features:

Provide free style “Comments” at most entry areas. A “Time/Date/User” stamp is extremely helpful when reviewing comments at a later date.

Above all, make sure that the information being requested is meaningful and has a clear purpose. Tracking insignificant data can be as much a problem as tracking too little information.

Backflow Prevention Event management:

Besides managing the above entities and their status at any point, a thorough backflow data management system should include mechanisms to easily manage and document the events listed below. Being able to quickly pull together groups of similar data will allow you to better effectively manage your program. Some examples are:

Compiling data to locate trends in failed backflow assemblies
Managing installation, repair and testing costs and time
Summarizing what percentage of tests is being performed based upon a first notice only.

Events to document:

Scheduling scheme
Due dates or other schedule coding scheme
(e.g. “09” = Tests due in September):

Annual (or at regular frequency) tests due
Installations
Replacements and retrofits
Surveys
Inspections

Customer notification of:
Annual tests due
Surveys
Inspections

Sending follow-up notices of the above:

An indexed and searchable “Response due date” should be established when sending notices. When a customer has not responded to a notice by the given date, and a follow-up notice is sent, the “Response due date” should be extended, not the “Test due date”, as the “Test due date” should be an “anniversary date”.

Phone calls and other communications
Assembly installation and replacement information, including the installed date and installer information
Survey data
Inspection reports
Test results

It may be helpful to categorize test information by the purpose of the test:

Annual test
Test at time of installation or replacement
Test after repair (other than at time of a failed test)

Test results may be captured in a variety of fashions, depending upon how the resulting data may be used:

Full test result data, including:
Valve pressure differential values
Specific and searchable failure and repair information
Date of Test
Tester information (Name or certification number)
Test kit information (Make/Model and Serial Number)

Minimal information
Pass or failure of initial test
Date of Test
Tester information

For annual tests, the tests results should be maintained so that it can be determined if a test fails the initial test. The purpose of this is to be able to analyze failure trends, e.g. by make/model, geographic area, tester, hazard type, etc. Some kind of coding scheme could be used to handle this, e.g. “PASS” for passing an initial test, “FAIL” to indicate test failure not yet repaired and “FAIL/PASS” for a test with an initial failure, a subsequent repair and final passing test.

Merge Letter Management

Merge letters should be able to be easily maintained, as changes will surely occur.

As letters are sent out, a record of that notice should be recorded.
It is helpful if a previous letter can be referenced in a follow-up letter.

Searching and Sorting

One of the most common complaints that this author hears is about the limitations with easily finding and ordering information. You should provide your users with the ability to find information by numerous criteria, e.g.:

Address	Assembly Type	Hazard Type
Account Number	Assembly Mfr	Hazard Level
Assembly Serial Number	Assembly Model	Service Type
Customer Name	Last Tester	Meter Number
Test Dates	Installation Due Date	Compliance Month

Maintaining Digital Images

Having a digital image tracking capability in your program can be extremely helpful.

Inspectors can document installations.
Site maps can be scanned for later use in the field to locate backflow prevention assemblies.
Test Reports might be scanned so that you do not have to maintain the hard copy.

Reporting/Export Capabilities

Any information in the system should be easily put into some type of report format. A built-in report editor or a 3rd party editor should be available to extract the information in a meaningful format.

All data should be available for use by other systems, e.g. Billing, Building Permits, Finance, Distribution, etc. This can be accomplished by some kind of export editor or access via ODBC (Open DataBase Connectivity)