AX / Dynamics 365 For Finance and Operations blog

In Dynamics 365 for Finance and Operations (D365FO), managing data visibility effectively means ensuring the right information is always available, while also accommodating user-specific customizations. A common requirement arises when you need to apply specific filters that override user custom views, ensuring critical business data rules are always enforced. In this blog post, we’ll explore how to apply and parameterize filters to control data visibility, regardless of any customized views users may have applied.

Overriding User Custom Views with Persistent Filters

Imagine you have a scenario where you need to display production history, but you want to ensure that users only see records from the last three months. Users might have saved custom views that display all records or use different filters, but you want to enforce a specific filter to maintain consistency across your organization.

To override these user views, we can use some handy techniques in D365FO by directly editing the data source methods of the form. By using a locked filter, we ensure that the filter is always applied, regardless of user preferences.

Here’s a basic example of how you can do this by overriding the executeQuery() method in the data source:

public void executeQuery()
{
    QueryBuildDataSource qbds = this.query().dataSourceTable(tableNum(OTS_HistoryLam));
    QueryBuildRange qbr;

    // Find if there is already a range applied to the ProdDate field
    qbr = qbds.findRange(fieldNum(OTS_HistoryLam, ProdDate));

    // If the range doesn't exist, add a new one
    if (!qbr)
    {
        qbr = qbds.addRange(fieldNum(OTS_HistoryLam, ProdDate));
    }

    // Calculate the start date (e.g., last 3 months)
    utcdatetime fromDate = DateTimeUtil::addMonths(DateTimeUtil::utcNow(), -3);

    // Set the range value to the desired interval
    qbr.value(SysQuery::range(fromDate, DateTimeUtil::utcNow()));
    qbr.status(RangeStatus::Locked); // Lock the filter to prevent modifications

    // Execute the original query
    super();
}

Key Techniques Explained

1. Finding or Adding the Filter Range: By using qbds.findRange(fieldNum(OTS_HistoryLam, ProdDate)), we can check if a filter on the production date already exists. If it does not, we create a new range to apply the filter. This ensures the filter isn’t duplicated every time the query is executed.
2. Locking the Filter: Setting qbr.status(RangeStatus::Locked) ensures that the filter cannot be overridden by user custom views or any manual change. This is crucial to ensure consistent data filtering, particularly when data integrity is key.

Parameterizing the Filter

One of the best practices in D365FO is to parameterize filters, making them flexible and allowing dynamic input based on user needs. In our example, instead of hardcoding the last 3 months, we can add a form control that allows the user to specify the number of months.

Here’s how you can do this:

1. Add a Control to the Form:
   • Add an IntegerEdit or int64Edit control to the form, where users can input the number of months they wish to view.
   • Let’s call this control NumMonthsEdit.
2. Modify the executeQuery() Method to Use the User Input:

public void executeQuery()
{
    QueryBuildDataSource qbds = this.query().dataSourceTable(tableNum(OTS_HistoryLam));
    QueryBuildRange qbr;

    // Retrieve the user-input value for the number of months
    int numMonths = NumMonthsEdit.value();

    // Default to 3 months if the user has not provided input
    if (numMonths <= 0)
    {
        numMonths = 3;
    }

    // Find or add a range for ProdDate
    qbr = qbds.findRange(fieldNum(OTS_HistoryLam, ProdDate));
    if (!qbr)
    {
        qbr = qbds.addRange(fieldNum(OTS_HistoryLam, ProdDate));
    }

    // Calculate the start date based on user input
    utcdatetime fromDate = DateTimeUtil::addMonths(DateTimeUtil::utcNow(), -numMonths);

    // Apply the range value
    qbr.value(SysQuery::range(fromDate, DateTimeUtil::utcNow()));
    qbr.status(RangeStatus::Locked); // Ensure the filter is locked

    // Execute the query
    super();
}

Benefits of Parameterizing the Filter

• Flexibility: Users can adjust the time range according to their needs, while you maintain control over the data visibility.
• Consistency: By locking the range (RangeStatus::Locked), you ensure that even if users have custom views, they still respect the core business logic of only seeing records from the desired period.
• Ease of Use: By providing an easy input for customization, users feel empowered, but within the guardrails of what the business requires.

Conclusion

By combining locked filters with parameterization, you can ensure that your business requirements are consistently enforced across your organization, while still allowing some degree of user customization. This is particularly important in scenarios where data integrity and visibility control are key.

This approach ensures that even if users apply their custom views, your filters take precedence, providing the flexibility for users to set parameters dynamically while ensuring the integrity of the data they see