Notifications
Clear all
Topic starter
I have been requested to clean up the Medication Custom List by our Clinical Services department but I am having a problem removing obsolete Custom Lists.
When I try to remove a Medication Custom List, I am getting a message that there is one or more user has the custom list set under Preferences.
My question - is there any way from the database to find out who has what Medication Custom List on their preferences?
Thanks,
Amar
Posted : October 23, 2015 8:32 am
You could write a CR using the PREF and USR tables that would give you the preference listing for each user. You will have to change each users' preference manually once you compile your list.
Posted : October 23, 2015 8:45 am
select df.ListName, p.PREFNAME
from PREF p, DoctorFacility df
where p.PREFNAME ='MedCustomList'
and p.DoctorFacilityId = df.DoctorFacilityId
Where "MedCustomList" can be replaced by whatever Med List Name you are looking for.
Posted : October 23, 2015 8:49 am
Topic starter
Gene, I ran the query you provided by replacing the "MedCustomList" to the MedListName that I was looking for and it did not yield any results.
Posted : October 26, 2015 3:32 am
I opened a ticket with GE back in 2012 because we could not delete a Problem Custom List. 3 years later...still an issue. We tried the query above, but it did not help. The issue on our side (as best I can recall) came about because of the transition from EMR to CPS, and the way that user accounts were migrated now that we are using Active Directory. The Problem Custom Lists that we cannot delete are tied to a phantom EMR user that is no longer in the system.
Our solution was to rename all of the old Custom Lists with the words "DON'T USE" at the beginning of the name.
Posted : October 26, 2015 8:09 am
Here is the Oracle version of what you want, though this returns all obsolete users and their Medication Custom List preference:
select u.firstname, u.lastname, u.status, p1.prefname,q.description from ml.pref p1, ml.usrinfo u, ml.qpicks q where p1.groupid = u.pvid and p1.prefvalue = q.qpid and p1.prefname = 'MedCustomList' and status = 'O' order by q.description --User Med Custom Lists
Posted : November 2, 2015 7:29 am
Topic starter
Thanks to David, I was able to identify the list of users who had a particular Medication Custom List set as their preference. Once, I knew that info, I was able to run an Update script to move all those to the Preferred Medication Custom List of my choice and thereafter go into CPS and remove what I did not need.
I modified David's query just a tiny bit but in any event, attached is script for anyone that might be interested. Medication Custom List. If there are any questions, feel free to let me know.
Big thanks to David!!!
Posted : November 3, 2015 7:36 am
