Dynamic query in D365FO.
In dynamics we have 2 types of queries.
- Static query
- Dynamic query
Static queries we can create in AOT and it's less code. But If we apply the range for that query If we use that query in any where It will apply same range.
Dynamic queries we are creating in run time and apply the ranges the ranges in run time. Below are the query classes for dynamic query.
- Query
- QueryBuildDataSource
- QueryBuildRange
- QueryRun
- QueryBuildFieldList
- QueryFilter
- QueryBuildDynaLink
- QueryBuildLink
Query:
Query query = new Query();
query.addDataSource(tableNum(MyTable));
-------------------------------------------------------------------------------------
QueryBuildDataSource:
Way 1:
Query query = new Query();
QueryBuildDataSource CustGroupQbds,custTableQbds;
custTableQbds = query.addDataSource(tableNum(CustTable));
CustGroupQbds = custTableQbds.addDataSource(tableNum(CustGroup));
CustGroupQbds.relations(true);
CustGroupQbds.joinMode(JoinMode::ExistsJoin);
// OR we can usw below one also
CustGroupQbds.addDynalink(fieldNum(CustTable, CustGroup), fieldNum(CustGroup, CustGroup));
CustGroupQbds.addDynalink('Parent field id','Child field id');
Way 2:
SysQuery::findOrCreateDataSource('Query', tableNum('Table'));
QueryBuildDataSource batchbQbds = SysQuery::findOrCreateDataSource('Query',
tableNum('Table'),//New table Id
tableNum('parent table'));//existing parent table Id
batchbQbds.relations(true);
batchbQbds.joinMode(JoinMode::ExistsJoin);
Example:
Query query = new Query();
QueryBuildDataSource batchbQbds;
query.addDataSource(tableNum(CustTable));
batchbQbds = SysQuery::findOrCreateDataSource(query,
tableNum(CustGroup),//New table Id
tableNum(CustTable));//existing parent table Id
batchbQbds.relations(true);
batchbQbds.joinMode(JoinMode::ExistsJoin);
------------------------------------------------------------------------------------------------------------------
QueryBuildRange:
Way 1:
Query query = new Query();
QueryBuildDataSource custTableQbds;
QueryBuildRange qbr;
custTableQbds = query.addDataSource(tableNum(CustTable));
custTableQbds.addRange(FieldNum(CustTable, AccountNum));
qbr.value("Value");
query.addDataSource(tableNum(CustTable)).addRange(FieldNum(CustTable,
AccountNum)).value("Value");
qr.status(RangeStatus::Locked); // To lock
Way 2:
SysQuery::findOrCreateRange('this.queryBuildDataSource()',
fieldNum(Mytable, Field)).value('Value');
Example:
Query query = new Query();
query.addDataSource(tableNum(CustTable));
SysQuery::findOrCreateRange(query.dataSourceNo(1),
fieldNum(CustTable, CustGroup)).value('1001');
Clear ranges:
query.addDataSource(tableNum(CustTable)).clearRange(FieldNum(CustTable, AccountNum));
query.addDataSource(tableNum(CustTable)).clearRanges();
---------------------------------------------------------------------------------------
QueryRun:
QueryRun qr = new QueryRun ("query");
CustTable custTable;
while (qr.next())
{
custTable = qr.GetNo(1);
}
qr.prompt() // Open the window in UI.
-----------------------------------------------------------------------------------------------------------------------
QueryBuildFieldList:
QueryBuildFieldList qbdList = qbds.fields();
qbdList.addField(fieldNum(CustTable, CreditMax), SelectionField::Sum);
qbdList.addField(fieldnum(CustTable, RecId), SelectionField::Count);
-----------------------------------------------------------------------------------------------------------------------
QueryFilter:
Query query = new Query();
QueryBuildDataSource custTableQbds;
QueryFilter accountNumFilter;
custTableQbds = query.addDataSource(tablenum(custTable));
accountNumFilter = query.addQueryFilter(custTableQbds, 'AccountNum');
accountNumFilter.value('Value');
-----------------------------------------------------------------------------------------------------------------------
QueryBuildLink:
Query query = new Query();
QueryBuildLink qbdLink;
QueryBuildDataSource custTableQbds, CustGroupQbds;
custTableQbds = query.addDataSource(tablenum(custTable));
CustGroupQbds = custTableQbds.addDataSource(tableNum(CustGroup));
qbdLink = CustGroupQbds.addLink(fieldNum(CustTable, CustGroup),
fieldNum(CustGroup, CustGroup));
-----------------------------or---------------------------
CustGroupQbds.range(true);
CustGroupQbds.joinMode(JoinMode::InnerJoin);
-----------------------------or---------------------------
query.dataSourceTable(tableNum(CustGroup)).addDynalink(
fieldNum(CustGroup, CustGroup), CustTable,//Parent table
fieldNum(CustTable, CustGroup));//Parent table field
--------------------------------------------------------------------------------------
Query add sort field:
qbds.addSortField(fieldNum(custTable, AccountNum), SortOrder::Descending);
-------------------------------------------------------------------------------------
Query add group by:
Query q = new Query();
QueryBuildDataSource qbds;
QueryRun qr;
QueryBuildFieldList qbfl;
CustSalesOpenLines custSalesOpenLines;
qbds = q.addDataSource(tableNum(CustSalesOpenLines));
qbds.firstOnly(true);
qbfl = qbds.fields();
qbfl.addField(fieldNum(CustSalesOpenLines, SalesId));
qbfl.addField(fieldNum(CustSalesOpenLines, LineAmount), SelectionField::Sum);
qbds.addGroupByField(fieldNum(CustSalesOpenLines, SalesId));
q.addHavingFilter(qbds, fieldStr(CustSalesOpenLines, LineAmount), AggregateFunction::Sum).value('>0');
Multiple Ranges:
QueryBuildDataSource ledgerJournalTrans_DS = _contract.getQuery().dataSourceTable(tableNum(LedgerJournalTrans));
QueryBuildRange qbr = SysQuery::findOrCreateRange(ledgerJournalTrans_DS, fieldNum(LedgerJournalTrans, DataAreaId));
qbr.value(strFmt("(%1.%2 == %3) || (%1.%4 == %3)",
ledgerJournalTrans_DS.name(),
fieldStr(LedgerJournalTrans, AccountType),
enum2int(LedgerJournalACType::Bank),
fieldStr(LedgerJournalTrans, OffsetAccountType)));
qbr.status(RangeStatus::Hidden);
queryRun = new queryRun(_contract.getQuery());
Keep daxing!!