Data extraction protocol for secure settings
Updated 30 November 2018
The prison National Drug Treatment Monitoring System (NDTMS) data items to be extracted for a local audit of continuity of care should include the following:
- first name
- surname
- date of birth
- sex
- client reference (PNOMS ID)
- initial reception date
- drug 1
- drug 2
- drug 3
- discharge reason
- prison exit date
- prison exit reason
- prison exit destination (DAT code)
- referral on release status
These fields should be populated for each client released from the establishment and recorded on NDTMS as transferred to community treatment services. The criteria for the extract includes:
- discharge reason = transferred 鈥� not in custody
- prison exit date = [between the dates agreed to be covered by the audit]
- prison exit reason = released
- prison exit destination (DAT code) = [DAT code of receiving partnership who will be undertaking the audit]
To produce the NDTMS extract for populating the audit template spreadsheet, the provider in the participating prisons should follow these instructions:
1. Take a local extract from the data entry tool
Take a local extract from the data entry tool (DET) or local case management system on which NDTMS data is recorded. This will include clients鈥� full names to share with the community treatment service. Ensure the extract is in 鈥榲alues鈥� not 鈥榗ode鈥� for ease of interpretation.
To filter the extract to give only the relevant clients, follow these steps:
Step 1: Turn on filters, by highlighting the top row of the extract (spreadsheet) and then in the 鈥楬ome鈥� tab, click on 鈥楽ort & Filter鈥� and then select 鈥楩ilter鈥� from the dropdown list.
Step 2: Highlight column EPISODID, select 鈥楧ata menu鈥� and click on 鈥榬emove duplicates鈥�.
Step 3: When the box below appears, ensure 鈥榚xpand the selection鈥� is ticked and click 鈥榬emove duplicates鈥�.

Step 4: When the box below appears, click 鈥榰nselect all鈥�, scroll down and click EPISODID and click 鈥極K鈥� 鈥� this will ensure that only one row per episode is extracted.

Step 5: Find header EXITD 鈥� click on the arrow to the right of the header and then in the drop-down box untick 鈥榮elect all鈥� and select only those clients who exited the prison in the time frame agreed for the audit (for example, the last quarter).
Step 6: Ensure 鈥榖lanks鈥� is not ticked.
Step 7: Find header DISRSN 鈥� click on the arrow to the right of the header and then in the drop-down box untick 鈥榮elect all鈥� and tick only those client with a discharge reason of 鈥榯ransferred 鈥� not in custody鈥� (code 83).
Step 8: Find header EXITRSN 鈥� click on the arrow to the right of the header and then in the drop-down box untick 鈥榮elect all鈥� and tick 鈥榬eleased鈥� (code R).
Step 9: Select the whole worksheet by clicking the box in the top left hand corner between 鈥楢鈥� and 鈥�1鈥�.
Step 10: Right click and then copy and paste the data into a new spreadsheet, selecting 鈥榲alues鈥� under paste options.
Step 11: Delete all columns from the extract other than: FIRSTNAME (the header may be something other than FIRSTNAME depending on the local software system but it will be the column that contains the clients鈥� full first name 鈥� not their initial), LASTNAME (the header may be something other than LASTNAME depending on the local software system but it will be the column that contains the clients鈥� full last name 鈥� not their initial), DOB, SEX, CLIENT, INTRCPTD, DRUG1, DRUG2, DRUG3, DISRSN, EXITD, EXITRSN, EXITDEST, RTOAGNCY 鈥� this should result in a spreadsheet with just these headers.
Step 12: Save this as 鈥楥lients transferred from HMP XXXX between DDMMYY and DDMMYY鈥�.
2. Ensure each local authority only receives details of clients transferred to them
To do this:
Step 1: Turn on filters, by highlighting the top row and then in the 鈥楬ome鈥� tab, click on 鈥楽ort & Filter鈥� and then select 鈥楩ilter鈥� from the dropdown list.
Step 2: Find header EXITDEST and click on the arrow to the right of the header.
Step 3: Untick 鈥榮elect all鈥� and select the code for the first DAT of interest.
Step 4: Select the worksheet by clicking the box in the top left hand corner between 鈥楢鈥� and 鈥�1鈥�.
Step 5: Right click and then copy and paste the data into a new spreadsheet, selecting 鈥榲alues鈥� under paste options.
Step 6: Save this as 鈥楥lients transferred from HMP XXXXX to XXXX (DAT) between DDMMYY and DDMMYY鈥�.
Step 7: Return to the spreadsheet saved as 鈥楥lients transferred from HMP XXXX between DDMMYY and DDMMYY鈥�, untick the previously selected DAT code and select the next DAT code of interest.
Step 8: Repeat this process to produce a spreadsheet for each partnership of interest.
3. Share the information securely
Once you obtain the relevant authorisation to share this information with the community services, you should establish a secure transmission method (such as the Criminal Justice Secure Mail (CJSM) email with additional 7-zip encryption). Using this secure transmission method, you can send the treatment provider the spreadsheet of clients that were transferred to them.
You should be very careful to ensure that each provider is sent the correct spreadsheet to prevent any accidental data breaches.