Filter dynamic query data using Outerjoins in Ax / d365 fo using X++

 Sometimes we need to filter data in inquiry forms based on a particular value selected by the user.

It is very easy to filter the data by writing dynamic queries and applying ranges over the query and retrieve the required data. But if we use outer joins in our dynamic query the query range is not worked. Instead of query range we can simply use QueryFilter to get the filtered data.

As we know The major difference in between the query range and query  queryfilter will be explained by the following scenario. Query range will apply where condition just before the joins so we will get the data with joining of the result set to the other table which happened to fail the filtration in dynamic query with outer join.

QueryFilter is able to apply the where condition after the successful joining of the tables. So the range is applied to the resultset after which tends to get us the required data.

here is the sample code to filter data with outer joins in inquiry form.

filter a form with multiselection control.

1. In form Init method

public void init()

{

   

           TstCountry.visible(ProductLicenseParameters::find().TestEnableCountryFilter ? true : false);

         msCtrlCountry = SysLookupMultiSelectCtrl::construct(element, TSTCountry,                             queryStr(ProductLicenseCountryFilter));  //-------------------// staticquery

    }

2.  In Datasource Init

public void init()

{

    QueryBuildDataSource  productLicenseDs, licenseCustomerDS, custTableDs;

    super();

           productLicenseDs    = this.query().dataSourceTable(tableNum(ProductsLicenseTable));

        licenseCustomerDS   = productLicenseDs.addDataSource(tableNum(ProductLicenseCustomer));

        licenseCustomerDS.addLink(fieldNum(ProductsLicenseTable, LicenseNumber), fieldNum(ProductLicenseCustomer, LicenseNumber));

        licenseCustomerDS.joinMode(JoinMode::OuterJoin);

        custTableDs = licenseCustomerDS.addDataSource(tableNum(CustTable));

        custTableDs.addLink(fieldNum(ProductLicenseCustomer, CustAccount), fieldNum(CustTable, AccountNum));

        custTableDs.joinMode(JoinMode::OuterJoin);

        qf = this.query().addQueryFilter(custTableDs , "PartyCountry");

    }

    3. In Executequery