Monday 4 June 2018

Get Customer Details using Dynamic Query in Ax 2012

static void CustTableListPageDynamicQuery(Args _args)
{
    CustTable                   custTable;
    DirPartyTable               dirPartyTable;
    LogisticsPostalAddress      postalAddress;
    LogisticsElectronicAddress  electronicAddress;

    Query                       query;
    QueryRun                    queryRun;
    QueryBuildDataSource        qbds1,qbds2,qbds3,qbds4, qbds5;
    QueryBuildRange             range;

    query   =   new Query();
    qbds1   =   query.addDataSource(tableNum(CustTable));
    range   =   qbds1.addRange(fieldNum(custTable, AccountNum));
    range.value("1122");

    qbds2   =   qbds1.addDataSource(tableNum(dirPartyTable));
    qbds2.addSortField(fieldNum(dirPartyTable, Name), SortOrder::Ascending);
    qbds2.fetchMode(1);
    qbds2.joinMode(JoinMode::InnerJoin);

    qbds2.relations(true);

    qbds3   =   qbds2.addDataSource(tableNum(LogisticsPostalAddress));
    qbds3.joinMode(JoinMode::OuterJoin);
    qbds3.addLink(fieldNum(DirPartyTable, PrimaryAddressLocation), fieldNum(LogisticsPostalAddress , Location));

    qbds4   =   qbds2.addDataSource(tableNum(LogisticsElectronicAddress));
    qbds4.joinMode(JoinMode::OuterJoin);
    qbds4.addLink(fieldNum(DirPartyTable,PrimaryContactPhone), fieldNum(LogisticsElectronicAddress , RecId));

    qbds5   =   qbds2.addDataSource(tableNum(LogisticsElectronicAddress));
    qbds5.joinMode(JoinMode::OuterJoin);
    qbds5.addLink(fieldNum(DirPartyTable,PrimaryContactEmail), fieldNum(LogisticsElectronicAddress , RecId));

    queryRun    =   new QueryRun(query);

    while(queryRun.next())
    {
        custTable   =   queryRun.get(tableNum(CustTable));
        dirPartyTable   =  queryRun.get(tableNum(DirPartyTable));
        postalAddress   =   queryRun.get(tableNum(LogisticsPostalAddress));
        electronicAddress   =   queryRun.get(tableNum(LogisticsElectronicAddress));
        info(strFmt("%1, %2, %3",custTable.AccountNum, dirPartyTable.Name, postalAddress.Address));
    }
}

No comments:

Post a Comment