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

1. Mail Merge Category: Select Queries from the drop list
   
2. Highlight the relevant Query Type to assign/set up a template for Letter or SMS
   
   

Design Queries Letter / E-mail Template

1. Click the Edit icon to design the e-mail and/or Letter Template
1. Place your curser in the position you wish to insert the input Field
   
2. Mail Merge tab > Click Add Field to insert Input Fields required from the list available (i.e. Location or Provider information, Patient demographics etc)
   
3. Click to select field name (Ctrl key to select multiple rows) 
   
1. Repeat steps until you have added all input fields. (You are able to come back and add more if/when necessary at a later stage)
   
4. Click OK
   
   
2. Back in the Template:
1. Write/edit the template as required
   
2. File tab
   
3. Save > Exit
   
   

Design Queries SMS Template

1. Click the Edit icon to design the SMS template
   
1. Mail Merge tab > Click Add Field to insert Input Fields required from the list available (i.e. Patient Info, Appointment Time/Dates etc)
2. Click to select field name (Ctrl key to select multiple rows) 
   
1. Repeat steps until you have added all input fields. (You are able to come back and add more if/when necessary at a later stage)
   
3. Click OK
   
   
2. Back in the Template:
1. Write/edit the template as required
   
2. File tab
   
3. Save > Exit
   

Setup & Manage User-Defined Queries 

1. Examples for a User-Defined templates may be Promotional, End of Year Health Fund Reminder, Holiday Greetings to name a few. 
   
2. A User defined query has a limit of 400 characters
3. For the purpose of this instruction we will use "End of Year Health Fund Reminder" Campaign
   
4. Patients will only receive communication based on their Send To preferences setup in Patient page, 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
   

To setup Patient Communication instructions click here.

1. From the Queries page:
   
1. Click the navigation menu > Debtors & Marketing > Queries page
   
2. Queries toolbar > Create User-Defined Query
   
3. Name: Enter the name of the query, e.g. EOY Health Fund Reminder
   
   
4. Click CHANGE USING SIMPLE WIZARD
   
1. Column: Insurance Level
   
2. Operator: Doesn't Equal
   
3. Value: NULL
   
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 (DD/MM/YYYY)
   
8. Click OK
   
5. Then click Exit
   
6. Next, go to the Mail Merge Setup page to setup the Letter/E-mail and/or SMS templates 
   
7. With the new User Defined Query highlighted, follow the same design instructions to setup Templates
   
2. Or From the Mail Merge Setup page:
   
1. Click the navigation menu > Debtors & Marketing > Mail Merge Setup page
   
2. Select the User Define Queries Set
   
3. Click Add TYPE
   
4. Add the new type name
   
5. Click OK
   
6. With the new User Defined Query highlighted, follow the same design instructions to setup Templates
   

Edit a User-Defined Query Filter

1. Click the navigation menu > Debtors and Marketing > Queries page
2. Type: Select the query from the list (e.g. EOY Health Fund Reminder)
3. Queries toolbar > Modify User-Defined Query
   
   
1. CHANGE USING SIMPLE WIZARD
   or
2. CHANGE USING EXTENDED WIZARD
1. Set the new filter (e.g. Change the year to current)
   
   
2. Click OK
   
3. Click EXIT
   

Understanding Filters

Simple Query Filters

Simple Queries also use the following Logical filters:

Non-Reporting and Non-Treatment items are excluded.

Set Scale and Clean Items for Manual Recalls

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

Females Over the Age of 18: The Gender and Age of a patient can be found in Patients > Patients page. 

Questions like this can be added using the Custom Patient Fields feature. 

Extended Query Filters

1. Patients with a DOB this Month: 
   
1. MONTH (dob) = month (today())
   
2. Patients with a DOB Today: 
   
1. MONTH (dob) = month (today()) AND DAY (dob) = day (today())
   
3. Patients with a DOB Yesterday: 
   
1. datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-1
   
4. Patients with a DOB the Day Before Yesterday: 
   
1. datediff (day, (today()), YMD(Year(today()), month(dob), day(dob))) =-2
   
5. Patients with DOB within the next Week (including today): 
   
1. datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 6ANDdatediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >=0
   
6. Patients with DOB within the Next Calendar Week from Thursday (ie: Mon-Sun): 
   
1. datediff (day, (today()), Ymd(Year(today()), month(dob), day(dob))) <= 11ANDdatediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 4 
   
2. 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))) <= 13ANDdatediff(day, (today()), Ymd(Year(today()), month(dob), day(dob))) >= 6
   
7. Patients whose DOB is in a particular month: 
   
1. month(dob) = 5(month number)This will return patients with a birthday in May. 
   
2. Note: Replace “5” with any month required.
   
8. Patients who have a birthday in March and have visited the practice since 01/01/2025: 
   
1. month(dob) = 3ANDlast_visit >= Date('2025-01-01')
   
2. Note: Replace “3” with any month required and insert start date as required.
   
9. Patients whose DOB is between 20/08 and 27/08: 
   
1. month(dob) = 8 and day(dob) >=20ANDday(dob) <= 27
   

1. Patients who last visited this Month 24 months (2 years) ago: 
   
1. last_visit >= DATEADD (month, -24 , Now())ANDlast_visit <= DATEADD (month, -23 , Now())
   
2. Patients who last visited this Month 12 months (1 year) ago:
   
1. last_visit >= DATEADD (month, -12 , Now())ANDlast_visit <= DATEADD (month, -11 , Now())
   
3. Patients aged 18-24 who last visited This Month 24 months (2 years) ago:
   
1. last_visit >= DATEADD (month, -24 , Now())ANDlast_visit <= DATEADD (month, -23 , Now())ANDAge >= 18ANDAge <= 24
   
4. Patients who last visited this Month 24 months (2 years) ago with a health fund:
   
1. last_visit >= DATEADD (month, -24 , Now())ANDlast_visit <= DATEADD (month, -23 , Now())ANDinsurance_level = 'BUPA'
   
2. Note: ‘BUPA’ can be replaced with any health fund. This relates to the insurance plan code.
   
5. Patients who had item #011 performed this Month 24 months (2 years) ago: 
   
1. treatment_item_name = '011'ANDdate_treatment_item >= DATEADD (month, -24 , Now())ANDdate_treatment_item <= DATEADD (month, -23 , Now())
   
6. Patients who had item #012 OR item #013 performed this Month 24 months (2 years) ago:
   
1. date_treatment_item >= DATEADD (month, -24 , Now()) ANDdate_treatment_item <= DATEADD (month, -23 , Now()) ANDtreatment_item_name = '012' OR treatment_item_name = '013'
   
2. Note: Any item number can be used instead of 012 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.
   
7. Patients who had treatment performed on a Monday: 
   
1. DayName(date_treatment_item) = 'Monday'
   
2. Note: Replace with any day as required.
   
8. Patients who had item #011 performed on a Monday: 
   
1. DayName(date_treatment_item) = 'Monday'ANDtreatment_item_name = '011'
   
9. Patients who have not been seen since 19 months ago:
   
1. last_visit > DATEADD (month, -19 , Now())andlast_visit < DATEADD (month, -18 , Now())