AX / Dynamics 365 For Finance and Operations blog

In Dynamics 365 Finance and Operations (D365 FO), it’s common to need views that dynamically filter records based on the current date. For example, a view might need to retrieve only those records where:

• The ValidFrom field is less than or equal to today’s date.
• The ValidTo field is greater than or equal to today’s date.

However, setting up such filters directly in an AOT view can be tricky because:

• AOT filters like today() are evaluated statically at design time, not dynamically at runtime.
• Directly filtering on computed columns in the same view is not always straightforward.

This article demonstrates a modular and scalable solution using two AOT views to implement dynamic date filters.

.

The Two-View Solution

To implement dynamic filtering based on today’s date, you can structure your solution as follows:

1. Base View: This view calculates a field (IsValidRange) to determine whether a record falls within the date range.
2. Filtered View: This view consumes the base view and applies a filter on the IsValidRange field to retrieve only the valid records.

This separation ensures that the logic is modular and reusable.

Step 1: Creating the Base View

The base view is responsible for computing the IsValidRange field, which indicates whether a record’s date range is valid.

Steps to Create the Base View

1. Create the View:
   • Go to Application Explorer > Data Model > Views.
   • Create a new view (e.g., BaseView).
2. Add Data Sources:
   • Add the table containing ValidFrom and ValidTo fields (e.g., MyTable) as the primary data source.
3. Define a Computed Column:
   • Add a method in the Methods section of the view to compute the IsValidRange field dynamically:

public static str isValidRangeComputed()
{
    return 'CASE 
                WHEN ValidFrom <= GETDATE() 
                AND ValidTo >= GETDATE() 
            THEN 1 
            ELSE 0 
            END';
}

1. Add the Computed Column:
   • Go to the Fields section of the view.
   • Add a new field named IsValidRange and link it to the method isValidRangeComputed.

Result of the Base View

The BaseView will now compute the IsValidRange field for all records, which evaluates to:

• 1 if the record falls within the valid date range.
• 0 otherwise.

The SQL generated for the BaseView will look like this:

SELECT *,
       CASE 
           WHEN ValidFrom <= GETDATE() 
           AND ValidTo >= GETDATE() 
       THEN 1 
       ELSE 0 
       END AS IsValidRange
FROM MyTable;

Step 2: Creating the Filtered View

The filtered view uses the base view as its data source and filters records based on the IsValidRange field.

Steps to Create the Filtered View

1. Create the View:
   • Go to Application Explorer > Data Model > Views.
   • Create a new view (e.g., FilteredView).
2. Add the Base View as a Data Source:
   • Add BaseView as the primary data source.
3. Add a Range on the IsValidRange Field:
   • Expand the BaseView data source in the filtered view.
   • Add a range to filter records where IsValidRange = 1.

Result of the Filtered View

The FilteredView will filter out records that are not valid based on today’s date. The SQL generated for the FilteredView will look like this:

SELECT *
FROM (
    SELECT *,
           CASE 
               WHEN ValidFrom <= GETDATE() 
               AND ValidTo >= GETDATE() 
           THEN 1 
           ELSE 0 
           END AS IsValidRange
    FROM MyTable
) AS BaseView
WHERE IsValidRange = 1;

Why Use Two Views?

1. Modularity: The computation logic for IsValidRange is centralized in the base view, making it reusable for other scenarios.
2. Scalability: You can create multiple filtered views based on the base view for different filtering requirements.
3. Maintainability: Any changes to the date range logic only need to be made in the base view.

Testing the Solution

1. Compile Both Views:
   • Ensure that both BaseView and FilteredView compile without errors.
2. Preview Data:
   • Use the Preview Data option in AOT to verify that the FilteredView retrieves only records with IsValidRange = 1.
3. Integrate with Other Modules:
   • Use the FilteredView in forms, reports, or data entities to dynamically retrieve valid records based on today’s date.

Conclusion

By using two AOT views—one for computation and one for filtering—you can effectively implement dynamic date filtering in Dynamics 365 FO. This approach not only addresses the limitations of AOT filters but also ensures a clean, maintainable, and scalable solution for handling dynamic conditions.