I need help creating an update connectR script

I need help creating an update connectR script

Postby jorsini » Sat Feb 16, 2013 9:20 am

I need to be able to upate the EIEID and the EIEDTTM from the document table in the Works DB. In our document interface coming in from the hospital and the documentID is the accession number in the table since the documentID in the document table is actually auto generated.

So, I would like to be able to remove a document from the chart viewer IF this document was cancelled in the upstream application. I can do this via SQL manually and the process works just fine. Is anyone able to help create something for me?

Ideally the script would work like this:

If MSH1.8 component 2 = T11 run script: update EIEDTTM and EIEID with the accession number provided in the actual HL7 message on PV1.19 component 2. If the accession number does not exist, block message. Is this possible?

Thank you

Jim Orsini
IT Project Leader
Corporate and Physician Applications
West Penn Allegheny Health System
(O): 412-330-5650
(F): 412-330-5606
Posts: 3
Joined: Thu Dec 27, 2012 2:45 pm

Re: I need help creating an update connectR script

Postby tsuacci » Wed Feb 20, 2013 12:18 am


I've had to perform updates via a ConnectR script and I found the following format to work. This isn't the full script, but this is the 'update' portion.

Set oCon = CreateObject("ADODB.Connection") 'This Creates the database object
oCon.Open "Data Source=" & datasource, user, pass 'This opens the object -- data source, username, *****

vcommand = "select document.* " & _
"from document " & _
"where document.AccessionNumber = '" & sAccessionNumber & "' " & _
"and document.InterfaceID = '" & sInterfaceID & "' " & _
"and document.NextVersionID = 0 "

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open vcommand, oCon, 3, 3

Do While Not oRS.EOF
oRS.fields("EIEID") = <userID>
oRS.fields("EIEDTTM") = sNow
oRS.fields("Status") = "Invalidated"

I don't recall if all versions of a note need to be EIE, or just the current version. If all need to be EIE, remove the line above that checks that NextVersionID = 0.

I hope this helps.
Tyler Suacci
Galen Healthcare Solutions
Posts: 55
Joined: Mon Apr 04, 2011 12:08 pm

Return to Tools: Queries, Scripts

Who is online

Users browsing this forum: No registered users and 1 guest