Services

The Florida Association of Community Health Centers is responsible for maintaining communication with over 600 federally-funded Community Health Centers throughout Florida during disasters like hurricanes.

Like many organizations doing this type of work, they send out a long online survey during disasters asking participants for status reports and needs requests. This information is collected into a spreadsheet. The form takes over 15 minutes to fill out. A big reason it takes so long is that the CHC have to give FACHC information it already has: the name and location of the health centers and information about the people who're filling out the forms.

This makes what could be a quick survey into a long one that CHCs are less likely to complete. This process also wastes FACHC's time because they need to collate the submissions to records of CHC locations and people that they already have from previous information collection processes.

The results: a situational awareness picture that is never as complete as it could be.

Sarapis developed a two-part solution for FACHC:

• an Airtable for use by FACHC staff organized like a CRM where they can manage organization, contact and site information, project event and task information, and even use Airtable Apps to send text messages and email messages to CHC member messages.
• a custom web app for use by CHC members where they can login with their email or SMS messages, see news and updated from the network, fill out information about themselves and their sites, and see information on a map. All this info goes to the FACHC Airtable, which is the one and only backend for the app.

The Airtable Backend

FACHC uses HRSA's CHC data as a single source of truth for the state's CHCs. We loaded that data into Airtable and then FACHC added in their own lists of CHC contacts. We then added additional fields and tables, enabling FACHC to track contact attendance at events and other relevant organizing info. At this point, FACHC had a working CRM system and used it to further enrich their data.

Next, we designed and built a custom web app that uses the FACHC Airtable as a database. The app is designed to be as easy as possible for CHC members. Instead of logging in with a traditional user names and (easily forgettable) password, users login by submitting their email or phone number and receiving a magic link that logs them in with a click.

Once logged in, they can edit and confirm their contact info, and fill out a brief survey about the status of each of their organization's sites. Since we already have lots of information about the organization, their sites and their contacts in the Airtable, we can significantly reduce the amount of time they spend on their responses. With one click a user can declare a site fully operational or they can indicate they're having issues, which triggers additional questions based on their location characteristics and their responses. FACHC gets all the info they need to keep track of everything in the Airtable they're already using. This reduces the time a CHC members needs to update a site from 10 minutes to 1 minute - making a huge difference in response rates. In a recent test, 5x more CHC members updated site info using the app than they did when asked to fill out the online form.

Responses are instantly logged in the Airtable, updating data dashboards with pivot tables, maps and more in real time. Since the Airtable is so easy to customize, FACHC staff have added tables to enable them to track the network's inventory and assets. They've also build new features with the Airtable's forms that makes it easier for CHCs to request inventory items like PPE and generators from FACHC.

We believe the flexibility of Airtable with the capabilities of light-weight custom apps is a transformative combination of the disaster management sector, and we look forward to spreading this practice throughout the sector.

Interested in learning more? Contact us.

New York City’s 59 Community Boards (CBs) are our beloved city's most locally-focused government agencies. The boards, which consist of community members appointed by local elected officials, as well as three to six operational staff led by a district manager, perform a number of functions related to land use, capital budgeting and evaluating the efficacy of city service delivery.

BetaNYC, a leading civic technology organization in New York City, has been providing CBs technology tools and training for years. During that time they methodically documented the various technology needs they heard from CB members and staff and turned it into a detailed list of the features CBs wanted in an information management tool set. When an opportunity arose in 2019 to present a toolset to CBs, BetaNYC worked with Sarapis to figure out the best path forward.

We evaluated the notes, spoke with BetaNYC’s team and CB stakeholders, and studied how CBs are currently meeting their own needs with a smattering of spreadsheets, documents and paper systems. The main impression we got was that CBs wanted more powerful tools, but they didn’t want to sacrifice their unique workflows, data structures and methods of doing business. It quickly became apparent that they wanted the flexibility of a spreadsheet with the power of a custom-built database. A perfect use case for DIY database technologies!

As such, we proposed a multi-phase approach where we first build a custom database backend “with” (instead of “for”) each CB, then work with them to design an appropriate interface for their database. We'd knit the database to the interface to create a working prototype, and then, if the prototypes were appealing, consider implementing them into a standardized open source software platform.

For the first phase of work, we documented how each of the 15 CBs that signed up for the program was collecting information related to constituent issue tracking and contacts. We then listed each field of data they collected and how the CBs grouped those fields together. We analyzed the similarities, merged the same fields together and gave each data field a popularity ranking. These became the fields where then implemented in Airtable and turning into a template that each of the CBs could use as they like.

Our initial analysis resulted in a mapping out over 650 fields into about 20 data tables. With deeper analysis and active collaboration with BetaNYC and CBs, we whittled that down into a functional Airtable template of 9 tables and approximately 300 fields. We deployed this template for our CB partners by importing their existing data resources into it, and then training them, one by one, to use it.

As they began to adopt the solution and use it to actively manage their data, we quickly discovered numerous ways the template could be optimized to meet the needs of individual CBs, and how that could inform the template as a whole. In October we did an analysis of each CBs use of data fields, and used that information sharpen the template: reducing the number of tables and fields, and creating new views that made it easier for CBs to use the template for more things. Once we were confident our new template was as good as could be, we migrated CBs from the old template to the new one. This result is 15 CBs all using optimized and compatible Airtable templates.

Thanks to COVID-19 sending NYC government agencies, and their budgets, into disarray, it's hard to tell what will become of the project. Hopefully the Airtable will continue to be used, and refined to meet CB needs, and maybe in the not too distant future we'll be able to build some custom front ends on top of these Airtable databases. Or maybe some CBs will chose to export their data from Airtable into a different database solutions. The flexibility is there for each of the CBs to do any and all of these things.

We're excited to see where the project goes!

To learn more about the CBDB program check out the program’s page on Beta.NYC.

Mutual Aid NYC is a city-wide network of neighborhood-scale mutual aid groups that emerged during the COVID-19 crisis. Volunteers came together to help people in their area get access to food, medication and social services during the height of the crisis.

The central Mutual Aid NYC group quickly amassed a resource list that they published on their website and used as a directory for a volunteer-managed phone hotline -- basically establishing a 2-1-1 system for a city without one.

Sarapis originally deployed the mutaulaid.nyc website and set up its first Airtables, so we were in a good position to introduce Mutual Aid NYC's #resources-team to the Open Referral Airtable template and help them consolidate their various resource directories into that template.

We then worked with them to deploy an ORServices instance that published their Airtable's resources in a familiar, mobile-friendly directory interface at resources.mutualaid.nyc.

We look forward to continuing our work with Mutual Aid NYC!