February 15, 2019 at 3:29 am #37163Mark RatcliffeParticipant
I want to add an email field to the existing Payment list report.
How do I go about doing this?February 15, 2019 at 12:04 pm #37164WilliamKeymaster
Thanks for the post. In order to add this, you will need to update the SQL Source of the report. By default the source for the payment list is just “select * from view_payments” but in order to add the email address, we will need to expand open the SQL and then add the value per.email which will add the email address to this report. The full SQL code is below for this.
pay.id, paynum, datetime, pay.amount, pay.reference,
pay.per_id as receivedfrom_per_id, trim(both ‘ ‘ from per.lastname || ‘, ‘ || coalesce(per.firstname,”) || ‘ ‘ || coalesce(per.title,”)) as _receivedfrom,
per.lastname, per.firstname, per.title,
paymethod_id, paymethod.name as _paymentmethod,
pay.clinic_id, clinic.name as _clinicname, clinic.prefix as _clinicprefix,
(coalesce(clinic.prefix, ”) || cast(pay.paynum as varchar(20))) as _payref,
coalesce(view_payamtalloc.amtalloc, 0) as amtallocated,
(pay.amount – coalesce(view_payamtalloc.amtalloc, 0)) as amtunallocated,
when (pay.amount = 0) or (view_payamtalloc.amtalloc >= pay.amount) then
when (view_payamtalloc.amtalloc > 0) and (view_payamtalloc.amtalloc < pay.amount) then
end as varchar(20)) as _status,
pay.createdby_staff_id as takenby_staff_id, cr_staff.knownas as _takenby,
pay.createdon, pay.createdby_staff_id, cr_staff.knownas as _createdby,
pay.updatedon, pay.updatedby_staff_id, up_staff.knownas as _lastupdatedby,
inner join clinic on (pay.clinic_id = clinic.id)
inner join per on (pay.per_id = per.id)
inner join paymethod on (pay.paymethod_id = paymethod.id)
left join view_payamtalloc on (pay.id = view_payamtalloc.pay_id)
left join staff as cr_staff on (pay.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (pay.updatedby_staff_id = up_staff.id)
Please copy the above and then follow the steps below.
- Go into the Reports and Templates in ClinicOffice
- Right-click on the Payment List report and select Report Source
- Delete all the text in the SQL Source window
- Copy the above text into the SQL Source window in ClinicOffice
- Click OK
When you run the report next the patient’s email will now be present in the grid as well.
- You must be logged in to reply to this topic.