Queries give you an unprecedented level of power and control over your data. Because of Open Dental’s MySQL database, you have access to all of your data, and can create custom queries to generate any report in any format and export any data you want. Your data is no longer locked inside a proprietary black box. If you can imagine it, Open Dental can do it.
Open Dental has a few built in queries to list tables and over 800 Query Examples that can be copied and pasted for use. You can also write your own queries, or we can write custom queries for you. See Custom Queries below.
How Queries Work
Open Dental uses an SQL (Structured Query Language) database. The simplest query statement looks like this: SELECT * FROM fee; In English, this statement means "show all columns from the table called fee". The result looks similar to a spreadsheet document. The query returns only the information you specify (select) in your query statement.
In Open Dental, most tables are named in the singular rather than plural because they describe the data a single row holds. So the 'fee' table is not named 'fees' because 'fee' is a more accurate description of the information contained in one row.
Using SQL for querying data allows the user to perform many built-in functions, including:
- Specify the columns to display and the order and names of columns.
- Filter out rows that do not fit your criteria.
- Order the rows by whichever column you wish
- Add columns that are formed using mathematical formulas that operate on other columns.
- Get aggregated results from multiple tables at once.
Experienced users: For useful information about writing queries, see Using SQL to Write Reports and Programming Resources, Database Documentation.
For a fee, we can write custom queries for you.
- Cost: The fee starts at $90 and goes up depending on complexity. A typical custom query costs a few hundred dollars.
- Process: First check existing Reports and Query Examples to see if a solution already exists. If not, contact Open Dental support to make a request.
The support technical will work with you to generate a formal query request. You will need to provide the following information:
- Practice Name
- Individual making the request
- Contact phone number
- Why you want the query (e.g. how you plan to use the query)
- Detailed description of query (e.g. functionality)
- Fields or Column names to display
- Special conditions to meet (e.g. dates)
The request will be evaluated by a programmer. If needed, you will be contacted for clarification.
3. The support technician will get back to you and if the query is approved, you will receive a formal quote.
If you approve the quote, the query will be sent to programming to be written.
5. Once complete, the support technical will contact you to arrange delivery and collect payment.
6. The query will be published to Query Examples for use by others.