Back in March 2020, when the reality of COVID-19 was just beginning to sink in, our clients pivoted in ways that would reshape how they work.
One such client was the Washington State Council of Fire Fighters (WSCFF). Since firefighters are called every day to attend to people in their homes, on the street, in nursing homes or hospitals, the WSCFF quickly realized they would need to track exposures to COVID-19 because all their members were at risk.
A big job
The first thing they tried was a daily survey that could be loaded into an Excel spreadsheet, which they used to record and track who had tested positive, how many members were in quarantine, isolation, and out of service.
This was a big job for the office staff, who had to oversee numbers daily from over 122 participating locals. One of the locals set up a questionnaire that fed into a database. Staff had to pull an Excel file, manually correct errors, enter formulas and format it daily before posting the information to the website. Although a vital task, it wasn’t very efficient and quickly became overwhelming and frustrating.
Rhizome to the rescue
The WSCFF called us and asked if we could help. Once we had a copy of their master spreadsheet, we figured out that if each local entered their own data into an online form every day, all the staff would have to do is monitor it. If the right tool was in place, there would be fewer entry errors, the person entering the data could do it more quickly, the information that was submitted to the WSCFF would be more consistent, and the data would be up-to-date in real time.
At Rhizome, we use Airtable for everything. So we wondered: could we create an Airtable base with all the fields they needed and then turn it into an online form? The answer was yes. It took us a few tries, but the form was up and running in less than a week.
How we did it
We set up the form behind the member portal of WSCFF.org. Every day, representatives from each local would login to the member area, complete the form, and submit their record.
Once we had some data from each of the locals, we embedded a reporting table on WSCFF.org so members could see the COVID-19 numbers from across the state. Every week for about a month, we tweaked the field labels, fixed the occasional hiccup, and fine-tuned instructions to make them clearer and enhance the user experience.
What it looked like
In this example, you can see the number of firefighters who have tested positive for COVID-19 since March 17, 2020, by district.
This next table shows the number of members who tested positive by month statewide.
What we learned
One issue we had was not being able to display the data as embedded charts and graphs on the website. Airtable’s charting app didn’t have that functionality. So, our developer programmed a JavaScript solution to pull the Airtable data, customize it, and display it in a way that would be useful for WSCFF members.
Sometimes, the simplest things tripped us up. For instance, we had to instruct the people entering the data that even if they had no COVID-19 cases to report, they still needed to enter zero (0) in the form. If it was left blank, it would not work with our formulas and throw off the reporting.
The right tool for the job
This project was a reminder that even when there isn’t the time, money, or resources to develop an ideal system, you can still figure out a workaround. You may not get everything you want, but you can get a useful reporting mechanism that gives your client what they need, when they need it.
Using Airtable was the most expedient way for us to hit the ground running. It was a solution that made it easy for users to enter data, easy for staff to monitor, and is easy to replicate. What we did for the Washington State Council of Fire Fighters is also being used by the state councils in Alaska, Idaho, and Montana to collect similar information.
A low-tech solution, and the right tool for the job.