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

    Mark Ratcliffe
    Participant

    Hi,

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

    How do I go about doing this?

    #37164

    William
    Keymaster

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

     

    select

    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,

    cast(case
    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’
    else
    ‘Unallocated’
    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,
    per.email

    from pay
    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.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.