Open Dental Forum

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!

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.

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.

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?

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.