human readable in MySQL

For complex topics that regular users would not be interested in. For power users and database administrators.

human readable in MySQL

Postby cmandile » Mon Feb 11, 2019 3: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?
cmandile
 
Posts: 2
Joined: Mon Feb 11, 2019 3:05 pm

Re: human readable in MySQL

Postby cmcgehee » Tue Feb 12, 2019 8: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/OpenDentalDocumentation18-3.xml#appointment contains a description of AptStatus and what each number corresponds to.
Chris McGehee
Open Dental Software
http://www.opendental.com
User avatar
cmcgehee
 
Posts: 604
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: human readable in MySQL

Postby jsalmon » Tue Feb 12, 2019 10: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
User avatar
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: human readable in MySQL

Postby cmandile » Tue Feb 12, 2019 3: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?
cmandile
 
Posts: 2
Joined: Mon Feb 11, 2019 3:05 pm

Re: human readable in MySQL

Postby jsalmon » Tue Feb 12, 2019 6: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
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: human readable in MySQL

Postby cmcgehee » Wed Feb 13, 2019 8: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
User avatar
cmcgehee
 
Posts: 604
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 3 guests