ExecuteQuery Method In D365 Forms: How To

X++ developers can override the ExecuteQuery in D365 forms to change what data is read from the underlying table. By default, data sources will return all records in the table. There are times you only want to show a certain subset of data. In an earlier article, I showed you how you can use standard functionality built into various forms to filter the data. In this article I will explain how to override the ExecuteQuery method in D365 forms to change what data is shown.

What Is The ExecuteQuery Method?

The ExecuteQuery method in D365 forms refers to a method is called once when the form is opened. But it is also called when the ‘refresh’ button is pushed on a form. It can also called by other controls on the form.

Consider this common use of the ‘executeQuery’ method. You have a form that contains a grid. The grid shows records from a table. And each record has a ‘status’ field or ‘Type’ field that you want to filter by. While you can use a quick filter, sometimes it makes the form a little easier to use, to add a Combo Box drop down field above your grid. Then, when the user selects a different option from the Combo Box, the grid is filtered.

In my example, I have a Vehicle service workbench form. On the form, there is a grid that shows each vehicle being services. I have a QuickFilter control on the form. But I can also use a combo-box drop down to filter the grid on what type of car should be shown in the grid.

Init Vs ExecuteQuery method

Based on how you design a form, a developer may want to automatically apply a filter when a form first opens. This can be done by overwriting the init method on the datasource, or the ‘executeQuery’ method on the datasource.

The init method is only called once when the form first opens. This is useful if you want a specific filter to be applied only when the form first opens, but use a different filter criteria afterwards. This can still be done in most scenarios with overwriting the executeQuery method. So for the purposes of this article will just cover the ExecuteQuery method. But similar code can be used in the init method to filter the data as well.

Add Combo Box Form Control

First, create a header group above your main grid, if you do not have one already.

Next, Add the Combo Box control to your form, by right clicking on the header group control that is above the grid, then selecting New>Combo Box.

Next, right-click on the control and select properties. In the Properties window, change the Name property to ‘VehicleTypeComboBox’.

In my example, the field in the grid that I want to filter uses an enum. This means that there are pre-defined specific values it is allowed to be. I can make my Combo Box filter show these possible values, by setting the ‘Enum Type’ property on the Combo Box.

The enum that is being used by the field I want to filter is named rsmVehicleType. So I will set the ‘Enum Type’ property on my Combo Box to ‘rsmVehicleType’.

Now, when I use the form, the Combo Box will only show values in this enum.

Lastly, change the ‘Auto Declaration’ property ‘Yes’. This will allow us to reference this Combo Box and retrieve the value later in this article.

SelectionChange Method

Next, I need to make it so anytime the user changes the selection of the Combo Box, it refreshes the data. The way to do that is to override the ‘selectionChange’ method on the Combo Box control. Then have this method call the ‘executeQuery’ method on the datasource that is associated with the grid I am trying to filter.

To override the ‘selectionChange’ method, expand the Combo Box control. Then select the ‘Methods’ node. Finally, right click on the Methods node and select Override>selectionChange.

The system will generate x++ code for the selectionChange method, and open a new tab.

Right now, this code does not do anything. The line of code ‘ret = super()’ essentially just tells the system to do what the control is supposed to do, and display a new value anytime a new value is selected. Now however, we want to also make sure the system refreshes the data show in the grid.

Call ExecuteQuery Method

To do that, we need to call the executeQuery method on the main data source used by the grid. I can see this by selecting the grid control in the form designer, then viewing the ‘Data Source’ property on the grid. In my case, the main data source is named ‘rsmVehicle’.

Therefore, in the selectionChange method, I need to add the line of code rsmVehicle_ds.executeQuery(); My code now looks like this:

[Control("ComboBox")]
    class VehicleTypeComboBox
    {
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public int selectionChange()
        {
            int ret;
        
            ret = super();

            rsmVehicle_ds.executeQuery();
        
            return ret;
        }

    }

The format is <Data Source name>_ds.executeQuery().

It is a little hard to explain, but when I need to access the Data Source itself, and not just the currently selected record, I need to add on ‘_ds’ to the name of my Data Source in order to access the object that contains the Data Source methods. Behind the scenes, this is always how the system defines the name of the Data Source object.

The system will now refresh the grid whenever the combo box value is changed. Lastly though, we need the data to be filtered by the value we select.

Override ExecuteQuery Method In D365 Form

Overriding the ExecuteQuery method in D365 forms, will change what data is shown on the form.

To do this, locate the Data Source in the form designer. In this example, my data source is named ‘rsmVehicle’.

Expand the Data Source node, and select the Methods node. Right click on ‘Methods’ and select Override>executeQuery.

The system will generate x++ code for the executeQuery method and open a new tab.

Filtering By The Value In The Combo Box

In the ExecuteQuery method, we need to add code to cause the system to filter the data to only return records that match the value of the Combo Box.

Earlier in this article we set the ‘Auto Declaration’ property on the Combo Box to be ‘Yes’. This allows us to reference it now. Add code to make the method look like this:

[DataSource]
    class rsmVehicle
    {
        /// <summary>
        ///
        /// </summary>
        public void executeQuery()
        {
            QueryBuildDataSource  queryBuildDataSource;
 
            queryBuildDataSource = this.query().dataSourceTable(tablenum(rsmVehicle));
            queryBuildDataSource.clearRanges();
            queryBuildDataSource.addRange(fieldnum(rsmVehicle, VehicleType)).value(queryValue(VehicleTypeComboBox.selection()));

            super();
        }

    }

I will explain the code. First, declare a variable of type QueryBuildDataSource, and named ‘queryBuildDataSource’.

QueryBuildDataSource queryBuildDataSource;

Next, when you add a Data Source to a form, behind the scenes there is sql code to retrieve the data from the database. There can be multiple data sources on a form, which would increase the length of the sql code used. In our case, we need to modify just the Data Source and sql code that has to do with the table we want to filter. So this line of code retrieves just the Data Source we are interesting in.

queryBuildDataSource = this.query().dataSourceTable(tablenum(rsmVehicle));

Next, there might already be a filter on the Data Source. We want to remove any filters before adding back our own. This essentially removes any ‘where’ clauses from the sql code.

queryBuildDataSource.clearRanges();

Finally, we need to add back the filter we want. This essentially adds a ‘where’ clause to the underlying sql code. In our example, we tell the system to filter the rsmVehicle table, and only retrieve records where the field ‘VehicleType’ is equal to the value that is selected in the Combo Box named ‘VehicleTypeComboBox’.

queryBuildDataSource.addRange(fieldnum(rsmVehicle, VehicleType)).value(queryValue(VehicleTypeComboBox.selection()));

The code ‘addRange(fieldnum(rsmVehicle, VehicleType))’ tells the system what field to filter on.

The code ‘.value(queryValue(BehicleTypeComboBox.selection());’ gets the value from the combobox and uses the value to filter the field.

Conclusion

In this article you learned how to override the ExecuteQuery method in D365 forms. Overriding this method can cause the form to show a subset of the data that exists in the underlying table. In our example, we used to change what data is shown in a grid, based on the value selected in a Combo Box. This allows a user to very easily filter the data in a form.

Peter Ramer

Peter Ramer is a part of the Managed Application Services team at RSM working on Microsoft Dynamics 365. He focuses on the Retail and Commerce industries. When he is not solving problems and finding ways to accelerate his clients' business, he enjoys time with his three kids and amazing wife.