do a lookup against an external CSV file

[joejoubert]

Hi

I would very much appreciate assistance with solving below:


Incoming channel messages need to be looked up against a CSV file (which will be frequently updated), the hospital number (PID 3.4.1 segment) will be used to do the lookup


When the incoming message's hospital number match the hospital number on the CSV file, the visit number (PV1.19.1) of the incoming message have to be replaced by the visit number on the CSV file

Reading through some of the documentation one possible solution is to load the table in the ‘deploy’ channel scripts and store the but not sure how to achieve this

Thanks in advance for any help

[pacmano]

joejoubert, moving your post to "support". This area of the forums is for development of mirth itself.

[joejoubert]

Not very elegant but I have solved the issue with the following code

File reader connector type

A destination transformer is reading the CSV data into an array

const hospitalNumber = [];
const currentSpell = [];
const oldSpell = [];
var i = 0;
var loop;


do {



try {
hospitalNumber[i] = msg['row'][i]['column1'].toString();
} catch (e) {
hospitalNumber[i] = '';
}




try {
currentSpell[i] = msg['row'][i]['column2'].toString();
} catch (e) {
currentSpell[i] = '';
}


try {
oldSpell[i] = msg['row'][i]['column3'].toString();
} catch (e) {
oldSpell[i] = '';
}

i++;


try {
msg['row'][i]['column3'].toString();
loop = true;
}
catch(e){
loop = false;
}
}
while(loop)




channelMap.put('i',i);
channelMap.put('hospitalNumber', hospitalNumber);
channelMap.put('currentSpell', currentSpell);
channelMap.put('oldSpell', oldSpell); 

Reading the data into the DB I have created a destination with the following code

var dbConn;


var hospitalNumber = channelMap.get('hospitalNumber');
var currentSpell = channelMap.get('currentSpell');
var oldSpell = channelMap.get('oldSpell');
var m = channelMap.get('i');

if(globalMap.containsKey("epma_dbConn")){
dbConn = globalMap.get("epma_dbConn");

try{
dbConn.isConnected();
if (dbConn == null || !dbConn.isConnected())
{
dbConn = getDBConnection_EPMA_PAS();
globalMap.put("epma_dbConn", dbConn);
}
}
catch (e)
{
dbConn = getDBConnection_EPMA_PAS();
globalMap.put("epma_dbConn", dbConn);
}
}
else{
dbConn = getDBConnection_EPMA_PAS();
globalMap.put("epma_dbConn", dbConn);
}




for (var i = 0; i < m; i++)
{


var result = dbConn.executeCachedQuery("SELECT * FROM [EPMA_PAS_Merge_DB].[dbo].[EPMA_Spell_codes_NEW] WHERE Hospital_Number ='" + hospitalNumber[i] + "'");


if(!result.first())
{

try{

var query = "INSERT INTO EPMA_Spell_codes_NEW (Hospital_Number, Current_Spell, Old_Spell) VALUES ('"+ hospitalNumber[i] +"','"+ currentSpell[i] +"','"+ oldSpell[i] +"')"

dbConn.executeUpdate (query)

}

catch(e){throw e}
}
}