Skip to main content

Appointment slot issue data extract - NHS e-Referral Service

A guide to using NHS e-Referral Service (e-RS) data extracts to identify appointment slot issues (ASI) of more than 180 days old that required acting upon.


Referrals remain on the provider worklist in e-RS (and its predecessor system, Choose and Book) until the earliest of three events occurs. Either:

  • an appropriate action is recorded on e-RS to manage the referral
  • an appointment date passes (Referral for Review Worklist only)
  • 180 days pass since the unique booking reference number (UBRN) last 'entered' the worklist

Where providers follow the guidance for managing and minimising appointment slot issues they will have a complete record of all referrals within their Patient Administration System, even where they have been unable to book an appointment through e-RS within the 180 days window.

Where providers do not have full assurance that they have appropriate records of all appointment slot issues patients, this page will support them in identifying these patients using the e-RS extracts. This guide will identify referrals that have not been managed by providers within e-RS and talk through the principles to be followed but will not provide example Structured Query Language (SQL) code to be used.

Because it is not possible to exactly replicate the front-end behaviour of e-RS (worklist re-entry criteria) from the information in the data extracts, there may be a small number of referrals identified through this process that are still on an appropriate worklist in the front end of e-RS, even when applying any filters more than 180 days.

Find out more information about managing and minimising appointment slot issues.

The structure of e-RS data extracts

Data extracts are made available within e-RS on a monthly basis. The key extracts are:

EBSX02 – UBRN Action: A record of every recorded action taken for all UBRNs with which the organisation had a legitimate relationship in the given month

EBSX03 – Codified Fields: The main table used to decode the information contained within EBSX02 and EBSX05 (field names ending _CD)

EBSX04 – Organisation: An ODS code lookup table

EBSX05 – Service: Information about all the services on e-RS (whether published or unpublished) at the end of each month

The data extracts can be accessed by users with the e-RS information analyst role on their smartcard (B1130) and downloaded as zipped .csv files.

Getting started

Once your organisation has decided the point in time from which it wishes to check the extracts for unmanaged ASIs, it will be necessary to download:

  • all EBSX02 extracts from then to the most recent, to be combined into one data table
  • all EBSX05 extracts from the month preceding the chosen date to the most recent
  • the latest EBSX03 and EBSX04 to decode key fields

The EBSX05 only contains services on e-RS at month end. If a service is removed mid-month it will not appear on that month’s extract. This should be combined into one data table starting with the earliest EBSX05 and then only appending new rows from each subsequent month’s data.

Worked example: A trust changed its processes on the 23 April 2018 to support delivery of  paper switch off and wants to ensure that it has managed all ASIs since then. To support the analysis it will need to download extracts:

  • EBSX02 - from April 2018 to date
  • EBSX05 - from March 2018 to date
  • EBSX03 and EBSX04 -  latest available extracts

Relationships between the extracts and key fields to support analysis

While there are several fields that can be decoded on the EBSX02 and EBSX05 to make the information meaningful to non-analysts, the key fields for this analysis are:


REFERRING_ORG_ID - This lists the ODS code of the referring practice. It can be decoded with the EBSX04 ORG_NAME field. The PARENT_ORG_ID can also be pulled from EBSX04 to identify the practice’s clinical commissioning group (CCG).

ACTION_ID - A sequential reference for all actions recorded in e-RS. Used to establish the order in which actions occurred.

ACTION_DT_TM - The date and time that the action occurred. It should be noted that this field is a text field in the format YYYYMMDDHHMMSS and will need to be converted to a usable format to enable analysis.

ACTION_CD - Lists the actions recorded against each UBRN. Decoded using the EBSX03 DISPLAY field.

ORG_ID - Identifies the organisation of the user carrying out the action. It can be decoded with the EBSX04 ORG_NAME field.

ACTION_REASON_CD - Lists the drop-down reason selected in e-RS to give context to actions such as Appointment Cancellations and Referral Triage. Decoded using the EBSX03 DISPLAY field. Not every action will have an action reason.

SERVICE_ID - Identifies the Service with which the referral was associated (if any) when the action was performed in e-RS. Links to the EBSX05 to bring in key information to support the analysis. Recommended fields to link from the EBSX05 are: SERVICE_NAME, PROVIDER_ORG_ID, LOCATION_ORG_ID and SPECIALTY_CD. Not every action will be associated with a Service ID.

UBRN - The Unique Booking Reference Number for the referral. This will enable Operational users to find and manage the referral in the front end of e-RS.


PROVIDER_ORG_ID - Lists the ODS code of the organisation providing the e-RS service. It can be decoded with the EBSX04 ORG_NAME field.

LOCATION_ORG_ID - Lists the ODS code of the site from which the service is provided. It can be decoded with the EBSX04 ORG_NAME field.

SPECIALTY_CD - Identifies the specialty of the e-RS service. Decoded using the EBSX03 DISPLAY field.


Create a new table or query from the extracts containing the fields outlined above, plus the ‘user friendly’ decoded fields, to give the starting point for the analysis (base data).

Identifying appointment slot issues

Appointment slot issues (ASI) are identified by the ACTION_CD 1430 (Defer to Provider). 

Create a subset of the base data identifying the ASIs that occurred in the period that you are interested in. For this dataset, ensure that for each ASI the deferred to service belongs to your organisation (the PROVIDER_ORG_ID ODS code associated with the SERVICE_ID).

Then, identify all appointment slot issues within your base data. This is because referrals can be deferred to provider more than once and this impacts on the status of the referral. Because a subsequent ASI could have been to a different provider’s service there is no need to restrict by PROVIDER_ORG_ID.

This gives you two new tables/queries:

  1. ASIs of interest (1)
  2. All ASIs

Comparing these two datasets, you can match by UBRN to find later ASIs for the same referral (ACTION_ID is higher than the ACTION _ID of the original ASI). This resets the 180 days worklist period on e-RS with the referral is considered to have re-entered the worklist and means the ASI can be removed from the list of ‘ASIs of interest’.

For example: It is the beginning of September 2019 and a Trust wants to ensure that none of the ASIs received in February 2019 have been removed from the ASI worklist because no actions were recorded within 180 days. 

UBRN1 was Deferred to Provider on the 17th February 2019 and the 23rd March 2019. UBRN1 has a new 180 days period that started on the 23rd March and the Trust can be satisfied that the referral will still be on the ASI worklist (if no other action has been taken to manage it).

UBRN2 was Deferred to Provider on the 3rd February 2019, and again on the 10th and 14th of February. The ASIs on the 3rd and 10th can be ignored because the latest 180 days period started on the 14th of February. However, the Trust will still need to check if any other actions took place to manage the referral and prevent its removal from the worklist before the end of August.

This gives a new, reduced, dataset: ASIs of interest (2).

Identifying actions that manage ASIs in e-RS

As outlined in the Introduction, referrals will remain on the appointment slot issues worklist in the front end of e-RS until the earliest of:

  • an appropriate action is recorded on e-RS to manage the referral
  • 180 days pass since the UBRN last entered the worklist

There are 4 ACTION_CDS that will remove a referral from the ASI worklist:

  1. 1412 (Book Appointment)
  2. 1421 (Cancel Request)
  3. 1423 (Modify Request)
  4. 1810 (Record ASI Outcome) - see further information on these below

Create a subset of the base data identifying all instances of the above ACTION_CDs. You may want to further reduce the size of this ‘Actions’ dataset by restricting it only to actions associated with UBRNs in your ‘ASIs of interest 2’ dataset, or you may choose to leave this until you match the two datasets in the next step.

Matching by UBRN, compare the data in the ‘ASIs of Interest (2)’ and ‘Actions’ datasets. Where a match occurs and the ACTION_ID of the action is higher than the ACTION_ID of the ‘Defer to Provider’ then the UBRN has been managed in the front of e-RS and can be removed from the ASIs of interest (2). 

This leaves your final list of ASIs that have not been managed on e-RS (with the caveat about ‘Record Triage Outcome’ referrals below): if more than 180 days have elapsed since the last worklist entry, the referral will no longer be viewable on the appointment slot issues worklist. Users should be able to find and manage the referral using the patient enquiry functionality within e-RS (until referrals are archived, 550 days after the last action), after which the referring practice could be contacted for information about the UBRN.

Record triage outcome

Functionality introduced by NHS Digital in 2019 enables the triaging of patients on the appointment slot issue worklist before any appointment has been booked. This is done using ‘Triage’ functionality (ACTION_CD 1810 (Record ASI Outcome)) and there are 3 possible outcomes, viewable in the ACTION_REASON_CD field:

  1. 10000021 (Refer/Book Now)
  2. 10000022 (Return to Referrer with Advice)
  3. 10000023 (Accept and Refer/Book Later)

10000022 will, as the name suggests, return the referral to the referrer and the provider no longer needs to be concerned about its management (unless a subsequent booking/ASI occurs).

10000023 will move the referral to the appointment for booking worklist for further management. 10000021 should lead to a booking (or further ASI) of the referral but could also move the UBRN to the appointments for booking worklist, if the process is not completed.

Any ASIs that are moved to the appointments for booking worklist will remain there for 180 days from the date of the ‘Record Triage Outcome’ unless one of the following actions is recorded:

  1. 1412 (Book Appointment)
  2. 1421 (Cancel Request)
  3. 1423 (Modify Request) where the ORG_ID is not the ODS code of the provider managing the referral
  4. 1430 (Defer to Provider)

Using the principles outlined above, this further subset of patients should also be reviewed to ensure appointment slot issue patients have not exceeded 180 days on either the appointment slot issue worklist or the appointments for booking worklist.

Notes for CCG analysts

The CCG EBSX02 extract details all referrals from practices within the CCG and patients referred elsewhere whose registered practice is in the CCG. The same principles can be applied as above to ensure all ASIs have been managed, without the need to restrict the initial dataset of 'ASIs of interest (1)' to a specific provider. The CCG may choose to exclude referrals that did not originate from one of its practices.

The outcome will be a list of ASIs, by provider that look to have been unmanaged within 180 days.

Last edited: 4 February 2020 9:29 am