Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Does anyone have a SQL query to see documents that someone scanned ?

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

5 Posts

3 Users

0 Reactions

79 Views

RSS

tnc

(@tnc)

Posts: 517

Honorable Member

Topic starter

From time to time I get requests that an employee scanned some things into the chart of a patient but they are missing then I have to search. I usually focus on HL7 issues and search the llogic folder for the patient name but I bet there is an easier way with a SQL query. I know this is tricky since we are talking about a foreign system sending through the HL7 interface but any help would be welcomed.

It would be something like I would want to see all scans that Homer Simpson (employee) did from 1-1-21 through 1-8-21, because I am looking for patients Ned Flanders, Selma Bouvier and Hans Moleman. I can already see that the scans are certainly not in the patient charts so it would be best to be able to see if Homer scanned them into the incorrect chart, or not at all.

Mike Zavolas
Tallahassee Neurological Clinic

Posted : January 19, 2021 1:54 am

tippenring

(@tippenring)

Posts: 56

Trusted Member

Is the problem you're describing that a document is attached to the wrong chart, and you have to find it to remove the document from the incorrect chart and also get it to the correct chart?

We use Indxlogic for our external document indexing, so our workflow doesn't sound quite the same as yours. Are you using Docutrack?

I'll provide an overview of our workflow even though it's different, in case it is helpful.

With Indxlogic, the users scan to a network folder to start the indexing process. The indexing process is a separate workflow step which the person scanning the document often doesn't do. In our case, the only way we can identify who scanned a document is by file system audit log.

All of our HL7 interfaces flow through Mirth Connect. This lets us manipulate HL7 if we need to, store the messages easily for a period of time, and we can index and search HL7 messages much more simply than doing a file system search.

Our HL7 from Indxlogic contains data on who indexed a particular document if it were manually indexed. This is plaintext, and therefore searchable. If all of your indexing is manual (as I recall Docutrack is), then your HL7 might contain the data you're looking for. Does the HL7 contain data which identifies the person that scanned/indexed the document? The EVN segment often identifies the user that triggered an HL7 message. It could be there. Ours is in an OBX segment which identifies the person that manually indexed the document.

As far as a SQL query goes, it wouldn't take long to develop a query that performs a similar search if the correct data is ultimately stored in the database. I think Mirth Connect would be more user friendly for other users. We have clients where regular users can do their own searches and corrections in Mirth.

-dp

Posted : January 19, 2021 3:25 am

DavidShower

(@davidshower)

Posts: 1255

Noble Member

Mike, here are a couple of Queries I used back in my old life looking at chart documents that had been scanned. Replace the Indira% with whoever you are looking for:
select d.pid, dd.userid, dd.data, d.db_create_date, d.summary from ml.docdata dd, ml.document d where dd.sdid = d.sdid and d.sdid in (select sdid from ml.document where sdid > '1768867200000000' and sdid '1768867200000000' and sdid < 1768953600000000 and hasextref = 1) and dd.data like '%Imported By: Indira%' order by searchname

--Report for scanned documents by a user select p.searchname, p.medrecno, d.summary, ml.convert_id_to_date(substr(d.clinicaldate,1,10)*1000000) as CLINCALDATE,d.db_create_date from ml.docdata dd, ml.document d, ml.person p where dd.sdid = d.sdid and d.pid = p.pid and d.sdid in (select sdid from ml.document where sdid > '1768867200000000' and sdid < 1768953600000000 and hasextref = 1) and dd.data like '%Imported By: Indira%' order by searchname

Posted : January 19, 2021 11:08 am

tnc

(@tnc)

Posts: 517

Honorable Member

Topic starter

>Is the problem you're describing that a document is attached to the wrong chart, and you have to >find it to remove the document from the incorrect chart and also get it to the correct chart?

Yes. All I got from the help desk ticket is that the scanned doc is 'missing' so it could have gone to the wrong place, nowhere at all or maybe it wasn't even done (unlikely). They re-scanned it and it is there now but I still wonder about the disposition of the MIA one. I would like to file it in error but have to locate it first. I am sure there are instances of this in the db from the many years we have been uging CPS.

We are using DocuTrak. (Kryptiq DM). I haven't found any evidence of the patient names in question in the HL7 messages but it is easy to miss, which is why I was asking about the SQL query. In the imported documents there is a "imported by" field which I figure is in the db.

Is the Mirth implementation worth doing just to have more control/logging of HL7 messages? I recall it as a free alternative to Qvera if I recall correctly. I am not looking for extra projects but if it is worthwhile to do I would consider it.

Posted : January 21, 2021 4:09 am

tippenring

(@tippenring)

Posts: 56

Trusted Member

If the "imported by" is in the body text of the document in CPS, then it is probably contained in the OBX segment of the HL7, and it is in the CPS database. David Shower's SQL query above should be very close to what you need. It shows the text you are looking for ("Imported By") is in the data column of the docdata table.

As far as Mirth Connect, yes, it is a free (with some paid options and paid support) interface engine that is functionally very similar to Qvera. Qvera likes to graphically represent configurations and connectivity (which I find convoluted, but I have limited experience with it). Mirth doesn't do the graphic representation part. Personally, I find that Mirth is an invaluable tool for handling HL7 (and DICOM) traffic between systems.

First, at least half of our interfaces need some kind of data manipulation to get the systems to work well together. I find that if I can make most of the adjustments, the interface can go live much more quickly than when the outside party has to make adjustments. I no longer have to use the LL cross reference files either. Translations like in the cross-reference files happens in Mirth as well, which is easier to visualize, manage, and add commentary in the javascript code (if that is your preference of course).

Second, the ability to search through HL7 messages by text search (slow but it works), or alternately setting certain key search values as metadata search fields (very fast search) is great. I usually set patient IDs, accession numbers, and other often-searched fields as metadata values so I can find data quickly.

There are a lot of other benefits. There is also a pretty steep learning curve that can be discouraging. Once over that, I find that Mirth is indispensable. As a starting point, I'd suggest starting up an instance on your local PC or dev server and experiment with it. You can try loading some preconfigured channels to see how the various parts work.

I could go on, but I'll stop there. Feel free to email me if you have more questions.

-dp

Posted : January 21, 2021 5:44 am

24 Forums
9,942 Topics
39.9 K Posts
1 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed