In Dynamics 365 for Finance and Operations you may have encountered filtering data within certain form grid columns is not possible. This limitation often occurs when a column is bound to a display method, which calculates data dynamically as the grid is populated. Additionally, because display methods are not part of the SQL database, they can't be used in SQL "WHERE" clauses for filtering.

Despite these challenges, it's possible to make these dynamically calculated columns searchable. 🔍💡 This article describes a workaround to make all the columns in the D365FO grid searchable. Let’s dive in! 😉

How do you make a calculated column searchable?

The trick is that you don’t display data directly from tables using display methods but to use views as the data source for grid columns if possible. A view in D365FO is like a database view. It’s a virtual table based on the result-set of an SQL statement (query) containing rows and columns, just like a real table and the data is only stored in tables used in SQL statement. The fields in a view are fields from one or more real tables in the database.

For example, let’s start with DocEventRuleGridView form. In Docentric AX, we introduced this form to allow users to review all alert rules in a grid. 🔔

The DocEventRuleGridView form can be accessed from the Manage my alerts form by clicking the View in grid button. 🖱️

For the DocEventRuleGridView form data source, we use the DocEventRuleView view in addition to the standard EventRule table 📋.

If we look at DocEventRuleView, we can see that the data source for the view is the standard EventRule table.

So our DocEventRuleView contains fields from the standard EventRule table plus additional calculated columns.

A calculated column is actually a regular column whose value is the result of a calculation in the method. To create a calculated column in the view, you need to add one of the suggested calculated columns and then bind that column to the method that returns the value for it.

Since display methods can also be created on the View object, it is important to emphasise that methods returning values for calculated columns must calculate the value using SQL syntax only. SQL case statements, functions and direct statements can be used for this purpose.

In our example of DocEventRuleView, you can see that the computed OrganizationWide column is bound to the organizationWide() method, which returns a value for it.

As you can see in the images above, we used the SysComputedColumn framework to calculate the value of the OrganizationWide column. For more information about adding computed columns to the view, see the related Microsoft documentation.

The result of using the SysComputedColumn class to return a value for the computed column is a matching T-SQL statement, as shown in the next image.

This way, when the form is opened, the view collects the data from its data sources and calculates the values of its calculated columns. The collected data is then stored in memory, and users can apply filters based on this data.

Conclusion

Voila! Making form grid columns in D365FO searchable is breezy. Just craft a view with calculated columns, designate it as the form data source, and bid farewell to non-searchable columns. It's your gateway to easy, effective data filtering in D365FO. Easy-peasy lemon-squeezy 🍋! Happy searching 🔍!