[2023-07] Appointments now have “status” columns

This article describes changes to the Appointments data element which were introduced in July 2023. It was last updated on October 17, 2023.

Overview

Historically, the “Appointments” data element has only included what’s necessary to power Relevant’s visit planning module—that is, future appointments in some flavor of “pending” status. For analysis of no-show rates, canceled appointments, we’ve relied on the raw EHR tables.

Now we’re introducing several new columns to Appointments: status, raw_status, and show_in_visit_planning. These status columns will make it possible for us to build standard reports that analyze appointment data, and for these reports to work across EHRs. It will also provide users of Data Explorer the ability to see and filter data by appointment status. The show_in_visit_planning column, meanwhile, will allow health centers to continue to specify exactly which visits show up in the Visit Planning module.

  • status is a required column. To ensure consistent data, its values must be one of the following: “completed”, “pending”, “canceled”, “rescheduled”, “no show”, “not seen”, or “other”. If a status is not mapped in the data element, it defaults to “pending”.
  • status_raw is an optional column. It’s meant to contain the “raw” appointment status, as it’s recorded in the EHR. If status_raw is not mapped in the data element, it defaults to NULL.
  • show_in_visit_planning is a required column that must be TRUE or FALSE. This controls whether an appointment is displayed in Relevant’s Visit Planning module. If show_in_visit_planning is not mapped in the data element, it defaults to TRUE.

Mapping required

The new columns are live for all health centers. If you’d like to take advantage of them, you’ll need to map their values. This involves changes to the Appointments data element, and often also involves changes to an underlying “relevant_appointments” transformer. Below, we provide suggestions for the mapping approach, although it may differ based on your pipeline configuration. Reach out to your Relevant analyst lead if you’d like help with the mapping adjustments.

Updating the “Appointments” transformer

Map status and status_raw into your “relevant_appointments” transformer using a case statement to map appointment statuses from the EHR values to the standard statuses in Relevant. In the following example, we use the contains (~*) operator to accomplish this:

CREATE TABLE relevant_appointments AS
SELECT

// ...
// the rest of the select statement is not shown in this example
// the column "raw_ehr_status" is whatever column contains status in the EHR table

CASE WHEN raw_ehr_status ~* 'CANC' THEN 'canceled'
WHEN raw_ehr_status ~* 'CHK' THEN 'completed'
WHEN raw_ehr_status ~* 'CONF|ARR|PEN' THEN 'pending'
WHEN raw_ehr_status ~* 'N\/S' THEN 'no show'
WHEN raw_ehr_status ~* 'R\/S' THEN 'rescheduled'
ELSE 'other' END AS status,

// Typically, we suggest copying the contents of the EHR's appointment status
// field directly into status_raw
raw_ehr_status AS status_raw,

FALSE :: BOOLEAN AS show_in_visit_planning

FROM raw_ehr_appointments_table
WHERE ... ;

You can also add an update statement after your relevant_appointments table is created to use the defined appointments statuses for specifying which appointments are surfaced in visit planning. Since visit planning is used for morning huddles, it’s most useful to select the statuses that indicate an active status, as opposed to missed appointment statuses like ‘rescheduled’ or ‘canceled’.

-- Health center decides what goes into visit planning here

UPDATE custom.relevant_appointments
SET show_in_visit_planning = TRUE
WHERE status IN ('pending', 'completed')

Updating the “Appointments” Data Element

In the Appointments data element, you’ll need to add the newly-mapped fields, either as a Visual Query or with SQL like the following:

SELECT
// the rest of the select statement is not shown in this example
,status AS status
,status_raw AS status_raw
,show_in_visit_planning AS show_in_visit_planning
FROM "custom.relevant_appointments"

Tweaks to various screens

To ensure a smooth transition, we’ve made some tweaks in various modules of Relevant. Appointments are now grouped into “active” statuses (“pending” or “completed”) and “inactive” statuses (“canceled”, “rescheduled”, “no show”, “not seen”, and “other”.)

  • Relevant Outreach: when customizing text messages in Relevant Outreach, all of the next_appt parameters now choose the next “active” appointment that occurs in the future.
  • Excel export of patient listings: when showing a patient’s “next appointment” on an Excel export from a patient listings page, the next “active” appointment that occurs in the future will be chosen.
  • Patient details: when showing a patient’s appointments on the patient details screen, only active, future appointments are displayed.
  • Population Explorer: queries that use the “with appointments between” operator now only consider active appointments.
  • Visit Planning: only appointments where “showin_visit_planning” is TRUE are displayed. ( _Note: If you haven’t taken any action to re-map Appointments, this change will have no impact on visit planning, since the default value for this column is TRUE.)
  • Data Explorer: We’ve introduced new columns and filters, so you can control which appointments you’d like to see for existing or new Data Explorers.