ProcNote Stored Historical Change

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
awong
Posts: 2
Joined: Fri Dec 09, 2016 11:28 am

ProcNote Stored Historical Change

Post by awong » Fri Dec 09, 2016 11:40 am

Hi,

I noticed that ProcNote table will saved the changes of the procedure note, and when I try to pull out the raw data, it appear multiple times, I am guessing it will saved the change / delete note. Is there any way we can identified which note is the active one?

Thank you!

User avatar
Arna
Posts: 444
Joined: Tue Jul 09, 2013 3:16 pm

Re: ProcNote Stored Historical Change

Post by Arna » Fri Dec 09, 2016 11:48 am

The active note is whichever is showing in the chart module when not in Audit mode. It's typically the note with the latest date stamp.
Entropy isn't what it used to be...

Arna Meyer

JoeMontano
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: ProcNote Stored Historical Change

Post by JoeMontano » Fri Dec 09, 2016 11:49 am

Are you looking for a query?

SELECT *
FROM procnote pn
WHERE pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime) FROM procnote pn2 WHERE pn.ProcNum=pn2.ProcNum)
GROUP BY pn.ProcNum
ORDER BY pn.PatNum

I wouldn't recommend running this without additional WHERE clauses if you have a large procNote table. But this will return the most recent notes due the the subquery logic.
Joe Montaño
Software Engineer
Open Dental Software
Joe@OpenDental.com

awong
Posts: 2
Joined: Fri Dec 09, 2016 11:28 am

Re: ProcNote Stored Historical Change

Post by awong » Fri Dec 09, 2016 12:32 pm

Thank you for your help! I do looking for a query that can shown the active note in the chart module. Just discovered that MAX(EntryDateTime) is not accurate if there are more than one note in the same procedure. Is there a field that should help identify whether it is active or not?

JoeMontano
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: ProcNote Stored Historical Change

Post by JoeMontano » Fri Dec 09, 2016 1:20 pm

If you are looking for a query I'd recommend searching our query page:
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx

If you can not find something that works you can request your own query here:
http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx

If you go with the request route we will contact you to clarify everything about your request and quote you soon there after.

I am curious as to how MAX(EntryDateTime) does not work for you in terms of finding the 'active' procNote. The WHERE clause in the pn2 sub query requires we look at proc notes associated to the pn1 outer queries procedure and by pn.EntryDateTime = ... MAX(pn2.EntryDateTime) we are only looking at active procNotes.
Joe Montaño
Software Engineer
Open Dental Software
Joe@OpenDental.com

Post Reply