Announcement

Collapse

Mirth Connect 3.12.0 Released!

Mirth Connect 3.12.0 is now available as an appliance update and on our GitHub page. This release includes database performance improvements, improves visual HL7 representation, message pruning, keystore handling, PDF generation, community contributions, and fixes several security vulnerabilities. This release also contains many improvements to commercial extensions. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

OBX's and OBR's [Solved]

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • OBX's and OBR's [Solved]

    First off, this is one of the most complete systems i've seen (never mind in hl7) in long time so congratulations to all involved and thanks for making it available to the community.

    I've spent the last hour or so searching the forums for a solution to a problem that seems to come up again and again, but never seems to be resolved.

    I have a message with repeating elements of OBX inside OBRs and there doesn't seem to be a straightforward way of parsing these sensibly.

    My file is as below

    Code:
    MSH|^~&|WinPath|LAB|LABRESULTS|LABRESULTS|200803031528||ORU^R01|127127|T|2.3|||NE|NE||||
    PID|||WS01839291||PATIENT^TEST^^^||199010100000|F|||^^^^|||||||||||||||||||
    PV1|||SAMPLE CO|||||||||||||||||||||||||||||||||||||||||
    ORC|RE||0007T999740||CM||||200803031528|||||||||||
    OBR|1||0007T999740|GBSX^GROUP B STREP - SMS TEXT^WinPath||200710111919|200710110000||||||||^|SAMPLE CO||||||200710151927||1|F|||^|||||||||||||||||||
    OBX|1|ST|GBS1^Low Vaginal Swab:^EXT||Positive|||N|||F|||||
    OBX|2|NM|SMSP^Text Number^EXT||07798914737|||N|||F|||||
    OBX|3|ST|GBS2^Rectal Swab:^EXT||Positive|||N|||F|||||
    OBR|2||0007T999740|U/E^.UREA & ELECTROLYTES.^WinPath||200710111919|200710110000||||||||^|SAMPLE CO||||||200710121926||1|F|||^|||||||||||||||||||
    OBX|1|NM|NA^SODIUM^EXT||140|mmol/L^^L|135   - 145  |N|||F|||||
    OBX|2|NM|K^POTASSIUM^EXT||4.0|mmol/L^^L|3.5   - 5.1  |N|||F|||||
    OBX|3|NM|CL^CHLORIDE^EXT||103|mmol/L^^L|98    - 107  |N|||F|||||
    OBX|4|NM|HCO3^BICARBONATE^EXT||27|mmol/l^^L|22    - 29   |N|||F|||||
    OBX|5|NM|UREA^UREA^EXT||5.6|mmol/L^^L|1.7   - 8.3  |N|||F|||||
    OBX|6|NM|CREA^CREATININE^EXT||102|umol/L^^L|49    - 92   |H|||F|||||
    OBR|3||0007T999740|LFT^.LIVER GROUP.^WinPath||200710111919|200710110000||||||||^|SAMPLE CO||||||200710121926||1|F|||^|||||||||||||||||||
    OBX|1|NM|BILI^BILIRUBIN^EXT||10|umol/L^^L|0     - 20   |N|||F|||||
    OBX|2|NM|ALP^ALKALINE PHOSPHATASE^EXT||120|IU/L^^L|35    - 104  |H|||F|||||
    OBX|3|NM|AST^ASPARTATE TRANSFERASE^EXT||35|IU/L^^L|0     - 31   |H|||F|||||
    OBX|4|NM|ALT^ALANINE TRANSFERASE^EXT||35|IU/L^^L|10    - 35   |N|||F|||||
    OBX|5|NM|GGT^GAMMA GT^EXT||100|IU/L^^L|9     - 35   |H|||F|||||
    OBX|6|NM|PROT^TOTAL PROTEIN^EXT||75|g/L^^L|63    - 83   |N|||F|||||
    OBX|7|NM|ALB^ALBUMIN^EXT||40|g/L^^L|32    - 45   |N|||F|||||
    OBX|8|NM|GLOB^GLOBULIN^EXT||35|g/L^^L|19    - 35   |N|||F|||||
    OBR|4||0007T999740|^^WinPath||200710111919|200710110000||||||||^|SAMPLE CO||||||200710151443||1|F|||^|||||||||||||||||||
    OBX|1|ST|SUMX^Summary CCG/SCG^EXT||Positive|||N|||F|||||

    I've taken the MSSQL example channel and can get it working perfectly if i just want to read the OBX's, but the problem is that i need 2 tables, one for requests and one for results. ie i want to read each loop of OBRs and store the OBXs that go with it.

    My (hackup up frankenstein) code is as below:


    Code:
    if(msg['MSH']['MSH.9']['MSH.9.1'] == "ORU" && msg['MSH']['MSH.9']['MSH.9.2'] == "R01"«») 
    
    { 
    	
    	var driver = "com.mysql.jdbc.Driver"; 
    	var address = "jdbc:mysql://127.0.0.1:3306/test"; 
    	var username = "testuser"; 
    	var password = ""; 
    	var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver,address,username,password); 
    
    	var patientID = msg['PID']['PID.3']['PID.3.1']; 
    
    	
    	for each (seg in msg..OBR)
    	{
    	
    	var DateTimeRequested = "NULL";
    	var FillerOrderNumber = "NULL";
    	var UniversalServiceText = "NULL";
    	var OrderingProvider = "NULL";
    
    	var FillerOrderNumber = seg['OBR.3']['OBR.3.1'];
    	var UniversalServiceText = seg['OBR.4']['OBR.4.2'];
    	var DateTimeRequested = seg['OBR.6']['OBR.6.1'];
    	var OrderingProvider = seg['OBR.16']['OBR.16.2'];
    
    //	add in escape slashes here...when i get round to it...
    	var expression = "INSERT IGNORE INTO requests values ('', '" + FillerOrderNumber + "','" + UniversalServiceText + "','" + DateTimeRequested + "','');"; 
    	var result = dbConn.executeUpdate(expression); 
    	
    	var retrieveID = "SELECT last_insert_id();";
    	var RequestID  = dbConn.executeUpdate(retrieveID);
    		
    		for each (obx in seg)
    		{
    		var Ob_Range = "NULL";
    		var Ob_Name = "NULL";
    		var Ob_Value = "NULL";
    		var Units = "NULL";
    		var Abnormal_Flags = "NULL";
    		var Observ_result_status = "NULL";
    		
    		//variable obervation Name
    			var Ob_Name= obx['OBX']['OBX.3']['OBX.3.2']; 
    			var Ob_Value= obx['OBX']['OBX.5']['OBX.5.1']; 
    			var Ob_Range=obx['OBX']['OBX.7']['OBX.7.1']; 
    			var Units=obx['OBX']['OBX.6']['OBX.6.1']; 
    			var Abnormal_Flags=obx['OBX']['OBX.8']['OBX.8.1']; 
    			var Observ_result_status=obx['OBX']['OBX.11']['OBX.11.1']; 
    
    		//obr id needed to identify which obr this obx belongs to
    		var expression = "INSERT IGNORE INTO observations values ('', '" + RequestID + "', '" + patientID + "', '" + Ob_Name + "','" + Ob_Value + "','" + Ob_Range + "','" + Abnormal_Flags + "','" + Observ_result_status + "','" + Units + "');"; 
    		var result = dbConn.executeUpdate(expression); 
    		
    	}
    
    	}
    
    //simply closes the connection to the DB 
    dbConn.close(); 
    }
    
    return true;

    I think the basic problem with my code is that i don't really understand the usage of the iterate over segment function.
    again, i can get all the OBXs out without any difficulty using a nice loop, but obviously as the OBXs appear to be siblings of OBRs and not children, i can't seem to find a way of nicely delimiting the loop.


    Any help would be very much appreciated.

    Post edited by: rbbhghs, at: 03/24/2008 05:48

    Post edited by: rbbhghs, at: 03/24/2008 09:33

  • #2
    Re:Repeating of old themes (obx's and obr's)

    Give this a try. You may need to check the mySegment[3].split("^") lines to see if you are getting the correct value, or if it even executes. I did test this part.


    Code:
        var driver = "com.mysql.jdbc.Driver"; 
        var address = "jdbc:mysql://127.0.0.1:3306/test"; 
        var username = "testuser"; 
        var password = ""; 
        var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver,address,username,password); 
        var sRepValue = "####";
        var sReplace = "";
        var mySegment = "";
        var patientID = msg['PID']['PID.3']['PID.3.1']; 
        var RequestID = "";
    
    //Sequence of events
    //1)  Split raw message into array.
    //2)  Loop through each segment in array.
    //3)  If OBR segment, get RequestID for OBX inserts, insert values into requests table.
    	//3.1)  If next segment is another OBR get new RequestID.
    //4)  If OBX segment, insert values into observations table.
    
    if(msg['MSH']['MSH.9']['MSH.9.1'] == "ORU" && msg['MSH']['MSH.9']['MSH.9.2'] == "R01"«») 
    
    {     
    	//Split raw message into segment array
    	var myRawMessage = messageObject.getRawData().split('\r');
            //Loop through each segment
    	for(j = 0; j < myRawMessage.length; j++){
    				
    		//Reload mySegment with current segment.
    		//Replace "|" with "####"
    		//For some reason I couldn't split by "|"
    		sReplace = myRawMessage[j].replace("|", sRepValue);
    		mySegment = sReplace.split(sRepValue);
    		
    		
    		//OBR segment
    		if (myRawMessage[j].substring(0,3) == 'OBR'){	
    			
    			//Reset RequestID. This will make sure we get the correct 
    			//Request ID if 2 OBR segments are back to back.
    			RequestID = "";
    				
    			
    			//Split OBR Segment value
    			 var DateTimeRequested = "NULL";
    			 var FillerOrderNumber = "NULL";
    			 var UniversalServiceText = "NULL";
    			 var OrderingProvider = "NULL";
    						
    			 var FillerOrderNumber = mySegment[3].split("^"«»)(1);    //seg['OBR.3']['OBR.3.1'];
    			 var UniversalServiceText = mySegment[4].split("^"«»)(2); //seg['OBR.4']['OBR.4.2'];
    			 var DateTimeRequested = mySegment[6].split("^"«»)(1);  	//seg['OBR.6']['OBR.6.1'];
    			 var OrderingProvider = mySegment[16].split("^"«»)(2);  	//seg['OBR.16']['OBR.16.2'];
    			
    			//    add in escape slashes here...when i get round to it...
    			    var expression = "INSERT IGNORE INTO requests values ('', '" + FillerOrderNumber + "','" + UniversalServiceText + "','" + DateTimeRequested + "','');"; 
    			    var result = dbConn.executeUpdate(expression); 
    			    
    			    var retrieveID = "SELECT last_insert_id();";
        			    RequestID  = dbConn.executeUpdate(retrieveID);
        		}    
    		//OBX Segment
    		if (myRawMessage[j].substring(0,3) == 'OBX'){	
    		 	var Ob_Range = "NULL";
    		        var Ob_Name = "NULL";
    		        var Ob_Value = "NULL";
    		        var Units = "NULL";
    		        var Abnormal_Flags = "NULL";
    		        var Observ_result_status = "NULL";
    		        
    		        //variable obervation Name
    		            var Ob_Name= mySegment[3].split("^"«»)(2);			//obx['OBX']['OBX.3']['OBX.3.2']; 
    		            var Ob_Value= mySegment[5].split("^"«»)(1);			//obx['OBX']['OBX.5']['OBX.5.1']; 
    		            var Ob_Range=mySegment[7].split("^"«»)(1);			//obx['OBX']['OBX.7']['OBX.7.1']; 
    		            var Units=mySegment[6].split("^"«»)(1);			//obx['OBX']['OBX.6']['OBX.6.1']; 
    		            var Abnormal_Flags=mySegment[8].split("^"«»)(1);		//obx['OBX']['OBX.8']['OBX.8.1']; 
    		            var Observ_result_status=mySegment[11].split("^"«»)(1);	//obx['OBX']['OBX.11']['OBX.11.1']; 
    		
    		        //obr id needed to identify which obr this obx belongs to
    		        if (RequestID != ""«»){
    		        	var expression = "INSERT IGNORE INTO observations values ('', '" + RequestID + "', '" + patientID + "', '" + Ob_Name + "','" + Ob_Value + "','" + Ob_Range + "','" + Abnormal_Flags + "','" + Observ_result_status + "','" + Units + "');"; 
            			var result = dbConn.executeUpdate(expression); 
    			}
    		}
    	}
    }
    Reid Hospital and Healthcare

    Comment


    • #3
      Re:Repeating of ...(obx's and obr's) [solved]

      Right -
      Solved it at this end - thanks Dan!

      For future reference, this is how it's done provided there is more than one OBX per OBR (which there should be really):

      Code:
      if(msg['MSH']['MSH.9']['MSH.9.1'] == "ORU" && msg['MSH']['MSH.9']['MSH.9.2'] == "R01"«») 
      { 
      	
      	var driver = "com.mysql.jdbc.Driver"; 
      	var address = "jdbc:mysql://127.0.0.1:3306/test"; 
      	var username = "testuser"; 
      	var password = ""; 
      	var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver,address,username,password); 
      
      	var patientID = msg['PID']['PID.3']['PID.3.1']; 
      
      	var iObx = 0; // 0 based format
      
      	for (var iObr = 0; iObr < msg['OBR'].length(); iObr++)
      	{
      
      		// Process your OBR segment - msg['OBR'][iObr] - here
      		var DateTimeRequested = "NULL";
      		var FillerOrderNumber = "NULL";
      		var UniversalServiceText = "NULL";
      		var OrderingProvider = "NULL";
      		var SetNo = "NULL";
      	
      		var SetNo = msg['OBR'][iObr]['OBR.1']['OBR.1.1'];
      		var FillerOrderNumber = msg['OBR'][iObr]['OBR.3']['OBR.3.1'];
      		var UniversalServiceText = msg['OBR'][iObr]['OBR.4']['OBR.4.2'];
      		var DateTimeRequested = msg['OBR'][iObr]['OBR.6']['OBR.6.1'];
      		var OrderingProvider = msg['OBR'][iObr]['OBR.16']['OBR.16.2'];
      	
      	//	add in escape slashes here......
      		var expression = "INSERT IGNORE INTO requests values ('','" + SetNo + "', '" + FillerOrderNumber + "','" + UniversalServiceText + "','" + DateTimeRequested + "','');"; 
      		dbConn.executeUpdate(expression);
      
      		var rs = dbConn.executeCachedQuery("SELECT LAST_INSERT_ID()"«»);
      
      // NB: Bug in DatabaseConnection class?  In the executeQuery method, it (indirectly) closes the 
      // ResultSet before returning it, rendering it useless.  That's why it didn't work the first
      // time when we tried it with executeQuery.
      
      
      		rs.next();
      		var nRequestID = rs.getInt(1);
      	
      		// Now we'll go through this OBR's OBXs
      		do
      		{
      			// Process your OBX segment - msg['OBX'][iObx] - here
      			var Ob_Range = "NULL";
      			var Ob_Name = "NULL";
      			var Ob_Value = "NULL";
      			var Units = "NULL";
      			var Abnormal_Flags = "NULL";
      			var Observ_result_status = "NULL";
      				
      				//variable obervation Name
      			var Ob_Name= msg['OBX'][iObx]['OBX.3']['OBX.3.2']; 
      			var Ob_Value=msg['OBX'][iObx]['OBX.5']['OBX.5.1']; 
      			var Ob_Range=msg['OBX'][iObx]['OBX.7']['OBX.7.1']; 
      			var Units=msg['OBX'][iObx]['OBX.6']['OBX.6.1']; 
      			var Abnormal_Flags=msg['OBX'][iObx]['OBX.8']['OBX.8.1']; 
      			var Observ_result_status=msg['OBX'][iObx]['OBX.11']['OBX.11.1']; 
      		
      				//obr id needed to identify which obr this obx belongs to
      			var expression = "INSERT IGNORE INTO observations values ('', '" + nRequestID + "', '" + patientID + "', '" + Ob_Name + "','" + Ob_Value + "','" + Ob_Range + "','" + Abnormal_Flags + "','" + Observ_result_status + "','" + Units + "');"; 
      			var result = dbConn.executeUpdate(expression);
      		
      			iObx++;
      		} 
      		while (iObx < msg['OBX'].length() && 1 != msg['OBX'][iObx]['OBX.1']['OBX.1.1']);
      	
      	}
      	
      	//simply closes the connection to the DB 
      	dbConn.close(); 
      
      }
      
      return true;
      Post edited by: rbbhghs, at: 03/24/2008 09:36

      Comment


      • #4
        Re:OBX's and OBR's [Solved]

        Just as an aside - we used the model of order table and observation table for quite a while for our ELR process. After a few years of doing this we have found that our size of our database (20 million records) is severely hampering some of the applications and triggers we run against it. We have since created an analytical copy of the database that we feed at the same time that maintains a completely denormalized view of the data (ie. each observation would create a new record with duplicated data for the rest of the fields). We also truncated our other database and only keep the most recent data in it now.

        This may not pertain to your situation, but I thought it would be cool to share so you do not run into the same situation ;-)

        Frans
        Frans de Wet
        Uber Operations LLC

        Phone: (850) 445-7696

        Comment


        • #5
          Re:OBX's and OBR's [Solved]

          wow...
          What DB to you use?

          I don't expect to approach anything like 20m records in the next 10 years say, but nonetheless i'm surprised you're having those problems.

          Comment


          • #6
            Re:OBX's and OBR's [Solved]

            Wow thanks, from how it looks now I believe this has helped me get over the line.

            Thankyou very much for posting this solution.

            Comment


            • #7
              Re:OBX's and OBR's [Solved]

              When executing the line:

              sReplace = myRawMessage[j].replace("|", sRepValue);

              I receive the error:
              Cannot call method "replace" of undefined.

              The code looks like the following and the declarations at the top are listed below as well.

              var myRawMessage;
              var sRepValue = "####";
              var sReplace="";
              var mySegment = "";
              var messlen=0;

              myRawMessage = messageObject.getRawData().split('\r');
              messlen = myRawMessage.length;
              for(j = 0; j <= messlen; j++)
              {
              //Reload mySegment with current segment.
              //Replace "|" with "####"
              //For some reason I couldn't split by "|"
              sReplace = myRawMessage[j].replace("|", sRepValue);
              mySegment = sReplace.split(sRepValue);
              }

              Comment


              • #8
                Re:OBX's and OBR's [Solved]

                Hi all,

                I'm very interested in fransdw experience about database de-normalization. Because this is not a Mirth issue, please fransdw could you contact with me?.

                Thanks in advance,

                Ricard Bernat

                Comment


                • #9
                  Re:OBX's and OBR's [Solved]

                  rbbhghs, we are using MS SQL 2000/2005 for the db.

                  ricber, I'll let him know you want him to contact you (just in case he didn't see it).

                  -Noggin

                  Comment


                  • #10
                    Originally posted by rbbhghs View Post
                    Right -
                    Solved it at this end - thanks Dan!

                    For future reference, this is how it's done provided there is more than one OBX per OBR (which there should be really):

                    Code:
                    		// Now we'll go through this OBR's OBXs
                    		do
                    		{
                    			// Process your OBX segment - msg['OBX'][iObx] - here
                    		
                    			iObx++;
                    		} 
                    		while (iObx < msg['OBX'].length() && 1 != msg['OBX'][iObx]['OBX.1']['OBX.1.1']);
                    What is the "1 != msg['OBX'][iObx]['OBX.1']['OBX.1.1']" doing at the end? In order to figure out that the OBX is part of the associated OBR you make sure you aren't past the end using iObx < msg['OBX'].length() but how do you know when you get to the next OBR's associated section?

                    I get OBR messages like this:
                    OBR|||||||20090306183300

                    So all it contains is the timestamp. How I would be able to distinguish which OBX belong to which OBR if that is all I get?

                    Comment


                    • #11
                      Originally posted by jeffg View Post
                      What is the "1 != msg['OBX'][iObx]['OBX.1']['OBX.1.1']" doing at the end? In order to figure out that the OBX is part of the associated OBR you make sure you aren't past the end using iObx < msg['OBX'].length() but how do you know when you get to the next OBR's associated section?

                      I get OBR messages like this:
                      OBR|||||||20090306183300

                      So all it contains is the timestamp. How I would be able to distinguish which OBX belong to which OBR if that is all I get?
                      Anyone with an answer to this question?

                      Comment


                      • #12
                        Originally posted by jeffg View Post
                        Anyone with an answer to this question?
                        Nevermind, I figured it out. The = 1 is looking for the start of the next OBX segment so if you have:

                        OBR||
                        OBX|1|
                        OBR||
                        OBX|1|
                        OBX|2|

                        You know when you get to the second |1| that you are in the next OBR. It took me a while to figure that out because most of my OBX segments do not contain a count so I wan't sure where the =1 came in. Now that I see other OBX messages that have the counter, it all makes sense.

                        Comment


                        • #13
                          Where are you writing this code??

                          If the destination is database writer then those nodes are unavailable in the destination.

                          If write in code template then it's becomes easy to find which obx is under which obr but then the segments which are repeating under obx are un accessible.

                          would you kindly help me to access them??

                          Comment

                          Working...
                          X