Announcement

Collapse
No announcement yet.

Send outbound msg only once and update database column after sending?

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

  • Send outbound msg only once and update database column after sending?

    ==========
    Mirth 1.3.1
    Outbound Channel: Database Reader to File Writer

    Requirement:
    ==========
    Send ORU^R01 Transcription message for each row in DocumentList table with Status = 0

    After sending the message, update the Status column to 1 so that the same document is not sent again.

    Valid Status Codes Are:
    ================
    0 - Not Sent
    1 - Sent
    3 - Error

    ==================
    1. SELECT Statement

    SELECT DocumentID, DoccumentText, (plus other fields for PID, PIV, OBR fields)
    FROM DocumentList
    WHERE Status = 0

    2. UPDATE Statement

    UPDATE DocumentList
    SET Status = 1
    WHERE DocumentID = {$documentid} (from the select statement above)
    ==================

    I set the option to "Yes" to run the UPDATE SQL

    Problem:
    =======

    Mirth keeps sending multiple messages for the same document and the Status column is not getting updated to 1.

    Any help would be appreciated. Also, I would like to know if there's any "Best practice" to implement this requirement.

    Thanks in advance.

  • #2
    Re: Send outbound msg only once and update database column after sending?

    Try:

    WHERE DocumentID = ${documentid}
    Chris Lang

    Comment


    • #3
      Re: Send outbound msg only once and update database column after sending?

      Thank you. I got it working.

      The database connection string was using the PatientDB and the documents were in DocumentDB.

      jdbc:jtds:sqlserver://TestServer:1433/PatientDB

      The actual SQL statment used in the channel has multiple table JOINs. To make the forum post easy to understand, I used a simple SQL statment without the join.

      ============
      SELECT P.LastName, D.DocumentID, D.DocumentText
      FROM DocumentDB.dbo.Document D
      INNER JOIN PatientDB.dbo.Patient P ON D.PatientID = P.PatientID
      ============

      The UPDATE statement did not have the full database qualifier name and had just the table name. After I changed the UPDATE statement to
      ============
      UPDATE DocumentDB.dbo.DocumentDB
      SET Status = 3
      WHERE DocumentID = ${documentid}
      ============
      it worked fine.

      Thank you!

      Comment


      • #4
        Re: Send outbound msg only once and update database column after sending?

        Status = 1 not 3

        UPDATE DocumentDB.dbo.DocumentDB
        SET Status = 1
        WHERE DocumentID = ${documentid}

        Comment


        • #5
          Re: Send outbound msg only once and update database column after sending?

          SELECT P.LastName, D.DocumentID, D.DocumentText
          FROM DocumentDB.dbo.Document D
          INNER JOIN PatientDB.dbo.Patient P ON D.PatientID = P.PatientID
          WHERE Status = 0

          =========

          UPDATE DocumentDB.dbo.DocumentDB
          SET Status = 1
          WHERE DocumentID = ${documentid}

          Comment

          Working...
          X