AX / Dynamics 365 For Finance and Operations blog

In Dynamics 365 Finance and Operations (D365FO), tables with validity dates automatically filter out expired records. A common example is the LogisticsPostalAddress table, which hides expired addresses by default. This behavior can cause issues when displaying sales order lines joined with this table, as orders linked to expired addresses will also be excluded.

To override this behavior, you can modify the ValidTimeStateAutoQuery property and set it to DateRange, ensuring all records, including expired ones, are visible.

Scenario

Consider a form that displays sales order lines joined with LogisticsPostalAddress. The system automatically filters out expired addresses, making the related sales orders invisible. However, in some cases, such as historical order management or address verification, you may need to display all addresses, even expired ones.

Solution: Modify ValidTimeStateAutoQuery via X++

To remove the automatic filter, update the ValidTimeStateAutoQuery property of the LogisticsPostalAddress data source to DateRange and set a full date range using validTimeStateDateTimeRange.

Sample Code

[ExtensionOf(formdatasourceStr(SalesLineOpenOrder, SalesLine))]
final class SalesLineOpenOrder_SalesLine_DLXFormDS_Extension
{
    public void executeQuery()
    {
        utcdatetime utcMaxDate, utcMinDate;

        // Set the min and max valid dates
        utcMinDate = DateTimeUtil::minValue();
        utcMaxDate = DateTimeUtil::maxValue();

        // Get the datasource for LogisticsPostalAddress
        var logisticsPostalAddress_DS = element.dataSource(formDataSourceStr(SalesLineOpenOrder, LogisticsPostalAddress)) as FormDataSource;
        
        // Modify ValidTimeStateAutoQuery to DateRange to include expired records
        logisticsPostalAddress_DS.validTimeStateAutoQuery(ValidTimeStateAutoQuery::DateRange);
        logisticsPostalAddress_DS.query().validTimeStateDateTimeRange(utcMinDate, utcMaxDate);

        // Execute the original query
        next executeQuery();
    }
}

How It Works

• Setting ValidTimeStateAutoQuery to DateRange: This removes the default filtering that hides expired records.
• Using validTimeStateDateTimeRange: By setting the range from DateTimeUtil::minValue() to DateTimeUtil::maxValue(), all records are included, regardless of validity.
• Preserving Standard Behavior: Calling next executeQuery() ensures that all other query logic remains unchanged.

Why This Matters

By modifying ValidTimeStateAutoQuery, you gain control over how validity-dated tables behave. This approach is particularly useful when working with historical data, auditing old transactions, or ensuring data visibility beyond default system constraints.

Conclusion

If you need to display expired records in Dynamics 365, setting ValidTimeStateAutoQuery to DateRange is a simple yet powerful solution. This small adjustment ensures that all relevant data remains accessible without altering core table functionality.