Difference between revisions of "Patient Record (PAS) Lookup"

From TMS Support Wiki
Jump to navigation Jump to search
 
(59 intermediate revisions by the same user not shown)
Line 1: Line 1:
Your PTS application is likely to be connecting to a patient record database of some sort. This is the facility that looks up the patient details on the booking in screen.
Your PTS application is likely to be connecting to a patient record database of some sort. This is the facility that looks up the patient details on the booking in screen.


By far the most popular choice is to connect PTS to the JAC/WellSky/CMM (hereafter "CMM") dispensing database, and our technician will have created this link for you during installation if the option was available to them.
=ODBC=
 
For now the most popular choice is to connect PTS to the JAC/WellSky/CMM or Ascribe/EMIS database using an ODBC data source (Administrative Tools > ODBC Data Sources).
When the CMM database (or other patient record database) is moved or modified (e.g. as part of an upgrade or server decommission) the link between PTS and the patient record database will be broken.
==Application Settings==
 
===Important Settings===
It's worth pointing out that PTS remains usable even if the patient lookup goes down - users can just enter patient details manually.
<ul>
<li><strong>PASDSNS</strong><br/>
The name of the patient lookup data source. This can also be set to a direct connection string. You can specify multiple comma-separated entries here which is useful for switchover periods.</li>
<li><strong>LOCALIDQRY</strong><br/>
Query used by booking-in screen when searching for a patient using localID.</li>
<li><strong>NATIDQRY</strong><br/>
Query used by booking-in screen when searching for a patient using nationalID.</li>
</ul>
PTS decides whether to send a local or national ID query based on what the end-user entered on the booking-in screen. Local ID usually refers to <strong>Hospital number</strong>, and National ID usually refers to <strong>NHS number</strong>. More often than not the two queries are near identical save for the WHERE clause.


==PTS 5.0.3.1 and newer==
With regard to server moves and updates, assuming the data structure of the new patient record database remains the same as before, more than likely you'll only need to amend the <strong>PASDSNS</strong> setting and/or the server data source itself to repoint it to the new database location. However an advanced user could redesign the queries here if necessary.
Starting in PTS 5.0.3.1, the PAS link information is stored in Application Settings and is modifiable by a system administrator. The relevant settings are as follows:


===Optional Settings===
<ul>
<ul>
<li><strong>LOCALIDF</strong><br/>
<li><strong>LOCALIDF</strong><br/>
Format of Local ID. This allows prefix or suffix characters to be added to Local IDs. Alternatively you could handle this in the query itself.</li>
Format of Local ID. This allows prefix or suffix characters to be added to Local IDs. You could handle this in the query itself but it may be tidier to format it here.</li>
<li><strong>LOCALIDQRY</strong><br/>
Query used by booking-in screen when searching for a patient using localID. PTS will look for the following field names returned by the query: NationalID, LocalID, Title, Forename, Familyname, Gender, Mobile, EMail, DOB, Address.</li>
<li><strong>NATIDF</strong><br/>
<li><strong>NATIDF</strong><br/>
Format of National ID. This allows prefix or suffix characters to be added to National IDs. Alternatively you could handle this in the query itself.</li>
Format of National ID. This allows prefix or suffix characters to be added to National IDs. You could handle this in the query itself but it may be tidier to format it here.</li>
<li><strong>NATIDQRY</strong><br/>
Query used by booking-in screen when searching for a patient using nationalID. PTS will look for the following field names returned by the query: NationalID, LocalID, Title, Forename, Familyname, Gender, Mobile, EMail, DOB, Address.</li>
<li><strong>PASDSNS</strong><br/>
Patient Lookup DSNs. This can also be set to a direct connection string.</li>
</ul>
</ul>


Assuming the data structure of the new patient record database remains the same as before, more than likely you'll only need to amend the <strong>PASDSNS</strong> setting and/or the server data source itself to repoint it to the new database location, however an advanced user could redesign the queries here if necessary. Use aliases in the queries to ensure the field names match what is required by PTS.
==After changing Application Settings==
==PTS 5.0.3.0 and older==
Unlike most other Application Settings, changes to the PAS link may require the PTS application to be restarted for any changes to take effect. If the DNS connection test passes but PTS still cannot find any patients (an error message may be written to the forename or surname fields, or nothing may happen at all), try restarting the web site in IIS, and/or recycle the PTS application pool. If it's easier, restarting the server entirely should also work.
If you are running a slightly older version of PTS the data source name will be hardcoded - <strong>PTSPAS</strong> - so you should locate this data source on the PTS server and either modify it, or replace it with a new data source while retaining the PTSPAS name.
 
If the problem persists make a note of the error message and request a support ticket.
 
==Query guidelines==
===Patient ID parameter===
Include "{PATIENTID}" (without quotes) in your WHERE clause at the point PTS should pass through what the user entered in the Local or National ID field.


Much older versions of PTS had the patient record connection information embedded in a DLL file that cannot be modified. If this is the case you should contact us to arrange a PTS software update.
No matter whether the local or national ID is used, the input parameter is always {PATIENTID}.


==When data source is not yet in use==
===Output===
The query should return values with the following field names. Use aliases to 'rename' fields. You don't have to return fields you don't want to use.
<ul>
<li>NationalID</li>
<li>LocalID</li>
<li>Title</li>
<li>Forename</li>
<li>FamilyName</li>
<li>Gender</li>
<li>Mobile</li>
<li>EMail</li>
<li>DOB</li>
<li>Address</li>
</ul>


You should install and configure the data source and connect PTS up to it.
===Example JAC/WellSky/CMM Local ID query===
<nowiki>
SELECT patient_hospital_no.lnkphn LocalID, patient_basic.forenames Forename, patient_basic.surname FamilyName, patient_basic.nhs_no NationalID, CASE patient_demographics.sex WHEN 'Male' THEN 1 WHEN 'Female' THEN 2 ELSE 3 END Gender, CASE patient_demographics.patient_reference WHEN 'MR' THEN 4 WHEN 'MS' THEN 1 WHEN 'MRS' THEN 3 WHEN 'MISS' THEN 2 ELSE 0 END Title, patient_demographics.telephone_no Mobile, TRIM(LEADING ', ' FROM ifnull(patient_demographics.address1,'',UPPER(patient_demographics.address1))||ifnull(patient_demographics.address2,'',', '||UPPER(patient_demographics.address2))||ifnull(patient_demographics.address3,'',', '||UPPER(patient_demographics.address3))||ifnull(patient_demographics.address4,'',', '||UPPER(patient_demographics.address4))||ifnull(patient_demographics.postcode,'',', '||UPPER(patient_demographics.postcode))) Address, patient_demographics.birth_date DOB FROM JAC.patient_basic patient_basic, JAC.patient_demographics patient_demographics, JAC.patient_hospital_no patient_hospital_no WHERE patient_demographics.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkphn = ' {PATIENTID}'</nowiki>
Note the space character preceding {PATIENTID}. This is a quirk of the CMM database, and is an example of something you could handle in the LOCALIDF setting.


Install the 64-bit ODBC data source on the PTS application server using the CACHE or IRIS drivers, configured in the same way that the data source is installed on pharmacy PCs, and name it "PTSPAS" ("CACHE" seems to be the name used on a typical pharmacy PC).
===Example JAC/WellSky/CMM National ID query===
<nowiki>SELECT patient_basic.hospital_no LocalID, patient_basic.forenames Forename, patient_basic.surname FamilyName, patient_basic.nhs_no NationalID, CASE patient_demographics.sex WHEN 'Male' THEN 1 WHEN 'Female' THEN 2 ELSE 3 END Gender, CASE patient_demographics.patient_reference WHEN 'MR' THEN 4 WHEN 'MS' THEN 1 WHEN 'MRS' THEN 3 WHEN 'MISS' THEN 2 ELSE 0 END Title, patient_demographics.telephone_no Mobile, TRIM(LEADING ', ' FROM ifnull(patient_demographics.address1,'',UPPER(patient_demographics.address1))||ifnull(patient_demographics.address2,'',', '||UPPER(patient_demographics.address2))||ifnull(patient_demographics.address3,'',', '||UPPER(patient_demographics.address3))||ifnull(patient_demographics.address4,'',', '||UPPER(patient_demographics.address4))||ifnull(patient_demographics.postcode,'',', '||UPPER(patient_demographics.postcode))) Address, patient_demographics.birth_date DOB FROM JAC.patient_basic patient_basic, JAC.patient_demographics patient_demographics, JAC_System.PatientName patient_name WHERE patient_basic.lnkpid = patient_name.PatientId AND patient_demographics.lnkpid = patient_basic.lnkpid AND patient_name.NationalNo = '{PATIENTID}'</nowiki>


Once the data source passes a connection test, PTS needs to be modified to use it. The following DLL file on your PTS server contains the PAS connection information:
==PTS 5.0.3.0 and older==
If you are running a slightly older version of PTS the data source name will be hardcoded - <strong>PTSPAS</strong> - so you should locate this data source on the PTS server and either modify it, or replace it with a new data source while retaining the PTSPAS name. You are not able to modify the query yourself prior to PTS 5.0.3.1.


<blockquote>
Much older versions of PTS had the patient record connection information embedded in a DLL file that cannot be modified. If this is the case you should contact us to arrange a PTS software update.
<i>[PTS Installation Path]</i>\bin\PTSPatientLookup.dll<br/>
e.g. c:\inetpub\wwwroot\PTSWeb\bin\PTSPatientLookup.dll
</blockquote>


<ol>
=HL7=
<li>Make a backup of your existing PTSPatientLookup.dll file.</li>
Patient lookup via HL7 message is also possible with a QBP Q21/K21 query/response as per the [https://tmsinsight.sharepoint.com/:w:/s/PTS/ES58-VE6SSZMkYV3mp_Gy6kBI_CxyU9vZEc_9O-S1YnZXw?e=0bA6Yy HL7 Specification] (see pages 46 & 47).
<li>Do not keep the backup anywhere inside the PTSWeb directory or its subdirectories, even if you rename it. It will cause a conflict.</li>
<li>[[Media:PTSPatientLookup.zip|Download the updated PTSPatientLookup.dll file]]. This version references the PTSPAS data source.</li>
<li>Extract the DLL from the zip file and move it to the bin folder, confirming the overwrite.</li>
<li>Test the patient lookup on the booking in screen. It's normal for PTS to take a while to load immediately after changing a DLL. Testing using Internet Explorer on the web server itself may not work as it often has Javascript disabled.</li>
</ol>


If the test fails, restore the backup and contact TMSi to arrange a date and time for one of our support technicians to remote access your PTS server and set this up for you.
PTS sends a QPD Q21 query, and expects a PID in the K21 response.
==Application Settings==
<ul>
<li><strong>PASDSNS</strong><br/>
Set this to "<i>HL7,</i>" followed by a list of listener IP addresses and ports in the format IP;PORT. e.g. <i>HL7,192.168.1.0;1000,192.168.1.1;1000</i>.</li>
<li><strong>LOCALIDQRY</strong><br/>
Set this to the identifier type code for a local ID lookup e.g. <i>MRN</i>.</li>
<li><strong>NATIDQRY</strong><br/>
Set this to the identifier type code for a national ID lookup e.g. <i>NHS</i>.</li>
<li><strong>COMPANY</strong><br/>
The company setting is sent as the <strong>Receiving Facility</strong> in the MSH. If this is checked and needs to be a specific string, you can adjust it here.</li>
</ul>
PTS decides whether to send a local or national ID query based on what the end-user entered on the booking-in screen. Local ID usually refers to <strong>Hospital number</strong>, and National ID usually refers to <strong>NHS number</strong>.


Have the following information handy:
==HL7 Examples==
<ol>
Following are two examples of a Q21 query that might be sent by PTS to lookup a patient.
<li>The connection details of your new patient record database (server name, database/namespace name, uid, password, port etc.).</li>
<li>The date and time the patient record migration/update/etc is taking place.</li>
<li>A date and time after this you would like the remote update to take place.</li>
</ol>
Note: CMM seems to use the same database name, uid and password across the board, so we can usually infer these.


==Timing of the update==
===Local ID/Hospital Number===
We're commonly asked if we can implement the update beforehand on a test basis and have it ready for the CMM server switchover. Unfortunately this isn't logistically possible. Firstly, until the new server is live, we cannot test a connection to it. Secondly implementing the change prematurely would break the existing patient lookup for no good reason; there is no mechanism to connect to two databases from one data source.  
In this example, the user searched for Hospital Number "123456789". The Local ID identifier type code has been configured to "MRN" and the Receiving Facility is "My NHS Trust" (see above):
<nowiki>MSH|^~\&|PTSWeb|Dispensary|PAS|My NHS Trust|20240221140617||QBP^Q21|381b7131-c9bf-4c43-ae08-bb6aec0ae438|P|2.4
QPD|Q21^Get Patient||123456789^^^MRN^MRN
RCP||1^RD</nowiki>


Essentially when the connection would pass any meaningful test, the change would be ready to implement anyway.
===National ID/NHS Number===
In this example, the user searched for NHS Number "123456789". The National ID identifier type code has been configured to "NHS" and the Receiving Facility is "My NHS Trust" (see above):
<nowiki>MSH|^~\&|PTSWeb|Dispensary|PAS|My NHS Trust|20240221142737||QBP^Q21|396236dd-ea41-4048-a328-10223a2c5912|P|2.4
QPD|Q21^Get Patient||123456789^^^NHS^NHS
RCP||1^RD</nowiki>


It's important to reiterate the non-critical nature of this facility. It is just an auto-complete of the patient's name on the booking in screen which can be typed in by hand at any time.
===Example Response===
A typical response may look like this:
<nowiki>MSH|^~\&|PAS|My NHS Trust|PTSWeb|Dispensary|20240321103803||RSP^K21^RSP_K21|0b0efc65-a100-41f1-1585-f7b02e6d6c87|P|2.4|||NE|NE|
MSA|AA|f1fe8462-a884-4e89-af9f-0b3c9e80353f|
QAK||OK|Q21^Get Patient|1|
QPD|Q21^Get Patient||CB02190122|
PID|1||CB02190122^^^REN^MRN||TIE^TESTING01^^^MS^^L||19490101000000|1|||PALM GROVE^^PRENTON^MERSEYSIDE^CH43 1TE^^H||01512222222^PRN^PH~07777777779^PRN^CP~CCCCCCCC@CCC.NHS^NET||||D|||||N||||||||N|
PD1|1||ABBEY SURGERY^^L83101|G9213263^RODGERS^PJ|</nowiki>

Latest revision as of 14:13, 7 August 2024

Your PTS application is likely to be connecting to a patient record database of some sort. This is the facility that looks up the patient details on the booking in screen.

ODBC

For now the most popular choice is to connect PTS to the JAC/WellSky/CMM or Ascribe/EMIS database using an ODBC data source (Administrative Tools > ODBC Data Sources).

Application Settings

Important Settings

  • PASDSNS
    The name of the patient lookup data source. This can also be set to a direct connection string. You can specify multiple comma-separated entries here which is useful for switchover periods.
  • LOCALIDQRY
    Query used by booking-in screen when searching for a patient using localID.
  • NATIDQRY
    Query used by booking-in screen when searching for a patient using nationalID.

PTS decides whether to send a local or national ID query based on what the end-user entered on the booking-in screen. Local ID usually refers to Hospital number, and National ID usually refers to NHS number. More often than not the two queries are near identical save for the WHERE clause.

With regard to server moves and updates, assuming the data structure of the new patient record database remains the same as before, more than likely you'll only need to amend the PASDSNS setting and/or the server data source itself to repoint it to the new database location. However an advanced user could redesign the queries here if necessary.

Optional Settings

  • LOCALIDF
    Format of Local ID. This allows prefix or suffix characters to be added to Local IDs. You could handle this in the query itself but it may be tidier to format it here.
  • NATIDF
    Format of National ID. This allows prefix or suffix characters to be added to National IDs. You could handle this in the query itself but it may be tidier to format it here.

After changing Application Settings

Unlike most other Application Settings, changes to the PAS link may require the PTS application to be restarted for any changes to take effect. If the DNS connection test passes but PTS still cannot find any patients (an error message may be written to the forename or surname fields, or nothing may happen at all), try restarting the web site in IIS, and/or recycle the PTS application pool. If it's easier, restarting the server entirely should also work.

If the problem persists make a note of the error message and request a support ticket.

Query guidelines

Patient ID parameter

Include "{PATIENTID}" (without quotes) in your WHERE clause at the point PTS should pass through what the user entered in the Local or National ID field.

No matter whether the local or national ID is used, the input parameter is always {PATIENTID}.

Output

The query should return values with the following field names. Use aliases to 'rename' fields. You don't have to return fields you don't want to use.

  • NationalID
  • LocalID
  • Title
  • Forename
  • FamilyName
  • Gender
  • Mobile
  • EMail
  • DOB
  • Address

Example JAC/WellSky/CMM Local ID query

SELECT patient_hospital_no.lnkphn LocalID, patient_basic.forenames Forename, patient_basic.surname FamilyName, patient_basic.nhs_no NationalID, CASE patient_demographics.sex WHEN 'Male' THEN 1 WHEN 'Female' THEN 2 ELSE 3 END Gender, CASE patient_demographics.patient_reference WHEN 'MR' THEN 4 WHEN 'MS' THEN 1 WHEN 'MRS' THEN 3 WHEN 'MISS' THEN 2 ELSE 0 END Title, patient_demographics.telephone_no Mobile, TRIM(LEADING ', ' FROM ifnull(patient_demographics.address1,'',UPPER(patient_demographics.address1))||ifnull(patient_demographics.address2,'',', '||UPPER(patient_demographics.address2))||ifnull(patient_demographics.address3,'',', '||UPPER(patient_demographics.address3))||ifnull(patient_demographics.address4,'',', '||UPPER(patient_demographics.address4))||ifnull(patient_demographics.postcode,'',', '||UPPER(patient_demographics.postcode))) Address, patient_demographics.birth_date DOB FROM JAC.patient_basic patient_basic, JAC.patient_demographics patient_demographics, JAC.patient_hospital_no patient_hospital_no WHERE patient_demographics.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkpid = patient_basic.lnkpid AND patient_hospital_no.lnkphn = ' {PATIENTID}'

Note the space character preceding {PATIENTID}. This is a quirk of the CMM database, and is an example of something you could handle in the LOCALIDF setting.

Example JAC/WellSky/CMM National ID query

SELECT patient_basic.hospital_no LocalID, patient_basic.forenames Forename, patient_basic.surname FamilyName, patient_basic.nhs_no NationalID, CASE patient_demographics.sex WHEN 'Male' THEN 1 WHEN 'Female' THEN 2 ELSE 3 END Gender, CASE patient_demographics.patient_reference WHEN 'MR' THEN 4 WHEN 'MS' THEN 1 WHEN 'MRS' THEN 3 WHEN 'MISS' THEN 2 ELSE 0 END Title, patient_demographics.telephone_no Mobile, TRIM(LEADING ', ' FROM ifnull(patient_demographics.address1,'',UPPER(patient_demographics.address1))||ifnull(patient_demographics.address2,'',', '||UPPER(patient_demographics.address2))||ifnull(patient_demographics.address3,'',', '||UPPER(patient_demographics.address3))||ifnull(patient_demographics.address4,'',', '||UPPER(patient_demographics.address4))||ifnull(patient_demographics.postcode,'',', '||UPPER(patient_demographics.postcode))) Address, patient_demographics.birth_date DOB FROM JAC.patient_basic patient_basic, JAC.patient_demographics patient_demographics, JAC_System.PatientName patient_name WHERE patient_basic.lnkpid = patient_name.PatientId AND patient_demographics.lnkpid = patient_basic.lnkpid AND patient_name.NationalNo = '{PATIENTID}'

PTS 5.0.3.0 and older

If you are running a slightly older version of PTS the data source name will be hardcoded - PTSPAS - so you should locate this data source on the PTS server and either modify it, or replace it with a new data source while retaining the PTSPAS name. You are not able to modify the query yourself prior to PTS 5.0.3.1.

Much older versions of PTS had the patient record connection information embedded in a DLL file that cannot be modified. If this is the case you should contact us to arrange a PTS software update.

HL7

Patient lookup via HL7 message is also possible with a QBP Q21/K21 query/response as per the HL7 Specification (see pages 46 & 47).

PTS sends a QPD Q21 query, and expects a PID in the K21 response.

Application Settings

  • PASDSNS
    Set this to "HL7," followed by a list of listener IP addresses and ports in the format IP;PORT. e.g. HL7,192.168.1.0;1000,192.168.1.1;1000.
  • LOCALIDQRY
    Set this to the identifier type code for a local ID lookup e.g. MRN.
  • NATIDQRY
    Set this to the identifier type code for a national ID lookup e.g. NHS.
  • COMPANY
    The company setting is sent as the Receiving Facility in the MSH. If this is checked and needs to be a specific string, you can adjust it here.

PTS decides whether to send a local or national ID query based on what the end-user entered on the booking-in screen. Local ID usually refers to Hospital number, and National ID usually refers to NHS number.

HL7 Examples

Following are two examples of a Q21 query that might be sent by PTS to lookup a patient.

Local ID/Hospital Number

In this example, the user searched for Hospital Number "123456789". The Local ID identifier type code has been configured to "MRN" and the Receiving Facility is "My NHS Trust" (see above): 

MSH|^~\&|PTSWeb|Dispensary|PAS|My NHS Trust|20240221140617||QBP^Q21|381b7131-c9bf-4c43-ae08-bb6aec0ae438|P|2.4
QPD|Q21^Get Patient||123456789^^^MRN^MRN
RCP||1^RD

National ID/NHS Number

In this example, the user searched for NHS Number "123456789". The National ID identifier type code has been configured to "NHS" and the Receiving Facility is "My NHS Trust" (see above): 

MSH|^~\&|PTSWeb|Dispensary|PAS|My NHS Trust|20240221142737||QBP^Q21|396236dd-ea41-4048-a328-10223a2c5912|P|2.4
QPD|Q21^Get Patient||123456789^^^NHS^NHS
RCP||1^RD

Example Response

A typical response may look like this: 

MSH|^~\&|PAS|My NHS Trust|PTSWeb|Dispensary|20240321103803||RSP^K21^RSP_K21|0b0efc65-a100-41f1-1585-f7b02e6d6c87|P|2.4|||NE|NE|
MSA|AA|f1fe8462-a884-4e89-af9f-0b3c9e80353f|
QAK||OK|Q21^Get Patient|1|
QPD|Q21^Get Patient||CB02190122|
PID|1||CB02190122^^^REN^MRN||TIE^TESTING01^^^MS^^L||19490101000000|1|||PALM GROVE^^PRENTON^MERSEYSIDE^CH43 1TE^^H||01512222222^PRN^PH~07777777779^PRN^CP~CCCCCCCC@CCC.NHS^NET||||D|||||N||||||||N|
PD1|1||ABBEY SURGERY^^L83101|G9213263^RODGERS^PJ|
Retrieved from ‘https://tmsinsight.com/TMSWiki/index.php?title=Patient_Record_(PAS)_Lookup&oldid=2454’