Creating and Editing Patient List Queries

You can create or edit queries from the List query templates window. This window can be accessed when creating patient lists (contact lists), see below or via Configure - Patient Query Templates. You can click +1 to create a new query or you can highlight an existing query and click the e|d button to edit it.

Queries work on a logical basis, It will always search for exactly what you have asked it to so it’s important these queries are set up correctly.

A basic query has been created below, it is searching for patients on a practice plan (Active PPLAN Patients) which can be used to message the Practice Plan patients or simply find out how many the practice have. 

1. When you first create a query using the +1 button it will present you with a blank screen.

2. Enter a title that describes the patient list well so that all users understand what this list will create if they were to run it.

3. Start building the list by adding conditions, to begin click the add condition button in the bottom right.

4. Choose your condition (Payor has been used in this example) then click OK.

5. Next put in the entry you want to search for (PPLAN has been added in this example).

Prompt for entries checkbox:

If this box is checked, every time this query template is used a window will open to prompt the query template user to specify data such as a date or date range.

You now have a query with one condition but queries are very rarely built using one condition, it’s usually multiple conditions brought together. In this example another condition has been added to exclude inactive patients (A condition you will use often) to do this:

1. Click the same button as before, select “A check in inactive” and click OK.

2. Change Patients with to Patients without in the drop down menu and Click OK.

3. The query has now been created, click OK once more and its ready to use.

If you make a mistake in entering a patient selection condition you can:

• Remove the condition by clicking the Remove button

• Edit the condition by clicking the Edit button

After you have finished and exited the query template creation, open the List Query Templates window to check that your Query has been added to the list.

Using Date Ranges

The example before was a basic query and we will start looking at more complicated options. When using a date range options you can either specify a single date or you can make the query futureproof but putting in a value such as Start of the Month or 2 Years ago, this is useful for date of births or appointment searches.

When you are presented with a date you can either type in the date, choose it from the calendar or choose a selection from the drop down list. This includes options such as Today, Yesterday, 1 week ago, Start of the year and if added in will always look for that time regardless of when you run it so today will always bring up the current date.

You can take this a step further by typing in your own versions of these. For example D/M/Y-2 means two years ago or 1/M-2/Y always looks at 1^st of two months ago (so if ran today at time of this blog it would be 01/06/2022). For searching for under 18s we would do

Date from: D/M/Y-18

Date to: D/M/Y

Finally check the video out below which uses one of our most commonly asked for lists. A lapsed patients list where we will search for patients who have been into the practice but have not revisited the practice in the last 2 years.

Creating Lapsed List Example

And/Or functions are used to refine a search further. For example you may want to find patients that are registered with two of your dentists, however if you simply add the conditions you will find the result is 0 patients. This is because it's not possible for a patient to be registered with both Dentist A AND Dentist B. This is where the OR function allows us to request for patient that are registered to Dentist A OR Dentist B.

You can see in the bottom left the AND/OR switch where we can determine what kind of search we are doing.

Sometimes using one or the other is not enough and we need to use a mixture of both functions. This is where brackets come into play. For example, we cannot search for patients who have had an extraction by either Dentist A or Dentist B without the assistance of brackets. The query we need to create is 'Patient with a treatment code of EXTRACT AND (With provider DENTIST A OR DENTIST B). See the video below demonstrating how to create this query.

And/Or Advanced query demonstration

To import Queries

Template records may be imported into or exported from EXACT via Comma-Separated Variable (*.csv) files.

1. Open the List Query Template screen as described above.

2. Click the Import button, browse for the appropriate file and select it:

3. Select the Open button to import it.

   The templates in the file will be added to the list of templates available in EXACT.

To export Queries

1. Highlight the file to export in the List Query Templates window.

2. Click the Export button.

3. When prompted, select a location to save the Query and specify a filename for the Query.

   The File name defaults to QUERY.CSV but can be overwritten with another name.

4. Click the Save button.