Home Forums Discussions General Discussion A programming question for the techies

To make a long story short:

I have done quite a bit of programming for my practice, built a rudimentary EMR / patient database in the past, and still use parts of my patient database to keep a flowsheet of labs.

For various reasons (mainly my interest in websites / HTML back in the 90's), I have done most of my programming and development in PHP and MySQL (I have also done database work in Microsoft Access, but don't know much Visual Basic). My lab flowsheet displays on a web page; I parse the lab data (HL7) by uploading the file and then manipulating it in PHP. It works ok, but now that I am more desktop oriented with AC (and logmein), I am thinking of porting this to a desktop format. Also more secure having these things off the cloud.

My question is: what programming platform would be easiest to learn and use for this? C++? Java? MS Access / VB? Something else?

Being in practice, I don't have oodles of time to spare, so maybe I will just leave things as they are. But given my relative ease with PHP and MySQL, does anyone (Sandeep?) have any idea how difficult it would be to port a fairly simple application that parses a text file (HL7 labdata) and dumps it into a database? Currently I use a webpage with forms to input some of the data that isn't in a file.

Thanks for any suggestions!

Michael Jacobson
NY

Micheal
I have done just what you describe in VB, both VB6, then ported to VB.Net.
I had trouble deploying the latter, and have many more years (of course) with VB6, so I went back to my VB6 code recently.
I parse HL7 files from our hospital lab, and plug into an Access MDB. I plan to move that lab table to a more robust DB engine (MySQL, SLQ Express or Advantage) "real soon now", as soon as I have extra time.
The kicker is, you have to either write code for each lab source, or preferably use a look up table, as a "Hgb" in 1 HL7 file for lab A may be "Hb" or "Hemoglobin" in another. The HL7 "standard" is a framework, and doesn't define names of data elements. If however, the Lab HL7 includes LOINC codes, then you can use the LOINC code for the test to put into the field of your database for Hgb, regardless of what the lab "calls" it.
But you are in fact writing an interface.
I did so in Dataflex for DOS in 1990, first with ATSM files from SmithKLine Labs, then from our hospital lab, so use the language with which you are familar as long as it runs on your system and can handle string parsing well.
Good luck.

Roger,

Thanks!

You're right, what I did was essentially create a lab interface. Turns out about 90% of my labs are from Quest, and Quest gave me an interface that places the HL7 files locally on my computer. I then upload them and parse them with PHP (using a translation table that I constructed, as you noted) before pushing the data into a MySQL database. The lab data can then be displayed in tabular form by a custom webpage that accesses the database and displays it.

What I like about this system is that when an occasional lab comes in from elsewhere, I can input the values I'm interested in manually, so they're part of the same flowsheet. This is why I'm not using the standard Quest interface to AC.

In terms of displaying the flowsheet, since it's a webpage, I just put a link to the specific webpage (with the patient's id as a parameter) into the Imported Items of AC, and the flowsheet shows up there.

If I switch the whole thing to a local program that doesn't use a webpage to display the flowsheet, I will have to see what I can embed into Imported Items to display it. Maybe an Excel spreadsheet? I suppose another possibility would be to set up a webserver locally on my desktop and keep on doing what I'm doing locally, but that's not terribly attractive to me.

Lots to ponder.

Thanks again

Michael

------------
Michael Jacobson, MD
New York, NY

Another approach would be to use SQL Server Integration Services to handle your files. You could:

1. Download the file from a secure FTP site.
2. Provide a report on what files were downloaded.
3. Parse the files.
4. Show reports on what you got from the files.
5. Enter the information from the files into your database(s).

This screenshot shows the SQL Server Integration Services Package used to download 837 remit files from our clearinghouse, RealMed.



This screenshot shows the configuration of the Secure FPT component.



This screenshot shows the report from the file download package so we know what files came in. Notice the parameters you can choose from to narrow down your search.




This screenshot shows the autopayment posting program in SQL Server Integration Services. The Post Payments box does all the heavy lifing as far as posting the files goes.





Here is the contents of Post payments box. This shows a very small partial code listing.



This report shows the bad transactions that auto posting could not post and the error why. There are other reports for good transactions (those that came in without error) and denied transactions.




I don't see why you can't do something similar with HL7.

JamesNT

James,

Thanks. This is very interesting. Although I'm not sure I'm up to learning a whole new complex programming environment, it is intriguing. I'm going to read more about it. The key complexity as far as I'm concerned is extracting the data from the HL7 file.

It's been a few years since I developed my PHP code, but I think I just figured out how the Quest HL7 file was structured and then just wrote some scotch-taped-together code like "if the first line has the word blah in it, then skip ahead x columns and extract the number there". Probably it can be done much more elegantly and efficiently in a better string-processing language like PERL. How this would be done in SSIS is something I would have to look at.

I never thought of using the MS SQL server that AC installs for anything other than AC. I assume one can use it as a regular database, not just as a warehouse for AC's data.

As always, such interesting and helpful comments on this board (I just bought a new scanner, the Fujitsu fi6130z, based on comments from another thread, and my eyes are still popping at the speed).

Michael



---------------------
Michael Jacobson, MD
New York, NY

Michael,

Just to make sure you have all the details you need, Amazing Charts comes packaged with SQL Server Express Edition, which is free. The Business Intelligence tools of SQL Server are in Standard or higher Editions. Those are not free and do cost money. Furthermore, Amazing Charts does not publish the SA password for their instance of SQL Server - something I vehemently disagree with - so you will have to have an automated process that, after backing up,:

1. Shuts down the Amazing Charts SQL Server instance

2. Mounts their database in your SQL Server instance.

3. Unmounts the AC database from your SQL Server instance when you are done.

4. Starts up the AC SQL Server instance.

. . . So that way you can have a fully automated procedure that pulls data from your HL7 files into AC.

You are correct that you would have to learn a new platform. However, I can assure you that when it comes to moving data, SQL Server Integration Services is truly the way to go. Those pictures don't even begin to scratch the surface on what you can do.

JamesNT

I have already contacted Anonymous and they are putting their best hackers on it.

That would be really nice so we could create and run and share unlimited reports with Crystal Reports.

PeterS,

FYI, if you purchase SQL Server Standard, you get SQL Server Reporting Services with it. No need to spend even more money on Crystal.

ryanjo,

Please inform anonymous that they may receive an anonymous payment from an anonymous source if they can make the SA password to Amazing Charts a little less anonymous.

JamesNT

Thanks, that is good to know.
I already have Crystal Report XI.

James,

I didn't realize SQLserver Express wasn't compatible with the whole suite. I don't think I want to start switching server instances and all that.

I was also unhappy about AC not allowing access to the database. When I originally spoke to them about this, wanting to do some transfer of patient demographics into AC myself, they said this was out of concerns for security.

Actually, it seems to me this cuts two ways. If the password to AC's database is the same for everybody, but they don't give this password out to users, there is a security issue because anyone who does get hold of this password can now access everyone else's data. On the other hand, if each user sets their own password, this wouldn't be the case.

Then again, I suppose for medico-legal reasons there is some protection in not granting users the password. By withholding the password, users can't easily redact the medical record.

Not sure how these two issues balance each other.

Michael


---------------
Michael Jacobson,
New York, NY

Here's what I would like, and what they (AC) could easily do.
I know that many commercial suites/applications, including enterprize EMR vendors, provide read-only access to the data (called "views" in SQL parlance). These views can be PW protected for use by by non-SA (the system administrator user name) users to extract data. Databases remain unchanged.

I really want to my hands on my data, I mean, really!

There is some reasonable data export via the reports function. In other ways very limited, for instance: I have a query into GA support as I can not find a date field for the imported labs. I could export all creatinine values by patient, for instance, but alas, the report excel file has no date attached to the result. Worthless!!!

The fact is AC DOES give access to their database: to Updox for instance.

The MU wizard is great, as an example of what to do with data.

AC users have a wide variety of technophiles. Loosen the strangle hold on the data some, and see terrific ideas and even add-ons that would flow forth!

mjmd,

"Compatible" is probably not the best word to use as you go from one edition of SQL Server to the next. The idea is that as you pay more money, you get more features.

As for AC not giving out the SA password, that has more to do with keeping support costs down more than anything. Security isn't even on the table. The reason I say that is because if a bad guy can get on your machine with Administrator rights, that bad guy can just take a copy of your Amazing Charts database files and do with as he pleases. Regarding the redaction of medical records, that is the practice's problem, not Amazing Charts', as far as I can tell (I welcome further discussion on that).

JamesNT

Sorry to belabor this, but it is not the SA password that is needed. What is need is a DB user with READ only privileges to report the data.
Crystal Reports has it.
Updox can make changes to the tables (add records, read patient names)
Just let us semi-literate techies do the same as Crystal Reports does.

I came to this thread late, quickly perused the comments so may have missed a point. Michael, what are you intending to do with the lab data, once parsed and stored in a database?

I have a similar situation, receiving HL7 from the hospital. Mostly from frustration with having to have someone sit there to answer non-matching demographic questions (to which the AC interface has no memory), I now parse, filter, and alter demographics so that only my patient with the correct AC name (e.g. Bob instead of Robert) are on the list before AC import. A side effect is that I now have a database of lab values and dates. I chose Access for processing, since I use Access at the front end as well. As Roger puts it, it works fine for us "semi-literate techies". I have used MySQL in the past, but find Access to be adequate for communication between its databases and tables.

JackChoi,

So here's how I handle my lab data. It's a carryover from before AC, when I had my own primitive EMR. It was a simple database (with demographics, problem list, rx list and labs), coupled with a pdf file for each patient that had all the progress notes, reports etc.

The database was implemented in MySQL on an internet server; the forms for accessing the database were done in Microsoft Access, with an ODBC connection to the database. I used MySQL because I wanted to be able to access the database from various locations. Whenever I needed to do any programming for the database, I used PHP because I was familiar with it from my experiments with website hosting.

As for the labs, I used to handwrite flowcharts of important lab values. Then I switched to adding the labs to the database, typing in the values into a form in the Access database. So the labs would get added to my database, and I would display them in a webpage, programmed in PHP, which would display inside the Access database. Obviously, this was time-consuming (typing in the lab values), so the next step was importing them automatically. Quest, which I use for about 90% of patients, is able to dump the HL7 file onto my desktop. I just needed to find a way to get the HL7 into the database. I did this using PHP and MySQL, which is clearly not the most efficient way to do it, but since I was comfortable with PHP, that's what I did, and it works.

When labs come in from different sources, I can still input them (via an online form), which is important to me. This is really the reason I don't use the interface to Quest exclusively -- I like to see labs flowsheeted even if they're from somewhere else.

Now that I use AC, I have an Imported Item which is just a link to an online form that displays the lab flowsheet.

That's how I deal with labs; sorry for the somewhat rambling explanation.

Michael


-----------------
Michael Jacobson, MD
New York, NY

Hi Michael,

I tried to stay out of this thread, but I couldn?t help myself. Ummm? if I understand correctly, you would like to have a desktop version of your PHP (web-based) application, right? If so, then I would suggest you learn VB.Net or C#, leaning more toward VB.Net, for learning and time sake. I would love to help you out with your project, but as you are well aware of, I?m currently trying to finish the new Letter Writer. I think what you are asking for wouldn?t take that long to program, since you already have some programming experience, and you know the HL7 structure and fields, which is a plus.

Also, the solutions that Roger and JamesNT (nice flow diagrams James) have provided are good ones.

Thank you, Lawrence_Baratheon!

(I've just watched four episodes of Game of Thrones back-to-back on Blue-Ray. Everyone is going to have to deal with this for a while. Would Bert make a good Stark or Lannister?)

JamesNT

Lawrence,

Yes, that's what I'm thinking of doing, and thanks for the advice, I'm going to look into VB.NET and C#.

Funny how things morph and change. When I was majoring in Computer Science, way back in the era of punchcards and mainframes, IBM was the big evil empire that corrupted "pure" programming with terrible things like the programming language PL/1. Then along came Microsoft, with its programming practices that were just as heavy-handed and proprietary, and in my mind IBM went from Big Blue to making cute PC laptops.

And now, my distaste for Microsoft (which never stopped me from using their software, but did push me towards open-source programming) is yielding to paranoia about Google taking over the world, and suddenly .NET programming doesn't seem like such a terrible thing. The more things change...

Thanks again,

Michael


---------------------
Michael Jacobson, MD
New York, NY

Funny. My distaste for open source programming and the open source community pushed me towards Microsoft back in the 90's.

JamesNT

My son is my tech support (he has a real business doing this, supports about 30 other physician offices) and turned me on to Mirth Connect .
We are looking to see if can implement for translating hospital HL7 files (no AC interface) to Quest Lab HL7 flavor.
We'll post our experiences.

Michael,

You're welcome. If you would like, the Free express editions of Visual Basic 2008 or C# 2008 could be downloaded here. It's a jump-start for learning the languages at least. I have Visual Studio 2008 Professional installed on my development PC. Of course, that costs a pretty penny. Visual Studio 2010 and 2011 express versions are also for download here. I thought you might want to check out those free tools.

@JamesNT - James, I hope "Lawrence_Baratheon" is a good guy in the kingdom.

@Nephros - Roger, Thanks for sharing that "Mirth Connect" link. It looks interesting.

Thanks, all.

I'm not sure Mirth is a good name for a serious project, but it looks very interesting.

And looking into all the VB, C#, Express and Studio products. Might be time to plunge back into some programming...

[James, I also have mixed feelings about open source -- a good idea in theory, and sometimes great in practice (Apache, Linux), but often holier-than-thou and lacking in consumer-oriented business sense. But not to start a whole new topic here...]

Michael


--------------------
Michael Jacobson
New York, NY

Michael, looking at one of your posts (I think it was one of yours) suggested that you had created a file (HTML?) to place into the imported items list that would link to your external Db or data.

AH HA says I: create an HTML file, mass import into AC (by copying the file to each patient's II folder, then running the administrator menu item: Imported Items: repair missing files and links.
That works for a page that lauches explorer to the existing folders I have images in (from 6 yr of scanning pre-AC). (I have issues with importing all of those files: seems to really drag down the performance when opening II.)

That works.

I have a small VB6 file viewer for those files that I wanted to link to as well, but can't figure out how to get it to run inside of AC: the page itself works find but I get an "Automation Server cant create object" error. at the last line:

Files for Blah Blah
<p></p>
<a href="file:///N:/Documents/AGUE1264/"> Click Here For Scanned Documents</a>
<p></p>
<a href="file:///S:/Access/KDHDocuments/AGUE1264/"> Click Here for Imported KDH Notes</a>
<p></p>
<a href="file:///N:/RMGDocuments/AGUE1264/"> Click Here for RMG Created Notes</a>
<p></p>
<input type="button" value="See Files" onClick="var w = new ActiveXObject('WScript.Shell'); w.run('S:/access/Medrec/DocViewer.exe AGUE1264'); return true;">

Again, stand alone, button works fine; within AC the links to local folders run an Explorer session inside the AC II space, but the button errors out.
So with your PHP experience (and I have NONE, and very little JS or Java knowledge) I ask if you might have an Idea for a way that I could run and external EXE from a link in the II file.
Thanks.

Roger,

You're reaching the limits of my programming knowledge with your question, but here are my thoughts.

It sounds to me like the "browser" that runs inside the AC II tab has certain security limitations that the normal IE browser, running as a standalone, doesn't. For instance, when you run normal IE, there are all sorts of security options that you can set (from Tools... Internet Options etc), which will enable or disable certain things like running scripts. These options don't seem to be available when the IE browser is running inside AC, and maybe running an exe from a script isn't allowed, for security reason. This would explain why your viewer program runs from a normal browser but not inside AC. Maybe there is a way to rewrite the button script that would work, but my knowledge of JS is minimal, and if there really is a security limitation on running exe's, maybe no way to do this.

As for PHP, you can do all sorts of things with PHP, but PHP runs on a web server. When you write a PHP program, the output is a webpage with all the html; the PHP interpreter itself runs on the webserver that serves up the webpage. So you could conceivably rewrite your VB viewer in PHP, but you would have to have everything it needs sitting on a webserver. If you wanted to do it locally, you would have to set up a webserver on your computer, add PHP capability to it and then port your VB program to PHP.

Very interested to hear what others think.

Michael


---------------------
Michael Jacobson, MD
New York, NY