AirTable & Open Referral

Solutions to the Homeless Services Directory Challenge

A Training by Sahana Software Foundation

Devin Balkind, President - devin@sahanafoundation.org

January, 2018 in New York City

Who We Are

  • Sahana Software Foundation makes open source software for disaster management and humanitarian aid organizations.
  • Open Referral is an “initiative to develop data standards and open platforms that make it easy to share and find information about community resources.”
  • Our shared goal is to help nonprofit service and referral providers in New York City create, share and manage information about their services, and the services of other nonprofit and government agencies.
  • Our solution is easier to use than a document or spreadsheet, and can be turned into an online directory for the public and an API for data sharing with other software systems.

Who You Are

A staffer or volunteer at a nonprofit in New York City who wants to:

  • Share information about the health and human services your organization provides.
  • Manage an internal directory of health and human services to which you can refer people.
  • Create a public directory website of health and human services for your specific community.
  • Learn better practices in data management.

Our Offer

  • Training in using AirTable, a free online software program for creating custom databases with a familiar spreadsheet-style interface.
  • An AirTable Template designed to help you manage health and human services data such as: hours, location and eligibility criteria for services.
  • Technical support to help you get your data into the AirTable Template.
  • A directory website (example) that displays your service info to the public.
  • An Open Referral API (example) that makes its easy to share your data with other nonprofits such as Single Stop, Healthify and more.

Short-Term Outcomes

An easy-to-use database for you.

A directory website for the public.

An API for software developers.

Long Term: NYC Nonprofit Data Exchange

HDX (Humanitarian Data Exchange @ humdata.org) is a UN funded project to organize the humanitarian sectors data. We can use best practices they’ve developed to help New York City’s social safety net be more coordinated..

What is Data?

  • Definitions
  • Types
  • Formats
  • Views

What is Data?

“…tokens of value encoded into information.”

http://en.wikipedia.org/wiki/Data

“Data is a value assigned to a thing.”

http://schoolofdata.org/handbook/courses/what-is-data

Information

Data

Knowledge

Conveyed through messages, experiences, observations.

Information structured so it can be analyzed by humans and/or computers.

“Familiarity, awareness, or understanding”

Data is expressed as a “Key-Value” Pair

  • Key defines what is being described
  • Value does the describing

Keys are the top row of a spreadsheet.

Values are the rows below it.

Key

value

value

value

What is a Data Model?

Data models structure information.

They emerge naturally as people identify the info they need - just like walking paths.

Data models answer questions like:

  • What info do you need?
  • How do you describe something?
  • How does one thing relate to another?

Data Models: Schemas

Schema is basically the “Key” in the key-value pair.

It’s the “top row of a spreadsheet”.

It’s also denotes how a column related to a table.

Key

Reference

Data Models: Taxonomies

Sets of terms used to categorize items.

AIRS and OpenEligibility.org offer different taxonomies for human and social services.

Open Referral doesn’t currently specify taxonomies

Data Models: Formats

.csv (comma separated values) is the most common data type.

All spreadsheets (and AirTable) can import/export .csv format on a sheet by sheet basis.

Open Referral uses .csvs and JSON.

Data Models: Licenses

Define what people can do with your data.

“Open” licenses allow you to share your data with others and they can share with you.

Open Referral uses a Creative Commons license.

“A piece of data or content is open if anyone is free to use, reuse, and redistribute it — subject only, at most, to the requirement to attribute and/or share-alike.”

http://opendefinition.org/

What is a Data Standard?

If groups use the same data model, then it becomes very easy for them to share data between their systems.

Data Standards are “official” data models meant to be utilized by many groups.

Components of a Standard

  • Brand & Identity
  • Governance Structure
  • User Community
  • Documented Data Model
  • Tools for using the Data Model

Data Models: Governance

Data model need to change over time so they need a clear processes for deciding what to change and when.

Open Referral semi-regular video meetings and a suite of online collaboration tools to arrive at consensus-based decisions.

Learn more at openreferral.org/about

http://en.wikipedia.org/wiki/File:King_Arthur_and_the_Knights_of_the_Round_Table.jpg

Introducing Open Referral

WHICH agencies?

WHAT services?

WHERE are they located?

WHEN & HOW are they accessed?

What Type of Information Does Open Referral Help Manage?

These details are always in flux. They’re not shared automatically in any particular way. They have to be manually collected and maintained.

CURRENT LANDSCAPE: SILOS: CHAOTIC, WASTEFUL, INEFFECTIVE

SERVICE PROVIDERS spend time chasing information instead of helping clients.

POLICY-MAKERS AND FUNDERS struggle to make data-driven decisions.

INNOVATORS struggle to build & scale new tools that are useful to any of the above.

As a Result...

PEOPLE IN NEED struggle to find and use services that could improve their lives.

So there are many ‘referral services’ — such as call centers, resource directories, and web applications — that collect directory information about health, human, and social services. However, these directories are all locked in fragmented and redundant silos.

A HEALTHY ECOSYSTEM: ACCESSIBLE, INTEROPERABLE, SUSTAINABLE

We need (1) a data standard and (2) free/open publishing platforms to build the foundation of a healthy information ecosystem that enables people to find what they need, wherever they look.

In order to build a world in which everyone can access information about the resources that are available in their community,

we will help community resource directory aggregators address their shared challenge of maintaining up-to-date data,

by developing a publishing platform that enables many different referral providers to collaboratively edit and verify the same information,

so that records can be updated once and instantaneously accessible to an ecosystem of tools and organizations that help people find the information that they need.

We will know that our product has succeeded when community resource data aggregators are publishing and subscribing to a shared set of directory data (rather than continuing to manage it in isolation from each other).

Flow of Information from Your AirTable to the Public and Partners

Mobile-Friendly Directory Website

Open Referral API Facade

ReferNYC

iCarol

Healthify

Etc

Your AirTable

NYC Open Referral Community

As a result, higher quality data about human services will be produced at lower costs to individual referral providers. At the same time, their resource data will be made available to an exponentially broader range of users and uses. This will accelerate innovation of new tools that connect resource data to the people who need it, driving costs further down and yielding a cascade of benefits to governments, funders, service providers, help-seekers, and communities at large. Altogether, Open Referral will improve human service discovery and deliverability, yielding healthier people, more informed decision-makers, and more resilient communities.

Easier for people in need to find and use services.

Easier for service workers to address complex needs.

Easier for referral providers to have better data for cheap.

Easier for policymakers and funders to make data-driven decisions about resource allocation for community health.

AirTable Training

Let’s Get Started

  • Log into your Email
  • Accept the Invitation
  • Create an Account
  • Click on the AirTable that you’ve been assigned

AirTable Resources

  • Guide.airtable.com
  • Airtable.com/templates

Basic Components

  • Bases
  • Tables
  • Views
  • Fields
  • Records

Field Types

  • Single line text
  • Long text
  • Attachment
  • Checkbox
  • Single select
  • Multiple option
  • Date and time
  • Phone number
  • Email
  • URL
  • Number
  • Currency
  • Autonumber
  • Formula

Working with Records

  • Create
  • Link
  • Edit
  • Expand
  • Export

Collaborating and Sharing

  • Sharing
  • Exporting
  • API

Exercise: Create a Resource List

Create Records

+

Paste Spreadsheet Data

+

Connect one Table to Another

+

Share it with a Friend

Open Referral AirTable Template

Open Referral AirTable Template

Simplified to 10 tables:

  • New “Details” table replaces 10 tables.
  • No “Service Location At” table.
  • Two Schedule tables now a single table.

Access the template at: tinyurl.com/ortemplate

Basic Schema

Services

Location

Organizations

Contacts

Details

Phones

Address

Schedule

Service Area

Taxonomy

Table

Description

Notes

Services

Name, descriptions and details about services.

Main table connects to others.

Locations

Geographic information

Connects services to addresses

Organizations

Service providers

Connects services, people, locations

Contacts

People involved in service provisioning.

People you can connect to

Phones

Address

Schedule

Service Area

Taxonomy

Details

Tips

Notes:

  • Use details for custom key/value pairs
  • Addresses are separate from locations.
  • Locations are physical spaces that tie together addresses and organizations. Location is extremely unimportant.

Now that you have an AirTable...

  • Join the Community Support Google Group to get support and connect to other Open Referral AirTable users in NYC.
  • Request a Directory Website which will make your AirTable data publicly accessible in a mobile-friendly format.
  • Request an API so you can join the ecosystem and share your information with other Open Referral compatible software systems.

All this and more at sahanafoundation.org/or-nyc

Thanks for Participating

Contact Me

Devin Balkind

President, Sahana Software Foundation

devin@sahanafoundation.org

Extra Stuff

Types of Data

Qualitative

Quantitative

Categorical

Definition

Refers to the quality of something.

Refers to a number.

Associates with a term, defined by a taxonomy.

Examples

A description of colours, texture and feel of an object , a description of experiences, and interviews.

The quantity of a thing, sizes, the prices, scores, ratings, etc.

Defined an item as “new”, “used”, “broken” or status as “active” or “inactive”.

http://schoolofdata.org/handbook/courses/what-is-data/

Different formats for different software

CSV

XML

JSON

Text Editors and Spreadsheets

News (RSS) & Events (iCAL) Feeds

New web applications and linked databases

“Views” are visualization of data

Grid/Table

Map

Card

How do you create one?

  • Stakeholders come together to determine scope of data they want to standardize.
  • Collect examples of how stakeholders are currently structuring that data.
  • Compare and contrast those various data structures to arrive at insights about the schemas, taxonomies, formats, licenses and governance.
  • Arrive at a data model that everyone can “live with”.
  • Document the model so it’s easy for others to adopt.
  • Create tools that help people adopt it such as templates and validators.
  • Establish a routine for continuous collaboration.

Open Referral’s Schema

Schema’s can be represented as a diagram of connected tables.

They often also have a “specification” document that explains every single field, it’s type and purpose.

The Data Standard

20 Data Tables:

  • Service
    • Eligibility
    • Language
  • Organization
    • Program
    • Contacts
  • Location
    • Address
    • Schedule

As a result, higher quality data about human services will be produced at lower costs to individual referral providers. At the same time, their resource data will be made available to an exponentially broader range of users and uses. This will accelerate innovation of new tools that connect resource data to the people who need it, driving costs further down and yielding a cascade of benefits to governments, funders, service providers, help-seekers, and communities at large. Altogether, Open Referral will improve human service discovery and deliverability, yielding healthier people, more informed decision-makers, and more resilient communities.

Easier for people in need to find and use services.

Easier for service workers to address complex needs.

Easier for referral providers to have better data for cheap.

Easier for policymakers and funders to make data-driven decisions about resource allocation for community health.

Open Referral protocols:

“Human Services Data Spec” and “Human Services Data API”

  • Free to use: Creative Commons
  • Developed by an open community of practice
  • Enables interoperability: systems can ‘talk’ to each other
    • Call centers: 2-1-1, 3-1-1
    • Case management: Salesforce, CiviCRM, etc
    • The Web: Google, Bing, Facebook, Yelp, etc

Open Referral

Data Standards

Our resource data toolkit enables referral providers to:

  • Aggregate data from multiple sources
  • Transform to interoperable format
  • Resolve conflicts between records
  • Verify accuracy of records
  • Republish as open data

Open Referral

Publishing Platform

Open Referral Data and AirTable Training - Google Slides