Building queries with fetchmode property in AX 2012

Continue reading

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);

}

More articles
Explore our blog

What can we do for you?

We'd love to hear about your project. Our team will get back to you within two working days.

Thank you for inquiry, we’ve passed it to our sales department, our representative will reach you back in his earliest convenience.
Oops! Something went wrong while submitting the form.