Reminder report - looking for a data dump report

Does a report exist that is just a data dump of all reminders in the system? I see reports for single clients, reports that show all reminders sent and several for sending reminders but nothing that just lists all reminders due. 

The fields I would be interested in would be client name, pet name, reminder due, due date (future and past). 

Am I just overlooking where this currently exists?

Thanks, Alan

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Reminder report - looking for a data dump report

I'm not aware of any, but the following SQL could be used as the basis of such a report.

select reminder.act_id reminder_id,
       reminder.activity_end_time due_date,
       customer.entity_id customer_id,
       customer.name customer_name,
       patient.entity_id patient_id,
       patient.name,
       reminder_type.entity_id reminder_type_id,
       reminder_type.name reminder_type_name
from acts reminder
join participations ppat
    on reminder.act_id = ppat.act_id
        and ppat.arch_short_name = 'participation.patient'
join entities patient
    on ppat.entity_id = patient.entity_id
join participations ptype
    on reminder.act_id = ptype.act_id
        and ptype.arch_short_name = 'participation.reminderType'
join entities reminder_type
    on ptype.entity_id = reminder_type.entity_id
join entity_relationships owner
    on owner.target_id = patient.entity_id
        and owner.arch_short_name = 'entityRelationship.patientOwner'
        and owner.active_end_time is null
join entities customer
    on owner.source_id = customer.entity_id    
where reminder.arch_short_name = 'act.patientReminder' and reminder.status = 'IN_PROGRESS'
    order by customer.name, patient.name, reminder_type.name, reminder.activity_end_time;

Re: Reminder report - looking for a data dump report

Alan - I have updated the resource library versions to provide the ability to find customers/patients with either:
a) no reminders of the specified type(s) - if 'Include None' is checked; or
b) who have current reminders - if 'Include Current' is checked; or
c) whose reminders are overdue if 'Include Overdue' is checked

See http://www.openvpms.org/customisation/export-customer-postal-and-email-a...

Regards, Tim G

Syndicate content