report to find reason for first invoice
Hi all,
Can I interest anyone in creating an export report which would find the items invoiced for the very first visit for each client?
requirements:
- Find the very first invoice or counter sale for each client
- Filter by client created date
- Filter by product but default is %
- List all products on the invoice in the report
- List all client details required for marketing ie. first name, surname, mailing address, email address, phone number, mobile number
Thanks,
Matt Y.
Re: report to find reason for first invoice
YES. Presently do this manually and is a PITA, so gets done about every few years.
NB. The stats don't ever seem to change. Just need to know how to make those stats change. Can you work on that too!!!
Re: report to find reason for first invoice
Gary - to what does the remark about stats refer? Regards, Tim G
Re: report to find reason for first invoice
Lol Tim, I think they use this report to monitor vital pratice statistics Tim, I think Gary would like to know how to improve his...:)
Re: report to find reason for first invoice
Matt - what output format are you looking for - just a report (in which case I would expect one line per invoice line item and other header lines giving the invoice header data and the other 'client details suitable for marketing', or do you want it set up for export (in which case there is going to be a problem because of the line items data - unless we use a tag field to identify the line type so that you can sort on this in the spreadsheet so a to group say all the client data together).
Can I assume that you only want active customers?
Regards, Tim
Re: report to find reason for first invoice
Tim,
I would like it to export to csv. I know it would mean multiple lines per customer where there is >1 matching items on the invoice but that can get sorted in the spreasheet later as you say. (unless they can be concatenated?)
I would like both active an inactive clients but with their active status listed. eg active: true or false
Thanks,
Matt.
Re: report to find reason for first invoice
Guys
Sorry to confuse re Stats
We track the reason for first visit. I haven't done it for a couple of years as to get the data I want, it means manually going through records. And the data is easily corrupted when the person doing it doesn't pay attention or do it properly.
We would want to download ALL of the client details(name, address, pet, email, phone etc), First visit date, Items on first visit date and clinician.
If we could have total spend of the client(that would give us an idea of how often they have returned) and reminder details that would be amazing.
Re: report to find reason for first invoice
Garry - you had not responded so I assumed that you had lost interest. After some off forum discussion with Matt, the report I delived to him generates data like the following: (Note that this is coming off my anonymised database and so the customer and product names look peculiar)
Looking at your requirements list, we are missing:
1) the clinician (easy to add - but you need to specify which field from the user record - the name or the description - and whether you want the clinician set for the invoice, or the line items or both)
2) the 'reason for first visit' - this is more problematic because the report is running through the invoice data whereas the the visit reason needs to be pulled from the visit data - but it is probably possible
3) the 'total spend of the customer' - is this the sum of all invoices + counter sales less credits? Again - this needs some sexy SQL.
4) the 'reminder details' - again you need to define what data you want - are you looking for the current reminders or the reminder history or what. Are you sure that the reminder data is not better handled in a separate report?
Note that one of the problems in constructing a 'tell me everything' report is that currently OpenVPMS does not support having multiple subreports invoked by the one report. If it did, then doing what you want would be a lot easier. The current version uses a subreport to pull in the invoice line items. It would make things easier if one could use other subreports to pull in the reminder and other data.
Finally (Matt - take note) - reading through the above description of what the report does, the "shows the customers who were created and had a first sale between the specified dates" is probably the wrong approach. If a new customer rang up today and booked an appointment for two weeks ahead, the 'created date' would be today, but the 'first sale' date would be in two weeks time.
Regards, Tim G
Re: report to find reason for first invoice
If the invoice info is there, like in your example, that would kind of work. The only issue would be, did they come in for a lameness and anticipated we were going to be their surgeon, or did they come in for a lameness and were new to the area and had no prior vet. We would have to sort that manually, although referral information might help there.
Current reminders would be adequate.
Yes, total spend as you defined.
Name - THE MEGA-NEW-CLIENT REPORT THAT ANSWERS ALL THE BIG QUESTIONS!!!