A former colleague, a brilliant guy I might add, had a quote printed out over a full sheet of paper at his desk: Never Assume.  Verify.

I ran across a great example of this yesterday.  A woman who had taken one of my Allscripts Database Architecture and Reporting classes emailed me asking about how to determine the user who created a Note in Allscripts Enterprise.  She thought it would be the Author, but the data seemed to contradict this.  I almost sent a response saying that she was correct, and the only chance of this differing would be in Scanned Images and Transcribed Notes coming in over an interface.

I decided to confirm what I was sure to be true.  As it turns out, I was wrong. It seems as though the Author of a Note is the Owner of the Note as of Note creation.  You can change the Owner afterwards and the Author remains the same.

So my answer to Judy is – the person who created the Note, isn’t necessarily the Author, but you can find it.  Note History is tracked and found in the DocumentEvent table.  Here’s a simple query that pulls Notes (v10 Notes, v11 Notes and Unstructured Notes), as well as the Owner, Author and Creator:

FROM Document d (NOLOCK)
INNER JOIN DocumentEvent de (NOLOCK) ON d.DocumentID = de.DocumentID
INNER JOIN Document_Event_DE ded (NOLOCK) ON de.DocumentEventDE = ded.ID
INNER JOIN PhysicalManifestation_DE pmd (NOLOCK) ON d.PhysicalManifestationDE = pmd.ID
WHERE ded.EntryName = ‘Created’
— AND d.AuthorID <> de.UserID — Find entries where the Author is not the Creator
AND pmd.EntryName IN (‘HTML’, ‘RTF’, ‘NOTEFORM’) — Only look at textual notes
AND isnull(d.EIEID, 0) = 0
AND d.NextVersionID = 0

Please run this with care, and only in non-production environments!

Another lesson learned.  No matter how confident you are that you know something:

Never Assume.  Verify.

Facebook Twitter Email

+ There are no comments

Add yours

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