Delivering Open Referral Solutions with Airtable

In 2018, with a small grant provided by the Alliance of Information and Referral Systems to the Sahana Software Foundation, Sarapis developed an Airtable template of a community resource database using Open Referral’s Human Services Data Specification (HSDS). This project responded to a need articulated by many in our community for a lightweight, easy-to-use resource database for the management of complex service directory data.

With the functionality of a basic database, and the user interface of a common spreadsheet, we hypothesized that an Airtable template could provide a workable solution for organizations and projects with very limited funding.

Airtable’s free tier could be used to manage directories with under 200-300 services, and if they had more services, they could upgrade to thousands of records for only $10-$20/month. This is a few orders of magnitude less than the cost of Open Referral-compatible database software on the market at that time, and so much easier to use.

Developing Airtable for eldercare services in NYC

The first validation of our hypothesis came from DOROT, a nonprofit agency focused on enhancing the lives of older adults and bringing generations together. DOROT’s headquarters is located in New York City’s Upper West Side and they serve seniors in Manhattan and Westchester. DOROT wanted to develop a standardized resource directory information system, but they didn’t have the resources necessary to build and deploy a custom database of their own. We gave them the Open Referral Airtable template and a quick Airtable training — and they were off! Within months their database had a few hundred services.

Next, they wanted to make this service information easily accessible to the social workers, social work interns, and supportive staff in their Manhattan and Westchester offices. They wanted to deliver that in the form of a simple, familiar resource finder interface. To achieve this we built ORServices, an open source platform that takes data from the Airtable and displays it as a convention directory web app, with search, mapping, export features and more. This was a big success! DOROT is now managing over 600 services in their Airtable, and their private directory interface is used daily to match people with services. DOROT appreciates the ease in which they can update the database and make changes in real-time. The online resource directory was a long-time dream and they are thrilled to see it come to fruition.

The needs of DOROT have turned out to be relatively common in the Open Referral community. Organizations want an easy way to manage complex data – Airtable is perfect for this – and they also want to make it available, publicly or internally, via a familiar directory interface.

The Chatham County Safety Net Airtable

Here’s an example of another project we did from the past year.

In 2017, the Chatham County Safety Net Planning Council (Planning Council) received an innovation grant from the Office of Children Youth and Families in Georgia’s Department of Behavioral Health and Disabilities, to strengthen the System Of Care (SOC) for children and youth — specifically by connecting them to appropriate behavioral health services, and evaluating the effectiveness of that process.

The Planning Council’s first objective was to build a ‘living directory,’ through which they could aggregate this accurate, detailed data from providers to share with other providers – the specialists, case managers, social workers, etc – who do the work of helping youth and finding services.

This work coincided with the development of a multi-agency resource center, specifically to divert youth from the justice system and into services that can help them. The resource center includes some staff capacity to build relationships with providers. However, the Planning Council had few resources to design and build a system from scratch, and did not need many of the elaborate features that make enterprise information-and-referral platforms so expensive.

The Planning Council discovered Open Referral and reached out, expressing interest in our Airtable template. Open Referral connected them with Sarapis, and Sarapis helped them deploy, configure, and customize the Airtable. The Chatham County Safety Net Planning Council now has a resource directory including 30 different types of behavioral health services. This is small enough that the ‘resource center’ can maintain it with their limited capacity.

They also needed a front end that could make their resource directory publicly available. So we deployed our ORServices platform again, and worked with them to build a few features they needed, such as the ability to collect custom analytics that enable them to track how users are searching and what they are clicking on. We also created a mechanism that puts vetted organizations at the top of search results.

“I’d say we saved at least two thirds of our budget by working with these free / open source tools,” says Patricia Merritt, who led this process of discovery and deployment for the Planning Council. “That’s funding that we easily otherwise might have spent deploying a proprietary solution, and which we can instead invest in resource data collection and maintenance.”

Merritt tells us that the Planning Council has already received plenty of positive feedback about the project.

“We presented it to our advisory board for the Front Porch — they are basically an information-and-referral service for youth services – including courts, law enforcement, schools, state contracted behavioral health providers, the state and county, and other community partners. One of the judges took the tablet and typed in the service he wanted, and it came right up – and he said ‘I could have used this in court yesterday, this is the service I needed then to help a family.’ It’s still a work in progress, but we’re hearing that people think it’s a big deal.

The Planning Council now is interested in exploring opportunities to embed this tool into existing case management platforms operated by their partner agencies — in order to improve linkage to care — which is possible given the API that comes with the Airtable template.

In the meantime, they have shared their customized Airtable template, tweaked specifically for Behavioral Health services, for others in the community to use!

The DC Community Resource Information Exchange implementation

In the District of Columbia, our Airtable was used for more tactical purposes: deployed not necessarily as a long-term solution, but as a convenient test-bed that enabled community leaders to quickly engage in research, development and deliberation.

As part of the DC Community Resource Information Exchange initiative, Open Referral’s consulting team was tasked with developing a prototype of ‘community resource inventory’ information infrastructure. Rather than either procuring an expensive enterprise system – or building an entire database from scratch – Open Referral was able to meet the project’s technical objectives while minimizing the budgetary burden, by deploying and customizing our Airtable as a demonstration implementation.

Airtable is a free web-based database application, and the Open Referral Airtable template enabled us to quickly deploy a Content Management System for DC’s community resource inventory.

The DC project collected resource datasets from multiple sources, matched the contents together, transformed the matched dataset into the Open Referral format, and loaded it into the Airtable. This amounted to approximately one month of work, and yielded an operational content management system with hundreds of records contributed by stakeholders across DC. The Open Referral team then engaged in methodical verification of the contents of the database, checking the accuracy of each record and tracking our progress in the Airtable. (Here is the shareable verification workflow guide that we produced.)

Airtable acted as a backend for managing the information collection and verification process, as well as a service directory backend that powered a publicly available ORServices platform.

(Read more about the DC Community Resource Information Exchange initiative in this blog post here.)

The Dawn of Accessible Databases

When the web was first created, you had to be able to code to publish written statements. Then, content management systems like WordPress were created, enabling people to use a word processor-like interface to publish directly to the web – no coding required. The results: billions of people are now publishing their thoughts online.

A similar trend has begun with database technologies. Just a few years ago, only software developers or advanced users of sophisticated, specialized software could create databases. But now, with DIY database technology like Airtable, anyone with basic spreadsheet skills can create their own custom databases and make their content available via well-documented APIs. The impact will be significant, enabling front-line social service providers to organize information however they like, and share that information between each other and their institutions faster, better and cheaper than ever before.

Open Referral and Sarapis are excited to explore this future with you!


8 responses to “Delivering Open Referral Solutions with Airtable”

  1. Hi guys, looks like a great project and I’m downloading the specs now.
    Just curious if anyone is working on a Drupal module to expose a data collection via REST or other API technique?
    We have a geospatial directory of over 700 Community Gardens across Australia, and I’m in the process of re-building the site now. Was just curious if I could add in some HSDS schema compatibility at the same time.

    1. Hi. Sorry for the delay in responding. You might find this link relevant:

  2. Hi Devin – I’m working with several projects in London where this approach would be really appropriate. We aim to develop asset/services directories, geographic and network maps. Love to connect with you

    1. Hi. Sorry for the delay. Please reach out! Would love to chat:

  3. […] This post was originally published on the Open Referral website March 9th, 2020 […]

  4. […] (The code for the website is available on BAC’s Github repo.) The team is now migrating to the Open Referral Airtable as a ready-made, customizable content management system — an improvement over the Google sheets […]

  5. […] The initial ORServices solution was relatively popular because it enables organizations to manage data in the easy-to-use Airtable platform and then make a subset of that data publicly accessible via the ORServices app. This popularity attracted a number of clients who invested in development of the software to the point it’s at today. You can read more details about those projects here. […]

  6. […] The initial ORServices solution was relatively popular because it enables organizations to manage data in the easy-to-use Airtable platform and then make a subset of that data publicly accessible via the ORServices app. This popularity attracted a number of clients who invested in development of the software to the point it’s at today. You can read more details about those projects here. […]

Leave a Reply

Your email address will not be published. Required fields are marked *