Personalized Community is here!
Quickly customize your community to find the content you seek.
‘Better Together’ Integration forum available
We're launching a how-to forum where you can learn and engage about how Dynamics 365 integrates with other Power Platform products.
Read about Better Together forum
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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.
First part is easy, just add a condition that the Estimated Close Date is in the next 90 days.
But that will include all opportunities with Estimated Close Date any time from today and the following 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.
You simply add an outer join (link-entity) joining on the primary key of the entity.
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.
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.
The complete query will now look like this:
<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' />
<link-entity name='opportunity' from='opportunityid' to='opportunityid' link-type='outer' alias='opptlessthan90days' >
<condition attribute='estimatedclosedate' operator='next-x-days' value='89' />
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.
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 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
The post Querying date intervals in the Common Data Service appeared first on The Dynamics 365 Trenches.
Business Applications communities