Dynamic look-up table?

[Mike Campbell]

I’ll try to be concise in my question, but bear  with me.  We are needing to build a new thread from a Cerner Surgi-Net scheduling system to an anesthesia pharmacy dispensing interface.  The receiving feed only wants patients that are actually in surgery or scheduled.  

We were thinking of pulling off the Patient Identifier information from a Surgi-Net extract and populate a lookup table with the information.  

Is it possible to have a changing lookup table that is continually updated?  

Would it be better to create a dynamic SQL-lite table instead?  

(Or find a different vendor???)

Thanks all.

Topic

[Jerry Tilsley]

I would definitely use a SQLite table for this!

[Jim Kosloskey]

Define continually updated.

Once per day, once an hour, indeterminate.

Will the extract build a true Cloverleaf lookup table with prologue, etc. or rather something like a CSV type file?

If a true Cloverleaf Lookup Table is being created, then you could schedule via your system scheduling tool a purge caches command for the thread(s) affected as often as you like. Otherwise you would need to schedule something that converts to a true Cloverleaf lookup table and purges the caches.

Take into consideration the impact on your system, etc. to have these caches continually purged.

Another option would be to investigate something like SqlLite.

You have probably already addressed this but yet another optin would be if the source system (where you are extracting from anyway) could provide the information you seek in the inbound message. Then you would only need a filtering proc.

Is the source of the information you need in an accessible DB – assuming it cannot be in the message. If so, another option could be to do an ODBC query of the DB as each message arrives to fill out the provided information.

I am sure there are other ways to handle this.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Mike Campbell]

Thanks all for the ideas.  Currently we are in the discovery stage, but it seems that it could ramp up fairly quickly.  

I’ll update with what our final solution is…in case anyone else might need a similar process.

[Jim, we would be looking for at least the possibility of updating every 15 minutes.  So the purge might be detrimental to the overall performance.]

[Tom Rioux]

We have physician logic in place currently that does something like this.   We are working on an ODBC call to replace it, but here is what is happening now:

Doctors are entered into the HIS system and assigned a number.  This number needs to be translated to a number the is specific to a particular facility.  So we may get P1234 for a doctor # and have to translate this to 123 for one facility but have to translate it to 456 for another facility.

The HIS system ftp’s to us facility specific files that include the assigned number plus the facility specific ID.  These files are .csv files.  Since there could be doctors added into the HIS at any time during the day or night, the HIS system ftp’s these files to us on an hourly basis.

Cloverleaf in turn has two scripts that run a few minutes apart from the receipt of the .csv files.   The first file builds the tables based on facility and copies these files to their respective Tables directory.   Then, a second script comes behind that and runs a purge cache for each of the designated facilities.

We have had this in place now for about a year.   It is scheduled to be going away in the next couple of months and be replaced by the ODBC call.  So far, we experienced no hits in system performance across any of our sites.   We had considered SQLite, but since this was only going to be temporary, the tried and true Table lookup was utilized.  Depending how large your tables may be, it is possible that SQLite would be a bit more helpful.

Hope this helps….

Tom Rioux

[Max Drown (Infor)]

I completely agree with the SQLite suggestion.

-- Max Drown (Infor)

[Tom Rioux]

One question you should consider.   What are you going to with the date in the SQLite database once the patient after they are out of surgery and no longer in surgery?   It seems to me that, unless you do some sort of purge each time before you load the data from the extract, the database will continue to grow and grow.   I wouldn’t think that would be what you wanted.   I’m sure there is an SQLite solution for this.

In my earlier example, I wanted to and had partially built the SQLite database for my Physician Logic interface.  However, the powers that be shot it down before I could even demonstrate what it could do.

Now, one thing that does happen when we get the extract and create the tables in our Table lookup solution.   Each time the extract is sent over to us, my script makes a backup copy of the old one and then replaces the old one with the new one.   So, each time, I’m building with fresh data.  It will add those that have been added to the HIM but will also get rid of those entries from my tables in the event a doctor is deleted from the HIM.

Hope this helps…

Tom Rioux

[Charlie Bursell]

This is something I have done a lot. First of all, unless you have a lot of time on your hands, the Cloverleaf table would not be the best option.  You could write a script to update table, purge cache, etc.  but not the way to go

If you are pulling data from messages, the best option is sqlite.

On the other hand if you are maintaining doctor tables or such these could be maintained and updated by an office clerk in Excel.  All the clerk has to do when the table is updated is to kick out a copy in CSV format to a known location that Cloverleaf can access.

The code is pretty simple.  When thread starts, read CVS file into an array, keyed list or a combination depending on complexity.  Also stat file and save tme last modified.  Then with each message stat file and compare to saved time.  If same just use what you have.  If different, reload.

So simple anyone can do it   😀

Much easier if using namespaces which we all are, right!  😉

[Author]

Replies