Exploration of Airtable for use with Legal Capacity Inclusion Lens

Owned by Justin Obara
Last updated: Jan 26, 2022

Overview

The Legal Capacity Inclusion Lens (LCIL) is a tool being developed and planned for use with the Right to Decide (R2D) project. The purpose is to assess various provisions within a regime against a set of measures. The LCIL has been developed by Chronicle Analytics as an Excel Application; which will be distributed to researchers and provides capability to upload the data to a centralized database.

The IDRC is exploring the creation of  a web application for the LCIL. Some goals are:

  • improved portability and availability across platforms
  • ease of updating and maintainance
  • eventual integration with a legal capacity learning exchange platform that will be developed for the R2D project

An initial spike (or technical prototype) is using Airtable to help explore the technical requirements, workflows, and usage. Additionally we are able to explore if Airtable can be used as an alternative to the Excel based LCIL, and Airtable as a rapid prototyping platform in general.

Summary of findings

In the right contexts, Airtable is a useful as a tool for entering, storing and viewing data. It also has potential for rapidly prototyping applications used for viewing and manipulating data. However, cursory accessibility testing has uncovered usability issues.

Overall, there are a number of a limitations that prevent it from being used to implement the LCIL.

Limitations

  • Pricing/Plans
    • The free tier only includes 1200 records per base (a base is analogous to a database, a record is a row in a table)
      • For context, storing the Measures alone already accounted for 53 records. Depending on the number of provisions in a Law/Policy and how many Jurisdictions are needed, that total could be exceed very quickly.
    • Limited to 100 automations per month in the free tier and can only automate scripts in the pro tier and higher
    • It's unclear how many records will eventually be needed and how many seats will be required.
      • Read-only collaboration, share links, and form submissions are not charged.
    • Some features are only available to pro and higher tiers. 
  • UI building
    • Limited ability to construct forms and interfaces
      • Limited ability to link between forms
        • Can really only provide bare urls in the text descriptions for a form field
      • Can only add fields which are reflected in the table
      • Can't group fields (e.g. using a fieldset) 
      • Can't edit a record with a form
      • Can't add forms to interfaces
      • limited options for elements that can be added to an interface
    • There are some accessibility concerns. See "Initial Accessibility Review" below.
  • Views of data
    • While there are several presentations of views (e.g. grid/table, kanban, etc.) It may not always be enough for what is needed. Some options like gant charts are only available in paid tiers.
    • These views can be visually complex with many different controls and options. This may be a usability and accessibility issue for some.
    • Can't do traditional queries and joins of data. However, there are ways to link tables, do lookups and create views with filtering, grouping and sorting applied.
  • Workflow
    • Can't build out a step-by-step workflow for interacting with the data.
    • It isn't clear where to start or what order data would need to be entered in across tables.
    • Can't require a field/record be unique; even with the primary field. There may be workarounds with apps, automations, or other integrations.
  • Accessibility - A very superficial evaluation of accessibility was done using Windows 10 and NVDA:
    • Visual order does not match tab order or document order. For example, some content in the Kanban table receives visual focus at the bottom first, before content above it.
    • Some form fields that are marked as required are not announced as required by the screen reader
    • Not all interactive elements (like menus) have focus styling
    • Can not specify custom text for URLs in form field descriptions - the result is the whole URL being read out by the screen reader.
    • Form check boxes do not have proper labels or aria-labelledby associations

Airtable Prototype / Spike

We created an LCIL base to hold and organize the information for the LCIL. Since this is a prototype, the data, views and etc. will require further refinement as the project progresses.

Because Airtable does not allow creation of customizable workflow interfaces, the following are the forms used to enter data into the tables:

The following are the different views of the data were created to help with viewing the progress of making the assessments and reviewing the results.

  • The kanban view quickly shows the status of all the Provision Measures, allowing for a quick view of what needs work and what has been completed. 
  • The Measure view organizes the Provision Measures by Measure.
  • The Source of Law and Policy view organizes the Provision Measures by Law and Policy Source.
  • The Jurisdiction view organizes the Provision Measures by Jurisdiction.
  • The Regime Assessment view organizes the Provision Measures by Regime Assessment

An example Regime Assessments Interface was created to provide a dashboard for the status of Regime Assessments. Other interfaces could be created for other reports, summaries, or as alternative ways to enter data. However, construction of interfaces is limited to pre-defined elements that can be placed in an interface builder tool.

Airtable Database Schema

The overview of the schema structure is:

  • Dimensions
    • Dimension (primary key): name of the dimension
    • Code
    • Indicators: link to many indicators
  • Indicators
    • Code (primary key)
    • Description
    • Dimension: link to parent dimension
    • Measures: link to many Measures
  • Measures
    • Name (primary key): a concatenation of the Code and Measure fields.
    • Code
    • Description
    • Provision Type
    • Indicator: link to parent indicator
    • Measure: name of measure
    • Law and Policy Source: back reference to Law and Policy Sources; created when linking to Measures in the Provision Measures
  • Jurisdictions
    • ID (primary key): a concatenation of Country, Territory, and Municipality fields.
    • Country
    • Territory: the territory within a country, e.g. province, state, territory, etc.
    • Municipality: the municipality, e.g. city, town, etc.
    • Jurisdiction Level
    • Law and Policy Source: back reference to Law and Policy Sources; created when linking the source to a Jurisdiction
    • Regime Assessments: back reference to the Regime Assessments; created when linking Regime Assessment to a Jurisdiction
  • Law and Policy Source
    • Name (primary key)
    • Core/Supplemental
    • Type
    • Reference: URL reference to the source
    • Year in effect
    • Created By: generated with the collaborator info if available (unknown if created with a form)
    • Created: date and time the record was created
    • Last Modified: date and time the record was most recently modified
    • Last Modified By: generated with the collaborator info
    • Provisions: link to many Provisions
    • Jurisdiction: link to the Jurisdiction the Law or Policy applies to
    • Regime Assessments: back reference to the Regime Assessments; created when linking Law and Policy sources to a Regime Assessment
  • Provisions
    • ID (primary key): a concatenation of the Source of Law and Policy and Section/Subsection fields
    • Provision Text
    • Source of Law and Policy: link to a Law and Policy Source that the provision belongs to.
    • Section/Subsection: section within a Law or Policy. e.g. 1.1
    • Link to Provision: URL reference to the source
    • Type of Decision
    • Subject to Constitutional Challenge
    • Is result of a court challenge
    • Decision Citation
    • Provision Measures: back reference Provision Measures that linked to the provision
    • Jurisdictions: lookup from the linked Law and Policy Source indicating the Jurisdiction the provision applies to
  • Provision Measures
    • ID (primary key): a concatenation of the Measures and Provisions fields.
    • Comments
    • Status
    • Assessment
    • Measures: a link to the Measure being assessed
    • Provisions: a link to the Provision being assessed
    • Regime Assessments: a link to the Regime Assessment this assessment is being performed for
  • Regime Assessments
    • Name (primary key): a concatenation of the Jurisdiction and Date fields
    • Jurisdictions: a link to the Jurisdiction being assessed
    • Created By: generated with the collaborator info if available (unknown if created with a form)
    • Created: date and time the record was created
    • Last Modified: date and time the record was most recently modified
    • Last Modified By: generated with the collaborator info
    • Date: the date that the Regime Assessment is applicable for
    • Law and Policy Sources: link to many Law and Policy Sources that make up the regime
    • Provision Measures: link to the many Provision Measures that comprise the assessment
    • Status Rollup: the unique Provision Measure statuses. Used to indicate what the state of the Regime Assessment as a whole, in the Regime Assessment Interface

Database Schema for Airtable LCIL Data

Measures

The measures are used to assess provisions. The measures are defined across the Dimensions, Indicators and Measures tables. Although primarily one would access the information from the Measures table, and this is what is linked to from other tables in the base. The three tables organize the hierarchy of structure Dimensions > Indicators > Measures.

Jurisdictions

A Jurisdiction is a location that is used to define where a Law or Policy is applicable. A Jurisdiction can be a Country, Territory (e.g. Province), or Municipality. In the Airtable prototype / spike, these have all been added to the same table with fields for each level. There is also a Jurisdiction Level to indicate what level the law applies to. This current structure is just a rough approach and will likely need to be re-organized. For example having separate tables for each level of government similar to how the Measures are organized, or taking the approach from the LCIL Excel document and having all of the jurisdictions be part of the same table with references between the records to indicate parent-child relationships.

A Jurisdiction form was created to simplify adding new Jurisdictions. This can be shared with anyone to submit new Jurisdictions.

The Jurisdiction table has also been represented with other grid views to present the data by Country, Territory or Municipality. 

Law and Policy Sources

A Law and Policy Source documents a specific Law or Policy that will be assessed. A Law and Policy Source contains information about its type, when it came into effect and a reference link. Law and Policy Sources also link to their many Provisions. In the prototype / spike we've linked a Law and Policy Source to a single Jurisdiction. However, more thought needs to go into how these laws apply to lower levels of government. For example, is it possible that a federal act may not be applicable to all territories and regions. 

The Law and Policy table is also represented as a Law and Policy Source form to simplify adding new sources. This can be shared with anyone to submit new sources.

Provisions

A Provision contains a single provision from a Law or Policy. Each Provision will link to the Law and Policy Source it comes from, and includes information such as: section, text, reference link, and other metadata.

The Provision table is also represented as a Provision form to simplify adding new provisions. This can be shared with anyone to submit new provisions.

Provision Measures

A Provision Measure indicates how well, if at all, a provision applies to a measure. The Provision Measure will include links to a Measure and a Provision, and provide a ranking for how well the Provision meets the Measure, comments, and a status of the assessment.

It is assumed that there can only be one Measure/Provision pair per Regime Assessment. In the Excel version, the parent grouping is called an "assessment". What we refer to here as a Provision Measure is a MeasProv in the Excel application.

Regime Assessments

A Regime Assessment includes all of the Law and Policy Sources for the Jurisdiction under review, during a specified time/date. It will include Provision Measures to assess all how well those laws/polices meet the Measures. While typically the laws/policies will be from the same Jurisdiction as the regime assessment, there may be some cases where things cut across. For example, a court ruling at the federal level may affect a lower jurisdiction.

In the Excel version, the parent grouping is called an "assessment" ( like a project ).