Tuesday, 12 March 2019

Dynamics Query with Joins, converting normal While select statement

static void GetEmailShipmentCheckCust(Args _args)
{

    Query                       qr;
    QueryBuildDataSource        qbdscusttable, qbdscontactperson, qbdsdirpartytable, qbdsLEA;
    QueryBuildRange             qRange;
    QueryBuildLink              qlink;
    QueryRun                    qrun;

    CustTable                   _custtable;
    Contactperson               _contactperson;
    DirPartyTable               _dirpartytable;
    LogisticsElectronicAddress  _LogisticsElectronicAddress;
    
    //Converting this select and while select statement in Dynamic Query Result is same
    /*
    select party from _custtable
            where _custtable.AccountNum == "501-C02032";
                  while select party from _contactperson
                            where _contactperson.ContactForParty == _custtable.Party
                            join PrimaryContactEmail from _dirpartytable
                            where _dirpartytable.RecId == _contactperson.Party
                            join Locator from _LogisticsElectronicAddress
                            where _LogisticsElectronicAddress.RecId == _dirpartytable.PrimaryContactEmail
                        && _LogisticsElectronicAddress.ECSSendElectronicShipment == NoYes::Yes
    {
                info(strFmt("%1", _LogisticsElectronicAddress.Locator));
    }
    */
    
    //select AccountNum from _custtable where _custtable.AccountNum == "501-C02032";
    qr = new Query();
    //add a data source to the query
    qbdscusttable = qr.addDataSource(tableNum(CustTable));
    //add range to data Source
    qbdscusttable.addRange(fieldNum(CustTable, AccountNum)).value("501-C02032");
    //set the Value of the Range
    //qRange.value;

    qbdscontactperson = qbdscusttable.adddatasource(tablenum(ContactPerson));
    qbdscontactperson.joinMode(JoinMode::InnerJoin);
    qlink = qbdscontactperson.addlink(fieldnum(ContactPerson, ContactForParty), fieldnum(CustTable, party));

    qbdsdirpartytable = qbdscontactperson.adddatasource(tablenum(DirPartyTable));
    qbdsdirpartytable.joinMode(JoinMode::InnerJoin);
    qlink = qbdsdirpartytable.addlink(fieldnum(DirPartyTable, RecId), fieldnum(contactperson, party));

    qbdsLEA = qbdsdirpartytable.adddatasource(tablenum(LogisticsElectronicAddress));
    qbdsLEA.joinMode(JoinMode::InnerJoin);
    qlink = qbdsLEA.addlink(fieldnum(LogisticsElectronicAddress, RecId), fieldnum(DirPartyTable, primarycontactemail));
    qbdsLEA.addRange(fieldNum(LogisticsElectronicAddress, Ecssendelectronicshipment)).value(queryValue(noyes::Yes));

    //add sorting
    ////qbdsrc.addSortField(fieldNum(VendTable,AccountNum));

    //Add Second Data Source and then link the datasource on field to create join
    //qbdsrc2 = qr.adddatasource(tablenum(vendtrans));
    //qlink = qbdsrc2.addlink(fieldnum(vendtable, Accountnum), fieldnum(vendtrans, Accountnum));

    qrun = new QueryRun(qr);

    //verify the Query Results
    while(qrun.next())
    {
    _LogisticsElectronicAddress = qrun.get(tableNum(LogisticsElectronicAddress));
        info(strFmt("%1", _LogisticsElectronicAddress.Locator));
    }
}

No comments:

Post a Comment