QueryBuildDataSource Syntax

 

Let's suppose we have following tables.

- Leave Assignment table - (Base Table) - Namely Parent for demo purpose

- HcmEmployment table - Namely Child 1 for demo purpose

- Leave Plan table - Namely Child 1 for demo purpose

We want to link base table with both hcm employment (child1) and leave plan table (child2)  using query build data source. Below is the code used for performing this operation.

public QueryRun fetchMultipleTableQueryDemo()
{
QueryBuildDataSource hcmEmploymentds;
QueryBuildDataSource qbds;
Query query = new Query();

// Adding parent as a datasource
QueryBuildDataSource qbs = query.addDataSource(tableNum(Parent));


// Adding child 1 as a datasource
hcmEmploymentds= qbs.addDataSource(tableNum(Child1));
hcmEmploymentds.joinMode(JoinMode::InnerJoin);
hcmEmploymentds.addLink(fieldNum(Parent, Worker),fieldNum(Child1, Worker));
hcmEmploymentds.fetchMode(QueryFetchMode::One2One);


// Adding child 2 as a datasource
qbds = qbs.addDataSource(tableNum(Child2));
qbds.joinMode(JoinMode::InnerJoin);
qbds.addLink(fieldNum(Parent, LeaveDetail), fieldNum(Child1, RecId));
qbds.fetchmode(QueryFetchMode::One2One);

return new QueryRun(query);
}

QueryRange Expression syntax for DateTime Util:

queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',

 query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1

 fieldStr(InventTable, ItemType), // ItemType %2

 any2int(ItemType::Service), // %3

 any2int(ItemType::Item), // %4

 fieldStr(InventTable, ItemId), // ItemId %5

 fieldStr(InventItemBarCode, ItemId))); // %6

DateTimeUtil::tosTr  works for datetimeutil expression.

HEY HEY HEY!!! HACK OF THE DAY!!!

if we want to access AOT query or QueryBuildDataSource Table then we can use:

custInvoiceJourDatasource = salesInvoiceHeaderQuery.dataSourceTable(tableNum(CustInvoiceJour));