Find Customer Name and Address through Select statement Or Dynamic Query in AX 2012 using X++

 Hi guys ,Today we see how to find customer address and customer name using Select statement or Dynamic query in Ax 2012 using X++.

Select statement 1:

  LogisticsLocationRole   locationRole;

 DirPartyLocation        partyLocation;

DirPartyLocationRole    partyLocationRole;

LogisticsLocation       location;

LogisticsPostalAddress  postalAddress;

        select firstonly postalAddress

        exists join location

            where location.RecId == postalAddress.Location

        exists join locationRole

            where locationRole.Type  == LogisticsLocationRoleType::Invoice

        exists join partyLocation

            where

                partyLocation.Location == location.RecId &&

                partyLocation.Party == CustTable::find("DE-001").Party &&

                partyLocation.IsPrimary == NoYes::Yes

        exists join partyLocationRole

            where partyLocationRole.PartyLocation == partyLocation.RecId &&

                partyLocationRole.LocationRole == locationRole.RecId;

        info(strfmt("%1",postalAddress .address));

Select statement 2:

    CustTable                  custTable;

     DirPartyTable              dirPartyTable;

     LogisticsPostalAddress     logisticsPostalAddress;

     LogisticsLocation          logisticsLocation;

     LogisticsElectronicAddress logisticsElectronicAddress;

    select AccountNum from custTable

    where custTable.AccountNum =='US-004'

    join dirPartyTable

    where dirPartyTable.RecId == custTable.Party

    join logisticsPostalAddress

    where logisticsPostalAddress.Location==dirPartyTable.PrimaryAddressLocation

    join logisticsElectronicAddress

    where logisticsElectronicAddress.RecId== dirPartyTable.PrimaryContactEmail;

    info(strFmt('%1,%2,%3,%4',custTable.AccountNum,dirPartyTable.Name,logisticsElectronicAddress.Locator,logisticsPostalAddress.Address));

Using Dynamic Query:

Query query = new Query();

    QueryBuildDataSource qbds,qbds1,qbds2,qbds3,qbds4;

    QueryRun qrn;

    QueryBuildRange qbr;

    CustTable                     custTable;

    DirPartyTable                 dirPartyTable;

    LogisticsPostalAddress         logisticsPostalAddress;

    LogisticsElectronicAddress    logisticsElectronicAddress;

    DAXCustomerTable              customerTable;

    delete_from DAXCustomerTable;

    qbds = query.addDataSource(tableNum(CustTable));

    qbr = qbds.addRange(fieldNum(CustTable,AccountNum));

    qbr.value(CustomerAccount.valueStr());

    qbds1 = qbds.addDataSource(tableNum(DirPartyTable));

    qbds1.relations(false);

    qbds1.addLink(fieldNum(CustTable,party),fieldNum(DirPartyTable,RecId));

    qbds2 = qbds1.addDataSource(tableNum(logisticsPostalAddress));

    qbds2.relations(false);

    qbds2.addLink(fieldNum(logisticsPostalAddress,Location),fieldNum(DirPartyTable,PrimaryAddressLocation));

    qbds3 = qbds1.addDataSource(tableNum(logisticsElectronicAddress));

    qbds3.relations(false);

    qbds3.addLink(fieldNum(logisticsElectronicAddress,RecId),fieldNum(DirPartyTable,PrimaryContactEmail));

    qrn = new QueryRun(query);

    while (qrn.next())

    {

        custTable =qrn.get(tableNum(custTable));

        dirPartyTable=qrn.get(tableNum(DirPartyTable));

        logisticsPostalAddress=qrn.get(tableNum(LogisticsPostalAddress));

        logisticsElectronicAddress=qrn.get(tableNum(LogisticsElectronicAddress));

        DAXCustomerTable.CustomerAccountNum =custTable.AccountNum;

        DAXCustomerTable.CustomerName =dirPartyTable.Name;

        DAXCustomerTable.CustomerAddress=logisticsPostalAddress.Address;

        DAXCustomerTable.CustomerEmail=logisticsElectronicAddress.Locator;

        DAXCustomerTable.insert();

    }

In the above dynamic query i insert the data in my Custom table.

Keep Daxing!!