Update Lookup table from FRL

[Scott Rush]

We use FRL files in our interface in a few cases.

I’d like to know if there is a way to have it process an FRL into a lookup table.

We have a situation where we cannot get the sending system to change their interface to send the physician npi number.

I can access their database so my thought is to create an FRL and ftp it to our cloverleaf server with the name that is currently being sent along with the npi number.

If I can update a lookup table inside cloverleaf, I can automate the update of this table an avoid the manual process of keeping this table updated.

I can build in logic to only send new physicians.

Is this possible?

In class we only manually updated a lookup table.

Thanks in advance,

Topic

[Jim Kosloskey]

Scot,

The only method provided within the Cloverleaf toolset to maintain a lookup table is via the GUI (manually).

However, when faced with the challenge of very dynamic tables what I have done is to export the information from the source (typically a DB but sometimes a spreadsheet) in a CSV file.

That exported file is then either FTP’d to the Cloverleaf platform or picked up via FTP from the source system (using Cloverleaf Fileset/FTP protocol).

Once placed on Cloverleaf, the Tcl proc is executed and it makes a new copy of the table (making a backup of the old Table). Thus it is essential the exported CSV file be a full dump of the source information.

Once the new table is built, either a purge caches must be done or the process/processes using the table stopped and started to refresh the Table in the Cloverleaf engine memory.

Another potential is if you have ODBC (or perhaps JDBC) then you can actually (via Tcl) query a DB at the inbound TPS, or pre-route UPoC or even within the Xlate itself to get the information real time (not using the Cloverleaf Lookup Table but the actual ‘system of truth’). There are issues associated with this kind of usage so be sure to do the appropriate brainstorming if you think you would like to go this way.

If you think you would like a copy of the Tcl procedure that builds a new copy of a table from a CSV file, email me and I will send it to you along with user doc. You can use it for an example or if it fits, as is.

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

[Charlie Bursell]

Use sqlite.  That is why we include it

[Robert Milfajt]

I agree with Charlie, use SQL lite.

Robert Milfajt
Northwestern Medicine
Chicago, IL

[Jeff Dinsmore]

Is it necessary to restart the process(es) that use the updated table?

I’m assuming it’s necessary to open the SQLite database for every iteration of a given Tcl proc? Assuming so, there is some overhead associated with open/close of the database. How does the performance of SQLite compare to a Cloverleaf table?

Jeff Dinsmore
Chesapeake Regional Healthcare

[Robert Milfajt]

You will need to bounce the process to reload a table I think, especially if its used in an Xlate.

Robert Milfajt
Northwestern Medicine
Chicago, IL

[Jeff Dinsmore]

SQLite is a fine solution. I’ve used it extensively in other applications. Once the database is open, SQLite is plenty fast.

My question about performance is based on my assumption that the SQLite database file would need to be opened for every iteration of a given TCL proc (for each message) whereas a Cloverleaf table remains in memory.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jim Beall]

Regarding the issue of reloading the table that’s referenced within an xlate, we rebuild a table every night here (although we don’t use SQLite) via a script and we run hcicmd to purge cache on the affected thread:

/quovadx/qdx5.6/integrator/bin/hcicmd -p -c ” purgex”

this seems to work..

[Jeff Dinsmore]

So, the purge has the effect of reloading the updated table?

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jim Beall]

That’s what it looked like to me when I was testing it.  I’ve also done manual purge caches via the IDE when a table is updated and that seems to work too.  There was a time when purge cache seemed to be unreliable but now it seems like it works (we’re on 5.6).

[Jim Kosloskey]

Purge Caches reloads the Variants/formats, the Xlates, and the Tables referenced by a thread into memory.

There were some particular problems with the execution of purge caches from the IDE (5.5 comes to mind) but it is overall a reliable method to refresh the objects described when stopping and starting processes, etc. are not desired.

For Tcl procs there are two IDE commands – reindex and reload.

reindex is used when a new proc has been added to the TclIndex file, then a reload is necessary to load the actual Tcl code.

reload can be used when a Tcl proc is changed (not new – modified) to activate the code for the thread/process.

reload and reindex can be done either at the thread or the process level in the IDE.

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

[Author]

Replies