Notifications
Clear all
Topic starter
Our clinic's way of documenting preferred method of contact (Email, particularly) is a hold-over from when they went-live with Logician over 10 years ago. They have been putting "!!EMAIL!!" in the Registration notes to alert users rather than populating the METHCONTACT observation term.
Since we're about to go-live with Patient Portal and Automated Clinical Messaging (for Clinic Visit Summaries), I need to run a SQL script to populate METHCONTACT with "E-mail" for all patients with "!!EMAIL!! in the Registration notes. I would really appreciate some help. As far as I can get is the below query which pulls all those "!!EMAIL!!." Would anyone be willing to help add/modify it to populate the obsterm METHCONTACT with "E-mail"?
select *from dbo.PatientProfile where ProfileNotes like %!!EMAIL!!%
Thanks!
Posted : July 2, 2012 11:41 pm
I could write a script that would add a record in the OBS table for each of the records brought back by the query, but the problem is that the OBS record wouldn't be linked to a DOCUMENT record, and I'm scared to find out what happens if you do that. 🙁
Posted : July 3, 2012 4:20 am
No sweat. Here is a vbscript that I wrote to handle little conversion tasks like this from time to time. It loops through the results of a query creating one hl7 ORU message per record and saves the results to a folder on your drive. From there it's just a matter of highjacking one of your HL7 lab interfaces to copy the files over then watch them import observation data using linklogic. Ironically, it looks like I last used this script to import using our eformslab interface that came with our patient portal. I changed the query to yours.
1. Add a new line to cross-reference the METHCONTACT on your interface's .xref file
I,METHCONTACT,MLI-95886
2. Copy & Paste the script starting below '''''QUERY RESULTS TO HL7'''' into notepad. You can modify the doc type/summary by changing these variables:
DocumentType="PL" 'Preload or can be whatever existing doctype (abbreviation) you choose'
DocumentSummary="METHCONTACT Import"
3. Fill in your database connection information:
dbGE.open "Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=xxxxx;password=xxxxx;"
4. You will need to update the hl7 message headers to match your interface:
msh="MSH|^~\&|eFormsLab|Tahoe Fracture and Orthopaedic Clinic|LinkLogic|CPS|" & msgDate & "||ORU|" & msgDate & "|P|2.3|||||||"
5. Create the folder below or change the folder path to an existing folder. It will not create the folder for you. This is where the messages will be saved.
Set filetxt = filesys.CreateTextFile("c:\extract\" & PatientID & ".hr", True)
6. Save the file as "extract.vbs", make sure you save it as type: "All Files", instead of .txt.
7. Double click the file to run the script. If successful it should give you a total exported recordcount. Could take a while if you have quite a few records.
8. Open the folder the messages were saved to and copy and paste the files into the folder that your linklogic interface is monitoring. You might want to start by copying a couple test messages to make sure it's working. Might want to double check the documents are coming in as signed.
9. It should create a new document based on the document parameters provided above and update your METHCONTACT obsterms accordingly.
Sorry for the lack of comments and error handling in my code. Good luck...
Allen
'''''''''''''''''''''QUERY RESULTS TO HL7''''''''''''''''''''''''''''''''''''
function AddZero(strVal)
if strVal < 10 then
AddZero="0" & strVal
else
AddZero=strVal
end if
end function
function fmtHL7Date(theDate,lType)
dim yy
dim mm
dim dd
dim hl7Date
dim hl7Time
dim hh
dim nn
dim ss
yy=Year(theDate)
mm=AddZero(Month(theDate))
dd=AddZero(Day(theDate))
hl7Date=yy&mm&dd
hh=AddZero(Hour(theDate))
nn=AddZero(Minute(theDate))
ss=AddZero(Second(theDate))
hl7Time=hh&nn&ss
if lType=1 then
fmtHL7Date=hl7Date
else
fmtHL7Date=hl7Date & hl7Time
end if
end function
dim filesys
dim filetxt
dim dbGE
dim rs
dim msh
dim pid
dim pv1
dim obr
dim nte
dim obx
dim hl7
dim sql
dim DocumentType
dim DocumentSummary
dim TotalRecords
DocumentType="PL" 'Preload'
DocumentSummary="METHCONTACT Import"
Set dbGE=CreateObject("ADODB.Connection")
Set rs= CreateObject("ADODB.Recordset")
Set filesys = CreateObject("Scripting.FileSystemObject")
dbGE.open "Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=xxxxx;password=xxxxx;"
sql="select PatientProfileId,First,Middle,last,Address1,Address2,City,State,Zip,SSN,Birthdate,Sex from dbo.PatientProfile where ProfileNotes like '%!!EMAIL!!%'"
rs.open sql,dbGE,3,1
TotalRecords=rs.recordcount
for i= 1 to rs.recordcount
PatientID=rs("PatientProfileID")
PatientLast=rs("Last")
PatientFirst=rs("First")
PatientMiddle=rs("Middle")
PatientDOB=fmtHL7Date(rs("BirthDate"),1)
PatientSex=rs("Sex")
PatientStreet=rs("Address1")
PatientCity=rs("City")
PatientState=rs("State")
PatientZip=rs("Zip")
msgdate=fmtHL7Date(Now(),0)
msh="MSH|^~\&|eFormsLab|Tahoe Fracture and Orthopaedic Clinic|LinkLogic|CPS|" & msgDate & "||ORU|" & msgDate & "|P|2.3|||||||"
pid="PID|0001||" & PatientID & "||" & PatientLast & "^" & PatientFirst & "^" & PatientMiddle & "^^||" & PatientDOB & "|" & PatientSex & "|||" & PatientStreet & "^^" & PatientCity & "^" & PatientState & "^" & Patientzip & "^|||||||||||||||||||"
pv1="PV1|0001|||||||||||||||||||||||||||||||||||||||||||||||||||"
obr="OBR|0001|||^" & DocumentSummary & "|||" & msgdate & "|||||||" & msgdate & "||||" & DocumentType & "||||" & msgdate & "|||F||||||||||||||||||"
obx="OBX|0001|ST|METHCONTACT^METHCONTACT||E-mail||||||F|||" & msgdate & "|||" & vbcrlf
hl7=msh & vbcrlf & pid & vbcrlf & pv1 & vbcrlf & obr & vbcrlf & obx
Set filetxt = filesys.CreateTextFile("c:\extract\" & PatientID & ".hr", True)
filetxt.Write(hl7)
filetxt.Close
rs.movenext
next
set filesys=nothing
Set rs=nothing
dbGE.close
msgbox("Exported " & TotalRecords & " Records")
Posted : July 5, 2012 6:22 pm
Topic starter
Aballard,
This worked perfectly without a single hitch. I never could have accomplished this on my own and can think of a dozen other ways to utilize that vbscript. Thanks so much for your help!
Brandon
Posted : July 9, 2012 7:49 am
Brandon, I'm glad it worked for you! It works great if you want to change the data source (such as another database or reading from a file) and extract information needed for reporting. I have since moved over to using Mirth for my "live" interface tasks, but I think this still has it's place for those one and done tasks.
Allen
Posted : July 9, 2012 11:20 am
Mirth? What's that?
Posted : July 10, 2012 12:56 am
Mirth Connect is an open source "hl7" interface engine but it does much more than just hl7. It has all sorts of tools for automation/data transformation. Its javascript transformers are capable of just about anything you put your mind to. And it's free.
Posted : July 10, 2012 4:42 am
