Dynamics Community Forum Thread Details

Sending a birthday text based on Dynamics 365 date field

Hello Recruitment 2019,

You're on the right track with a scheduled flow and the "List records" action in Power Automate. The issue you're facing with "No dynamic content available" in the Filter Query is a common one when dealing with date comparisons in Dataverse (formerly CDS).

Here's a breakdown of why you're seeing this and how to construct the correct Filter Query to find contacts whose calculated birthday is today:

Why "No Dynamic Content Available" in Filter Query:

The "Filter Query" field in the "List records" action expects an OData filter expression as a string. It doesn't directly interact with the dynamic content picker in the same way as actions that allow you to directly compare fields. You need to manually construct the OData filter syntax.

Constructing the OData Filter Query for Today's Birthday:

You need to compare your calculated birthday date field with the current date. Here's how you can do it using the eq (equals) operator and the utcnow() function within an OData filter:

Identify the Schema Name of Your Birthday Date Field:
- In Dynamics 365, go to Settings > Customizations > Customize the System.
- Expand Entities and find your Contact entity.
- Click on Fields and locate the field you created for the calculated birthday date for this year.
- Note down the Schema Name of this field. It will be a unique name usually starting with a prefix (e.g., new_calculatedbirthday). Let's assume your schema name is new_calculatedbirthday.

Construct the Filter Query:
In the "Filter Query" field of your "List records" action, enter the following OData filter expression:
new_calculatedbirthday eq @utcnow()
Explanation:
- new_calculatedbirthday: Replace this with the actual schema name of your calculated birthday date field.
- eq: This is the OData operator for "equals."
- @utcnow(): This is a Power Automate expression that returns the current date and time in Coordinated Universal Time (UTC).

Handling Time Zones (Important Consideration):

The @utcnow() function provides the current date and time in UTC. Your Dynamics 365 environment and the data in your birthday field might be in a different time zone. To ensure accurate comparison, you might need to adjust for the time zone difference. Here are a couple of approaches:
- Convert Birthday Field to UTC (Less Ideal for Calculated Fields): If your calculated birthday field stores the date in a specific time zone, you might need to convert it to UTC within the filter. This can get complex with calculated fields.
- Compare Only the Date Part (Recommended): A more reliable approach is to compare only the date portion of both the birthday field and the current UTC date. You can achieve this using OData functions:
  date(new_calculatedbirthday) eq date(utcnow())
  
  This expression extracts only the date part from your birthday field and the current UTC date, ignoring the time component. This is generally the best approach for birthday checks.

Complete "List records" Action Configuration:

Your "List records" action for Contacts should look something like this:

Entity name: Contacts

Select Query: (Optional) Select the attributes you need for sending the SMS (e.g., phone number, first name).

Filter Query: date(new_calculatedbirthday) eq date(utcnow()) (Replace new_calculatedbirthday with your actual schema name).

Order By: (Optional)

Top Count: (Optional)

Next Steps in Your Flow:

Initialize a Variable (Optional but Good Practice): You can initialize an array variable to store the contacts who have a birthday today.

"Apply to each" Action: Add an "Apply to each" action to iterate through the list of contacts returned by the "List records" action.

Twilio "Send SMS" Action: Inside the "Apply to each" loop, add your Twilio "Send SMS" action. Use the dynamic content from the "List records" action (e.g., the contact's phone number) to send the birthday text. You can also personalize the message with their name.

Troubleshooting Tips:

Verify Schema Name: Double-check that you have the correct schema name for your calculated birthday date field.

Test with a Specific Date: To test your filter query, you can temporarily replace date(utcnow()) with a specific date in the OData format (e.g., date(2025-04-26)). This will help you confirm if your filter logic is correct.

Check Time Zones: Be mindful of the time zone differences between your Dynamics 365 environment and UTC. The date() function should help mitigate this, but ensure your calculated birthday field stores the date correctly.

Flow Run History: After activating your flow, monitor the run history to see which contacts are being retrieved.

By using the date() function in your OData Filter Query, you should be able to successfully retrieve the contacts whose calculated birthday date matches today's date, allowing you to proceed with sending your birthday SMS messages via Twilio.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak

Quick Links

Subscribe to this forum!