Building queries with fetchmode property in AX 2012
Continue readingLet'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);
}