Querying date intervals in the Common Data Service

Querying date intervals in Microsoft Common Data Service, Power Apps and Dynamics 365 is not always possible using Advanced Find nor is it as easy as working with plain SQL.

TL;DR – with FetchXML this is quite possible!

Have you ever wanted to tell your CRM (CDS, D365 etc.) things like:

Show me all opportunities
with Estimated Close Date
exactly 90 days from now!

Well, today a colleague of mine wanted to know if that was possible.

The challenge Thomas faced was:

I’m running a recurring Power Automate flow every day, and it shall send an email with a list of all opportunities with Estimated Close Date exactly 90 days into the future. Would it be possible to find those opportunities…?

It turns out this is not a noob question at all. But it is possible, with a little FetchXML trickstery.

Building the query

Dates next 90 days

First part is easy, just add a condition that the Estimated Close Date is in the next 90 days.

<condition
  attribute='estimatedclosedate'
  operator='next-x-days'
  value='90' />

But that will include all opportunities with Estimated Close Date any time from today and the following 90 days.

Filtering dates < 90 days

Unfortunately, there is no condition operator for 'not-next-x-days' and 'greater-than' requires a fixed date, so we need to come up with something else…

Removing records from a result set (opportunities for all days from today until 89 days from now) when there are no conditions available is usually best done with a “not-in-query”, by adding an outer join that should exclude all the records you don’t want to include in the result set.

But how would you join when you are just querying one entity? There is no relationship to use between an opportunity with ECD 89 days from now and… well the same opportunity…

And here comes the trick.

Not-in-outer-join

You simply add an outer join (link-entity) joining on the primary key of the entity.

<link-entity
  name='opportunity'
  from='opportunityid'
  to='opportunityid'
  link-type='outer'
  alias='opptlessthan90days'>

That will create a join from each individually returned opportunity to “itself”. A pretty stupid join… for most other scenarios.

Now to make sure that join aimed at excluding all opportunities with ECD up to 89 days from today targets the right records, we add a condition to this link.

<condition
  attribute='estimatedclosedate'
  operator='next-x-days'
  value='89' />

Excluding records

Finally to make sure these joined records are excluded from the final result set, we add a condition on the entity level of the query to make sure any opportunities that are within 89 days from now won’t get returned.

<condition
  entityname='opptlessthan90days'
  attribute='opportunityid'
  operator='null' />

Final query

The complete query will now look like this:

<fetch>
  <entity name='opportunity' >
    <attribute name='estimatedvalue' />
    <attribute name='customerid' />
    <attribute name='estimatedclosedate' />
    <attribute name='name' />
    <filter type='and' >
      <condition attribute='estimatedclosedate' operator='next-x-days' value='90' />
      <condition entityname='opptlessthan90days' attribute='opportunityid' operator='null' />
    </filter>
    <link-entity name='opportunity' from='opportunityid' to='opportunityid' link-type='outer' alias='opptlessthan90days' >
      <filter>
        <condition attribute='estimatedclosedate' operator='next-x-days' value='89' />
      </filter>
    </link-entity>
  </entity>
</fetch>

In my sample environment I have some opportunities, and from today (2020-06-01) it is exactly 90 days until August 30 (2020-08-30). So, the green opportunities should be returned from my query…

Executing the query with FetchXML Builder, I can now verify the results.

Applying in a Power Automate flow

If Thomas adds action List Records using the Common Data Service (current environment) connector he can now simply paste the FetchXML into the action, and then do whatever he wants with the returned opportunities.

Read more about when to use which CDS connector, by Sara Lagerquist.

Using in a view

Using the same technique, I used the FetchXML Builder to create a view in CRM showing open opportunities with Estimated Close Date between 60 and 90 days from now:

And the resulting view looks like this:

As expected, Advanced Find doesn’t really know how to render this query since it is “too complex” for the current version of OOB filtering capabilities.