Sending Daily Appointment Reminders: OData Filter Query with SharePoint Date columns!

In this training session, Nate Halliwell from Pragmatic Works walks through a practical use case that integrates Power Automate with SharePoint to streamline communication. Using the example of a doctor’s office, Nate demonstrates how to automate daily appointment reminder emails for patients with personalized details and unique virtual links.

Scenario Overview

Consider a doctor’s office managing two SharePoint lists:

• Appointments List – contains appointment dates and linked patient details.
• Patients List – stores patient information such as email addresses and unique virtual appointment links.

The objective is to create a Power Automate flow that runs daily, retrieves the next day’s appointments, and sends customized emails to each patient with their appointment details and personal meeting link.

Step 1: Building the Scheduled Flow

Nate begins by creating a scheduled flow in Power Automate. The flow is configured to run every Sunday through Thursday, since no appointments occur on weekends. This ensures reminders are sent only when necessary.

Step 2: Retrieving Appointment Records

The first task is to use the Get Items action in SharePoint to retrieve appointment records. Initially, all records are returned, but the goal is to filter for only the appointments scheduled for the next day.

Step 3: Filtering with OData Queries

To filter records effectively, Nate introduces the concept of OData filter queries. SharePoint date fields store data in a specific format (YYYY-MM-DD), so the flow needs to dynamically generate the date for “tomorrow” in the same format.

Key steps include:

1. Using the utcNow() function to capture the current date and time.
2. Formatting the result with formatDateTime() to match SharePoint’s format.
3. Applying addDays() to shift the date forward by one day, representing tomorrow’s appointments.

With the formatted value, Nate applies a filter query such as:
AppointmentDate eq '2023-08-07'

Step 4: Verifying the Results

To validate, Nate uses test runs and Compose actions to count the number of returned records. Adjusting appointment dates in SharePoint confirms the filter accurately includes or excludes appointments based on the target date.

Step 5: Linking Patient Data

Since each appointment record is connected to a patient through a lookup column, Nate uses the Get Item action to retrieve patient details from the Patients list. The flow automatically applies a loop to handle multiple records, ensuring each appointment is linked to the correct patient.

Step 6: Sending Reminder Emails

With appointment and patient data combined, Nate configures the Send an Email (V2) action. Each email includes:

• Recipient: The patient’s email address from the Patients list.
• Subject line: Personalized text such as “Don’t forget about your appointment tomorrow!”
• Body content: Patient’s name, appointment details, and a clickable hyperlink to their unique virtual appointment link, formatted using HTML <a> tags.

Step 7: Testing the Flow

To ensure accuracy, Nate runs the flow using sample appointments. Patients receive customized reminder emails with correct details and functional links, confirming the process works end-to-end.

Key Takeaways

• Power Automate’s OData filter queries are powerful for retrieving specific records from SharePoint.
• Date formatting and functions like addDays() are essential when working with time-sensitive automations.
• Lookup columns enable linking between SharePoint lists, enriching automation with related data.
• Email actions support HTML formatting, allowing dynamic and professional communication.

Conclusion

Nate Halliwell’s demonstration provides a practical guide for automating appointment reminders using Power Automate and SharePoint. By leveraging OData queries, dynamic date handling, and lookup fields, organizations can streamline routine communications, reduce missed appointments, and improve efficiency. This approach is applicable not only to healthcare but to any business scenario where timely reminders and linked data are critical.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on SharePoint and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.