preloader-matrix

Alfasith

Relation between HcmPositionDetail, HcmPositionWorkerAssignment, HcmWorker for query class in AX 2012 / D365

Hi,

Query                   query = new Query();
QueryRun                queryRun;
QueryBuildDataSource    qbdsHcmWorker, qbdsHcmPosition, qbdsHcmPositionDetail, qbdsHcmPositionWorkerAssign;
QueryBuildRange         qbrWorker,qbrdDept;
HcmPositionDetail       hcmPositionDetail;
HcmWorker               hcmWorker;
;

qbdsHcmWorker = query.addDataSource(tableNum(HcmWorker));

qbdsHcmWorker.addRange(fieldnum(HcmWorker,
PersonnelNumber)).value(‘1020’);

qbdsHcmPositionWorkerAssign = qbdsHcmWorker.addDataSource(tableNum
(HcmPositionWorkerAssignment));

qbdsHcmPositionWorkerAssign.relations(false);

qbdsHcmPositionWorkerAssign.joinMode(JoinMode::InnerJoin);

qbdsHcmPositionWorkerAssign.addLink(fieldnum
(HcmPositionWorkerAssignment,
Worker)
,fieldnum(HcmWorker,Worker));

//qbdsHcmPositionWorkerAssign.addRange(fieldnum
(HcmPositionWorkerAssignment,
ValidTo)).value(SysQuery::range(today(), dateMax()));

qbdsHcmPositionDetail = qbdsHcmPositionWorkerAssign.addDataSource
(tableNum(HcmPositionDetail));

qbdsHcmPositionDetail.relations(false);

qbdsHcmPositionDetail.joinMode(JoinMode::InnerJoin);
qbdsHcmPositionDetail.addLink(fieldnum
(HcmPositionWorkerAssignment,Position),
fieldnum(HcmPositionDetail,Position));
qbdsHcmPositionDetail.addRange(fieldnum
(HcmPositionDetail,ValidTo))
.value(date2str(dateMax(), 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4));

queryRun    = new queryrun(query);

while(queryrun.next())

{

hcmWorker        = queryRun.get(tableNum(
HcmWorker)) as HcmWorker;

hcmPositionDetail           = queryRun.get(tableNum(HcmPositionDetail)) as HcmPositionDetail;

info(strFmt(“%1-%2”,hcmWorker.PersonnelNumber, OMOperatingUnit::findbyRecid
(hcmPositionDetail.Department).Name));

}
**************************************

display Description getWorkerPosition()
{
HcmWorker                                    hcmWorker;
HcmPositionWorkerAssignment   workerAssignment;
HcmPosition                                  hcmPosition;
HcmPositionDetail                        hcmPositionDetail;

select worker, position from workerAssignment
where workerAssignment.Worker == this.RecId
join recid from hcmPosition
where hcmPosition.RecId == workerAssignment.Position
join position, description from hcmPositionDetail
where hcmPositionDetail.Position == hcmPosition.RecId;

return hcmPositionDetail.Description;
}

Regards

Leave a Reply

Your email address will not be published.