Setting Visit status to "Completed"
Greetings All,
After four months now of using OVPMS, I've discovered that there are numerous Visits tagged within the medical records with status Pending. I've looked through the forums, found this thread:
http://www.openvpms.org/forum/searching-progress-consults
and ran a modified version of the report provided by Tim G. We've got somewhat over 200 instances of Pending Visits that should have status as Completed. I've tracked down the issue to be mostly related to how workflow was being short-circuited - particularly in cases of $0 rechecks - and have reviewed the proper sequence with staff.
I would like to correct the records, and with there being so many, should probably do it through a script. I'm propsing the following, but was wondering if:
1) There are any unforeseen issues with doing it this way, and
2) Whether my script is well-formed - given my nearly total lack of experience with E-R database structure I'm not real confident in it.
UPDATE acts
SET activity_end-time = activity_start_time, status = "COMPLETED"
WHERE
arch_short_name = "act.patientClinicalEvent"
and status != "COMPLETED";
Thanks,
Sam Longiaru
Kamloops, Canada
![Syndicate content](/misc/feed.png "Comments for \"Setting Visit status to "Completed"\""){kind=small}
Re: Setting Visit status to "Completed"
Sam the code looks correct (apart from a mistype of an underline in activity_start-time ).
I normally write two blocks of code (and save them in the one .sql file), one to read out the current situation and one to do the update. Hence my code would be:
Here you can see that the select * showed 48 rows that match, and then the update result with 48 modified rows. So the select * shows me what I am going to change and allows me to check the selection, and then the udate should report the same number of rows affected.
Note also the date selection. Although here I am playing on my test system, I would not do an 'for all dates' update on production because there will almost certainly be some actual visits in progress (both from the night staff and for long stay patients in hospital). Hence when doing these sorts of update I always negotiate with the practice manager up to what date we should run the mass update and then they manually clean up the recent stuff.
Regards, Tim G
Re: Setting Visit status to "Completed"
Thank you Tim!
All excellent advice. I played with the SELECT query via phpMyAdmin before writing the UPDATE script so I know what I should expect, but writing that query into the script itself is a great general rule to follow. Also, I wrestled with the fact that everything was going to be set to COMPLETED. The vets said that would probably be all right, but I like much better your approach of letting them correct the most recent ones manually.
Thanks again. The structure of this database is very, very slowly making sense to me.
Sam
Re: Setting Visit status to "Completed"
No problem Sam. I do admit that being used to traditional databases, I did find the entity-relationship approach somewhat of a steep learning curve. However, having got used to it, I find that the flexibility given by both the design itself, and the archetype driven approach to be wonderful.
How else could I have added a 'Owner Communications' field to the investigation (where the vets put a note of what they have told the owner) and make it invalid to set the investigation status to complete unless there is something in the Owner Comms slot. All without touching a line of the code.
Regards, Tim G
Re: Setting Visit status to "Completed"
Yes, very powerful and flexible indeed! But getting my head around it is definitely a struggle. You wouldn't happen to know an approachable reference to the topic that might help would you? Most of what I've found online takes quite an academic rather than practical approach to the subject.
Thanks,
Sam
Re: Setting Visit status to "Completed"
Sorry Sam - when I was writing the CSH the best I could do was the 'what is an archetype' stuff at the bottom of http://www.openvpms.org/documentation/csh/1.7/admin/archetype
However, this is not of much use if you are trying to write the sql to extract information for a report. As always in the programming world, I found that the best approach was to copy from pre-existing stuff and look at the sql in existing reports to understand how things were put together.
There is arguably a need for a 'database structure' primer for OpenVPMS, but at the current time it is very low on my list of todo jobs.
Regards, Tim G
Re: Setting Visit status to "Completed"
Hi Tim...
I've looked through the OVPMS stuff, which is certainly helpful to an E-R noob as I am, (thank you for that!) and have also followed some of the links and references I found on-line that were also helpful, but I was just wondering if you knew of a good database textbook that covered E-R and the use of archetypes.
While I commend you for having it even at the bottom of your todo list, a primer on how to use the database and archetypes might be outside the scope of what the developers should have to do. There certainly are existing tools that can draw the schema from the database (would be great to have a wall-poster!) and information on the use of archetypes is out there. Just a matter of pulling it all together in an understandable way. So just wondering if you had a favorite reference.
Sam
Re: Setting Visit status to "Completed"
Sam - I suspect that you do not realise how ancient I am. I entered the industry in 74 in the days when Computer Science degrees were not expected (I have a background in Meterorology) though my first exposure was a Fortran programming lab in 2nd year Physics in 65. Hence I have no formal training. Although I can see a K&R 'The C Programming Language' and sundry other O'Reilly books on my bookshelf, I have not opened them since Stack Overflow and other resources became available.
So sorry - no favourite reference text on database design - though if pushed I could probably regenerate the presentations I used to do on Codd's rules - but that was before the ER stuff was conceived.
Schema design: when I started playing with OpenVPMS, I used the MySQL workbench to build an EER diagram of the openvpms database. However, it didn't really help me. I suspect that it would be more useful to document the purpose of each of the database files and explain what is held in each, and how the archetypes relate to the file contents, and then proceed to examples of data extraction.
Regards, Tim G
Re: Setting Visit status to "Completed"
Guess we're both ancient. My first computer course was also in 74. I have to say, though, that I am very pleased with the amount of OVPMS documentation that is already available in regards to its inner workings. So while more is always welcomed (right now I could use some direction in setting up a workable IntelliJ environment...), I just need to bring myself up to speed.
Thanks for all your help. The MySQL script worked great by the way. All is fixed now.
sam
Re: Setting Visit status to "Completed"
IF you guys are using windows try downloading the new MYSQL Workbench..those guys are producing some awesome software Opensource ...including the ability to produce a spider diagram of the database I have attached a diagram of the model I produced in around 20 seconds.
Aditionally the SQL editting and crosschecking far esceeds phpadmins ability - it also monitors database resources.
Sam if you need help with intelliJ let me know ...we might be able to do a RDP or something if there is a particular issue.
Re: Setting Visit status to "Completed"
Hi Ben,
We don't use Windows at all (except for interfacing with the xray machines :( ), but the new version of Workshop is available for Linux just the same and I certainly have used it. My presently installed version is a bit older, but that's where I saw the E-R diagram generator. I couldn't get mine laid out as nicely as yours however, so thanks for the png.
I'll be travelling for the next four or five months but after my return, will be doing the migration to the final release of 1.8. It's at that point that I will dig more into migrating to IntelliJ and might need a little help then. My stumbling block seems to be in setting up the sources. I see that you had recently consolidated those, but have not looked into it. That will be a fall project. Thanks, in advance for the help. I appreciate the offer.
Sam
Re: Setting Visit status to "Completed"
For the record, the OpenVPMS architecture was influenced by OpenEHR (archetypes), and the HL7 Reference Information Model (acts, entities, participations etc).
It doesn't implement either directly however, as they are more complex than is required.
Re: Setting Visit status to "Completed"
CSH updated - see http://www.openvpms.org/documentation/csh/1.8/admin/archetype#archetypeDef
I found Beale's paper interesting - see Archetypes: Constraint-based Domain Models for Future-proof Information Systems
Regards, Tim G
Re: Setting Visit status to "Completed"
Hi Tim,
Thanks for the reference. Very interesting paper.
My problem with "In Progress" appears to remain, however. After Completing the old "In Progress" visits via the SQL update, I see that we are still generating uncompleted visits at the rate of 3 or 4 a day. We must be doing something wrong. By far the most typical occurrence seems to be when items are being invoiced outside the normal workflow.
Visit successfully "Completed" scenario as best I understand it: Client phones in regarding a patient under treatment. After discussion with the vet, the vet suggests a new medication. Vet uses "Add Visit & Note" to create new medical record entry, then creates a new invoice via Charges. This seems to successfully "Complete" the visit.
Visit NOT successfully "Completed" but left "In Progress": Client comes in to purchase 'prescription' dogfood (merchandise). Receptionist creates, completes and finalizes an Invoice via Charges. Creating the invoice automatically places a new visit and invoice item in the medical record. Medical record visit left with "In Progress" status.
Having the dogfood listed in the medical record is useful. But staff are balking at having to get back into the medical record and manually set the visit to "Completed". Are we doing something wrong?
I hate having to go in periodically and run the SQL update - not so much for the work, but because I am lothe to tamper directly with the production database. It would seem to me that creating an Invoice outside of the normal workflow would clean up after itself somehow. So is something not right with our setup or with our procedures? Using 1.7.
Thanks,
Sam
Re: Setting Visit status to "Completed"
Sam - I had a play with my 1.8 test system. The behaviour (which I am certain is the same as 1.7) is as follows:
If you use Customers|Charges|Invoice to invoice the sale of an item to a patient (be it medication or merchandise or service), then a visit is created showing this - see below. I assume that the logic is 'stuff sold to a patient must be in the medical records'.
You can avoid the medical records entry by using Customers|Charges|Counter Sale. here you cannot specify a patient (whereas for invoices you must).
Here is the medical record after my playing:
The 17/5/15 entries are me playing - the items were on 3 separate invoices but for the 2nd and 3rd the system re-used the visit.
The other entries are real. By chance I happened to pick a customer/patient who in fact has never had a consult - the ONLY activity in the medical records is the purchase of specialty foods. The above database was pulled from production last September, and when I checked in production this has continued - every 2-3 weeks the customer make the same purchase - the last sale was on 7 May.
So what to do?
If the vets do not mind not having patient sales recorded in the medical record, use Counter Sale rather than Invoice.
If patient sales should be recorded, then perhaps the system should be changed so that if a visit is created as a result of a patient sale, then the visit status should be set to Completed rather than In Progress. It would be nice to also set the visit reason to 'Sale' if possible - the logic would be 'is there a Visit Reason with code "CUSTOMER_SALE" - if so set visit reason to this'.
Regards, Tim G
Re: Setting Visit status to "Completed"
Wow... you must get up early in the morning!
OK... so we are using the system correctly. We would like to have sales in the record, especially as we don't sell pet toys and the like, so the system for the most part works well. And if we did sell something we're not interested in tracking in the record, then using a countersale makes great sense.
But I agree with your comments that perhaps a sales-generated visit should have its status tagged as Completed; reason, Sale.
Thanks again for your help. I guess this is a logic thing to hash out with users/developers. But it does look like In Progress visits are pretty widespread and perhaps should be addressed.
Sam
Re: Setting Visit status to "Completed"
Early to bed, early to ... and my brain works best before 6am.
I agree re needing others to comment on the "sales-generated visit should have its status tagged as Completed; reason, Sale" proposal.
Regards, Tim G
Re: Setting Visit status to "Completed"
Tim A - I got the following from our practice manager after pointing her at the above discussion: "HI Tim, Yes! This is precisely the same issue we have with all of our CC deliveries. We want the record in the medical notes, but it keeps creating uncompleted visits.
Great suggestions."
Hence can we move to the suggestion I made above - ie change the system so that if a visit is created as a result of a patient sale, then the visit status should be set to Completed rather than In Progress. It would be nice to also set the visit reason to 'Sale' if possible - the logic would be 'is there a Visit Reason with code "CUSTOMER_SALE" - if so set visit reason to this'.
Regards, Tim G
Re: Setting Visit status to "Completed"
There current behaviour is as a result of the fix for OVPMS-1382.
Setting the status to COMPLETED is not enough. I think it also has to sent the end date when the invoice is finalised, unless invoicing being done from the Check-Out workflow. Here, it should be delayed until the workflow is completed.
Re: Setting Visit status to "Completed"
Project created - see http://www.openvpms.org/project/reduce-incidence-no-reason-visits
Regards, Tim G
Re: Setting Visit status to "Completed"
Yes, I'll have to change the code in 1.8 to re-establish the desired order of visits as the order is now hard-coded in. I use that as my excuse to set up the IntelliJ environment. Having the order of the visits available as an option in OVPMS would be ideal but it sounds like that is a bit of a complex issue. But I think the changes I made to the code are already well documented here. Tim A. and Ben outlined the required changes for me, so I just implemented them on our system.
As for adapting OVPMS for use in a multi-tax jurisdiction, I did all that work within the templates only. No change to the code or database structure was required. Hopefully those templates will just transfer to 1.8 with little modification. I've already posted a suite of 1.7 templates and instructions in the Users Shared Resources.
Sam
Re: Setting Visit status to "Completed"
You can use the same templates in 1.8, however you'll need to update them if you want to use the new features that allow line items:
Re: Setting Visit status to "Completed"
Sam - the process I use when upgrading is:
Regards, Tim G