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.

Translated: Is there time for noob fetch question number 1000?

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.

13 thoughts on “Querying date intervals in the Common Data Service

  1. For Power Automate, is it not easier to just compare against something with utcNow() instead of that complex FetchXml?

  2. Hi Jonas, no need for any code, just proper use of the Filter Query – use this one:

    estimatedclosedate eq substring(addDays(utcNow(),90),0,10)

    Isn’t that easier? Now your Fetch Xml Query can be empty.

    1. For the flow scenario you are absolutely right.
      Although I would claim my example here has no code, only a query, while your suggestion has code (the three methods substring, addDays and utcNow).
      And for scenarios where you cannot use any code, like creating a view to show these records, composing a query for it is the only option if you don’t go down the plugin path to intercept either the query pre operation or the results post operation.

      1. Of course, I am just addressing the specific requirement from Thomas, which can be done with my method much easier than with that complex FetXml. Always keep it simple, right?

        1. Indeed! 💯 😊
          I guess it also depends on where you come from and what is subjectively considered “complex”…
          But yes, totally agree with you.

  3. Hey, I come from the roots – Dynamics CRM 1.2, since 15 years living in this world.

    But Power Automate is a low-code/no-code platform and I guess a citizen developer would rather use few expressions directly in the designer, than construct a complex FetchXml, just in case they would even know what that is.

  4. How did you get the fetchxml query into advanced find please?

    1. Hi – sorry for very late response…
      In the last above is the Advanced Find, showing the query from the view by clicking to top/right next to the Settings.
      The query gets the fetchxml from the tool FetchXML Builder.
      Hope I helped a bit 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.