Introduction:
In the realm of Dynamics 365 and FetchXML queries, users often find themselves grappling with limitations, especially when it comes to date filtering. A date filter allowing the display of records “newer” than 90 days from today is currently unavailable. The absence of a straightforward “newer-than-x-days” option in FetchXML has led many to seek alternative solutions. Fortunately, developers like Jonas Rapp and Mark Carrington have proposed workarounds that involve leveraging outer-join functionalities. However, these methods may not simple to implement by end-users.
The Calculated Field Conundrum:
Attempting to create a calculated field to bridge the gap presents its own set of challenges. The error message, “This operation cannot be performed on values which are of different Date Time Behaviors,” is a roadblock, particularly when dealing with “Date Only” fields like “Estimated Close Date.” This limitation can be frustrating, hindering the seamless execution of desired date calculations.
A Glimmer of Hope: Microsoft Formula Columns:
Enter Microsoft formula columns, this alternative method provides a way to perform date calculations without running into the Date Time Behavior mismatch issue. By employing a straightforward formula, users can effortlessly calculate the number of days in the future from the current date.
The Solution: DateDiff Function:
The key to overcoming the FetchXML limitations lies in the DateDiff function provided by Microsoft formula columns. By utilizing this function, users can perform date calculations efficiently. Let’s take a look at a simple example:
DateDiff(UTCToday(), 'Estimated Close Date')
This formula calculates the difference in days between the current date (UTCToday()) and the value in the “Estimated Close Date” field. By using this approach, you can easily filter records based on a “newer-than-x-days” criterion.
Conclusion:
While FetchXML may present certain limitations, the innovative use of Microsoft formula columns provides a robust solution to the challenge of date filtering. By embracing these alternative methods, users can enhance their Dynamics 365 experience and gain more flexibility in managing and querying data. So, the next time you find yourself grappling with date-related queries, remember the power of Microsoft formula columns to streamline your processes and make your Dynamics 365 journey smoother.