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

[nshaik]

==========
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.

[chrisl]

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

Try:

WHERE DocumentID = ${documentid}

[nshaik]

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!

[nshaik]

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}

[nshaik]

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}