Contact Journal Report

[Support]

**SERVER EDITION ONLY**

Please find attached a new “Contact Journal” report which prints the full contact journal and all associated notes and SMS messages sent to patients/contacts.

Before importing this report you will first need to create a custom view. The steps below will outline how to do this.

1) Go to the Tools menu (tab)

2) Click the Advanced DB Operation button

3) Delete any text present in this window

4) Copy and paste the text below into the “Advanced Database Operation” window

create or replace view xview_logsms as

select

per.id as per_id, trim(per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _patient_name,
per.code, per.company,

(case
when extract(dow from actlog.datetime) = 1 then ‘Mon’
when extract(dow from actlog.datetime) = 2 then ‘Tue’
when extract(dow from actlog.datetime) = 3 then ‘Wed’
when extract(dow from actlog.datetime) = 4 then ‘Thu’
when extract(dow from actlog.datetime) = 5 then ‘Fri’
when extract(dow from actlog.datetime) = 6 then ‘Sat’
when extract(dow from actlog.datetime) = 7 then ‘Sun’ end)
as day_of_week,

actlog.createdon, actlog.createdby_staff_id, cr_staff.knownas as createdby,
actlog.updatedon, actlog.updatedby_staff_id, up_staff.knownas as updatedby,
actlog.datetime, actlog.notes, cast(” as varchar) as result,

actlog.actlogtype_id, actlogtype.name as _act_log_type,
per.clinic_id, clinic.name as _clinic

from per
join actlog on (actlog.per_id=per.id)
left join actlogtype on (actlogtype.id=actlog.actlogtype_id)
left join staff as cr_staff on (actlog.createdby_staff_id=cr_staff.id)
left join staff as up_staff on (actlog.updatedby_staff_id=up_staff.id)
left join clinic on (per.clinic_id=clinic.id)

union all

select

per.id as per_id, trim(per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _patient_name,
per.code, per.company,

(case
when extract(dow from sms_out.datetime) = 1 then ‘Mon’
when extract(dow from sms_out.datetime) = 2 then ‘Tue’
when extract(dow from sms_out.datetime) = 3 then ‘Wed’
when extract(dow from sms_out.datetime) = 4 then ‘Thu’
when extract(dow from sms_out.datetime) = 5 then ‘Fri’
when extract(dow from sms_out.datetime) = 6 then ‘Sat’
when extract(dow from sms_out.datetime) = 7 then ‘Sun’ end)
as day_of_week,

sms_out.createdon, sms_out.createdby_staff_id, cr_staff.knownas as createdby,
sms_out.updatedon, sms_out.updatedby_staff_id, up_staff.knownas as updatedby,
sms_out.datetime, sms_out.msg as notes, sms_out.result,

cast(null as bigint) as actlogtype_id, cast(‘SMS OUT’ as varchar) as _act_log_type,
per.clinic_id, clinic.name as _clinic

from per
join sms_out on (sms_out.per_id=per.id)
left join staff as cr_staff on (sms_out.createdby_staff_id=cr_staff.id)
left join staff as up_staff on (sms_out.updatedby_staff_id=up_staff.id)
left join clinic on (per.clinic_id=clinic.id);

ALTER TABLE xview_logsms
OWNER TO admin;
GRANT ALL ON TABLE xview_logsms TO admin;
GRANT ALL ON TABLE xview_logsms TO std_user;

5) Click Execute and OK

Now that you have created the view you can download the report via the link below.

https://pioneersoftware.co.uk/wp-content/uploads/2022/07/Contact-Journal-Report-Patients.zip

For instructions on how to import report files, please follow the instructions in this post:-
https://pioneersoftware.co.uk/forums/topic/how-do-i-import-a-report-file-into-my-database

[Author]

Posts