Color-coded appointments

For users or potential users.
Post Reply
mikebarrdds
Posts: 28
Joined: Tue Jun 24, 2014 9:15 pm

Color-coded appointments

Post by mikebarrdds » Sat Jul 26, 2014 7:20 am

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
Posts: 89
Joined: Sun Mar 29, 2009 7:11 am
Location: Poway, San Diego County, CA

Re: Color-coded appointments

Post by boboffice » Sat Jul 26, 2014 1:45 pm

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.
Robert Marcus DMD
Univ. of CT '93
Poway, CA

mikebarrdds
Posts: 28
Joined: Tue Jun 24, 2014 9:15 pm

Re: Color-coded appointments

Post by mikebarrdds » Sat Jul 26, 2014 2:16 pm

boboffice wrote: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?

User avatar
Justin Shafer
Posts: 596
Joined: Sat Jul 28, 2007 7:34 pm
Location: Fort Worth, TX.

Re: Color-coded appointments

Post by Justin Shafer » Sat Jul 26, 2014 3:53 pm

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.

User avatar
Justin Shafer
Posts: 596
Joined: Sat Jul 28, 2007 7:34 pm
Location: Fort Worth, TX.

Re: Color-coded appointments

Post by Justin Shafer » Sat Jul 26, 2014 4:00 pm

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
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Color-coded appointments

Post by KevinRossen » Sun Jul 27, 2014 2:48 pm

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.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

gthiele
Posts: 27
Joined: Tue Jan 24, 2012 9:57 pm
Location: Turlock, CA
Contact:

Re: Color-coded appointments

Post by gthiele » Sun Jul 27, 2014 4:10 pm

Kevin,

Would it be difficult to have appointment color based on productivity measured in $/hr? That would help us immensely in our block booking.
Gary L Thiele, DDS
http://symphonyofsmiles.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Color-coded appointments

Post by KevinRossen » Sun Jul 27, 2014 8:44 pm

gthiele wrote: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.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Color-coded appointments

Post by KevinRossen » Mon Jul 28, 2014 6:52 am

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:
Image

I'll work on the colors based on procedures in appointment and production per hour when I have some time this week.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
drtech
Posts: 1647
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: Color-coded appointments

Post by drtech » Tue Jul 29, 2014 7:54 am

ingenious
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Color-coded appointments

Post by KevinRossen » Tue Jul 29, 2014 12:06 pm

mikebarrdds wrote: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?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Color-coded appointments

Post by KevinRossen » Tue Jul 29, 2014 12:09 pm

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';
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

josiedds
Posts: 41
Joined: Tue Sep 18, 2007 11:59 am
Location: Southern California
Contact:

Re: Color-coded appointments

Post by josiedds » Wed Apr 01, 2015 12:04 pm

MARK

Post Reply