Building queries with fetchmode property in AX 2012

Building queries with fetchmode property in AX 2012

Tutorial
Published on
Aug 31, 2015

Let’s assume that we want to have a query which will return the same records like following select statement:

static void custTableSelect(Args _args)

{

   CustTable   custTable;

   CustGroup   custGroup;

   DlvTerm     dlvTerm;

   #define.custGroup('Net30')

   #define.dlvTerm(LogisticsLocationRoleType::None)

   while select custTable

       join custGroup

           where custGroup.CustGroup == custTable.CustGroup

              && custGroup.PaymTermId == #custGroup

       join dlvTerm

           where dlvTerm.Code == custTable.DlvTerm

              && dlvTerm.TaxLocationRole == #dlvTerm

   {

       info(custTable.AccountNum);

   }

}

We can use standard QueryBuildDataSource classes:

static void custTableQuery(Args _args)

{

   CustTable               custTable;

   QueryBuildDataSource    dsCustTable, dsCustGroup, dsDlvTerm;

   Query                   query = new Query();

   QueryRun                queryRun;

   #define.custGroup('Net30')

   #define.dlvTerm(LogisticsLocationRoleType::None)

   dsCustTable = query.addDataSource(tableNum(CustTable));

   dsCustGroup = dsCustTable.addDataSource(tableNum(CustGroup));

   dsCustGroup.addLink(fieldNum(CustTable, CustGroup), fieldNum(CustGroup, CustGroup));

   dsCustGroup.addRange(fieldNum(CustGroup, PaymTermId)).value(#custGroup);

   dsDlvTerm = dsCustTable.addDataSource(tableNum(dlvTerm));

   dsDlvTerm.addLink(fieldNum(CustTable, DlvTerm), fieldNum(DlvTerm, Code));

   dsDlvTerm.addRange(fieldNum(dlvTerm, TaxLocationRole)).value(queryValue(#dlvTerm));

   queryRun = new queryRun(query);

   while (queryRun.next())

   {

      custTable =  queryRun.get(tableNum(custTable));

      info(custTable.AccountNum);

   }

}

The problem is that above code will not work properly. The built queries will be separated, not joined. To make it work as expected we have to use FetchMode property for QueryBuildDataSource. By default QueryBuildDataSource has property FetchMode == QueryFetchMode::One2Many. We need to set this property to FetchMode == QueryFetchMode::One2One. See below:

static void custTableQuery(Args _args)

{

   dsCustGroup = dsCustTable.addDataSource(tableNum(CustGroup));

   dsCustGroup.addLink(fieldNum(CustTable, CustGroup), fieldNum(CustGroup, CustGroup));

   dsCustGroup.addRange(fieldNum(CustGroup, PaymTermId)).value(#custGroup);

   dsCustGroup.fetchMode(QueryFetchMode::One2One);

   dsDlvTerm = dsCustTable.addDataSource(tableNum(dlvTerm));

   dsDlvTerm.addLink(fieldNum(CustTable, DlvTerm), fieldNum(DlvTerm, Code));

   dsDlvTerm.addRange(fieldNum(dlvTerm, TaxLocationRole)).value(queryValue(#dlvTerm));

   dsCustGroup.fetchMode(QueryFetchMode::One2One);

}

Share it
Written by
Wlodzimierz Wardega