RSMUS.comUsing Power Automate I often need to filter records based on date to find only those matching a certain date. This can be to check tasks due in 30 days, to find records with no activity for 90 days, or many other scenarios. In this example, I will check Project records in D365 CE to identify any that are scheduled to start on the current date.
Although filtering like this can be done with Condition steps it is far more efficient in terms of flow run time and executions to use a Filter Query on the List records step.
Using Filter Query
Because D365 CE stores all date values with an associated time (regardless of if the time is displayed or not) we can’t just filter if the date is equal to the current date. We need to check if the start date is between the start and end of the day, including times. To do that, we have one condition checking if the date field is greater than or equal (ge) to the start of the day and a second checking if the date field is less than (lt) the start of the next day. The full List Records step will look like the below:
The first step checks if the Start Date field (msdyn_scheduledstart) is greater than or equal to the output of functions startOfDay(utcNow()) to ensure all results are on today’s date or later. The startOfDay() function returns a date field with all time values (hours, minutes, seconds) set to 0 while the utcNow() function returns the current time in Coordinated Universal Time (UTC). The result will be the earliest time for today’s date, so any time on this date will be greater than or equal to this value.
The next step checks if the start date field is less than but not equal to the beginning of tomorrow to ensure no results are after today’s date. To do that we use similar functions with an additional addDays() included to check tomorrow’s date (today’s date with one added day) instead of today’s: startOfDay(addDays(utcNow(),1))
With these functions, your List Records step should return all records with a date field value on today’s date.
Accounting for Time Zones
Note one possible issue to keep in mind is that the time zone difference between UTC time and your time means for at least part of the day utcNow() will return a different date than the date in your time zone. For example, there is a 6-hour offset between UTC and CST, so if this Flow executes after 6pm CST and before midnight the next day will be returned and filtered on instead. If your Flow is set to run on a daily recurrence at a time when your time zone and UTC are aligned on the same date then this is not a problem. Otherwise, you will need to make an addition to your filter query to account for it.
The easiest addition is to use addHours() to counter the offset between UTC and your time zone, which functionally converts the time zone. In the case of CST, add negative 6 hours. The first clause is now startOfDay(addHours(utcNow(),-6)). The second clause needs the same adjustment.
You can also use a time zone conversion function like convertFromUTC() to convert the date but I find that it requires additional date formatting and is ultimately more difficult than adding hours.
To use this functionality for dates other than the current date, you use the same structure with addDays() used on both the ge clause and the lt clause. For example, to get all dates 30 days from now you would use ge startOfDay(addDays(utcNow(),30)) and less than startOfDay(addDays(utcNow(),31)). You can also apply this to dates in the past by using a negative value with addDays().
Sam Stern
Sam is a Senior Consultant in RSM's Dynamics practice. He has experience working with many aspects of Dynamics CE as well as integration technologies like Scribe and SmartConnect.