Sum, Avg, Count, Max, Min functionalities in Query class in AX 2012 / D365

Hi,

Regards,

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

Sum() in Query class in AX 2012 / D365

Hi,

To perform sum() function in Query Class.

qbdsOverTimeTrans.addSelectionField( fieldnum( DAPOvertimeTransactions, Amount), SelectionField::Sum);

Regards,

QueryBuildRange class to filter with never date in AX 2012 or D365

Hi,

qbdsHcmPositionDetail.addRange(fieldnum(HcmPositionDetail,ValidTo)).value(date2str(dateMax(), 321,  DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4));
This is used for Valid Time State Tables particularly.

Regards,

Greater than or euqal to / Less than or equal to (<= or >=) in Query class in AX 2012 or D365

Hi,

Greater than or equal to:

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range(today(), dateMax()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(today(), dateMax()));

Less than or equal to:

 queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range( dateNull(), today()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(dateNull(), today())); 

Regards,