How to Filter a D365 F&O Data Entity by Date Range

on

Data entity range


Filtering Data Entities in Dynamics 365 Finance & Operations to return only active records—such as those where an EndDate is today or in the future—is a common integration requirement. While you could write custom X++ code or rely on endpoint-specific export settings, there is a far cleaner low-code approach. By configuring a query range directly on the entity's root data source in the AOT, you enforce dynamic date filtering at the metadata level. This guarantees that expired records are automatically excluded across all consumption channels, including OData, Data Management export projects, and the Excel Add-in.


The Scenario

Imagine you have a Data Entity with a parent table and a related child table joined on a common key. The parent table holds StartDate and EndDate fields that define how long a record remains valid.

The Goal: Automatically exclude expired records so that every export or query returns only active data (EndDate >= Today).


Why manual export filters fall short

Setting a filter inside a specific Data Management export project might solve the problem for that one project, but it leaves OData calls and Excel Add-in queries wide open. To make the rule bulletproof, the filter needs to live directly inside the entity definition itself. 

The Solution: Add an AOT Range to the Root Data Source

By placing a query range directly on the root data source of your entity in Visual Studio, the range gets compiled straight into the entity's underlying metadata query. D365 F&O will then enforce it consistently across all integration endpoints.

Step-by-Step Instructions

  1. Open your entity in the Visual Studio designer.

  2. Expand the tree: Go to Data Sources (this should be the main parent table).

  3. Add the Range: Right-click Ranges under that specific data source and select New Range.



  4. Configure the Range properties:

    • Field: Select EndDate.

    • Value: Type (greaterThanDate(0)).

    • Enabled: Set to Yes.



  5. Save & Sync: Save your changes, run a build on your project, and synchronize the database.


How (greaterThanDate(0)) Actually Works

If you haven't run across greaterThanDate() before, it’s a standard SysQueryRangeUtil helper method built into Dynamics 365.

  • Dynamic Execution: Rather than hardcoding a static date, this expression evaluates dynamically every time the query runs.

  • Day Offset: The number inside the parentheses represents a day offset relative to today. Passing 0 means "today" (0 = current date).

  • Syntax Matters: Don't forget the surrounding parentheses (). That wrapping tells the query parser to treat the text as a method call and resolve it before executing the SQL statement.


Reference
Microsoft Learn — Advanced filtering and query options: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/advanced-filtering-query-options

Comments

Post a Comment