Setup & Manage Queries in D4W

Setup & Manage Queries in D4W

Listed below are instructions for simple and extended query filters, setup manual query templates, how to create a user-defined query and to set duty of care to overwrite unsubscribe feature. 

Common Simple Query Filters

Simple Queries allow for less complex filters to be used compared to Extended Queries. The following filters can be used when creating a Simple Query either via the Simple Query Wizard, or when creating a User-Defined Query.

Simple Queries use the following Operators:

Equals: Returns all records where the the selected column equals the text entered
Greater Than: Returns all records where the selected column are higher than the text entered
Less Than: Returns all records where the selected column are less than the text entered
> Than or Equal: Returns all records where the selected column are higher than and including the text entered
< Than or Equal: Returns all records where the selected column are less than and including the text entered
Doesn't Equal: Returns all records where the selected column doesn't equal the text entered

Simple Queries also use the following Logical arguments:
And: Returns all records where all arguments are met
Or: Returns all records where either arguments are met

Last Visit

A patients' Last Visit is the last date treatment was entered in the Treatment tab. Non-Reporting (link coming soon) and Non-Treatment (link coming soon) items are excluded.
This example will retrieve all patients who last visited between 01/04/2023 and 30/04/2023.

Last Exam, Scale and Clean

Last Exam S+c: The last time one of the S/C items were entered in the Treatment tab. 

See Setup Manual Recalls section for setting up scale and clean items for Manual Recalls

See Set Scale and Clean Items for Automated Recalls (link coming soon) section

 

This example will retrieve all patients who last had a scale and clean between 01/01/2024 and 01/01/2025. This could be a different date to the Last Visit Date.

Patients with E-mail Address

The Value NULL is equivalent to a blank field. Therefore writing NULL means there is nothing entered in the E-mail field.

This example will retrieve all patients who DO NOT have an empty e-mail address, therefore have an e-mail address.

The same logic can be used when retrieving all patients with a mobile phone number.

Females Over the Age of 18

The Gender and Age of a patient can be found in Patient Records > Patients tab. 


This example will retrieve all female patients who are 18 years of age or older.

Interested in Whitening

InfoQuestions like this can be added using the Custom Patient Fields feature, see Create Custom Fields for Patient Details section. 

In this example, the Interested in improving smile? field is a check box.


If the Interested in improving smile? check box is not ticked, it is considered NULL. Therefore this example will retrieve all patients where this check box is ticked.

Patients with a Specific Insurance Fund who have Last Visit

Enter the Insurance Fund Code (in parenthesis), not the full name of the Insurance Fund.


This example will retrieve all patients who have the specified health fund (Bupa) and last visited between 01/01/2024 and 31/12/2024

 

This example will retrieve all patients who have either one of the specified health funds (Bupa or HCF) and last visited between 01/01/2024 and 31/12/2024

Common Extended Query Filters

Extended Queries allow for more complex filters to be used compared to Simple Queries. The following filters can be copied and pasted to Extended Queries where relevant. 

Birthdays
Patients with a DOB this Month:
      MONTH (dob) = month (today())
Patients with a DOB Today: 
      MONTH (dob) = month (today()) AND DAY (dob) = day (today())
Patients with a DOB Yesterday:
      datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-1
Patients with a DOB the Day Before Yesterday: 
      datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-2
Patients with DOB within the next Week (including today):
      datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 6
      AND
      datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >=0    
Patients with DOB within the Next Calendar Week from Thursday (i.e.: Mon-Sun):
      datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 11
      AND
      datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 4 
      Note: This would vary depending on the day of the week. For example from Tuesday:
      datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 13
      AND
      datediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 6
Patients whose DOB is in a particular month:
       month(dob) = 5
      (month number) This will return patients with a birthday in May. Note: Replace “5” with any month required. 
Patients who have a birthday in March and have visited the practice since 01/01/2019: 
      month(dob) = 3
      AND
      last_visit >= Date('2019-01-01')Note: Replace “3” with any month required and insert start date as required.
Patients whose DOB is between 20/08 and 27/08: 
      month(dob) = 8 and day(dob) >=20
      AND
      day(dob) <= 27

Reactivation
Patients who last visited this Month 24 months (2 years) ago:
      last_visit >= DATEADD (month, -24 , Now())
      AND
      last_visit <= DATEADD (month, -23 , Now())
Patients who last visited this Month 12 months (1 year) ago:
      last_visit >= DATEADD (month, -12 , Now())
      AND
      last_visit <= DATEADD (month, -11 , Now())
Patients aged 18-24 who last visited This Month 24 months (2 years) ago:
      last_visit >= DATEADD (month, -24 , Now())
      AND
      last_visit <= DATEADD (month, -23 , Now())
      AND
      Age >= 18
      AND
      Age <= 24
Patients who last visited this Month 24 months (2 years) ago with a health fund:
       last_visit >= DATEADD (month, -24 , Now())
      AND
      last_visit <= DATEADD (month, -23 , Now())
      AND
      insurance_level = 'BUPA' Note: ‘BUPA’ can be replaced with any health fund. This relates to the insurance plan code.    
Patients who had item #011 performed this Month 24 months (2 years) ago: 
      treatment_item_name = '011'
      AND
      date_treatment_item >= DATEADD (month, -24 , Now())
      AND
      date_treatment_item <= DATEADD (month, -23 , Now())
Patients who had item #011 OR item #013 performed this Month 24 months (2 years) ago:
      date_treatment_item >= DATEADD (month, -24 , Now())
      AND
      date_treatment_item <= DATEADD (month, -23 , Now())
      AND
      treatment_item_name = '011' OR treatment_item_name = '013'
      Note: Any item number can be used instead of 011 and 013. Note the item numbers with the OR clause is listed last. This will return any patient that   had either one of these items performed within the date range specified.
Patients who had treatment performed on a Monday:
      DayName(date_treatment_item) = 'Monday' Note: Replace with any day as required.
Patients who had item #011 performed on a Monday:
      DayName(date_treatment_item) = 'Monday'ANDtreatment_item_name = '011'
Patients who have not been seen since 19 months ago:
      last_visit > DATEADD (month, -19 , Now())
      and
     last_visit < DATEADD (month, -18 , Now()) 

Set Manual Query Templates

Each Query can be assigned its own SMS, e-mail and/or letter template within D4W/PSS. 
  1. Go to Debtors and Marketing > Mail Merge tab 
  2. Mail Merge Category: Queries
  3. Select the relevant query to assign/set up a template for   
                                           
  4. Letter Template: Click the Edit icon to design the e-mail and/or letter template
    1. Design the template as required for this query
  5. SMS Template: Click the Edit icon to design the SMS template
    1. Use the Insert menu to add Input Fields
    2. Click Template menu > Click Update
    3. Click Template menu > Click Exit        
       
InfoSee Design a HTML E-mail Template in MSWord section for more information on how to design the query template

Create a User-Defined Query Template

User-Defined Queries allow for multiple templates and filters to be created specifically for a topic, e.g. Reactivations. This is in place of working off the one template for Simple or Extended Queries, which require the user to overwrite previous templates in Mail Merge. 

  1. Go to Debtors and Marketing > Queries tab
  2. Queries menu > Create User-Defined Query
    1. Name: Enter the name of the query, e.g. Holiday Greeting
    2. Select the filter to be used

      1. Change using Simple Wizard                                                             
        1. Enter the New Filter Criteria (see Common Simple Query Filters above for examples)
        2. Click OK

      2. Change using Extended Wizard
        1. Enter the Filter Expression (see Common Extended Query Filters above for examples)
        2. Click Verify
        3. Click OK

      3. Click Exit

  3. Next, setup the Letter/E-mail/SMS Template(s) for this query (see above section)

Create an End of Year Health Fund Campaign Query Template

Towards the end of each year, an SMS, E-mail or Letter can be sent to patients with Private Health Insurance who have not had Scale and Clean items this calendar year. The following instructions are for those not using D4W Marketing only. 

Patients will only receive communication based on their Send To preferences, if sending via e-mail and they do not have this selected, they will not receive it. It is advised to use multiple communication channels where possible.

As EOY Health Fund Reminders can be sent every year, its best to create a User-Defined Query which will save the settings for the next time its to be used.

InfoTo update an existing User-Defined Query filter, click here (link coming soon). 
  1. Go to Debtors and Marketing > Queries tab
  2. Queries menu > Create User-Defined Query
    1. Name: Enter the name of the query, e.g. EOY Health Fund Reminder
    2. Click Change Using Simple Query Wizard

      1. Column: Insurance Level
      2. Operator: Doesn't Equal
      3. Value: Leave blank
      4. Logical: And
      5. Column: Last Exam S+C
      6. Operator: Less Than
      7. Value: Enter the date, e.g the start of this year
      8. Tick Treat NULL and Blank equivalently (recommended)
      9. Click OK
    3. Click Exit
  3. Next, create the template(s) to be used for this batch (see above for instructions)
  4. Then, generate the batch and send 
See Generate a Batch using a User-defined Query section (link coming soon)

Set Duty of Care to Overwrite Unsubscribe Feature

When a patient unsubscribes from a query (for example; a reactivation query) they will not be sent any further correspondence from Queries by that method. Sometimes a particular message must be received by all patients, and therefore the unsubscribe can be overwritten. 
  1. Go to Debtors & Marketing > Queries tab
  2. Create the query as required
  3. Print/Send
    1. Duty of Care: Tick to overwrite the patient's unsubscribe status

    • Related Articles

    • Setup & Manage Manual Queries in D4Web

      Setup, Design Query Templates for SMS, E-mail and/or Letters & Manage Queries Filters . Mail Merge Setup page Click the navigation menu > Debtors & Marketing > Mail Merge Setup page Setup the Manual Queries Template Mail Merge Category: Select ...

    • Setup & Manage Automated Queries in D4W

      Listed below are instructions on how to setup automated birthday messages and how to temporarily stop or resume sending queries via Automation. Set Up Automated Birthday Messages via Queries The below instructions are on how to set up automated ...

    • Queries Automation in Dental4Web

      Use Queries Automation in Dental4Web to create batches and automatically send any type of user defined communication, such as birthday messages, promotions and / or seasonal event notifications to name a few. Find out more about Query batches here ...

    • Setup & Manage Manual Recalls in D4Web

      Setup, Design & Manage Recall Templates for SMS, E-mail and/or Letters. Mail Merge Setup page Click the navigation menu > Debtors & Marketing > Mail Merge Setup page Setup the Manual Recalls Template Mail Merge Category: Select Recalls from the drop ...

    • Setup & Manage Patients Mail Merge in Dental4Windows

      Setup patient registration SMS template and add unsubscribe option to templates. Setup Patient Registration SMS Template With the Automated Patient Upon Registration SMS activated (link coming soon), setup the SMS template. Go to Debts/Marketing > ...