Personalized Community is here!
Quickly customize your community to find the content you seek.
Microsoft Customer Co-creation
Help impact how the tools and services you rely on are developed. Microsoft Customer Co-creation connects you directly with our engineers so you can provide feedback before a single line of code is written. Interested? Learn more at Microsoft Customer Co-creation
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All 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:
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