ProcNote Stored Historical Change

For complex topics that regular users would not be interested in. For power users and database administrators.

ProcNote Stored Historical Change

Postby awong » Fri Dec 09, 2016 12:40 pm

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!
awong
 
Posts: 2
Joined: Fri Dec 09, 2016 12:28 pm

Re: ProcNote Stored Historical Change

Postby Arna » Fri Dec 09, 2016 12:48 pm

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
arna@smarterdentist.com
http://www.SmarterDentist.com
User avatar
Arna
 
Posts: 428
Joined: Tue Jul 09, 2013 3:16 pm

Re: ProcNote Stored Historical Change

Postby JoeMontano » Fri Dec 09, 2016 12:49 pm

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
Engineer
Open Dental Software
Joe@OpenDental.com
JoeMontano
 
Posts: 34
Joined: Thu Aug 20, 2015 11:08 am

Re: ProcNote Stored Historical Change

Postby awong » Fri Dec 09, 2016 1: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?
awong
 
Posts: 2
Joined: Fri Dec 09, 2016 12:28 pm

Re: ProcNote Stored Historical Change

Postby JoeMontano » Fri Dec 09, 2016 2: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
Engineer
Open Dental Software
Joe@OpenDental.com
JoeMontano
 
Posts: 34
Joined: Thu Aug 20, 2015 11:08 am


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 1 guest