Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
  • #37163
    Mark Ratcliffe


    I want to add an email field to the existing Payment list report.

    How do I go about doing this?


    Hi Mark

    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 which will add the email address to this report. The full SQL code is below for this.


    select, 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, as _paymentmethod,
    pay.clinic_id, 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
    ‘Fully Allocated’
    when (view_payamtalloc.amtalloc > 0) and (view_payamtalloc.amtalloc < pay.amount) then
    ‘Part Allocated’
    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,

    from pay
    inner join clinic on (pay.clinic_id =
    inner join per on (pay.per_id =
    inner join paymethod on (pay.paymethod_id =
    left join view_payamtalloc on ( = view_payamtalloc.pay_id)
    left join staff as cr_staff on (pay.createdby_staff_id =
    left join staff as up_staff on (pay.updatedby_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.

Viewing 2 posts - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.