Medication last prescribed date
Hi
I'm looking for a way to get a complete list of the last time every medication was dispensed.
We have a number of medications / products in our system, that are not ordered through the major wholesalers, and the product price is not automatically updated. Staff don't know with these products if the price in the system is correct or if it is out of date. This is especially true for 'one off' medications (eg compounded medications) that may have been ordered in for a particular client but are not deactivated when that medication is no longer required.
Does anyone have a report that is able to provide this list?
Possibly this could be developed into a potential update if we could have an 'end date' on products that once the end date is reached the product becomes inactive? thoughts?
Thanks
David
Re: Medication last prescribed date
David - can I clarify what you want to be as follows:
Look through all act.patientPrescription acts, selecting the latest one for each different medication; print medication name, product id, prescription date; order by medication name
Is that it?
Regards, Tim G
Re: Medication last prescribed date
Hi Tim,
Thanks for your response; -to clarify...
Look through all act.patientPrescription acts, selecting the latest one for each different PRODUCT; print medication name, product id, PRODUCT TYPE, prescription date; order by medication name.
Many thanks
David
Re: Medication last prescribed date
David - see attached first (and I hope only ;-) cut. Run at least once with Display Expanation checked.
Regards, Tim G
Re: Medication last prescribed date
Hi Tim
Thanks for this. Sorry but I led you up the wrong path... (still not across all the archetype groups and the specificity of their names.) What you've given is a list for when a prescription was created (and dispensed) but I'm actually looking for a list of all the active products and the last time they were dispensed. I suspect that that what I should asked for was to look at the act.customerAccountInvoiceItem rather than the act.patientPrescription??
One small addition would be to either list (additional column) or have a check box for whether the product is active or not please.
Thanks again,
David
Re: Medication last prescribed date
Humm - I think that there are a couple of things to tighten up.
a) are you interested in all sales (ie the products listed in invoice line items) or just medications (ie medications listed in the medical records). That is if we take my trusty test patient, ZZ Dog, whose medical record shows:
are you interested in just the medication items (in which case we scan through act.patientMedication) or all the items sold (in which case we look through act.customerAccountInvoiceItem).
Obviously the second will include services and merchandise.
b) given that you have a 'prices may not be correct' problem, do you need the report to include the item unit price and when it was last set? That is, if the item was last sold yesterday but the current price was set in 2005, then one would expect that a price update was needed.
c) in terms of active/inactive products - do you want the report to ignore inactive products, or include them but indicate that they are inactive.
d) as well as the last used/sold date, are you interested in the number of times the item has been sold?
e) do you need selection by product type or classification? (Would be useful if the stuff that you suspect is mis-priced is all product type="not bought from our standard supplier", or has product group="not from standard supplier".)
Actually if the above applies, you might want to look at the Product Price Structure Report (added in the 1.9.1 release) - its selection screen is:
As you can see, you can look for products whose prices have not been updated for some time by setting the 'Price Starts Before' date to an earlier date.
f) "product should not be available after" facility: although the system does not support this, you can have some degree of control via the price end date. If you wanted a product not to be available after 31/5/17, then if you set its price end date to this, then after this date, the product will have price $0. A normal user may let this go through, so a better approach is to add a new price of -$9999999 with a start date of 1/6/17. This will mean that if the product is invoiced after 31/5/17 then the huge negative price will swamp the invoice total and make it negative which will not be allowed by the system.
[Note that one might argue that the system should be enhanced to include a Practice flag "disallow sales of un-priced items". If this was set, then setting the price end date (without any new price starting) would block the use of the product after the end date.]
So come back to me with some answers and I adjust the report.
Regards, Tim G
Re: Medication last prescribed date
Hi Tim,
What I'm try to achieve is probably best described as a 'product price history report'. ie what date was a product price changed last and when was the last time the product was invoiced. This is to achieve two things. One to make sure that all active products in the system have a 'current' price and that we can tidy up and remove any old / outdate products that have not been invoiced in a long time. The former is the more important aspect here. As far as I know OpenVPMS doesn't retain product price history information, so was trying find a way to review this.
From you comments above;
a) - I think it needs to be all products not just the medications so act.customerAccountInvoiceItem is what we should be looking at
b) - Y this would be good.
c) - Only really interested in active products, and happy for this to be all that we look at; but others may want an 'only include active' toggle.
d) - Not interested in the number.
e) - Product type is always useful.
I have downloaded and run the Product Price Structure Report (for 1.9.1); -we are on 1.9 and it appears to work well. It doesn't quite tell me what I'm looking for though, as it is giving the information related to when the product item was created rather than last price change or update. I've gone back and looked at a number of products and if I do a sales by product listing for a single product over a number of years I can see the product price changes within that information, but in the new product price structure report these items that have had recent price changes are still being listed because the first time a product price was added was years ago.
f) I like this idea of using the end date. I also think that having it as part of the system such that once an end date is reached then the product can not be invoiced would be good.
Many thanks
David
Re: Medication last prescribed date
OK - mark 2 is now available from the Resource Library at http://www.openvpms.org/customisation/product-sales-price-age-report . It is now called the 'Product Sales Price Age Report'. You will see that this is a modification of the Product Price Structure report that allows you to select only products sold in the last N months.
Running it over our system revealed some potential problems - we have 41 products with sales in the last 3 months whose prices were set more than 10 years ago.
For others reading this discussion, note than the report assumes version 1.9 and later.
Regards, Tim G