Built for the Alberta Law Foundation
Delivered 2021–2023 · since retired

Turning legal-clinic client surveys into evidence for funders

An Oracle APEX application that took raw client surveys from Alberta's legal clinics, cleaned and categorised them, and turned them into 116 charts on client outcomes, legal problems and demographics.

Oracle APEX pages
57
Oracle APEX pages
charts on outcomes, problems and demographics
116
charts on outcomes, problems and demographics
legal-problem types tracked per respondent
26
legal-problem types tracked per respondent
linked surveys: initial and follow-up
2
linked surveys: initial and follow-up

What the platform delivers

One evidence base

Initial and follow-up surveys from every clinic in one database instead of scattered spreadsheets.

Validated on the way in

Uploads were validated, and bad rows were kept for review instead of being lost.

Comparable categories

Free-form legal problems were mapped onto a maintained category list, so clinics compared like with like.

Each user sees their own clinics

Dashboards were filtered to the clinics each user had been granted.

Built with
  • Oracle APEX
  • Oracle Database
  • PL/SQL
  • Oracle JET charts
  • APEX data parser
  • DBMS_SCHEDULER
  • DML error logging
  • Liquibase
The application

From spreadsheet to evidence, in one place

Loading, cleaning, categorising and reporting all happened in the database, so every chart traced back to a validated survey row.

Survey upload

Initial or follow-up survey spreadsheets (XLSX or CSV) loaded in batches.

Upload batches

Every load numbered and logged; users reviewed their own batches.

Error review

Rows that failed validation went to an error log with a dedicated review page.

Data clean-up

Functions standardised clinic names, locations and rights-and-responsibilities answers.

Problem categorisation

Legal problems mapped to categories; an unmapped-problems page showed the gaps.

Category management

Admins maintained legal-problem and education categories and their mappings.

Initial-survey dashboards

Demographics, language, appointment length, legal problem and client outcomes.

Follow-up dashboards

Whether advice was followed, why not, and what clients did afterwards.

Appointment heatmap

How appointments spread across the calendar.

Advanced reports

Faceted search and interactive reports over the cleaned survey data.

Administration

Users, roles, clinic access, custom error messages, activity and feedback.

The workflow

From a clinic's spreadsheet to a funder's chart

The same path for every clinic and every survey round, so results were comparable across the province.

  1. 01 Sign-in

    Sign in

    The application's own login, with salted, hashed passwords.

  2. 02 Upload

    Load a survey

    A clinic's initial-survey spreadsheet is parsed, given a batch number and recorded.

  3. 03 Error log

    Review rejects

    Rows that failed validation are reviewed on their own page and fixed at source.

  4. 04 Clean-up

    Standardise

    Clinic and location values are standardised so they group correctly.

  5. 05 Scheduler

    Categorise

    Legal problems roll up into categories every 10 minutes; admins resolve the unmapped ones.

  6. 06 Upload

    Follow up

    The follow-up survey is loaded the same way and linked to the initial round.

  7. 07 Dashboards

    Read the evidence

    Reviewers open dashboards filtered to the clinics they may see.

  8. 08 Reports

    Drill and download

    Drill into cleaned records and download from the interactive reports.

Data quality & access

Built so the numbers could be trusted

Validation lived in the database, not in a spreadsheet macro, and no respondent was identifiable.

79 database checks

Check constraints validated every answer on the initial and follow-up survey tables.

Nothing silently dropped

Oracle DML error logging kept every rejected row for review.

Three roles

Administrator, contributor and viewer, each with its own authorization scheme.

Clinic-level filtering

Per-user clinic access filtered every dashboard's choices.

No client identities

Respondents were stored by ID and banded demographics; no names or contact details.

Category history

Triggers recorded every change to the category and mapping tables.

Delivery

Built in two waves, then retired

The application served its evaluation and has since been retired. Its source and schema remain in version control.

  1. 01

    Source and schema in git

    The APEX application and the database schema, captured as Liquibase changelogs, were kept in git.

  2. 02

    Features behind build options

    Access control, activity reporting and feedback could be switched on per deployment.

  1. Nov 2021
    Main build
  2. May 2022
    Automatic categorisation scheduled
  3. Sep 2022
    Second build wave
  4. May 2023
    Version 1.0, 57 pages
The full story

Alberta Legal Clinics — Summary Advice Evaluation

Alberta Law Foundation

Alberta’s legal clinics give clients summary advice: a short appointment that helps someone understand a legal problem and what to do next. The Alberta Law Foundation, which funds the clinics, wanted to know whether that advice worked. The answer was in client surveys taken at the appointment and again at follow-up.

The problem with the raw data

Each clinic’s surveys arrived as spreadsheets. Clinic and location names were spelled several ways, legal problems were described in free text, and a bad row could quietly disappear in a copy-and-paste. Comparing clinics, or linking an initial survey to its follow-up, meant cleaning everything by hand every time.

What the application did

Surveys were uploaded in numbered batches and parsed in the database. Every answer was validated by check constraints, and rows that failed were kept in an error log with their own review page rather than dropped. Clean-up functions standardised clinic and location values, and a scheduled job mapped legal problems onto a category list that administrators maintained, with a page listing anything still unmapped.

On top of the cleaned data sat 116 charts: who the clients were, what legal problems they brought, how long appointments took, whether clients followed the advice, and what they did next. Each user saw only the clinics they had been granted.

Status

The application was in use from 2021 to 2023 and has since been retired.

Ready to Build Something Similar?

Let's discuss how we can bring the same expertise to your organization.

Start a Conversation