AWS Standard 5.0.1(7): Reminder report shows nothing

(Ralf Lukner MD PhD) #1

Reminder report shows nothing … rule configuration is shown below. Process reminders shows not records.

Rule Detail

test (Passive Alert)
Funding Source**:**
Web Reference**:**
Reminder intervals

~Type~ ~Detail~
Clinical Warning: 2 Weeks, Past due: 2 Weeks
Patient Warning: 1 Weeks, Past due: 1 Weeks

Demographics filter criteria [(add)](
[(edit) ]Age Min (Years) Required Inclusion 8

Target/Action Groups
Clinical targets
None defined

[(edit)] Reminder - Appointment

Clinical targets [(add) ]

Actions [(add) ]

(Ralf Lukner MD PhD) #2

If the above just doesn’t work and no one knows how … assuming that I know how to query the openemr database directly, how to I query (SQL) for the patients who have not had an appointment (encounter) in the past 3 months?

(ViSolve) #3

Here is the Query that you can use to get your report:
select p.fname,o.pc_pid from patient_data p inner join openemr_postcalendar_events o ON pc_pid=pid where pc_eventdate NOT BETWEEN (NOW() - INTERVAL 90 DAY) AND NOW();

However, you may want to consider using a custom report form to save time and flexibility in getting reports.
-visolve OpenEMR Support

(Ralf Lukner MD PhD) #4

This query shows 2399 rows in set, which is more than the number of patients (~735)??

(Ralf Lukner MD PhD) #5

Also, when I try to create my own reminder, I get the following query error:

Query Error

ERROR: query failed: SELECT pc_eventDate FROM openemr_postcalendar_events WHERE pc_eventDate>? AND pid=? AND (date BETWEEN DATE_SUB(‘2019-07-03 10:29:05’, INTERVAL 3 MONTH) AND ‘2019-07-03 10:29:05’)

Error: Unknown column ‘pid’ in ‘where clause’

/var/www/localhost/htdocs/openemr/library/ at 171:sqlStatementNoLog
/var/www/localhost/htdocs/openemr/library/clinical_rules.php at 1961:sqlStatementCdrEngine(SELECT pc_eventDate FROM openemr_postcalendar_events WHERE pc_eventDate>? AND pid=? AND (date BETWEEN DATE_SUB(‘2019-07-03 10:29:05’, INTERVAL 3 MONTH) AND ‘2019-07-03 10:29:05’) ,Array)
/var/www/localhost/htdocs/openemr/library/clinical_rules.php at 1717:exist_database_item(307,openemr_postcalendar_events,pc_eventDate,gt,STR_TO_DATE(‘Dec 31, 2018’,’%M %d,%Y’),lt,1,month,3,2019-07-03 10:29:05)
/var/www/localhost/htdocs/openemr/library/clinical_rules.php at 1241:database_check(307,Array,Array,2019-07-03 10:29:05)
/var/www/localhost/htdocs/openemr/library/clinical_rules.php at 820:test_targets(307,rule_102,2019-07-03 10:29:05)
/var/www/localhost/htdocs/openemr/library/reminders.php at 220:test_rules_clinic(,patient_reminder,2019-04-03 10:29:05,reminders-due,307)
/var/www/localhost/htdocs/openemr/library/reminders.php at 49:update_reminders(2019-04-03 10:29:05,307)
/var/www/localhost/htdocs/openemr/interface/patient_file/summary/patient_reminders_fragment.php at 24:patient_reminder_widget(307)

(Ralf Lukner MD PhD) #6

One more thing … The following query “hangs” (gets stuck at processing 0/753):

Clinical targets (add)

Criteria Characteristics Requirements

(edit) (delete) openemr_postcalendar_events.pc_eventDate Required Inclusion Value: > 2018-12-31 | Frequency: < 1 | Interval: 3 x Months

(Stephen Waite) #7

in the calendar tables it’s pc_pid

(Ralf Lukner MD PhD) #8

how do I edit the clinical_rule.php? It’s read-only and that worries me.

(Stephen Waite) #9

almost every file on AWS packages are read only for security, you’ll want to play around with the scripts on a backup/development system.

(Brady Miller) #10


May be worth getting this to work in the CDR. Here’s the pertinent code:
openemr/clinical_rules.php at master · openemr/openemr · GitHub

And here’s the commit that added this functionality:


(ViSolve) #11

My Bad…The query report shows all of the reminders which are more than 90 days old…So, you may get multiple reminder for the same patients.

Here is the correct query which will give unique patients who did not have appointments for the past 90 days…
select p.fname, from patient_data p where pid not in (select distinct pc_pid from openemr_postcalendar_events where pc_eventdate BETWEEN (CURDATE() - INTERVAL 90 DAY) AND CURDATE());

Please let us know if you still have trouble with the report.
-ViSolve OpenEMR Support Team

(Ralf Lukner MD PhD) #12

I’m going to try ViSolve’s recommendation and wait for an official patch because it is the safest approach because I only have a production system … and I’m not going to fool myself into thinking that I can maintain a development system and properly test changes that I make … Thank you so much, everyone!