Responsible Backflow Prevention Program
Compliance and Data Management
Randy Engle, President
XC2 Software - EngSoft Solutions
© 2001-2006 Randy Engle
Presented at AWWA Annual Conference
New Orleans - June 2003
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:
Besides maintaining records for existing backflow prevention assemblies in active service, a thorough backflow prevention data management program will maintain records for the following:
- 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
- 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
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
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:
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.
- 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 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:
Other helpful features:
- 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
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:
Events to document:
- 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.
Sending follow-up notices of the above:
- Scheduling scheme
- Due dates or other schedule coding scheme
- (e.g. “09” = Tests due in September):
- Annual (or at regular frequency) tests due
- Replacements and retrofits
- Customer notification of:
- Annual tests due
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”.
It may be helpful to categorize test information by the purpose of the test:
- Phone calls and other communications
- Assembly installation and replacement information, including the installed date and installer information
- Survey data
- Inspection reports
- Test results
Test results may be captured in a variety of fashions, depending upon how the resulting data may be used:
- Annual test
- Test at time of installation or replacement
- Test after repair (other than at time of a failed test)
- 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)
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.
- Minimal information
- Pass or failure of initial test
- Date of Test
- Tester information
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.:
|Assembly Serial Number
||Installation Due Date
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.
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)