octort wrote on Thursday, April 05, 2012:
I am trying to make sure that every patient has a Medical Record ready for review by the doctor at least by the day of the visit. Is it possible to check if the scheduled patients have a Medical Record in their respective documents folder?
thanks in advance
bradymiller wrote on Friday, April 06, 2012:
Hi,
Would need to code this, but wouldn’t be too tough. Could just modify the Appointments report to query for this. To see how to query for docs from a certain document category, check out the code on the patient summary page that queries for Advanced Directives docs.
-brady
OpenEMR
octort wrote on Friday, April 06, 2012:
I’ll give it a try, thanks!
octort wrote on Friday, July 13, 2012:
I found a way to get a report that shows all the scheduled patients that don’t have a medical record:
SELECT Cal.pc_pid AS Num
, Pat.lname
, Pat.fname
, Pat.mname
, Cal.pc_eventDate AS Appointment
, TIME_FORMAT(Cal.pc_startTime, ‘%h:%i %p’) AS `Time`
, Fac.name AS Facility
FROM
openemr_postcalendar_events Cal
LEFT OUTER JOIN (SELECT CatToDocs.category_id
, CatToDocs.document_id
, documents.foreign_id
FROM
categories_to_documents CatToDocs
INNER JOIN documents
ON CatToDocs.document_id = documents.id
WHERE
CatToDocs.category_id = 3) PatsWithMedRec
ON Cal.pc_pid = PatsWithMedRec.foreign_id
INNER JOIN patient_data Pat
ON Pat.pid = Cal.pc_pid
INNER JOIN facility Fac
ON Fac.id = Cal.pc_facility
WHERE
Cal.pc_pid <> ‘’
AND Cal.pc_eventDate >= curdate()
AND Cal.pc_catid IN (5, 9, 10, 12, 13, 14, 15, 16, 17, 19)
AND PatsWithMedRec.foreign_id IS NULL
GROUP BY
Cal.pc_pid
, PatsWithMedRec.foreign_id
, Pat.lname
, Pat.fname
, Pat.mname
, Cal.pc_startTime
, Cal.pc_eventDate
, Fac.name
Based on Report - Patient List
It maybe helpful for someone else.