Last week I worked on one of my projects for Allscripts Vision accounting to add a few enhancements.
The major enhancement was the ability to export a detailed report for all users to Excel and create a Pivot table with chart of users’ logins over the report’s timeline.
You can read more on my project page for this project.
Allscripts ProEHR 9.10.3 database table and column extended properties text: ProEHR 9.10.3 – Table and Column Extended Properties
Been looking everywhere but cannot seem to find this. Perhaps you can help. What table is a Patients History stored in? Specifically, I am looking for Patients who are smokers. I have managed to find the following tables: HPSITE.HISTORYCATEGORY and HPSITE.HISTORYKNOWLEDGE, but cannot for the life of me figure out how these map to a patient. Any help would be appreciated.
Hi Charles,
You want HPSITE.HXDIAGNOSIS table which provides CATEGORY_ID column and a value of 25 (Allergy = 21, Immunization = 22, Family = 23, PastMedical = 24, Social = 25, Travel = 26, Pregnancy = 28)
In HPSITE.HXDIAGNOSIS you will find PATIENT_ID column to reference against HPSITE.DEMOGRAPHICS table on column DEM_EXTERNALID.
Hope this helps.
Edit: I re-read what was being asked and this didn’t really answer that, sorry. I will take a look at the tables I have again. In the meantime, I have an Excel sheet I made that contains all the Table and Column extended properties (descriptions of what tables/columns are for). Luckily Allscripts documented this. =) I will edit this post and the link will be at the bottom.
I am so very thankful for your help. I look forward to the Excel spreadsheet.
https://techish.net/wp-content/uploads/2012/05/ProEHR-9.10.3-Table-and-Column-Extended-Properties.xlsx
It seems that if I get PATIENT_NUMBER from PM.PATIENTS
I can get the IMREDEM_CODE from HPSITE.DEMOGRAPHICS where DEM_EXTERNALID=PATIENT_NUMBER
then get HX_DIAGNOSISID from HPSITE.HX_DIAGNOSIS where PATIENT_ID=IMREDEM_CODE
then I’ve got HPSITE.HXDIAGNOSIS_ATTRIBUTES where HXDIAGNOSIS_ID=HX_DIAGNOSISID
I think I have it. Could NOT have done it without your spreadsheet. I am very grateful for your help. Thank you so much Rich.
Charles
You Don’t happen to have one of those for the AllScripts PM database do you 🙂
Hi Charles,
I did a little more work on trying to get a combined, easily searched, file for the database info and came up with the following after a little tinkering:
https://techish.net/allscripts-pehr-dbinfo/
(That is for ProEHR)
I also built a compiled html file (Windows Help File) that is portable and ~6MB in size if interested. I have needed to do this for some time now since I’m constantly trying to ‘reverse engineer’ to solve problems for our clients.
Wow, that is cool
I don’t have Allscripts PM unfortunately – I can give you the TSQL to run a query and you can save it to a CSV and import to Excel and tidy up if you want.
SELECT u.name + '.' + t.name AS [table], td.value AS [table_desc], c.name AS [column], cd.value AS [column_desc] FROM sysobjects t INNER JOIN sysusers u ON u.uid = t.uid LEFT OUTER JOIN sys.extended_properties td ON td.major_id = t.id AND td.minor_id = 0 AND td.name = 'MS_Description' INNER JOIN syscolumns c ON c.id = t.id LEFT OUTER JOIN sys.extended_properties cd ON cd.major_id = c.id AND cd.minor_id = c.colid AND cd.name = 'MS_Description' WHERE t.type = 'u' ORDER BY [table], [column] ASCIt seems that does the trick for dbo tables but not for the rest, is there something that I could be doing wrong when I run it? I am just selecting the database and then new Query and executing it.
Once again I cannot even begin to thank you for the help. I am stuck on two remaining issues for my task. I am able to get the data out for smoking (thanks to your advice and help), however if I try and insert a new record (I am a newbie at Allscripts and MSSQL ) indicating that a patient smokes. The first problem is that It appears that the primary key for the HX_DIAGNOSIS table is pulled from some HXDIAGNOSIS_SEQ table (which seems to use the sequence table and some magic) and I cannot for the life of me figure out how to insert the next value (I can of course get the max value and increment it, but this causes the desktop client app to complain about a duplicate primary key when any other item is added via the GUI) obviously they know how to get the value into the table and I do not. If I do insert a new record (and break the client just for fun 🙂 into the HX_DIAGNOSIS table, it does not show up in the client application. In the meantime, I will run your query which will be of great use as a database diagram in SQLServer is too large to view at a size that is both readable and comprehensible 🙂
Any idea on the sequence thing or the diagnosis issue?
I will take a look at this when I’m back at the office Monday to see how it’s being entered. I believe it is a stored procedure (most of Allscripts’ routines involve their stored procedures).
thank you thank you thank you