Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #31493
    gdosteopathy
    Participant

    Hi

    In the patient information section under payments I have added a field called “provider_number”. I would like to add this field into my “Invoice for insurance company” report. How do I do this?

    #33421

    Hi there,

    I noticed that you mentioned you created a field in the PAYMENT screen and you want it to show on a report that shows INVOICES.

    Can you just confirm this is correct as PAYMENTS and INVOICES are seperate tables in the database.

    Thanks

    #33422
    gdosteopathy
    Participant

    Hello and thanks for the reply, let me clarify..

    In the “new Patient Record”, in the tab titled “More Details” there is a section called “Payment Information”. Under the book titled “Insurance Ref” I have added another box called “Provider Number” provider_number. It is this provider number that I would like to add to invoices.

    Most insurance companies request provider numbers and claim numbers or patients policy number and provider numbers or patients policy number and claim number etc… so I need an additional field to add this in to..

    The reports SQL is currently:

    select

    inv.id, inv.invoicenum, inv.datetime, cast(inv.datetime as date) as _invdate, inv.discpercent, inv.discamount, inv.nettotal, inv.taxtotal, inv.total, inv.customerref, inv.address, inv.notes,

    inv.clinic_id, clinic.name as _clinicname, clinic.address as _clinicaddress,

    inv.per_id as recipient_per_id, @NAME:recipientper as _recipientname,

    inv.patient_per_id, @NAME:patientper as _patientname, @ADDRESS:patientper as patientaddress,
    patientper.code as patientcode, patientper.dob as patientdob, patientper.insuranceref as patientinsuranceref,

    inv.staff_id, staff.knownas as _staffname,
    inv.apptype_id, apptype.name as _appointmenttype,
    inv.room_id, room.name as _roomname,

    coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,
    (inv.total-coalesce(view_invamtalloc.amtalloc , 0)) as outstanding

    from inv
    left join per as recipientper on (inv.per_id=recipientper.id)
    left join per as patientper on (inv.patient_per_id=patientper.id)
    left join staff on (inv.staff_id=staff.id)
    left join apptype on (inv.apptype_id=apptype.id)
    left join clinic on (inv.clinic_id=clinic.id)
    left join room on (inv.room_id=room.id)
    left join view_invamtalloc on (inv.id=view_invamtalloc.inv_id)

    where (inv.total>coalesce(view_invamtalloc.amtalloc, 0))

    order by _recipientname, _patientname, inv.datetime

    #33423

    Hi there,

    Thanks for clearing that up,

    Please make a copy of the report first and then paste this SQL into it just to make sure it works!

    select

    inv.id, inv.invoicenum, inv.datetime, cast(inv.datetime as date) as _invdate, inv.discpercent, inv.discamount, inv.nettotal, inv.taxtotal, inv.total, inv.customerref, inv.address, inv.notes,

    inv.clinic_id, clinic.name as _clinicname, clinic.address as _clinicaddress,

    inv.per_id as recipient_per_id, @NAME:recipientper as _recipientname,

    inv.patient_per_id, @NAME:patientper as _patientname, @ADDRESS:patientper as patientaddress,
    patientper.code as patientcode, patientper.dob as patientdob, patientper.insuranceref as patientinsuranceref,

    inv.staff_id, staff.knownas as _staffname,
    inv.apptype_id, apptype.name as _appointmenttype,
    inv.room_id, room.name as _roomname,

    coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,
    (inv.total-coalesce(view_invamtalloc.amtalloc , 0)) as outstanding,

    custom_patient_fields.provider_number

    from inv
    left join per as recipientper on (inv.per_id=recipientper.id)
    left join per as patientper on (inv.patient_per_id=patientper.id)
    left join staff on (inv.staff_id=staff.id)
    left join apptype on (inv.apptype_id=apptype.id)
    left join clinic on (inv.clinic_id=clinic.id)
    left join room on (inv.room_id=room.id)
    left join view_invamtalloc on (inv.id=view_invamtalloc.inv_id)
    left join custom_patient_fields on (patientper.id=custom_patient_fields.per_id)

    where (inv.total>coalesce(view_invamtalloc.amtalloc, 0))

    order by _recipientname, _patientname, inv.datetime

    #33424
    gdosteopathy
    Participant

    Perfect thanks

    So using the same system, I’d also like to add some automatic data into letters generated for GP’s. i.e. I’ve got a custom field on my “new case record” called diagnosis. How would I get the details from this case record i.e. date of visit, diagnosis etc… into a GP letter?

    custom_csee_fields.diagnosis

    left join custom_case_fields on….

    thanks again

    #33425
    Support
    Keymaster

    Yes what you put is correct on principle.

    The case table is called “cas” (‘case’ is a reserved word in SQL), so the report would take the form :-

    select ..., custom_case_fields.your_field ...
    ...
    from cas
    left join custom_case_fields.cas_id=cas.id
    ...
Viewing 6 posts - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.