Search database by Transaction

Submitted by YarraRanges on Tue, 07/07/2015 - 14:52

General User's Discussion

How does one search the database for a specific transaction? viz. A client has made a direct deposit to the practice Bank account, and only provided the invoice number as a reference and a specific amount. The amount may or may not equal the customer's outstanding balance

Re: Search database by Transaction

Submitted by tgething (not verified) on Tue, 07/07/2015 - 15:38.

I do not know of a report that will do this. However, if you are willing to run an SQL query (either via the console, or via MySQL Workbench) then the following SQL should do the trick:

select c.name as custname, c.entity_id as custid
from acts a
join participations pc on pc.act_id = a.act_id and pc.arch_short_name = 'participation.customer'
join entities c on c.entity_id = pc.entity_id
where a.arch_short_name = 'act.customerAccountChargesInvoice'
and a.act_id = 2825866;

Change the act_id to the one in the bank reference.

Here is the mysql client being used to run the above query. As you can see it shown the customer name and id.

Regards, Tim G

Re: Search database by Transaction

Submitted by YarraRanges on Tue, 21/07/2015 - 15:48.

Thanks Tim, but far too technical for me. Need an OpenVPMS Report

Thanks,

Paul Martin

Re: Search database by Transaction

Submitted by tgething (not verified) on Tue, 21/07/2015 - 16:40.

OK Paul - leave it with me and I will knock up something. Do you want it to 1.7 or 1.8 ?

Can I assume that the report design is:

Input parameter: transaction id

Action: find transaction with that ID and dump as much detail as possible, ie customer name and id, date, status, amount, transaction type (ie invoice, credit, etc)

Print 'no matching transaction found' if nothing with that id.

Is there anything I have forgotten?

Cost: a $50 (or more if you want) donation to OpenVPMS.

Regards, Tim G