D365 FO - X++ Join multiples tables with same table using query build data source

Join multiples tables with same table using query build data source

Today, I will be discussing about one of the common issue related to query creation using  query build data source. 

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 2 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 qbds = query.addDataSource(tableNum(Parent));

    // Adding child 1 as a datasource

    hcmEmploymentds= qbds.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 = qbds.addDataSource(tableNum(Child2));

   qbds.joinMode(JoinMode::InnerJoin);

   qbds.addLink(fieldNum(Parent, LeaveDetail), fieldNum(Child1, RecId));

   qbds.fetchmode(QueryFetchMode::One2One);

       

    return new QueryRun(query);

} 

Few of the common methods related to query and dates.

Date  related methods 

 -  Year(date  _date) : Method will be used to get year from specified date.

-   mkdate(int _day, int _month, int _year) : Method will be used to make and return a new date

-   mthOfYr(date _date) : Method will be used to get month from the the specified date parameter.

-   dayOfMth(date _date) : Method will be used to get day of the month from the specified date parameter.

Query  related methods 

QueryBuildRange         startAndEndDateRange ;

startAndEndDateRange = this.query().dataSourceTable(tableNum(EmplSickLeaveEncashment)).addRange(FieldNum(EmplSickLeaveEncashment, NextEncashDate));

startAndEndDateRange.value(SysQueryRangeUtil::dateRange(startDate,toDate));

startAndEndDateRange.status(RangeStatus::Hidden);

Here for adding date range we can using dateRange method from SysQueryRangeUtil class.

In order to hide range we can set its status to Hidden by using method of status.

Ref

Ref2