human readable in MySQL

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
cmandile
Posts: 2
Joined: Mon Feb 11, 2019 2:05 pm

human readable in MySQL

Post by cmandile » Mon Feb 11, 2019 2:08 pm

When I run a query within OD, appointment status, and insplan.PlanNum show as words not numbers. Toggling between human readable and raw changes this. I’m building queries in MySQL workbench… is there a way to know where these are coming from and/or display them as words in the MySQL code?

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: human readable in MySQL

Post by cmcgehee » Tue Feb 12, 2019 7:45 am

To show that in MySQL Workbench, you would either need to join onto the table that has the name of item or you would have to hand code a case statement to translate enum values into English names. For example with appointment status, https://www.opendental.com/OpenDentalDo ... ppointment contains a description of AptStatus and what each number corresponds to.
Chris McGehee
Open Dental Software
http://www.opendental.com

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: human readable in MySQL

Post by jsalmon » Tue Feb 12, 2019 9:10 am

And if you like to peruse source code you could check out FormQuery.MakeReadable() which contains our monster switch statement that takes raw query results and translates them to the Human Readable version that is displayed to the user via C#.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

cmandile
Posts: 2
Joined: Mon Feb 11, 2019 2:05 pm

Re: human readable in MySQL

Post by cmandile » Tue Feb 12, 2019 2:51 pm

Thanks guys, to clarify for appointment status and insplan.PlanNum there are no tables to join. For appointment status I'd just have to hand code the statuses from the appointment table, and for insplan.planNum I would have to maybe look it up within the software?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: human readable in MySQL

Post by jsalmon » Tue Feb 12, 2019 5:00 pm

cmandile wrote:Thanks guys, to clarify for appointment status and insplan.PlanNum there are no tables to join.
Status is an enum so no JOIN needed. However, PlanNum is the primary key of a table so odds are you need to JOIN to it from other tables. Basic rule of thumb will be any column that starts with "FK to [table].[Column]" means you have a JOIN in your future in order to get more information about said entity.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: human readable in MySQL

Post by cmcgehee » Wed Feb 13, 2019 7:47 am

I believe that in the program we display the carrier name in the insplan.PlanNum column. So you would use the insplan.CarrierNum to join to the carrier table to get the CarrierName.
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply