Allscripts Project

This content 8 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

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

Comments

  1. Charles Dasher says

    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.

  2. 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.

  3. Charles Dasher says

    I am so very thankful for your help. I look forward to the Excel spreadsheet.

  4. Charles Dasher says

    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

  5. Charles Dasher says

    You Don’t happen to have one of those for the AllScripts PM database do you 🙂

  6. 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] ASC
    • Charles Dasher says

      It 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.

  7. Charles Dasher says

    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 🙂

  8. Charles Dasher says

    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).

  9. Charles Dasher says

    thank you thank you thank you

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.