Color-coded appointments

[mikebarrdds]

Another newbie question...

I figured out how to color code the procedure codes in appointments. Just the text changes color. The only thing is that they are hard to see. The text is pretty small on the appointment screen, and unless the text is black, it's hard to see.

I wondered if there's a way to set the background color of the appointment according to the type of procedure being done. So, for example, for all C&B procedures, the background could be green. For restorative / operative, the background could be blue. I'd like the text to remain in black.... just change the background color.

Is there a way to do that? I couldn't figure it out, if there is.

Thanks!

[boboffice]

Hi Mike! Great to see you here.

The background color of appointments is provider specific as set up in definitions and the schedule setup screen. Use colors that look nice with the text colors you provide. It does not change based on the procedure scheduled.

[mikebarrdds]

Hi Mike! Great to see you here.

The background color of appointments is provider specific as set up in definitions and the schedule setup screen. Use colors that look nice with the text colors you provide. It does not change based on the procedure scheduled.

Hi Bob! Thanks! I'm really liking Open Dental. Fantastic program and amazing value.

Yeah.... I'm aware of that option. But, I don't really have a use for color-coding the "providers," since it's just me and my hygienist. I can figure out which room is which. That feature seems rather useless to me.

But, it would be cool to glance at a schedule and see how many "blue" and how many "green" appointments we have that day, for example.

I guess it's not possible, eh?

[Justin Shafer]

Nothing is impossible for these guys, seems like an easy request.

Change a couple of dialog windows, code, add a column in the database for procedure color in the procedure table?

Done.

All depends on what they are currently working on.

[Justin Shafer]

http://www.opendental.com/manual/version14_3.html


What they seem to be working on...

That is a big deal:
Bridge to Apteryx XVWeb.

[KevinRossen]

What I have setup for my office is a small script that changes the appointment color to red when a patient is 5 minutes late. The script runs every 5 mins during our office hours. I can share it when I'm back in the office.

The reason I say this is you could do something similar based on procedures attached to an appointment. I don't think it would need to run every 5 minites. Maybe every 4 hours or so would aacomplish this.

Actually, my script uses code that the mods frown upon, so I might just share the code with the dreaded "UPDATE" portion removed.

[gthiele]

Kevin,

Would it be difficult to have appointment color based on productivity measured in $/hr? That would help us immensely in our block booking.

[KevinRossen]

Kevin,

Would it be difficult to have appointment color based on productivity measured in $/hr? That would help us immensely in our block booking.

It wouldn't be real time, so not sure how much wit would help the front desk at the time they are making the appointment, but, yes, you could run a script in the background that would change the appointment color based on production per hour. I haven't done this, but I don't think it would be hard.

Before I post any scripts, let me just remind everyone that you should always have current, tested backups of your database whether or not you're going to be interacting directly with the database. I always test my scripts on backups first, not my live data.

[KevinRossen]

Ok, here is the query I have running every 5 minutes on our server to change the appointment color to red if the patient is 5 minutes late. It also removes the red if they've arrived/cancelled. I starred out the part of the code that actually updates the appointment table. Here's a line by line summary:

This first and fourth lines are comments. Anytime you see code surround with these: /* */ that's a comment. I use them so I can remember what the code is doing.
Lines two and five (*** SET Color ...) do the actual updating of the appointment color (make it red, make it normal respectively). I've starred out the actual code to prevent accidental manipulation of the database. You have to be really, really careful when interacting directly with your database.
Lines three and six (WHERE DATE ...) are the conditional clauses. All it is really looking for are appointments from the current date that either have or don't have a specific confirmation status and the time is 5 minutes passed the appointment start time.

THIS CODE WILL NOT WORK AS IT IS PASTED BELOW. PM me for details.

Code: Select all

/* Change ColorOverride When 5+ Mins Late */
***************** SET ColorOverride='-65536'
WHERE DATE(AptDateTime)=CURDATE() AND AptStatus=1 AND Confirmed NOT IN (24,240,242,244) AND TIMESTAMPDIFF(MINUTE,AptDateTime,NOW())>=5;
/* Remove ColorOverride on Future/Unsched Appts */
***************** SET ColorOverride='0'
WHERE (DATE(AptDateTime)=CURDATE() AND AptStatus=1 AND Confirmed IN (19,24,240,242,244)) OR AptStatus IN (3,5);

To make this run on a schedule, you need to save the code as a text file (I use .sql for the extension instead of .txt) and setup a batch file on your server. That's technical-speak for a text file that gives Windows specific commands (like you would run from the C:> prompt). Here's what my batch file looks like (It's just one line):

Code: Select all

"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysql" -u "root" "opendental" < "O:\Reports\LateApptsColor.sql"

The last step to make this all happen automatically is to setup a scheduled task in Windows. It's pretty easy once you've done it a few times. Here's a screenshot of what it looks like on our server:


I'll work on the colors based on procedures in appointment and production per hour when I have some time this week.

[drtech]

ingenious

[KevinRossen]

I wondered if there's a way to set the background color of the appointment according to the type of procedure being done. So, for example, for all C&B procedures, the background could be green. For restorative / operative, the background could be blue. I'd like the text to remain in black.... just change the background color.

Alright, here's a query that will change the background color for appointments. If the appointment has crowns/bridges/implants it will be green. Fillings/OS will be blue. I'm not certain if all default installations of Open Dental use the same definition numbers for these categories, so it might not work for all.

Once again, don't use this query on your live database before you try it on a backup. Also, I've starred out the part of the code that actually updates the appointment color change to prevent it from working as pasted. If you need help making it work send me a private message. Here's the query:

Code: Select all

/* Change ColorOverride Based on ProcCat */
************************
LEFT JOIN procedurelog pl ON a.AptNum=pl.AptNum
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
	SET ColorOverride=CASE
		WHEN pc.ProcCat IN (81,80) THEN '-16744384' /* Crowns & Bridges or Implants */
		WHEN pc.ProcCat IN (75,82) THEN '-16760704' /* Fillings or Oral Surgery */
		ELSE '0'
	END 
WHERE DATE(a.AptDateTime)>=CURDATE() AND a.AptStatus IN (1,4);

Questions?

[KevinRossen]

Here's a list of colors I tested that look decent in the appointment book:
Red -65536
Green -16744384
Blue -16760704
Yellow -256
Orange -32768
Light Blue -16711681
Purple -8355648

If you want to remove all the ColorOverrides, here's a query that will do that (again, starred out the bit of code that actually does the update).

Code: Select all

*************
SET ColorOverride='0'
WHERE ColorOverride<>'0';

[josiedds]

MARK