May 22, 2013

Multiple Tables In Query

Axapta query on multiple tables





Hey friends try this code to add multiple tables in query.

QueryRun selectReportQuery()
{
    Query                   query           = new Query();
    QueryRun                localQueryRun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildDataSource    qbds3;
    ;
    qbds1 = query.addDataSource(tableNum(salesTable));
    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');
    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));
    qbds2.fetchMode(JoinMode::InnerJoin);
    qbds2.relations(false);
    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));
    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);
    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);
    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));
    qbds3 = qbds1.addDataSource(tableNum(salesLine));
    qbds3.relations(true);
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.fetchMode(queryFetchMode::One2Many);
qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));


Here is some more code related to query in axapta

-Harry

No comments:

Post a Comment

Thanks