Backflow Prevention Record Keeping
Randy Engle, President
XC2 Software - EngSoft Solutions
© 2001-2006 Randy Engle
Why use software to manage your backflow prevention program?
Intelligently selecting the right backflow software.
What are the benefits? - What are the pitfalls?
The whole purpose of having any kind of computer based tracking system:
Some Elements of 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 item #1
- To quickly and effectively provide summary reports to management and authoritative agencies
But why use computers and software to manage your program?
- Knowing whereabouts of existing backflow assemblies
- Knowing whereabouts of potential hazards which need a backflow assembly installed.
- Scheduling of Tests Due
- Sending First Notices of Tests Due
- Sending Followup Notices
- Documenting Phone Calls and other Communications
- Documenting Installations
- Recording Test Results
- Managing Certified Tester Status
- Managing Test Gauge Accuracy
- Scheduling and Tracking Inspections
- Scheduling and Tracking Surveys
- Tracking New Installations and Replacements
- Tracking Installation, Repair and Testing Costs
- Reporting Activity Performed and Compiling Summary Information
Software Options - Pros and Cons
- There Is Nothing Wrong with a Paper System
- (but try doing the following with a paper system:)
- Quickly locate a single backflow prevention assembly, site address and device information and print out all of the associated and pertinent information
- Quickly print 100’s or 1000’s of merge letters to customers, notifying them that their backflow preventer is due for testing, or sending followup notices, etc.
- Quickly determine how many Test Reports were received within 30 days from a first notice.
- Schedule inspections for a particular region of your city.
- Pre-print test reports with assembly, customer and site information.
- Create Detail or Summary Reports to assist you in making administrative decisions.
Keep Using What You Currently Have
- Keep using what you currently have (No Change)
- Roll your own (In-House Custom Software)
- Get a hired gun (Contract Custom Software)
- Use a system that another organization has already developed
- Purchase a commercial software package
- Hire an Internet based service organization to manage your data
- Hire a service company to run your program for you
- Hire an outside consultant to tell you what to do regarding the above
Use what you have – Cons
- Use what you have – Pros
- Requires no new learning, i.e. time and effort
- (Unless the person who knows the system leaves)
- Requires no new equipment or expense
Roll your Own (In-House Custom Software)
- Current system might be awkward.
- Might be time consuming for simple functions
- Has minimal or no flexibility, aggravates users
- May not provide adequate functionality
- May not be upgradeable.
- Original programmer done gone!
Roll Your Own – Pros
- You or Your IS/IT department develops the system
- You get to have it your way
- You get to make changes when you want them
- No apparent additional expense.
- You may be able to use the resources you have.
Roll your own – Cons
Get a Hired Gun (a.k.a. consultant)
- What IS/IT department?
- (or…What is an IS/IT department?)
- Takes a LOT of time thinking through the functionality and various processes that you will need and conceptually designing the system to work the way that you need it to.
- If someone from your IS/IT department develops the system, you need to impart your knowledge of the various processes involved so that they really understand what is needed.
- Lots of development/debugging time.
- The person/group who originally develops the system might leave your organization or be pulled off into another project that takes priority based on someone else’s sense of best use of resources,
- i.e., you get left holding the bag with a half-baked system, which may or may not function to meet your needs, or not at all.
- As you add on functionality to the system over time, and if each new item is not carefully thought out before it is implemented, you might find yourself with: “The Backflow Software from the Black Lagoon”. This is the software that causes you more anxiety and work than the time it is intended to save you.
Have someone develop the system for you
- Hired Gun - Pros
- Same as “Roll your Own”, you get what you want.
- You can hire the consultant back on an as-needed basis.
- You don’t have to keep them on as an employee.
Use a backflow prevention software system that another organization has already developed
- Hired Gun - Cons
- You need to find a developer.
- Takes time and savvy to locate and interview to get the right developer.
- Same as “Roll your Own”.
- Takes a lot of thought, meetings and design time.
- What you think you wanted in the beginning may not turn out the way you’d like it to.
- Consultants can be pricey (I wouldn’t trust anybody who charges under $75/hr., and that’s on the low end).
- Such a project could easily cost well over $20K-50K or more.
- Making changes to the original design specification can be extra pricey.
- Did someone mention testing and debugging?
- The consultant might go get a real job. You may effectively need start over if you want some changes made.
- Use someone else’s system - Pros
- Probably very cost effective. You might be able share in their cost, or maybe they’ll just let you have it.
- You potentially know what you’re getting into.
- You have a built in “user group” of someone or several others who have been using the system that you can go to for support.
Purchase a Commercial Backflow Software Package
- Use someone else’s system - Cons
- You may not know of another organization that has developed their own or is willing to part with it, or the copyright might be retained by the original developer.
- Though inexpensive, the system could turn out to be inadequate or incompatible with the way you run your organization.
Commercial Software - Pros
Commercial Software - Cons
- You might be able to get a working demonstration version before you commit resources. You can actually see what you are getting before you get it.
- You might be able to get an on-site demonstration from the vendor showing you some of the more distinct features of their software.
Be sensitive to the fact that software vendors or developers who make Backflow Prevention Software are usually quite small companies. Their software pricing tends to be competitive and as such, they may not be able to hop on an airplane and come out to your site and give you a demonstration. To do so, might eat up any potential profit they might make in order to stay in business. One of the things that you want in a software vendor is for them to be strong economically and to stay in business so they can provide support and upgrades into the far future.
- You might have others in your area who are using the same software so you can see how it works for them.
- The software vendor has already thought about the majority of the features that you might need.
- Upgrades and updates might be made to the system on an on-going basis. These updates would be based upon input from several sources (existing and new customers of the software vendor). You get the benefit of a lot of knowledge.
Current commercial backflow prevention software packages available:
- You might have a special need that is not met by the commercial software.
- Your IS/IT department may not like bringing in a 3rd party package that they have no programmatic or structural control over.
- Customization can be quite expensive, and a dead-end road for upgrades.
- You will need to go through a learning curve to understand all of the functionality of the new software.
- The software vendor might go out of business. No more support, no more upgrades.
The current list of available packages changes frequently!
Probably the best place to check is the APBA web site for information on how to contact the various vendors and how to get more information about their products and services.
Be sure to stop by the vendor’s tables at the backflow prevention conferences that you attend and have them show you their software and ask all the questions that you would like. (Don’t be shy!)
Get a WORKING demonstration version and try it out for yourself!
Claims vs. Reality
- Would you buy a car just because it looks good on the showroom floor?
- Would you buy this car without driving it yourself?
- So, in regards to selecting software which is intended to run an entire department:
- Do you completely believe the pretty pictures in print, or the static screen shots that the salespeople show you?
- Do you completely believe the person that is trying to sell it to you when they say that their software functions in a certain way and a competitor’s software does or does not function in the same or some other way?
Have you SEEN it work in a REAL WORLD Scenario.
This is not necessarily a deal killer, but it might be nice if you can see someone else’s system who is using the same software.
Do the functions of the software meet your needs?
Besides all the cool features and functions the software purports to have, you might look at the following concept:
“Does the software do…?”
“How do you use the software to do…?”
It’s easy to ask the question, “Does the software do such-and-such a thing?”
The truth is, software by itself doesn’t do anything, it just sits there.
You as the user need to interact with it in some way.
Remember, “automatic” may not always be the best way.
Sometimes you need the software to perform tasks when you are good and ready to perform them, not when the software tells you it is time to perform them.
Too much “automation” can quickly become a nuisance if it gets in the way of what you need to accomplish in the moment.
If the software is ready to print letters and won’t let you do anything else until you print them, but you are not ready to print letters, there might be a problem.
As an example, you ask, “Does software ABC print merge letters?”
The software vendor says, “Of course!”
The reality might be that you have to jump through flaming hoops in order to get the letters printed as you need them, and that the printing of the letters may start an internal process that you are not able to easily stop. The software may force you to follow a course that is not very flexible and doesn’t take odd-ball factors into account. In order to stop the process, you may need to delete the record(s) and start all over.
So a more appropriate question might be:
“How do I print first notices for all of the backflow prevention assemblies due to be tested in such-and-such a month?”
Then, “How do I print followup notices for backflow prevention assemblies for which I did not receive a test report as a response to my first notice?”
It’s really best to find out how the backflow software performs the functions that you think that you want or need, and try them out yourself.
Use the demonstration version of the software to run completely through some of your normal processes such as:
Send one notice per device; send one notice per site, listing all devices at that site.
- Enter a new backflow prevention assembly record in the system, with all of the address information, assembly information, location and other related information.
- Use the query tools to locate that same record, or another record.
- Try to find that record by Address, Serial Number, Meter Number, Account Number, Customer Name.
- Do some lookups on some obvious as well as some oddball information, e.g. failed tests in a given month by a given tester, or lookup assemblies by last test date.
- If there is more than one backflow assembly associated with a specific customer or facility, what do you need to do to enter the new assembly information without repeating entering address information.
- Can you look at the data from the point of view of a list of assemblies as well as a list of facilities? Neither way is inherently best, but you may need to view the data one way or another for you own purposes.
- Go through the process of sending first notices, and followup notices.
- Create a new letter to send. Format it your way
Enter some test results. Be sure to pick a few odd-ball ones, some that pass the initial test, some that fail, reduced pressure assemblies, double-check assemblies, etc.
What happens if you don’t have all of the data for the test results?
Will the software allow you to save the record anyway?
Also while entering test results, what happens when you enter the tester information.
Will the software accept anything that you enter, or does it force you to select from a lookup list?
Does it make it easy to add a new tester to the system while entering test results.
Can you do a lookup for a tester by name as well as certification number?
Connectivity (Sharing data with other systems)
- Enter a backflow test where the tester certification has expired.
- Also try this for backflow test kits.
- Does the software perform adequate error checking of entered information without bringing you to a halt?
- Enter a new tester or test kit “on-the-fly”, i.e. without having to stop the current process?
- Print out a backflow test report.
- Print out some summary reports.
- Get a count of active backflow prevention assemblies in the system.
- Print out a list of active testers.
- Send a Certification Due letter to a list of testers.
- Enter an assembly with a duplicate serial number.
- Look at the history of installation, tests, repairs, inspections and letters sent for a given device.
- Change the name of the person or organization responsible for maintaining several assemblies.
- Change/Add some items in each of the lookup tables.
- Change a user access level and log on as that user.
- It’s very possible that performing some of these functions might seem very awkward.
This could be due to the fact that you are not familiar with the software, or there may well be more that one way to perform the same function or get the same results. Ask the vendor if you run into a problem.
It’s best not to assume that the function that you are trying to perform can only be done one way or in the way that you have been used to.
- What are the available printing options?
- Does it have a flexible report writer, or can you use a report editor such as Crystal Reports with it?
- Try putting together a simple report of information that you want, such as a list of all backflow assemblies which failed the initial test in the last 6 months, ordered by manufacturer, type, size and model.
- Are the Query/Search functions flexible?
- Are the Sort/Order functions flexible?
- Does it display the information in the fashion that you need?
- Can you easily create and edit merge letters?
- Will it handle digital images, photos, etc.
- Can you rename fields and tablesHow does it handle “Error Checking”
- What are the various entry checks that the software makes before it allows you to save the data?
- Is standard data checked against a “lookup table”
- How does it notify you if something is amiss?
- Is this notification annoying or does it help you get the proper data in quickly?
- Are the various lookup tables modifiable by the user: Hazards, Assembly Models, Assembly Types, Service Types, Scheduling System, etc.
The purpose here is to connect to other database systems in order to update data such as mailing address or other customer specific information. As well, the other systems may need information from you backflow program.
You need to ask the question: How many changes are there in a given period of time?
e.g. 10 x day, 10 x month, 10 x year
If the answer is “a lot”, i.e. a lot more than is reasonable to enter by hand, you may want to consider the various options:
“Direct” Connection to Billing, Permit or other Customer or Maintenance System
This means that the internal software for each system is configured to automatically send and/or retrieve information from the other system, independent of the user’s actions.
Direct Connection - Pros
Direct Connection - Cons
- Immediate access to all updated data.
- Seamless integration of data
Connection and Updates via a common file.
- Expensive and time-consuming to set-up and maintain.
- Requires 24/7 reliability of all systems:
- External System (CIS or Maintenance System)
- Backflow Prevention Software
(Transfer File Export/Import)
This means that some user intervention may be required on both systems. In most cases, this may be as little effort as clicking a button or menu item when it desired to update the system. We’re talking a handful of seconds here to start the process, where the actual process may take several seconds to a few minutes.
Transfer File - Pros
Transfer File - Cons
- Minimal time to implement
- The user can run the process when needed
- Does not require external/network system 24/7 reliability
- Relatively easy to change
Electronic Test Entry Considerations
- Requires some user intervention on both sides of the systems
- Requires that someone must think about it, and know when it’s time to do it.
- Not a lot of high-tech sizzle, it just gets the job done.
This can mean a lot of different things. Various possibilities are:
Remote Data Entry
One – Way Data Transfer
In this case, the backflow test data entered on the remote computer is transferred to the “server” or “master” database.
- Data entry in the field using a handheld device (e.g. Palm), tablet pc or a notebook computer.
- Gathering data in the field on paper, transferring to a computer in the office.
Via a direct connection like ODBC or a serial connection
Via a “Transfer File”
- This is fancy computer-speak for a “network connection”
- This would only apply to Water Purveyors with their own in-house testing staff.
Two – Way Data Transfer
- This option would allow for a large testing companies to submit their test reports in a large batch
- The testing company’s software would have to work within the guidelines of the water purveyor’s software, i.e. the data fields would have to match exactly
- Other considerations
- A unique identifier (e.g. System ID) would be needed to match the remote data with the water purveyors data
- Entering new installations
- These would probably need to be entered by hand
- Inaccurate data – missing key data
- How to notify the user. When to notify the user
- Qualifying Testing Companies who may use the process
Web Based Data Entry
Here we are talking about entry of Test Results by the Tester (or someone in their organization) via a Web Browser.
- In this case any data that has been updated on the “server” be transferred to the “remote” computer or handheld. This technique sounds great, and certainly would be great if the system is carefully developed. Development for this type of system could be very expensive if data is needed to go back and forth between a remote system and the “server”. Lots and lots of testing would be required to implement such a system.
- Very expensive means well upwards of $20K or more. Some of the considerations to take into account for this type of system are:
- Entering new installations
- Inaccurate data – missing key data
- How to notify the user. When to notify the user
- Qualifying data entry persons who may use the process
Entry person/Tester must be validated against a list of allowed persons to do web based entry
Adequate validation techniques would need to be implemented
Some method to review data entered would need to be implemented prior to fully accepting it into the system and updating the related data
Software Company Considerations
How long to learn the system?
- How is their technical support? (or your own tech support)
- Do they return your calls in a timely fashion?
- Are they able to help you?
- Are they pleasant?
- Do they call you a bonehead? (even when you are being a real bonehead?)
- Call their references.
- Ask their other customers how they feel about the technical support they have received from the vendor.
A typical question is: “How long does it take a person with average computer knowledge to learn the system?” There is no real answer to this question !!!
There is no such thing as a person with average computer knowledge!!!
Beware if you are told other wise.
A few years ago, you might be able to answer this question truthfully. As an example, a friend of this writer builds magnificent 3-D animated multi-media user-interactive web sites, but he’s terrible at using a simple word processor. These days, there are too many areas in which to specialize to consider someone of “average” knowledge.
What they do
How they do it
Beware of pretty pictures!Here’s the rub:
You will need to spend some time setting up your own testing plan ! Other user references are terrific, but your needs will be different.See the section above as a guideline to set up your own testing plan.2-3 hours spent here will save you considerable headache down the road.
Other possibilities other than having to do all of the above to determine which backflow software is best for you:
Hire an Internet based service organization to manage your data.
This is a relatively new service idea for backflow prevention management. In this case, the backflow software and hardware is managed by the service organization. The testers log onto the system via a web-browser to enter test results. The water purveyor can access the data via various tools provided by the service organization. The service may even be able to print test notices.
This writer has not had direct experience with or seen this type of system functioning in the real world to give a truly adequate analysis, but the following are some of the most obvious aspects.
Internet Service - Pros
Internet Service - Cons
- Reduced time in handling paper, i.e. printed notices and test results.
- Reduced time in entering test results.
- Convenience. Ability to access from any computer on the Internet with a browser.
- All backup could be handled by the service organization
Hire a service company to run your program for you.
- The system is completely dependent on several networks functioning at all times:
- Your own internal network
- Two Internet Service Providers(yours and theirs)
- High speed access providers (e.g. DSL service)
- Telephone company phone lines and equipment
- The service organization’s network
- The Internet
- If there is any problem at the service organization such as theft of equipment, power outage, flood, fire, etc, purveyors will not be able to access their data, testers will not be able to enter data until systems are restored.
- The service organization might go out of business. It may be difficult to get a hold of your data in this case. You might make an arrangement to get a copy of the data once a week or so.
- Ability to manipulate the data might be quite limited at first. This could also be said for any software system, though. Very likely, web based data manipulation tools should get better over time.
This plan might be good if your organization has little or no experience in running a backflow prevention program, or if you have minimal human resources.
In this case you can hire the service organization to do everything for you from writing your ordinance to sending notices, receiving test reports, doing inspections and surveys and testing of backflow assemblies managed by your organization.
In other words, you don’t need to do anything except pay the service company and have them hand you a report at regular intervals.
Of course you need to somehow certify that the service company is actually doing the work in the appropriate manner.
Hire an outside consulting company to recommend the best option for you.
You’ve read all of the above options and quite honestly, it’s got your eyes rolling around in your head. This whole issue has you in a place where you don’t know whether to soak your head or go blind. This might be where an engineering or environmental consulting company might come in handy. In this case, you pay the consulting company to do the research and figure out the best option.
Consulting Company – Pros
Consulting Company – Cons
- They might be an organization with whom you already have a working relationship and as such trust their judgment.
- You won’t have to spend additional time or effort looking for a new consultant.
- They do the requirements research
- Analyze the existing problem
- Research current industry standards
- Analyze your existing resources
- They do the commercial software research
- Acquire the software demo versions
- Test the demos
- Check up on vendor references
- They might do the initial installation and setup for you
- They might be able to offer training and support for the initial year.
- In general, you pay them to understand the problems and processes and to think of the details.
- This kind of consulting can be quite expensive.
- They might end up charging you $30K+ in consulting fees to recommend a $3-10K software package.
- The consulting company may have a lack of experience in the backflow prevention program management field. As such the criteria that they base their findings on may not be appropriate or might be out of proportion to the specific problems they are intended to address.
- If the consulting company is acting as a reseller, this may cause an undue delay in getting problems handled that the software company might be better equipped to handle if you had a direct relationship with them.
- Likewise, if you are needing additional functionality, moving through the various channels may take an extraordinary long time