Power Automate Flow DateTime Conditions with CDS List Record

Jordan Hohepa, 04 May 2020

Power Automate Flows have grown in popularity since being introduced by Microsoft. One of the many reasons why you’d use them is because of the hundreds of connectors available, making it easy to get and manipulate data from a variety of places. In this blog we will be looking at one way we can retrieve data from a Dynamics 365 system, by querying with DateTime conditions.

To start with we can create a Flow that is triggered daily. Once triggered we’d want the Flow to retrieve records from a Dynamics 365 system using the Common Data Service action ‘List Records’. With this action you can put in different expressions to query for specific data.

If you go to Advanced Find in Dynamics 365 you can build a query using the Advanced Find Editor. As you can see in the screenshot above you can compare values using operators like ‘eq’ which can be put straight into Flow. Dynamics 365 also has DateTime comparers, but these don’t translate directly to Flow.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="createdon" operator="on" value="2010-10-10" />
    </filter>
  </entity>
</fetch>

For example, if I wanted to get all records created on 10/10/10, in Dynamics 365 I could use an ‘on’ operator with the day 10/10/10 as shown above. If I wanted to use this in Flow I would have to use ‘Microsoft.Dynamics.CRM.On(PropertyName='createdon',PropertyValue='10/10/10'), where PropertyName is the field I want to use and PropertyValue is the date value.

Because this is a Dynamics 365 specific operator, you’d need to use the full Microsoft.Dynamics.CRM then add what your operator is. A couple of other examples that are commonly used are OnOrAfter, OnOrBefore and LastXHours with examples shown below:

• Microsoft.Dynamics.CRM.OnOrAfter(PropertyName='createdon',PropertyValue='10/10/10')
• Microsoft.Dynamics.CRM.OnOrBefore(PropertyName='createdon',PropertyValue='10/10/10')
• Microsoft.Dynamics.CRM.LastXHours(PropertyName='createdon',PropertyValue=1)