6.9.1 "Invalid Tooth ID" error

[apollonia]

continuing from another thread:

Also, I am getting a fair number of Unhandled exception errors: Tooth ID not valid
"Continue" hangs the program (just the chart part)

this post should be reviewed by OD team before being sure of it's user scope

this problem plagued us for a couple of days, preventing us from opening the chart module for several thousand patients (but leaving others working just fine).

we poked around in the data tables and found some patients with garbage in the Surf column of procedurelog, and all of these had ProcDates prior to our conversion from Dentrix some months ago. so this is probably a conversion problem from Dentrix, but i can't verify that.

you can find any of these patients in your data tables by looking with this query (uncomment GROUP BY line to get list of patients):

Code: Select all

SELECT PatNum, ProcDate, Surf
FROM procedurelog p
WHERE Surf <''
#GROUP BY PatNum
ORDER BY PatNum

this correlated with the patient's that threw the error.

it seemed like OD was (in the latest chart rendering version) having trouble dealing with these garbage values. i tried to remove the garbage values with:

Code: Select all

UPDATE procedurelog p
SET Surf = ''
WHERE Surf <''

and while that removed the garbage, it still didn't fix the "Invalid Tooth ID" error, and we still couldn't open a lot of patient's charts.

looking through the patient records, it looked these patients all had some sort of procedure that required a tooth range, but didn't have a range in the ToothRange column. i suspect that was a glitch with the Dentrix conversion that may hit a number of OD users.

i was able to narrow the thing down by looking for patients with empty ToothRange values, where the procedure would normally require them.....

Code: Select all

SELECT ProcNum, PatNum, ProcDate, Surf, ToothNum, ToothRange, ProcStatus, CodeNum
FROM procedurelog p
WHERE ToothRange = ''
AND CodeNum IN
(SELECT CodeNum
FROM procedurecode p
WHERE TreatArea = 7)
ORDER BY PatNum

i was able to fix many of these cases with the following query:

Code: Select all

UPDATE procedurelog p
SET ToothRange = ToothNum
WHERE ToothRange = ''
AND CodeNum IN
(SELECT CodeNum
FROM procedurecode p
WHERE TreatArea = 7)

there was a similar group of patients who had procedures requiring an arch, but having no "U" or "L" values in the Surf column of procedurelog

we were able to find them with this query:

Code: Select all

SELECT ProcNum, PatNum, ProcDate, Surf, ToothNum, ToothRange, ProcStatus, CodeNum
FROM procedurelog p
WHERE Surf = ''
AND CodeNum IN
(SELECT CodeNum
FROM procedurecode p
WHERE TreatArea = 6)
GROUP BY PatNum
ORDER BY PatNum

but the problem is that i would have to go through each patient record individually to find out what arch the [usually denture] belonged to, so i compromised and set them all as "U"

this last query seemed to do the trick and hopefully we have banished the "Invalid Tooth ID" error from our OpenDental universe:

Code: Select all

UPDATE procedurelog p
SET Surf = 'U'
WHERE Surf = '' # edited to reflect js's better query
AND  CodeNum IN
(SELECT CodeNum
FROM procedurecode p
WHERE TreatArea = 6)

[jordansparks]

You are spinning your wheels here. We already fixed this bug days ago. You know how to watch the bug tracker, right?

Please don't ever ever ever run an UPDATE query on your live database. There can be unintended consequences. For example, I think your query was wrong and should have been more like this:

Code: Select all

UPDATE procedurelog p
SET Surf = 'U'
WHERE Surf = ''
AND CodeNum IN
(SELECT CodeNum
FROM procedurecode p
WHERE TreatArea = 6)

But I still wouldn't run it without a great deal of careful thought. And there might be issues that are just not documented anywhere.

[apollonia]

if you set up a procedure's treatment area as "arch", and the paint type as "crown", then the error shows up when the chart is opened (for patients with that procedure)

obviously, this is user issue, and not a flaw in OD, but still it used to not throw the error -- perhaps NOT throwing an error was a flaw .

[jordansparks]

I've put it down as a bug.

[jordansparks]

I couldn't duplicate it, but I still added more validation of tooth numbers. So chances are that it will fix the error you are seeing. If not, we may need to get a copy of your database along with some info about which patient is having the problem.

[apollonia]

just a follow up. we persisted in getting these errors with a limited number of patients, until we updated to version 7, and now all errors have vanished.

we never fixed the bad data, but somehow you did!

you guys (the OD development team) are the greatest.

[Justin Shafer]

Awesome!